Mortgage payment schedule

Mortgage payment schedule Excel formula dynamic array

Learn how to create a mortgage payment schedule in Excel using a formula based on multiple dynamic array functions.

How to calculate Mortgage payment schedule in Excel?

Here are the steps to calculate a mortgage payment schedule in Excel:

  1. Select cell E3.
  2. Use a LET formula with SEQUENCE, PMT, IF, IPMT, PPMT, SCAN, LAMBDA, CUMPRINC, and HSTACK.
  3. Press Enter.
  4. The formula will generate a dynamic mortgage payment schedule.

We use dynamic array functions, like SCAN, SEQUENCE, and HSTACK avoid manual copying. The LET function defines reusable variables to make the formula efficient and readable.

Example

In this example, we have a dataset calculating a mortgage payment schedule for a loan of $800,000 with an annual interest rate of 8.13% over 12 months. The goal is to decompose each monthly payment into interest and principal components while tracking the remaining loan balance and cumulative principal paid.

The formula in cell E3 utilizes the LET function to define intermediate calculations, such as monthly interest, payment breakdowns, and cumulative totals, ensuring accurate and organized outputs across the dataset.

Formula:

=LET(
rate, C3 / 12,
periods, C4,
loan, C2,
months, SEQUENCE(periods + 1, 1, 0, 1),
payment, IF(months=0, 0, PMT(rate, periods, loan, 0, 0)),
interest, IF(months=0, 0, IPMT(rate, IF(months=0, 1, months), periods, loan, 0, 0)),
principal, IF(months=0, 0, PPMT(rate, IF(months=0, 1, months), periods, loan, 0, 0)),
balance, SCAN(loan, IF(months=0, 0, principal), LAMBDA(total,part, total + part)),
cum_principal, IF(months=0, 0, CUMPRINC(rate, periods, loan, 1, months, 0)),
HSTACK(months, payment, interest, principal, balance, cum_principal))

Here is the result:

Mortgage payment schedule Excel formula dynamic array

Explanation

Mortgage payment schedules are common in financial planning to analyze loan repayment structures, including how much goes toward interest vs. principal, and to track the declining balance over time.

Evaluate the formula!

Constants and variables for mortgage payment schedule:

  • rate: the formula C3 / 12 converts the annual interest rate into a monthly interest rate. For example, if the annual rate is 8.13%, dividing by 12 gives 0.006775 (0.6775%).
  • periods: C4 refers to the total number of months (loan term). For example, a 12-month loan means periods = 12
  • loan: C2 captures the loan amount (e.g., $800,000 in this example).

Create a sequence of months:

  • SEQUENCE(periods + 1, 1, 0, 1) generates a column of sequential numbers from 0 (start of the loan) to periods (end of the loan). The sequence includes an additional entry (month 0) to represent the starting balance before payments begin.
  • Example output for 12 months: {0; 1; 2; 3; …; 12}

Calculate the monthly payment:

  • IF(months=0, 0, PMT(rate, periods, loan, 0, 0)) uses the PMT function to calculate the fixed monthly payment required to repay the loan. The formula assumes no balloon payment (the loan balance is fully paid off at the end) and sets the future value (fv) to 0.
  • IF(months=0, 0, …) ensures no payment is recorded in the initial month (month=0). For example, for a $800,000 loan at 8.13% over 12 months, the monthly payment is approximately $69,638.84.

Payment’s breakdown: Interest and principal:

  • interest: In the formula IF(months=0, 0, IPMT(rate, IF(months=0, 1, months), periods, loan, 0, 0)) the IPMT function calculates the interest portion of the payment for a given month. The adjustment IF(months=0, 1, months) ensures the formula works even when month=0.
  • principal: In the formula, IF(months=0, 0, PPMT(rate, IF(months=0, 1, months), periods, loan, 0, 0)) the PPMT function calculates the principal portion of the payment for a given month and ensures no principal is recorded for the initial month.

Together, the interest and principal add up to the total payment.

Calculate the remaining loan balance:

  • balance: SCAN(loan, IF(months=0, 0, principal), LAMBDA(total, part, total + part)) calculate the running total of remaining loan balance. Starts with the initial loan amount (loan) and iteratively reduces the balance by the principal paid (total + part) each month.
  • Example for month 3: starting balance = $606,035.29, principal paid = $65,091.95 and the new balance = $606,035.29 – $65,091.95 = $540,502.34.

Calculate cumulative principal paid:

  • cum_principal: The formula IF(months=0, 0, CUMPRINC(rate, periods, loan, 1, months, 0)) uses the CUMPRINC function to compute the total principal paid from the first month up to the current month. In other words, it helps track progress in reducing the loan’s principal. For instance, for month 6, the cumulative principal equals $325,474.60 (the sum of all principal portions paid through Month 6).

Finally, HSTACK combines all the arrays (months, payment, interest, etc.) into a single table. The result is a dynamic spill range displaying the amortization schedule.

You can download the practice file here.

Workaround with older Excel versions

Suppose your Excel version does not support modern dynamic array functions. In this case, however, you can add some manual work, yet still achieve the result above. Therefore, we use a traditional method with multiple formulas for older Excel versions to ensure compatibility for all users.

Use the following formulas and copy them down:

  • Interest: =IPMT(interest/12,E3,Months*12,-loan)
  • Principal: =PPMT(interest/12,E3,Months*12,-loan)
  • Total Payment: =F3+G3
  • Balance: =loan-G3
Workaround with older Excel formulas

To improve the readability, we are using named ranges.

This tutorial is a part of our definitive guide on Excel formulas.

Wrapping things up

In this guide, we demonstrated how to create a mortgage payment schedule in Excel using advanced dynamic array formulas. By integrating functions such as SEQUENCE, PMT, IPMT, PPMT, SCAN, LET, and HSTACK, you can use a method to calculate monthly payments, break them into interest and principal components, and track the remaining loan balance dynamically.