An amortization schedule contains details with respect to a loan, including the following:
- The principal amount of the loan.
- The interest on the loan (typically reflected in annual terms).
- The length of the loan (typically reflected in years; except in specific short term scenarios)
- Frequency of repayment.
It is also normal to see the dates on the loan; though those can result in some complex conditional formulas! The top of an amortization schedule should look something like this:
The Problem with Excel Formulas
The concern with building any amortization schedule is the number of periods. In order to build a schedule, normally the Periods field needs to accommodate for any potential amortization scenarios.
In an extreme case, such as a 30-year mortgage with bi-weekly installments, this would mean that 30 X 26 rows (780 rows) would be needed. To eliminate the risk of #VALUE! and #NUM! values on rows, an IF() formula will be needed on every row in order to properly hide unnecessary values.
This is where M / Power Query comes to the rescue!
First, name the ranges!
In order to generate the periods, the script needs to know the number of periods that will need to be amortized. For this schedule, it is a good idea to have the total number of periods available in a hidden section. Once the rows are named, change the colour of text in the cell to white.
For the purpose of this schedule, the total number of periods is being named ‘AmortPeriods’. The other periods that should be named (as they will be used in formulas later) include:
- Opening Balance
- Periodic Interest Rate
Create a query, referring to the total periods.
While remaining on in the AmortPeriods range, click on Data > From Table / Range. This will bring up the Power Query editor in a new window.
The Power Query Editor should only show the one number, a Column1 with a value of the total number of periods.
Set up the List in Power Query
This is where things get tricky. The number in Column1 tells us the total number of periods for amortizing but what we are looking for is a list of numbers starting at 1 and going up to the total number of periods.
In order to set this up in Power Query, you will need to use the advanced editor.
The Advanced Editor should show a row named #”Changed Type”. This is the starting point for our list generation. The first line that you will need to enter after the Changed Type line is as follows:
PeriodList = List.Generate(()=>1,each _ <= #”Changed Type”{0}[Column1], each _ + 1)
What is a List.Generate()?
The List.Generate is made up of 4 parts. This is very similar to a FOR loop, for those who are familiar with some programming languages, such as JavaScript.
PeriodList =
This provides the list that will be generated after the List.Generate function completes.
List.Generate(()=>1,
The List.Generate function starts with a value of 1.
each _ <= #”Changed Type”{0}[Column1],
This says that the list will keep going until the counter reaches the first value {0} of the [Column1] column.
each _ + 1
The list will increment by ones. This formula does not have to increment by ones, it can increment by any number, including negative numbers!
When you close your advanced editor, it should show a list, like the one in the image below!
Finishing up in Power Query
At this point, you have a list of numbers, however, this list cannot be used in Excel. First, you need to convert the list into a table using the Convert To Table option.
The options that are given can safely be ignored. Just hit the OK button.
Finally, right-click on the Column1 text above Row 1 and rename the column to something more relevant to an amortization schedule – like Periods.
After that is done, you can click on Close and Load To. Since you want the amortization table next to the variables, it is a good idea to pick an existing sheet. For this example, we have used cell D2.
You now have a list of periods generated in Excel for your schedule! The only thing left to do is add the balances.
Adding the Balances in Excel
You have almost completed the schedule! Great job! The only thing left is to add in the financial information.
There are 5 balances that need to be calculated in order to complete the schedule.
- Opening Balance
- Payment
- Interest Payment
- Principal Payment
- Closing Balance
The Payment, Interest Payment and Principal Payment functions are the same as if the periods were not generated using Power Query.
A discussion on the PMT function is available at: https://macrordinary.ca/2019/10/11/using-pmt-to-calculate-installments/
The payment function will always remain the same on every row and doesn’t use any information from the new periods generated.
Payment =-PMT(PeriodicInterestRate,AmortPeriods,OpeningBal)
The interest and principal payments use the [@Periods] in order to get the correct period on every row.
Interest =-IPMT(PeriodicInterestRate,[@Periods],AmortPeriods,OpeningBal)
Principal=-PPMT(PeriodicInterestRate,@Periods],AmortPeriods,OpeningBal)
The closing balance is calculated by taking the starting opening balance, then subtracting (adding the negative values) the cumulative payments made on the loan.
Closing Balance =OpeningBal+CUMPRINC(PeriodicInterestRate ,AmortPeriods,OpeningBal,1,[@Periods],0)
Finally, the opening balance is simply the closing balance plus the principal paid during the period.
Opening Balance =[@[Closing Balance]]+[@Principal]
Refreshing the Values in the Amortization Schedule
Unfortunately, the number of periods will not automatically adjust every time the values in the table change. This can cause some schedules to not show all periods (if the number of periods is increased) or show too many periods (if the number of periods is decreased).
There are 2 ways to resolve this issue.
- Manually refresh the table when values change.
- Update the auto-refresh schedule to 1 minute.
Manually Refresh the Amortization Table
If you click anywhere on the table, you have an option to Refresh the table.
Update the Refresh Schedule
Under Data > Existing Connections, the query you created in Power Query can be modified by right-clicking on the query name.
Within the query properties, the query can be changed to refresh every minute. Since the list generation query is small, it should not take significant system resources to refresh.
Conclusion
This example provides a very simple use of the List.Generate() function in Power Query. In fact, the financial functions do not need to be done in Excel, Power Query can handle them within the advanced query editor as well!