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.

Dataset of Generating Excel Mortgage Calculator

 


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

The formula of the PMT function is

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

Where

  • 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.

Overview of PMT Function

 

  • Select D10 and use the following formula.
  • Press Enter.

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

Mortgage Calculator Using PMT Function


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

The syntax of the PPMT function is

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

Overview of PPMT Function

A value for Period was added.

  • Select D11 and use the following formula.
  • Press Enter.

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

Mortgage Calculator Using PPMT Function


How to Create a Loan Amortization Schedule in Excel?

Combine the IPMT function and arithmetic calculations.

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

Dataset to Calculate Amortization Schedule

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.

Payment Calculation Using PMT Function for Amortization Schedule

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)

Principal Calculation Using PPMT Function for Amortization Schedule

To calculate the interest of each periodic payment, use the IPMT function.

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

  • Use the formula in column E.

Calculating Imterest Using IPMT Function for Amortization Schedule

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.

Loan Amortization Schedule Calculation

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).

3. Can I calculate loans with irregular payment amounts and schedules in Excel?

Yes, you can create a unique amortization schedule that takes into account various payment amounts and due dates.


Excel Mortgage Calculator: Knowledge Hub


<< Go Back to Finance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Beni Yameen Jonayed
Beni Yameen Jonayed

Beni Yameen Jonayed, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, works as an Excel & VBA Content Developer at ExcelDemy. His deep interest in research and innovation extends to his passion for Excel. In his role, Jonayed not only skillfully addresses challenges but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedicated commitment to delivering outstanding content. He is interested in Data Analysis with MS... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo