# Loan Amortisation Scedule with new Spiller functions

#### DRSteele

##### Well-known Member
The revised calculation engine for Excel365 includes new functions that spill and allow for dynamic arrays. Using this new technology, it's easy to create a loan amortisation schedule that omits the need to create the correct number of rows or complex formulas to account for the correct number of rows (as exist in MS's templates available under File|New| search for "Loan'). This example shows how to use just one row of formulas and have the schedule fill itself out to be the correct size. If there is no data below this row of formulas (i.e., row 13 onwards) it will spill all the way down 360 rows (or whatever number is in B7). I hope I've done this right - is it okay?

Book1
ABCDEF
1references
2https://www.mrexcel.com/forum/excel-questions/1084707-calculate-cumulative-interest-paid-month-15-mortgage-amortized-25-years.html
5
6Principal495,000.00Open Period1
7Term360Close Period5
8Rate p.a.4.25%
9Pmt2,435.10Cumulative Interest-8741.39-8741.39
10
11PaymentOpenInterest PmtPrincipal PmtTotal PmtClose
121495,000.001,753.13681.982,435.10494,318.02
132494,318.021,750.71684.392,435.10493,633.63
143493,633.631,748.29686.822,435.10492,946.81
154492,946.811,745.85689.252,435.10492,257.56
165492,257.561,743.41691.692,435.10491,565.87
176491,565.871,740.96694.142,435.10490,871.73
187490,871.731,738.50696.602,435.10490,175.14
198490,175.141,736.04699.072,435.10489,476.07
Sheet7
Cell Formulas
RangeFormula
B9B9=PMT(B8/12,B7,-B6)
E9E9=CUMIPMT(B8/12,B7,B6,E6,E7,0)
F9F9=SUM(IPMT(B8/12,SEQUENCE(E7,,E6),B7,B6))
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#

Last edited by a moderator:

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Edit: The forumla in F9 should have been posted as
Excel Formula:
``=SUM(IPMT(B8/12,SEQUENCE(E7-E6+1,,E6),B7,B6))``

Last edited by a moderator:
Check out Jeff Lenning's terrific treatment of the topic:

Last edited by a moderator:

Replies
7
Views
2K
Replies
6
Views
711
Replies
22
Views
713
Replies
0
Views
598
Replies
13
Views
1K

1,203,502
Messages
6,055,772
Members
444,822
Latest member
Hombre

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