# Loan Amortisation Schedule

Excel Version
1. 365
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/19, 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.}

{Appended 8/31/20: Mike Girvin from ExcelIsFun has another interesting way to do this. Check it out here.}

Book1
ABCDEFGHIJKLMN
6Principal495,000.00Open Period1open balance on period5\$492,257.56
7Term36030 yrClose Period360close balance on period5\$491,565.87
8Rate p.a.4.25%
9Pmt2,435.10Cumulative Interest-381,636.89-381,636.89total interest as portion of Principal77.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%
121495,000.001,753.13681.982,435.10494,318.024.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:N16What-if Analysis - Data Table
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#
mdileep and Patropi
Author
DRSteele
Views
3,188
First release
Last update
Rating
1 ratings

### Latest reviews

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

### 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.

### Which adblocker are you using?

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