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

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.

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

Where

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

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.

2. Use Apply PPMT Function to Generate Mortgage Calculator

The basic syntax for the PPMT function is

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.

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.

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

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

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

• The formula goes down to the column E.

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.

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.

Conclusion

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 Template | Excel Templates