AMORTIZE

=AMORTIZE(b,r,p)
b
starting balance
r
annual interest rate
p
nr. of periods
builds amortization table NEW!! SCAN
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0
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.
 
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.
 
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)
   )
)
 
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.
 
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.
 
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.
 
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.
 
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.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,808
Messages
6,181,073
Members
453,020
Latest member
mattg2448

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top