AMORTIZE

[tboulden]

A simple amortization schedule generator that leverages SCAN and MAKEARRAY. Using CHOOSE and MAKEARRAY, we can create an array of LAMBDAs to index sequential manipulations, then SCAN from starting value across the array of LAMBDAs. Once we have our basic amortization, we break out into a table to capture the details: beginning balance, interest, principal, ending balance. Finally, use MAKEARRAY to add periods column.

The big thing here is using CHOOSE to create an array of LAMBDAs as it can be used with MAP, REDUCE, SCAN in different ways. For example, can create an array of LAMBDAs that are commonly applied sequentially, and compose them together, i.e. define COMPOSE=LAMBDA(f,g,LAMBDA(x,g(f(x)))) then REDUCE(,CHOOSE({1,2,3},f,g,h),COMPOSE) is equivalent to LAMBDA(x,h(g(f(x)))).

=LAMBDA(balance,int_rate_pct,periods,
    LET(
        bal,NUMBERVALUE(balance),
        rate_,int_rate_pct/12,
        pmt_,PMT(rate_,periods,-balance),
        select_array,
            MAKEARRAY(periods,3,LAMBDA(i,j,j)),
        fxns,
            CHOOSE(select_array,
                LAMBDA(x,x),
                LAMBDA(x,x*(1+rate_)),
                LAMBDA(x,x-pmt_)
            ),
        amort,SCAN(bal,fxns,LAMBDA(prior,fn,fn(prior))),
        table,MMULT(amort,TRANSPOSE({1,0,0;-1,1,0;-1,0,1;0,0,1})),
        MAKEARRAY(periods,5,
            LAMBDA(i,j,IF(j=1,i,INDEX(table,i,j-1)))
        )
    )
)

LAMBDA_COMPOSE.xlsx
	A	B	C	D	E	F	G	H
1				Period	BegBal	Int	Prin	EndBal
2	Balance	100,000		1	100,000.00	416.67	-8,144.08	91,855.92
3	Rate	5%		2	91,855.92	382.73	-8,178.02	83,677.90
4	Periods	12		3	83,677.90	348.66	-8,212.09	75,465.81
5				4	75,465.81	314.44	-8,246.31	67,219.51
6				5	67,219.51	280.08	-8,280.67	58,938.84
7				6	58,938.84	245.58	-8,315.17	50,623.67
8				7	50,623.67	210.93	-8,349.82	42,273.85
9				8	42,273.85	176.14	-8,384.61	33,889.25
10				9	33,889.25	141.21	-8,419.54	25,469.70
11				10	25,469.70	106.12	-8,454.62	17,015.08
12				11	17,015.08	70.90	-8,489.85	8,525.23
13				12	8,525.23	35.52	-8,525.23	0.00
SCAN

Cell Formulas
Range	Formula
D2:H13	D2	=AMORTIZE(B2,B3,B4)
Dynamic array formulas.

 

[tboulden]

Updated to incorporate ACCUMULATE.

=LAMBDA(balance,int_rate_pct,periods,
      LET(
          bal,NUMBERVALUE(balance),
          rate_,int_rate_pct/12,
          pmt_,PMT(rate_,periods,-balance),
          fxns,
              CHOOSE({1,2,3},
                  LAMBDA(x,x),
                  LAMBDA(x,x*(1+rate_)),
                  LAMBDA(x,x-pmt_)
              ),
          amort,ACCUMULATE(bal,fxns,periods),
          table,MMULT(amort,TRANSPOSE({1,0,0;-1,1,0;-1,0,1;0,0,1})),
          MAKEARRAY(periods,5,
              LAMBDA(i,j,IF(j=1,i,INDEX(table,i,j-1)))
          )
      )
)