• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

Loan Amortisation Schedule

We can use Excel's new calc engine and dynamic array functions to create a loan amortisation schedule in a way that omits the need to create the correct number of rows or complex formulas to account for the correct number of rows. The formulas go in one row and they spill down to the correct number of rows as if by magic! This example spllls down 360 rows to Row371.

There are other useful calculations here too.

I included some other links to our work earlier in this forum. In June, Leila Ghirani from XelPlus was thrilled by all this amortising fun too when I shared it with her. She is one smart cookie!

{Appended 1/23/20: The key here is the use of a dynamic function to report the Opening Balance; the alternative of using a constant or straight reference to another cell won't work. Without this trick, the whole thing won't spill appropriately.}

Book1
ABCDEFGHIJKLMN
6Principal495,000.00Open Period1open balance on period5
$492,257.56
7Term360
30 yr
Close Period
360
close balance on period
5
$491,565.87
8Rate p.a.4.25%
9Pmt
2,435.10
Cumulative Interest
-381,636.89
-381,636.89
total interest as portion of Principal
77.10%
60120240360
103.50%
9.15%
18.66%
39.19%
61.66%
11PaymentOpenInterest PmtPrincipal PmtTotal PmtClose3.75%
9.82%
20.07%
42.29%
66.72%
12
1
495,000.00
1,753.13
681.98
2,435.10
494,318.02
4.00%
10.50%
21.49%
45.44%
71.87%
132494,318.021,750.71684.392,435.10493,633.634.25%
11.18%
22.93%
48.62%
77.10%
143493,633.631,748.29686.822,435.10492,946.814.50%
11.86%
24.37%
51.84%
82.41%
154492,946.811,745.85689.252,435.10492,257.564.75%
12.54%
25.82%
55.09%
87.79%
165492,257.561,743.41691.692,435.10491,565.875.00%
13.23%
27.28%
58.39%
93.26%
176491,565.871,740.96694.142,435.10490,871.73
187490,871.731,738.50696.602,435.10490,175.14
LoanAmortSpiller
Cell Formulas
RangeFormula
J6J6=PV($B$8/12,B7+1-I6,-$B$9)
C7C7=B7/12
E7E7=B7
I7I7=I6
J7J7=J6-PPMT(B8/12,I7,B7,-B6)
B9B9=PMT(B8/12,B7,-B6)
E9E9=CUMIPMT(B8/12,B7,B6,E6,E7,0)
F9F9=SUM(IPMT(B8/12,SEQUENCE(E7-E6+1,,E6),B7,B6))
J9J9=SUM(C12#)/B6
K10:N16K10{=TABLE(B7,B8)}
A12A12=SEQUENCE(B7)
B12B12=PV(B8/12,B7+1-A12#,-B9)
C12C12=IPMT(B8/12,A12#,B7,-B6)
D12D12=PPMT(B8/12,A12#,B7,-B6)
E12E12=C12#+D12#
F12F12=B12#-D12#
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Excel Version
365
  • Like
Reactions: mdileep and Patropi
Author
DRSteele
Views
796
First release
Last update
Rating
5.00 star(s) 1 ratings

More Excel articles from DRSteele

Latest reviews

This helped me take multiple workbooks with hundreds of rows of data and simplify it.
DRSteele
DRSteele
That's super.

Some videos you may like

This Week's Hot Topics

Top