How-To-Calculate-Mortgage-Payment-In-Excel-Someka-Blog-Featured-Image

How to Calculate Mortgage Payment in Excel? Easy Instructions and Examples

Excel Tips
Updated on January 24, 2024

In this article, we’ll explain how to calculate mortgage payment in Excel. Basically, we’ll provide you instructions both with PMT formula and also a ready-to-use template.

Table Of Content

1. Key Variables of Mortgage Loans
2. How to calculate mortgage payment in Excel with functions?
3. Ready-to-use Mortgage Calculator
4. Key Takeaways

A mortgage is essentially a loan specifically used for the purchase of real estate. When you take out a mortgage, a lender (like a bank) provides you with a certain amount of money to buy a home. In return, you agree to pay back this loan over a set period, typically spanning 10 to 30 years.

1. What are the Key Variables of Mortgage Loans?

Before we start on how to calculate monthly payments, it’s important the understand the key components in a mortgage credit agreement:

Loan Amount: This is the amount of money you borrow to buy your home.
Interest Rate: The lender charges you interest, which is a percentage of the loan amount. This can be a fixed rate or a variable rate.
Loan Term: It is the number of installments you will pay through your mortgage schedule.
Monthly Payment: This is the monthly installment that you will pay. It has two components: 1. Interest Payment, 2. Principal payment.
Repayment Schedule: This is the timeline over which you repay the loan.

So, understanding these basic elements of a mortgage is crucial before diving into the specifics of calculating mortgage payments in Excel.

2. How to calculate mortgage payment in Excel with functions?

Now, we’ll create a mortgage schedule from scratch. We’ll use PMT function of Excel to calculate our home loan credit payments.

Step 1: Prepare variables

Firstly, you should establish your loan assumptions.

We’ll input our Loan Amount, Interest Rate and Loan Terms here.

Note: In our example we’ll use annual interest rate and loan terms in months. You can adapt your calculations accordingly if you want to use monthly interest rates or different payment frequencies other than the monthly payments.

Step 2: Create mortgage schedule layout

Secondly, we’ll make a table for our mortgage schedule with Period, Monthly Payment, Interest Payment, Principal Payment and Remaining Balance headings.

We’ll make all the schedule calculations on this table.

Step 3: Calculate Mortgage Payment in Excel

Then, we’ll calculate monthly payments and the principal and interest split using Excel functions.

What is Mortgage Payment Formula in Excel?

The main mortgage payment formula in Excel is PMT function.

Actually, Excel has a bunch of built-in functions to calculate amortization payments for loans. We’ll also mainly use these functions to calculate our mortgage payments:

PMT Function: Calculates total monthly payment.
IPMT Function: Calculates the interest portion of each payment.
PPMT function: Calculates the principal part of each payment.

Total Monthly Payment = Monthly Interest Portion + Monthly Principal Portion

Calculate Monthly Payments:

So, we’ll use PMT function to calculate our total monthly payment:

=PMT(rate,nper,pv)

Rate: The interest rate for each period. If you have an annual rate and are making monthly payments, divide this rate by 12.
Nper: Total number of payments for the loan
Pv: Present value or the total amount of the loan

Assume, we borrow $500,000 with an annual interest rate of 8% and we’ll pay this mortgage in 10 years with monthly payments.

Here’s our monthly payment calculation:

Here we have used PMT function with Annual Rate/12,Loan Term in months and Loan Term to calculate our monthly payments.

So, we’ll see the result in the selected cell. Also, it’s important to note that the PMT function returns a negative number, representing an outgoing payment.

Calculate Interest and Principal Payments

If you want to see the monthly Interest/Principal Payment Split of your monthly payments, we’ll use IPMT function.

So, we’ll calculate interest for each payment with Excel IPMT function:

=IPMT(rate, per, nper, pv)

IPMT function has a very similar syntax with PMT function. Now we also add the per variable, which is the the period for which you want to find the interest and must be in the range 1 to nper.

Let’s continue with our example:

Similarly, IPMT formula also gives an negative number as a payment.

After calculating the Interest Payment portion, it’s very easy to calculate the principal part. Thus, We’ll simply subtruct interest payment from the total monthly payment:

Alternatively, you can directly calculate the Principal Payment with the PPMT function:

= PPMT(rate, per, nper, pv)

So, this PPMT function is very similar to the IPMT in terms of syntax.

Calculate Remaining Balance Calculation

As this is not a preset Excel function, we’ll calculate our monthly balances by subtracting the principal paid to date from the original loan amount. While using minus sign, we should also turn the negative principal payments to positive by multiplying by -1 coefficient.

Now your main calculations are completed.

Please always pay attention to absolute cell references in Excel calculations. Using dollar sign ($) in Excel is an important skill.

Finally, we’ll copy all the formulas in the first row until the end of our table, which will complete our mortgage schedule:

Step 4: Mortgage Loan Summary

Lastly, we’ll summarize the total amount paid over the course of the loan period at the end of the table.

Installment Amount = The total of the monthly payments.
Total Interest Paid = The Sum of Interest Rate Column.
Total Payment = The sum of all Interest and Principal Payments.

Now we have a clear overview about our mortgage credit. Therefore, we know how much we will pay in total, and how much of the total will go to the interests.

3. Ready-to-use Mortgage Calculator

If you do not want to spend your time creating a mortgage schedule from scratch, then you can use our template for a hassle-free calculation.

Download Someka Mortgage Calculator NOW!

Basically, this template consists of four main areas: 1. Assumptions, 2. Outputs, 3. Visual Charts, 4. Amortization table.

– This is a screenshot from Someka’s Mortgage Calculator Excel Template –

This is very easy to use this template:

Fill out your assumptions
Analyze your loan summary and charts
Print your mortgage schedule

So on the assumptions table, you will input:

Loan Amount
Interest Rate
First Payment Date
Payment Frequency
Loan Term
Balloon Payment (Optional)

Then, the rest of the template will automatically populate. You do not need to do any calculation manually.

Finally, if you prefer an online mortgage template, then you can also download our Google Sheets Mortgage Calculator template.

4. Key Takeaways: How to calculate mortgage payment in Excel?

In this article, we have explained how to calculate mortgage payments in Excel. So, let’s make a summary of key takeaways:

Firstly, Microsoft Excel is a powerful tool for financial calculations. And also with the flexibility, offline access, user-friendly layout it offers, many people prefer using Excel for mortgage calculations.
Secondly, you can use preset loan amortization functions in Excel to create a mortgage schedule from scratch. We have explained all the needed syntax and format knowledge needed for this.
But if you prefer a more easy and instant tool for your mortgage schedules, then you can download Someka Mortgage Calculator, available in Excel or Google Sheets formats.

Thus, with this ready tool, you’ll not bother yourself with formatting or calculations: