Amortization Schedule with Spill Ranges

This is the second post in a series on dynamic array formulas and spill ranges. In this post, we’ll create a dynamic amortization schedule … and it is actually pretty fun! Before we had spill ranges, it was a hassle to update an amortization schedule when the number of periods changed. That is, you may want to see it for a loan with 360 months, and then update it for a loan with 180 months, and so on. As the number of periods change, so does the number of lines we want displayed. So, we’d have to fiddle with the formula rows or use some clever technique like conditional formatting or dynamic print ranges. But, now we can use dynamic array formulas and spill ranges.

Objective

In summary, we would like to enter the key loan terms into a few cells, like this:

Since the loan term is 6 months, we want to display 6 periods in our amortization schedule, like this:

But, if we change the number of months to say 12, we want 12 periods displayed in our amortization schedule, like this:

And, we DO NOT want to have to edit any formulas, hide or unhide any rows, or fill formulas down. This is exactly what dynamic array formulas and spill ranges accomplish 🙂

I’ve created a video and narrative to walk through the steps.

Video

Narrative

After storing the key loan terms in a few cells, we’ll need to write a formula for each column. We’ll write them in the following order:

• Period
• Interest
• Principal
• End Bal
• Beg Bal

Let’s get to it.

Note: depending on your version of Excel, you may not have the dynamic array functions demonstrated. At the time of this writing, it is available via O365 subscription only, and only on the Insiders Fast update channel. If you don’t yet have access to them, they are certainly something to look forward to 🙂

Period

The Period column simply needs to start at 1 and end at the number of months entered. We can use the SEQUENCE function for this. Assuming the number of months (6) is stored in C8, we write the following formula in B14:

=SEQUENCE(C8)

Note: the SEQUENCE function supports additional arguments, including the number of columns, the start value, and the step value. 

Excel enters 1 into B14 (the formula cell) and, since the SEQUENCE function returns multiple values, the remaining values spill down into adjacent cells as shown below.

As the user changes the number of months in C8, Excel will dynamically update the Period column accordingly.

Interest

To compute the interest for each period, we’ll use the IPMT function. The IPMT function has been in Excel for decades. It is not a dynamic array function, however, guess what happens when we pass it a spill range reference like B14#? Yes … it spills too! (Awesome, right?!)

Assuming the dynamic period formula is in B14, the annual interest rate is stored in C7, the loan amount in C6, and the number of months in C8, the following formula would work.

=-IPMT(C7/12,B14#,C8,C6)

Notes:

• C7/12 takes the annual interest rate and divides by 12 to determine the month interest rate. This is needed since we are creating monthly rows.
• B14# is the spill reference. B14 references the period (computed by the SEQUENCE function above) and the hash # tells Excel to reference the entire spill range (all rows returned by the Period formula). This causes the IPMT function to return multiple results and spill as well.
• C8 is the number of months
• C6 is the loan amount
• We flip the sign from a default negative value to a positive value with the leading subtraction operator (–)

Here we can see that IPMT also spills:

Principal

Writing the principal amount for each period is very similar to the interest calculation, except we use the PPMT function. We write the following formula in E14, as follows.

=-PPMT(C7/12,B14#,C8,C6)

Same basic arguments as the interest formula.

End Bal

To compute the ending balance for any given period, we’ll use the CUMPRINC function which computes the cumulative principal paid for any period. This function has been in Excel for decades, and is not a dynamic array function. However, when we use the SEQUENCE function as an argument … guess what? Yes, it returns multiple values and spills.

Let’s start by computing the cumulative principal paid at any given period. We can use the following formula in F14:

=CUMPRINC(C7/12,C8,C6,1,SEQUENCE(C8),0)

But, for the Ending Balance column, we don’t want to display the cumulative principal paid, we want to show the remaining balance. So, we just need to display the difference between the total loan amount (C6) and the principal paid (CUMPRINC calculation) by updating the formula as follows:

=C6+CUMPRINC(C7/12,C8,C6,1,SEQUENCE(C8),0)

We are looking good, and just need to compute the beginning balance column.

Beg Bal

We saved this one for last since it refers to the Ending Balance amount.

We just need to add the Ending Balance to the principal amount for the period. We’ll use spill references in our formula so that this formula spills as well:

=E14#+F14#

Once complete, we have a dynamic amortization schedule that adapts to the number of periods entered.

If you’d like to see all of these steps working together, feel free to download the sample file below. Or, if you don’t yet have these features in your version of Excel, check out the video above.

If you have any other fun spill range techniques or amortization schedule tips, please share by posting a comment below … thanks!

Sample file: AmorizationSpill.xlsx