How to Create a Mortgage Calculator in Excel - 2 Methods

Download Practice Workbook

The dataset showcases Market Price of Asset, Total Loan Amount, Down Payment, Loan Repayment Tenure, and Rate of Interest.

Method 1 – Use the PMT Function to Create a Mortgage Calculator

The formula is:

PMT(rate, nper, pv, [fv], [type])

Rate (required) – the constant interest rate per period (a percentage or decimal number).
Nper (required) – the number of payments for the loan.
Pv (required) – the present value: the borrowed amount.
Fv (optional) – the future value after the last payment. If omitted, the future value of the loan is assumed to be zero.
Type (optional) – specifies when payments are due.

Select D10 and use the following formula.
Press Enter.

=PMT(D9/12,D8*12,-D6)

Method 2 – Applying the PPMT Function to Generate a Mortgage Calculator

The syntax is:

PPMT(rate, per, nper, pv, [fv], [type])

A value for Period was added.

Select D11 and use the following formula.
Press Enter.

=PPMT(D10/12,D9,D8*12,-D6)

How to Create a Loan Amortization Schedule in Excel?

Create an amortization table with: Total Loan Amount, Loan Repayment Tenure, Payments Per Year, and Annual Rate of Interest.

Calculate the Payment amount using the PMT function.

Enter the following function in C11 and press Enter.

=PMT($D$8/$D$7,$D$6*$D$7,$D$5)

Drag the formula to column C.

To calculate the Principal, use the PPMT function.

Select D11, enter the following formula, and drag it down.

=PPMT($D$8/$D$7,B11,$D$6*$D$7,$D$5)

To calculate the interest of each periodic payment:

=IPMT($D$8/$D$7,B11,$D$6*$D$7,$D$5)

Use the formula in column E.

To calculate the balance for each period, use two different formulas.

To find the balance after the first payment in C11, add the loan amount (D5) and the principal of the first period (D11).

=D5+D11

To find the balance after the second period, sum the previous balance and the principal of the second period.

=F11+D12

Drag down the formula.

It will take 24 months to pay the loan.

Which Things Should You Remember?

These examples are applicable to compound interest.

Frequently Asked Questions

1. How can I calculate the remaining mortgage balance after making payments in Excel?

Determine the remaining mortgage balance by deducting the principal paid from the original loan amount. By eliminating the interest component of the payment from the entire payment, you can determine the amount of the principal payment. Calculate the principal payment using the PPMT function.

2. How can I calculate the effective interest rate of a loan in Excel?

Use the RATE function to calculate the effective interest rate. The syntax is “=RATE(nper, pmt, pv, [fv], [type])“: “nper” is the total number of payment periods, “pmt” is the monthly payment, “pv” is the present value or loan amount, “[fv]” is the future value (optional), “[type]” indicates whether payments are due at the beginning or end of the period (optional).