• If you would like to post, please check out the MrExcel Message Board FAQ and register here.
    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

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#
  • Like
Reactions: mdileep and Patropi
Author
DRSteele
Views
1,477
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.

This Week's Hot Topics

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top