Dynamic Amortization Schedule with SCAN

In this tutorial, we’ll create a fully dynamic amortization schedule based on the number of months entered. For example, if you enter 12 months, the amortization schedule will span 12 rows. If you enter 360 months, the amortization schedule will span 360 rows. The technique demonstrated in this tutorial uses the following functions: SCAN, SEQUENCE, PMT, IPMT, and PPMT. We will walk through three exercises to help us build the schedule gradually. By the end of the tutorial, you will be able to generate a fully dynamic amortization schedule that allows you to customize the number of months, interest rate, and loan amount.

Note: not all versions of Excel support the SCAN or other functions presented. At the time I’m writing this, it is available in Excel 365.

Video

Step-by-step

In summary, we’d like Excel to compute the monthly payment for a loan given the entered terms, PLUS, we’d like it to generate a full loan amortization schedule. We can set up a worksheet with the basic loan terms, like this:

And we want Excel to generate the related amortization schedule, like this:

We’ll start by understanding the SEQUENCE and SCAN functions.

Exercise 1: Creating a Sequence

The SEQUENCE function will generate a dynamic list of periods based on the entered number of months. This will be key in our amortization schedule, so, let’s just warm up and create a sequence of numbers to see how it works.

SEQUENCE

The first argument of SEQUENCE defines the number of rows, which in this case is the number of values we’d like it to return. It has additional arguments that can be helpful in other situations, so definitely check them out.

So, we write the following formula in B10:

=SEQUENCE(6)

We hit Enter, and bam:

We can change the number of rows by modifying the first argument in the SEQUENCE function.

Now let’s talk about the SCAN function, which will also be key in our amortization schedule.

SCAN

For the purposes of our loan amortization schedule, we’ll use the SCAN function to compute various running totals.

To see how it works, let’s compute a running total for the results of the SEQUENCE function. In C10, we write the following:

=SCAN(0, B10#, SUM)

• 0 is the initial value (we want our running total to begin at 0)
• B10# is the reference to the dynamic array returned by the SEQUENCE function, and includes the # spill operator
• SUM is the function to apply to the range, which in our case provides a running total

We hit Enter, and bam:

We now have a running total of the sequence. We will be using this technique several times to build out our amortization schedule. So … you ready? Me too!

Exercise 2: Creating an Amortization Schedule

Now that we understand how the SEQUENCE and SCAN functions work, we can move on to creating a dynamic amortization schedule.

First, we create a few cells where the user can input the loan assumptions:

With these input cells in place, the first thing we will compute is the monthly payment.

Monthly Payment

Let’s use the PMT (payment) function to calculate the monthly payment based on the loan amount, annual interest rate, and number of months.

In cell C9, we use the following formula:

=PMT(C7/12, C6, -C8)

Where:

• C7/12 is the monthly interest rate
• C6 is the number of periods
• -C8 is the loan amount, expressed as a negative so that the resulting payment is expressed as a positive

We hit Enter and bam:

For the basic amortization schedule, we’ll need to write a formula to populate each of our desired columns:

Period

Let’s start with the Period column. For this, we’ll simply use the SEQUENCE function. In cell B15, we write the following formula:

=SEQUENCE(C6)

Press Enter and bam:

We’ll skip the Beginning Balance column for now and head to the Interest column.

Interest

Now, let’s calculate the current period interest for each row using the IPMT (interest payment) function. We write the following formula in cell D15

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

Where

• C7/12 is the monthly interest rate
• B15# is the current period (used the # spill operator to refer to all results in the Period column)
• C6 is the total number of periods in the loan
• -C8 is the loan amount, expressed as a negative

We hit Enter, and bam:

Principal

Now, let’s calculate the amount of the payment applied to the principal each period with the PPMT (principal payment) function.

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

Where:

• C7/12 is the monthly interest rate
• B15# is the current period (used the # spill operator to refer to all results in the Period column)
• C6 is the total number of periods in the loan
• -C8 is the loan amount, expressed as a negative

We hit Enter, and bam:

Next, we will calculate the ending balance.

Ending Balance

The ending balance is essentially a running total. The SCAN function can help us out with this. We write the following formula in F15:

=SCAN(C8, -E15#, SUM)

Where

• C8 is the initial value, the loan amount
• -E15# is the principal column, expressed as a negative so that each is subtracted from the running total
• SUM is the function we want to use

We hit Enter:

Beginning Balance

Now let’s revisit the Beginning Balance column. For this, we will just add the principal and ending balance columns with the following formula:

=E15#+F15#

And with that, our amortization schedule is complete:

Now, our amortization schedule is fully dynamic! You can change the loan amount, annual interest rate, or number of months, and the schedule will update accordingly. The formulas will use exactly the number of rows needed based on the number of periods.

Exercise 3: Bonus – Additional Columns

Depending on your needs, you may want a few additional informational columns, such as the payment amount, cumulative interest, and cumulative principal.

For these, we can once again use SEQUENCE and SCAN.

Payment

Let’s start by adding the payment amount column. We can use the following formula in G15:

=SEQUENCE(C6,1,C9,0)

Where:

• C6 is the number of rows
• 1 is the number of columns
• C9 is the payment amount
• 0 is the step value, meaning, don’t change the payment amount

Cumulative Interest

We can populate the Total Interest column by writing the following formula in H15:

=SCAN(0,D15#,SUM)

Where

• 0 is the initial value
• D15# is the interest payment column
• SUM is the function we want to use

Cumulative Principal

To add a column for the total principal paid, we can write the following formula in I15:

=SCAN(0,E15#,SUM)

Where

• 0 is the initial value
• E15# is the principal payment column
• SUM is the function to use

Our updated amoritzation schedule:

And that is one way to create a dynamic amortization schedule. I like this approach because it leverages the dynamic array formulas that spill into the number of rows needed. That way, our amortization schedule fills only the number of rows needed.

If you have any suggestions, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

Common Questions: FAQ

Q: Can I change the number of months in the amortization schedule?

A: Yes, you can change the number of months by modifying the value in the Months input cell. The amortization schedule will update automatically.

Q: How do I change the loan amount?

A: You can change the loan amount by modifying the value in Loan input cell. The amortization schedule will adjust accordingly.

Q: Can I customize the interest rate?

A: Yes, you can change the annual interest rate by modifying the value in Rate input cell. The amortization schedule will reflect the new interest rate.

Q: What if I want to view a shorter or longer amortization schedule?

A: You can modify the number of months to view a shorter or longer amortization schedule. The schedule will update accordingly.

Q: Can I use this amortization schedule for different types of loans, such as mortgages or car loans?

A: Yes, you can use this amortization schedule for different types of loans. Simply adjust the loan amount, interest rate, and number of months to match the terms of your specific loan.

Q: What exactly does the SEQUENCE function do, and how do I use it?

A: The SEQUENCE function generates a list of sequential numbers, typically used for creating numeric sequences in a column or row. The basic syntax is =SEQUENCE(rows, [columns], [start], [step]) where:

• rows is the number of rows to fill with sequential numbers.
• [columns] is optional and specifies the number of columns to fill (default is 1).
• [start] is optional and determines the first number in the sequence (default is 1).
• [step] is optional and sets the increment between each number in the sequence (default is 1).

For example, =SEQUENCE(4,1,10,2) will generate a column of 4 numbers starting at 10 and increasing by 2, resulting in 10, 12, 14, 16.

Q: Can you break down how the PMT function works in the context of the amortization schedule?

A: The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. The syntax is =PMT(rate, nper, pv, [fv], [type]) where:

• rate is the interest rate for each period.
• nper is the total number of payments for the loan.
• pv is the present value, or total amount of the loan.
• [fv] is optional and represents the future value, or a cash balance you want to attain after the last payment (default is 0).
• [type] is optional and indicates when payments are due: 0 at the end of the period (default), or 1 at the beginning.

For instance, =PMT(0.03/12, 360, 100000) calculates the monthly payment for a $100,000 loan at 3% annual interest over 360 months.

Q: What purpose does the IPMT function serve in calculating interest?

A: The IPMT function calculates the interest portion of a payment for a particular period of a loan. Its syntax is =IPMT(rate, per, nper, pv, [fv], [type]) where:

• rate is the interest rate per period.
• per is the period for which you want to find the interest and must be between 1 and nper.
• nper is the total number of payment periods.
• pv is the present value.
• [fv] and [type] have the same meanings as in the PMT function.

An example might be =IPMT(0.03/12, 1, 360, -200000) which calculates the interest part of the first payment for a $200,000 loan over 360 months.

Q: How is the PPMT function used to calculate the principal part of a payment?

A: The PPMT function calculates the principal portion of a payment for a given period. The syntax is similar to that of the IPMT function: =PPMT(rate, per, nper, pv, [fv], [type]). The arguments carry the same meaning, except that this formula results in the principal part of the payment rather than the interest part.

For example, =PPMT(0.03/12, 1, 360, -200000) computes the principal portion of the first payment for a $200,000 loan at 3% yearly interest rate over 360 months.

Q: What does the SCAN function do, and how was it used in the tutorial?

A: The SCAN function calculates a running total or a running calculation across a range. It has the following syntax: =SCAN(initial, array, function) where:

• initial is the starting value of the running total.
• array is the array or range over which the function should run.
• function is the operation to perform.

In the tutorial, SCAN is used to compute a running total of the principal to determine the ending balance. For instance, =SCAN(0, -E15#, SUM) starts from an initial value of 0 and subtracts each period’s principal from the running total, using the SUM function.