Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 837
- Office Version
- 365
- Platform
- 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.
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 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | b,balance;r,rate;p,periods | |||||||||||
2 | b,100000,r,.05,p,12 | b,150000,r,.06,p,24 | ||||||||||
3 | =AMORTIZE(100000,0.05,12) | =AMORTIZE(100000,0.06,24) | ||||||||||
4 | Balance | Interest | Principal | End bal. | Balance | Interest | Principal | End bal. | ||||
5 | 100000 | 416.67 | 8144.08 | 91855.92 | 100000 | 500 | 3932.06 | 96067.94 | ||||
6 | 91855.92 | 382.73 | 8178.02 | 83677.9 | 96067.94 | 480.34 | 3951.72 | 92116.22 | ||||
7 | 83677.9 | 348.66 | 8212.09 | 75465.81 | 92116.22 | 460.58 | 3971.48 | 88144.74 | ||||
8 | 75465.81 | 314.44 | 8246.31 | 67219.51 | 88144.74 | 440.72 | 3991.34 | 84153.4 | ||||
9 | 67219.51 | 280.08 | 8280.67 | 58938.84 | 84153.4 | 420.77 | 4011.29 | 80142.11 | ||||
10 | 58938.84 | 245.58 | 8315.17 | 50623.67 | 80142.11 | 400.71 | 4031.35 | 76110.76 | ||||
11 | 50623.67 | 210.93 | 8349.82 | 42273.85 | 76110.76 | 380.55 | 4051.51 | 72059.25 | ||||
12 | 42273.85 | 176.14 | 8384.61 | 33889.25 | 72059.25 | 360.3 | 4071.76 | 67987.48 | ||||
13 | 33889.25 | 141.21 | 8419.54 | 25469.7 | 67987.48 | 339.94 | 4092.12 | 63895.36 | ||||
14 | 25469.7 | 106.12 | 8454.62 | 17015.08 | 63895.36 | 319.48 | 4112.58 | 59782.78 | ||||
15 | 17015.08 | 70.9 | 8489.85 | 8525.23 | 59782.78 | 298.91 | 4133.15 | 55649.63 | ||||
16 | 8525.23 | 35.52 | 8525.23 | 0 | 55649.63 | 278.25 | 4153.81 | 51495.82 | ||||
17 | 51495.82 | 257.48 | 4174.58 | 47321.23 | ||||||||
18 | 47321.23 | 236.61 | 4195.45 | 43125.78 | ||||||||
19 | 43125.78 | 215.63 | 4216.43 | 38909.35 | ||||||||
20 | 38909.35 | 194.55 | 4237.51 | 34671.83 | ||||||||
21 | 34671.83 | 173.36 | 4258.7 | 30413.13 | ||||||||
22 | 30413.13 | 152.07 | 4280 | 26133.14 | ||||||||
23 | 26133.14 | 130.67 | 4301.4 | 21831.74 | ||||||||
24 | 21831.74 | 109.16 | 4322.9 | 17508.84 | ||||||||
25 | 17508.84 | 87.54 | 4344.52 | 13164.32 | ||||||||
26 | 13164.32 | 65.82 | 4366.24 | 8798.08 | ||||||||
27 | 8798.08 | 43.99 | 4388.07 | 4410.01 | ||||||||
28 | 4410.01 | 22.05 | 4410.01 | 0 | ||||||||
29 | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A3,F3 | A3 | =FORMULATEXT(A5) |
A5:D16 | A5 | =AMORTIZE(100000,0.05,12) |
F5:I28 | F5 | =AMORTIZE(100000,0.06,24) |
Dynamic array formulas. |
Upvote
0