Loan Amortisation Scedule with new Spiller functions

[DRSteele]

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
	A	B	C	D	E	F
1	references
2	https://www.mrexcel.com/forum/excel-questions/1084707-calculate-cumulative-interest-paid-month-15-mortgage-amortized-25-years.html
3	https://www.youtube.com/watch?v=ZmLu0vMRrGs
4	https://www.youtube.com/watch?v=QN8KJmRLilo
5
6	Principal	495,000.00		Open Period	1
7	Term	360		Close Period	5
8	Rate p.a.	4.25%
9	Pmt	2,435.10		Cumulative Interest	-8741.39	-8741.39
10
11	Payment	Open	Interest Pmt	Principal Pmt	Total Pmt	Close
12	1	495,000.00	1,753.13	681.98	2,435.10	494,318.02
13	2	494,318.02	1,750.71	684.39	2,435.10	493,633.63
14	3	493,633.63	1,748.29	686.82	2,435.10	492,946.81
15	4	492,946.81	1,745.85	689.25	2,435.10	492,257.56
16	5	492,257.56	1,743.41	691.69	2,435.10	491,565.87
17	6	491,565.87	1,740.96	694.14	2,435.10	490,871.73
18	7	490,871.73	1,738.50	696.60	2,435.10	490,175.14
19	8	490,175.14	1,736.04	699.07	2,435.10	489,476.07
Sheet7

Cell Formulas
Range	Formula
B9	B9	=PMT(B8/12,B7,-B6)
E9	E9	=CUMIPMT(B8/12,B7,B6,E6,E7,0)
F9	F9	=SUM(IPMT(B8/12,SEQUENCE(E7,,E6),B7,B6))
A12	A12	=SEQUENCE(B7)
B12	B12	=PV(B8/12,B7+1-A12#,-B9)
C12	C12	=IPMT(B8/12,A12#,B7,-B6)
D12	D12	=PPMT(B8/12,A12#,B7,-B6)
E12	E12	=C12#+D12#
F12	F12	=B12#-D12#

 

[DRSteele]

Edit: The forumla in F9 should have been posted as

=SUM(IPMT(B8/12,SEQUENCE(E7-E6+1,,E6),B7,B6))

 

[DRSteele]

Check out Jeff Lenning's terrific treatment of the topic:

Amortization Schedule with Spill Ranges - Excel University

An amortization schedule makes it easier to track loan payments and balances. Here's how to build on in Excel using spill ranges.

www.excel-university.com