How to Create Mortgage Calculator in Excel? (2 Simple Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, you will learn – How to create mortgage calculator in Excel using
PMT function
PPMT function

Excel does not have any built-in mortgage calculator of its own. But you can easily construct one by applying certain features and criteria. In this article, we will show you how to create an Excel mortgage calculator. For better understanding, we have imposed some required data.

The PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods, and the loan amount.

The PPMT function in Excel calculates the principal portion of loan payment for a given period based on a constant interest rate and payment schedule.

We have used Microsoft 365 for all the processes. However, you can try them on other versions too.

Download Practice Workbook

How to Create Mortgage Calculator in Excel?

First, we need some particulars regarding the mortgage. We have enlisted Market Price of Asset, Total Loan Amount, Down Payment, Loan Repayment Tenure, and Rate of Interest as follows.

Dataset of Generating Excel Mortgage Calculator

We will use these as function arguments to calculate the monthly EMI or monthly payment.

1. Use PMT Function to Create Mortgage Calculator

The general formula for the PMT function is

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


  • Rate (required) – the constant interest rate per period. you can supply it as a percentage or decimal number.
  • Nper (required) – the number of payments for the loan, i.e. the total number of periods over which the loan should be paid.
  • Pv (required) – the present value, i.e. the total amount that all future payments are worth now. In case of a loan, it’s the original amount borrowed.
  • Fv (optional) – the future value after the last payment is made. If omitted, the future value of the loan is assumed to be zero.
  • Type (optional) – specifies when the payments are due.

Overview of PMT Function

Now you need these steps to use this function to make a mortgage calculator.

  • Select cell D10 and put the following formula there before tapping Enter.


Mortgage Calculator Using PMT Function

2. Use Apply PPMT Function to Generate Mortgage Calculator

The basic syntax for the PPMT function is

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

Overview of PPMT Function

Here we have inserted another field, Period, with a certain value. Now follow the given steps.

  • Select cell D11 and put the following formula there before pressing Enter.


Mortgage Calculator Using PPMT Function

How to Create Loan Amortization Schedule in Excel?

Now that we know the basics of PMT and PPMT functions, we can use them to calculate the payment and principal for a loan amortization schedule. In combination with the IPMT function and arithmetic calculations, we can also find out the interest and end balance of a period. Thus, we can create a full loan amortization schedule in Excel.

First, we have created an amortization table where we put certain values such as Total Loan Amount, Loan Repayment Tenure, Payments Per Year, and Annual Rate of Interest.

Dataset to Calculate Amortization Schedule

  • We will calculate the Payment amount using the PMT function as well. For that, insert the following function in cell C11 and tap Enter.


  • Drag the formula to column C.

Payment Calculation Using PMT Function for Amortization Schedule

  • After that, to calculate the Principal, you need to use the PPMT function.
  • Select cell D11, put the following formula, and drag it down to the entire column.


Principal Calculation Using PPMT Function for Amortization Schedule

  • Now To calculate the interest part of each periodic payment, we will use the IPMT function.


  • The formula goes down to the column E.

Calculating Imterest Using IPMT Function for Amortization Schedule

To calculate the balance for each period, we will apply two different formulas.

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


  • For the second and all succeeding periods, sum up the previous balance and this period’s principal.


  • Finally, copy the formula down to the entire column as well.

Loan Amortization Schedule Calculation

It will take 24 months to pay off this loan.

That’s how you can create a loan amortization schedule in Excel.

Which Things Should You Remember?

You need to remember the following while applying the two examples mentioned above.

  • These examples will be applicable to compound interest.
  • You have to insert the required data to generate the mortgage calculator.
  • While using the PPMT function, you need to add an additional field name Period.

Frequently Asked Questions

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

After making payments, you can determine the remaining mortgage balance by deducting the principal paid back from the original loan amount. By eliminating the interest component of the payment from the entire payment, one can determine the amount of the principal repayment. You can calculate the principal component of payment using the PPMT function.

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

In Excel, you can use the RATE function to calculate the effective interest rate. The formula syntax is “=RATE(nper, pmt, pv, [fv], [type])” where “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, Excel is capable of managing mortgages with irregular times and payment amounts. A unique amortization schedule that takes into account the various payment amounts and due dates would need to be drawn up. Based on the actual payment amounts and schedule, adjust your calculations to determine the interest and principal components of each payment.


With Excel, you can handle different types of loans, including mortgages with fixed or variable interest rates and loans with irregular payment amounts or schedules. Excel allows you to customize your calculations based on specific loan parameters, enabling you to make informed financial decisions.

We have included two functions (PMT and PPMT) to make this article understandable to you.  As we have discussed two examples in this article to generate a mortgage calculator in Excel, we hope that this will help to serve your purpose.

If there is any confusion or doubt regarding this article, feel free to ask any questions.

Excel Mortgage Calculator: Knowledge Hub

<< Go Back to Finance TemplateExcel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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