AMORTIZE

=AMORTIZE(b,r,p)

b
starting balance
r
annual interest rate
p
nr. of periods

builds amortization table NEW!! SCAN

Xlambda

Active Member
AMORTIZE builds amortization table. NEW!! SCAN.
Thanks to tboulden for bring this function up. My accounting knowledge is less than none. Had to watch YT to understand how it works.
Don't know if round(x,2) is ok or roundup should be used.
Excel Formula:
=LAMBDA(b,r,p,LET(x,r/12,m,PMT(x,p,-b),
s,SCAN(b,IF(SEQUENCE(p)-1,1,0),LAMBDA(b,a,b*(1+x*a)-m*a)),
ROUND(CHOOSE({1,2,3,4},s,s*x,m-s*x,s*(1+x)-m),2)
)
)
Book1
ABCDEFGHIJ
1b,balance;r,rate;p,periods
2b,100000,r,.05,p,12b,150000,r,.06,p,24
3=AMORTIZE(100000,0.05,12)=AMORTIZE(100000,0.06,24)
4BalanceInterestPrincipalEnd bal.BalanceInterestPrincipalEnd bal.
5100000416.678144.0891855.921000005003932.0696067.94
691855.92382.738178.0283677.996067.94480.343951.7292116.22
783677.9348.668212.0975465.8192116.22460.583971.4888144.74
875465.81314.448246.3167219.5188144.74440.723991.3484153.4
967219.51280.088280.6758938.8484153.4420.774011.2980142.11
1058938.84245.588315.1750623.6780142.11400.714031.3576110.76
1150623.67210.938349.8242273.8576110.76380.554051.5172059.25
1242273.85176.148384.6133889.2572059.25360.34071.7667987.48
1333889.25141.218419.5425469.767987.48339.944092.1263895.36
1425469.7106.128454.6217015.0863895.36319.484112.5859782.78
1517015.0870.98489.858525.2359782.78298.914133.1555649.63
168525.2335.528525.23055649.63278.254153.8151495.82
1751495.82257.484174.5847321.23
1847321.23236.614195.4543125.78
1943125.78215.634216.4338909.35
2038909.35194.554237.5134671.83
2134671.83173.364258.730413.13
2230413.13152.07428026133.14
2326133.14130.674301.421831.74
2421831.74109.164322.917508.84
2517508.8487.544344.5213164.32
2613164.3265.824366.248798.08
278798.0843.994388.074410.01
284410.0122.054410.010
29
Sheet1
Cell Formulas
RangeFormula
A3,F3A3=FORMULATEXT(A5)
A5:D16A5=AMORTIZE(100000,0.05,12)
F5:I28F5=AMORTIZE(100000,0.06,24)
Dynamic array formulas.

Xlambda

Active Member
For fun, this is how the function look only with SCAN and MAKEARRAY , no CHOOSE:
Excel Formula:
=LAMBDA(b,r,p,
LET(x,r/12,m,PMT(x,p,-b),s,
SCAN(b,IF(SEQUENCE(p)-1,1,0),LAMBDA(b,a,b*(1+x*a)-m*a)),
ROUND(MAKEARRAY(p,4,LAMBDA(r,c,LET(a,INDEX(s,r,c^0),SWITCH(c,1,a,2,a*x,3,m-a*x,4,a*(1+x)-m)))),2)
)
)
amortize.xlsx
ABCDEFGHIJ
1b,balance;r,rate;p,periods
2b,100000,r,.05,p,12b,150000,r,.06,p,24
3=AMORTIZE2(100000,0.05,12)=AMORTIZE2(150000,0.06,24)
4BalanceInterestPrincipalEnd bal.BalanceInterestPrincipalEnd bal.
5100000416.678144.0891855.921500007505898.09144101.9
691855.92382.738178.0283677.9144101.9720.515927.58138174.3
783677.9348.668212.0975465.81138174.3690.875957.22132217.1
875465.81314.448246.3167219.51132217.1661.095987.01126230.1
967219.51280.088280.6758938.84126230.1631.156016.94120213.2
1058938.84245.588315.1750623.67120213.2601.076047.03114166.1
1150623.67210.938349.8242273.85114166.1570.836077.26108088.9
1242273.85176.148384.6133889.25108088.9540.446107.65101981.2
1333889.25141.218419.5425469.7101981.2509.916138.1995843.04
1425469.7106.128454.6217015.0895843.04479.226168.8889674.16
1517015.0870.98489.858525.2389674.16448.376199.7283474.44
168525.2335.528525.23083474.44417.376230.7277243.72
1777243.72386.226261.8770981.85
1870981.85354.916293.1864688.67
1964688.67323.446324.6558364.02
2058364.02291.826356.2752007.75
2152007.75260.046388.0545619.7
2245619.7228.16419.9939199.7
2339199.71966452.0932747.61
2432747.61163.746484.3526263.26
2526263.26131.326516.7819746.48
2619746.4898.736549.3613197.12
2713197.1265.996582.116615.02
286615.0233.086615.020
29
Sheet2
Cell Formulas
RangeFormula
A3,F3A3=FORMULATEXT(A5)
A5:D16A5=AMORTIZE2(100000,0.05,12)
F5:I28F5=AMORTIZE2(150000,0.06,24)
Dynamic array formulas.

Xlambda

Active Member
new AMORTIZEX(b,r,p,[ex]) where [ex]: expand argument.
Another cool thing in favor of using MAKEARRAY in general, and not CHOOSE, is that, if we need to create variable array width/height outcomes , it is extremely easy. To see how it works for this function, the only things we have to do is to add an extra "expand" argument,[ex], and change the columns argument of MAKEARRAY(p,4,....to MAKEARRAY(p,ex...Thats it. This is possible because SWITCH construction inside does not care what columns nrs. are triggered or not.
For safety, addapted to our case, I used MAKEARRAY(p,IF(ex,MEDIAN(1,4,ex),4),…....
amortize.xlsx
ABCDEFGHIJKLMNOPQRS
1balance100000
2rate0.05ex,0 or omittedex,3ex,2ex,1
3periods12=AMORTIZEX(B1,B2,B3,0)=AMORTIZEX(B1,B2,B3,3)=AMORTIZEX(B1,B2,B3,2)=AMORTIZEX(B1,B2,B3,1)
4100000416.678144.0891855.92100000416.678144.08100000416.67100000
591855.92382.738178.0283677.991855.92382.738178.0291855.92382.7391855.92
683677.9348.668212.0975465.8183677.9348.668212.0983677.9348.6683677.9
775465.81314.448246.3167219.5175465.81314.448246.3175465.81314.4475465.81
867219.51280.088280.6758938.8467219.51280.088280.6767219.51280.0867219.51
958938.84245.588315.1750623.6758938.84245.588315.1758938.84245.5858938.84
1050623.67210.938349.8242273.8550623.67210.938349.8250623.67210.9350623.67
1142273.85176.148384.6133889.2542273.85176.148384.6142273.85176.1442273.85
1233889.25141.218419.5425469.733889.25141.218419.5433889.25141.2133889.25
1325469.7106.128454.6217015.0825469.7106.128454.6225469.7106.1225469.7
1417015.0870.98489.858525.2317015.0870.98489.8517015.0870.917015.08
158525.2335.528525.2308525.2335.528525.238525.2335.528525.23
16
17ex out of range
18ex,-1 <=> ex=1ex,8 <=> ex=4
19=AMORTIZEX(B1,B2,B3,-1)=AMORTIZEX(B1,B2,B3,8)
20100000100000416.678144.0891855.92
2191855.9291855.92382.738178.0283677.9
2283677.983677.9348.668212.0975465.81
2375465.8175465.81314.448246.3167219.51
2467219.5167219.51280.088280.6758938.84
2558938.8458938.84245.588315.1750623.67
2650623.6750623.67210.938349.8242273.85
2742273.8542273.85176.148384.6133889.25
2833889.2533889.25141.218419.5425469.7
2925469.725469.7106.128454.6217015.08
3017015.0817015.0870.98489.858525.23
318525.238525.2335.528525.230
32
Sheet3
Cell Formulas
RangeFormula
D3,I19,D19,M3,P3,I3D3=FORMULATEXT(D4)
D4:G15D4=AMORTIZEX(B1,B2,B3,0)
I4:K15I4=AMORTIZEX(B1,B2,B3,3)
M4:N15M4=AMORTIZEX(B1,B2,B3,2)
P4:P15P4=AMORTIZEX(B1,B2,B3,1)
D20:D31D20=AMORTIZEX(B1,B2,B3,-1)
I20:L31I20=AMORTIZEX(B1,B2,B3,8)
Dynamic array formulas.

Xlambda

Active Member
AMORTIZEX(b,r,p,[ex])
Excel Formula:
=LAMBDA(b,r,p,[ex],
LET(x,r/12,m,PMT(x,p,-b),s,SCAN(b,IF(SEQUENCE(p)-1,1,0),LAMBDA(b,a,b*(1+x*a)-m*a)),
ROUND(MAKEARRAY(p,IF(ex,MEDIAN(1,4,ex),4),LAMBDA(r,c,LET(a,INDEX(s,r,c^0),SWITCH(c,1,a,2,a*x,3,m-a*x,4,a*(1+x)-m)))),2)
)
)

Xlambda

Active Member

AMORTIZE3(b,r,p) , another CHOOSE free one, this time with REDUCE embedded inside MAKEARRAY.
Excel Formula:
=LAMBDA(b,r,p,
LET(x,r/12,m,PMT(x,p,-b),
ROUND(MAKEARRAY(p,4,LAMBDA(r,c,LET(y,REDUCE(b,IF(SEQUENCE(r)-1,1,0),LAMBDA(b,a,b*(1+x*a)-m*a)),SWITCH(c,1,y,2,y*x,3,m-y*x,4,y*(1+x)-m)))),2)
)
)
amortize.xlsx
ABCDE
1b,balance;r,rate;p,periods
2b,100000,r,.05,p,12
3=AMORTIZE3(100000,0.05,12)
4BalanceInterestPrincipalEnd bal.
5100000416.678144.0891855.92
691855.92382.738178.0283677.9
783677.9348.668212.0975465.81
875465.81314.448246.3167219.51
967219.51280.088280.6758938.84
1058938.84245.588315.1750623.67
1150623.67210.938349.8242273.85
1242273.85176.148384.6133889.25
1333889.25141.218419.5425469.7
1425469.7106.128454.6217015.08
1517015.0870.98489.858525.23
168525.2335.528525.230
17
am3
Cell Formulas
RangeFormula
A3A3=FORMULATEXT(A5)
A5:D16A5=AMORTIZE3(100000,0.05,12)
Dynamic array formulas.

Xlambda

Active Member
This is a different approach concept of another function (a bit shorter), AMORTIZE4
amortize.xlsx
ABCDEFGHIJK
1Concept for even a shorter function, the following AMORTIZE4
2balance100000rate/12pmt
3rate0.050.0041678560.7482
4periods12
5
6to calculate the end balance
7we can use SCAN :
8=SCAN(B2,SEQUENCE(B4),LAMBDA(b,a,b*(1+D3)-E3))
991855.92
1083677.90to calculate the balance I have used the trick
1175465.81with IF(SEQUENCE(b3)-1,1,0) …:
1267219.51=SCAN(B2,IF(SEQUENCE(B4)-1,1,0),LAMBDA(b,a,b*(1+D3*a)-E3*a))
1358938.84100000.00
1450623.6791855.92and we used this second one as main "reference" for
1542273.8583677.90the other columns calculations inside MAKEARRAY
1633889.2575465.81Then I realized that we can use as "reference" the
1725469.7067219.51end balance one, and go backwards to balance with this simple formula
1817015.0858938.84bal=(endbal+pmt)(1+rate/12) :
198525.2350623.67=(A9#+E3)/(1+D3)
200.0042273.85100000.00
2133889.2591855.92
2225469.7083677.90
2317015.0875465.81
248525.2367219.51
2558938.84
2650623.67
2742273.85
2833889.25
2925469.70
3017015.08
318525.23
32
am4
Cell Formulas
RangeFormula
D3D3=B3/12
E3E3=PMT(D3,B4,-B2)
A8,E19,C12A8=FORMULATEXT(A9)
A9:A20A9=SCAN(B2,SEQUENCE(B4),LAMBDA(b,a,b*(1+D3)-E3))
C13:C24C13=SCAN(B2,IF(SEQUENCE(B4)-1,1,0),LAMBDA(b,a,b*(1+D3*a)-E3*a))
E20:E31E20=(A9#+E3)/(1+D3)
Dynamic array formulas.

Xlambda

Active Member

AMORTIZE4(b,r,p) ; REDUCE embedded inside MAKEARRAY
Excel Formula:
=LAMBDA(b,r,p,
LET(x,r/12,m,PMT(x,p,-b),
ROUND(MAKEARRAY(p,4,LAMBDA(r,c,LET(y,REDUCE(b,SEQUENCE(r),LAMBDA(b,a,b*(1+x)-m)),z,(y+m)/(1+x),SWITCH(c,1,z,2,z*x,3,m-z*x,4,y)))),2)
)
)
amortize.xlsx
ABCDE
1b,balance;r,rate;p,periods
2b,100000,r,.05,p,12
3=AMORTIZE4(100000,0.05,12)
4BalanceInterestPrincipalEnd bal.
5100000416.678144.0891855.92
691855.92382.738178.0283677.9
783677.9348.668212.0975465.81
875465.81314.448246.3167219.51
967219.51280.088280.6758938.84
1058938.84245.588315.1750623.67
1150623.67210.938349.8242273.85
1242273.85176.148384.6133889.25
1333889.25141.218419.5425469.7
1425469.7106.128454.6217015.08
1517015.0870.98489.858525.23
168525.2335.528525.230
17
am 4 f
Cell Formulas
RangeFormula
A3A3=FORMULATEXT(A5)
A5:D16A5=AMORTIZE4(100000,0.05,12)
Dynamic array formulas.

Xlambda

Active Member
Neck to neck test, AMORTIZE4/3/2, for speed and "limits" ( b=1M, r=0.05 , p=50years=600 months )
results: speed-instantaneous ; limits/errors-none

amortize.xlsx
ABCDEFGHIJKLMNOPQ
1AMORTIZE4600=ROWS(B5#)AMORTIZE3600=ROWS(G5#)AMORTIZE2600=ROWS(L5#)
2
3b,1E+6,r,0.05,p,600b,1E+6,r,0.05,p,600b,1E+6,r,0.05,p,600
4=AMORTIZE4(1000000,0.05,600)=AMORTIZE3(1000000,0.05,600)=AMORTIZE2(1000000,0.05,600)
510000004166.67374.72999625.2810000004166.67374.72999625.2810000004166.67374.72999625.28
6999625.284165.11376.28999249999625.284165.11376.28999249999625.284165.11376.28999249
79992494163.54377.85998871.159992494163.54377.85998871.159992494163.54377.85998871.15
8998871.154161.96379.42998491.72998871.154161.96379.42998491.72998871.154161.96379.42998491.72
9998491.724160.38381.01998110.72998491.724160.38381.01998110.72998491.724160.38381.01998110.72
10998110.724158.79382.59997728.12998110.724158.79382.59997728.12998110.724158.79382.59997728.12
11997728.124157.2384.19997343.94997728.124157.2384.19997343.94997728.124157.2384.19997343.94
12997343.944155.6385.79996958.15997343.944155.6385.79996958.15997343.944155.6385.79996958.15
13996958.154153.99387.4996570.75996958.154153.99387.4996570.75996958.154153.99387.4996570.75
14996570.754152.38389.01996181.74996570.754152.38389.01996181.74996570.754152.38389.01996181.74
15996181.744150.76390.63995791.11996181.744150.76390.63995791.11996181.744150.76390.63995791.11
16995791.114149.13392.26995398.85995791.114149.13392.26995398.85995791.114149.13392.26995398.85
17995398.854147.5393.89995004.96995398.854147.5393.89995004.96995398.854147.5393.89995004.96
18995004.964145.85395.53994609.43995004.964145.85395.53994609.43995004.964145.85395.53994609.43
19994609.434144.21397.18994212.25994609.434144.21397.18994212.25994609.434144.21397.18994212.25
20994212.254142.55398.84993813.41994212.254142.55398.84993813.41994212.254142.55398.84993813.41
21993813.414140.89400.5993412.91993813.414140.89400.5993412.91993813.414140.89400.5993412.91
22993412.914139.22402.17993010.74993412.914139.22402.17993010.74993412.914139.22402.17993010.74
23993010.744137.54403.84992606.9993010.744137.54403.84992606.9993010.744137.54403.84992606.9
24992606.94135.86405.53992201.38992606.94135.86405.53992201.38992606.94135.86405.53992201.38
25992201.384134.17407.22991794.16992201.384134.17407.22991794.16992201.384134.17407.22991794.16
26991794.164132.48408.91991385.25991794.164132.48408.91991385.25991794.164132.48408.91991385.25
27991385.254130.77410.62990974.63991385.254130.77410.62990974.63991385.254130.77410.62990974.63
28990974.634129.06412.33990562.31990974.634129.06412.33990562.31990974.634129.06412.33990562.31
29990562.314127.34414.04990148.26990562.314127.34414.04990148.26990562.314127.34414.04990148.26
30990148.264125.62415.77989732.49990148.264125.62415.77989732.49990148.264125.62415.77989732.49
31989732.494123.89417.5989314.99989732.494123.89417.5989314.99989732.494123.89417.5989314.99
32989314.994122.15419.24988895.75989314.994122.15419.24988895.75989314.994122.15419.24988895.75
33988895.754120.4420.99988474.76988895.754120.4420.99988474.76988895.754120.4420.99988474.76
34988474.764118.64422.74988052.02988474.764118.64422.74988052.02988474.764118.64422.74988052.02
35988052.024116.88424.5987627.51988052.024116.88424.5987627.51988052.024116.88424.5987627.51
36987627.514115.11426.27987201.24987627.514115.11426.27987201.24987627.514115.11426.27987201.24
37987201.244113.34428.05986773.19987201.244113.34428.05986773.19987201.244113.34428.05986773.19
38986773.194111.55429.83986343.36986773.194111.55429.83986343.36986773.194111.55429.83986343.36
39986343.364109.76431.62985911.73986343.364109.76431.62985911.73986343.364109.76431.62985911.73
40985911.734107.97433.42985478.31985911.734107.97433.42985478.31985911.734107.97433.42985478.31
41985478.314106.16435.23985043.08985478.314106.16435.23985043.08985478.314106.16435.23985043.08
42985043.084104.35437.04984606.04985043.084104.35437.04984606.04985043.084104.35437.04984606.04
43984606.044102.53438.86984167.18984606.044102.53438.86984167.18984606.044102.53438.86984167.18
44984167.184100.7440.69983726.49984167.184100.7440.69983726.49984167.184100.7440.69983726.49
45983726.494098.86442.53983283.96983726.494098.86442.53983283.96983726.494098.86442.53983283.96
46983283.964097.02444.37982839.59983283.964097.02444.37982839.59983283.964097.02444.37982839.59
47982839.594095.16446.22982393.37982839.594095.16446.22982393.37982839.594095.16446.22982393.37down to
48982393.374093.31448.08981945.28982393.374093.31448.08981945.28982393.374093.31448.08981945.28600 rows
49981945.284091.44449.95981495.33981945.284091.44449.95981495.33981945.284091.44449.95981495.33⬇⬇⬇⬇⬇⬇
am 4 f
Cell Formulas
RangeFormula
C1,M1,H1C1=ROWS(B5#)
D1,N1,I1D1=FORMULATEXT(C1)
B4,L4,G4B4=FORMULATEXT(B5)
B5:E604B5=AMORTIZE4(1000000,0.05,600)
G5:J604G5=AMORTIZE3(1000000,0.05,600)
L5:O604L5=AMORTIZE2(1000000,0.05,600)
Dynamic array formulas.

Xlambda

Active Member
Last one, AMORTIZE5(b,r,p), this time with SCAN embedded inside MAKEARRAY. (using same "concept" as in ARESIZE4)
Excel Formula:
=LAMBDA(b,r,p,
LET(x,r/12,m,PMT(x,p,-b),
ROUND(MAKEARRAY(p,4,LAMBDA(r,c,LET(y,INDEX(SCAN(b,SEQUENCE(r),LAMBDA(b,a,b*(1+x)-m)),r,c^0),z,(y+m)/(1+x),SWITCH(c,1,z,2,z*x,3,m-z*x,4,y)))),2)
)
)
Techniques covered here that I am positive will be helpful in many other case scenarios:
-replace CHOOSE functionality with MAKEARRAY (AMORTIZE2)
-create expandable array outcomes with MAKEARRAY (AMORTIZEX)
-embed REDUCE inside MAKEARRAY (AMORTIZE3,AMORTIZE4)
-embed SCAN inside MAKEARRAY (AMORTIZE5)
amortize.xlsx
ABCDEFGHIJK
1b,balance;r,rate;p,periodsAMORTIZE5600=ROWS(F5#)
2b,100000,r,.05,p,12
3=AMORTIZE5(100000,0.05,12)b,1E+6,r,0.05,p,600
4BalanceInterestPrincipalEnd bal.=AMORTIZE5(1000000,0.05,600)
5100000416.678144.0891855.9210000004166.67374.72999625.28
691855.92382.738178.0283677.9999625.284165.11376.28999249
783677.9348.668212.0975465.819992494163.54377.85998871.15
875465.81314.448246.3167219.51998871.154161.96379.42998491.72
967219.51280.088280.6758938.84998491.724160.38381.01998110.72
1058938.84245.588315.1750623.67998110.724158.79382.59997728.12
1150623.67210.938349.8242273.85997728.124157.2384.19997343.94
1242273.85176.148384.6133889.25997343.944155.6385.79996958.15
1333889.25141.218419.5425469.7996958.154153.99387.4996570.75
1425469.7106.128454.6217015.08996570.754152.38389.01996181.74
1517015.0870.98489.858525.23996181.744150.76390.63995791.11
168525.2335.528525.230995791.114149.13392.26995398.85
17995398.854147.5393.89995004.96
18995004.964145.85395.53994609.43
19994609.434144.21397.18994212.25
20994212.254142.55398.84993813.41
21993813.414140.89400.5993412.91
22993412.914139.22402.17993010.74
23993010.744137.54403.84992606.9
24992606.94135.86405.53992201.38
25992201.384134.17407.22991794.16
26991794.164132.48408.91991385.25
27991385.254130.77410.62990974.63
28990974.634129.06412.33990562.31
29990562.314127.34414.04990148.26
30990148.264125.62415.77989732.49
31989732.494123.89417.5989314.99
32989314.994122.15419.24988895.75
33988895.754120.4420.99988474.76
34988474.764118.64422.74988052.02
35988052.024116.88424.5987627.51
36987627.514115.11426.27987201.24
37987201.244113.34428.05986773.19
38986773.194111.55429.83986343.36
39986343.364109.76431.62985911.73
40985911.734107.97433.42985478.31
41985478.314106.16435.23985043.08
42985043.084104.35437.04984606.04
43984606.044102.53438.86984167.18
44984167.184100.7440.69983726.49
45983726.494098.86442.53983283.96
46983283.964097.02444.37982839.59
47982839.594095.16446.22982393.37down to
48982393.374093.31448.08981945.28600 rows
49981945.284091.44449.95981495.33⬇⬇⬇⬇⬇⬇
amt 5
Cell Formulas
RangeFormula
G1G1=ROWS(F5#)
H1H1=FORMULATEXT(G1)
A3A3=FORMULATEXT(A5)
F4F4=FORMULATEXT(F5)
A5:D16A5=AMORTIZE5(100000,0.05,12)
F5:I604F5=AMORTIZE5(1000000,0.05,600)
Dynamic array formulas. Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

1,152,570
Messages
5,770,915
Members
425,652
Latest member
Pemby We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back