Calculating a mortgage payment in Microsoft Excel is really easy to do. This article will walk thru the steps needed to set up the calculation of the monthly loan payment. In the end, you will learn how to calculate a mortgage payment with Excel.
Excel has a number of financial functions built in. Namely, a function called PMT() that used to calculate the payment for a loan for a fixed amount of time with a constant or fixed interest rate.
Just a Few Steps
In the next few steps we’ll set up the basic template to calculate a mortgage payment with Excel.
Start Microsoft Excel and get to a blank workbook to begin. This example will assume that you started in the very upper left corner of the workbook in cell A1.
Start Microsoft Excel
This can be any desktop version or the online version.
Start Microsoft Excel and get to a blank workbook to begin. This example will assume that you started in the very upper left corner of the workbook in cell A1.
Starting in cell A1, enter the text into the first set of cells. Adjust the width of column A so that the labels fit.
Set up Your Fields
We’ll use these few fields to run the mortgage payment calculator. Use the labels below.
Loan Amount
Interest Rate
Loan Duration
Loan Payment
Starting in cell A1, enter the text into the first set of cells. Adjust the width of column A so that the labels fit.
You can choose the different value types for each of the cells by clicking in the cell, and then clicking the button in the ribbon that matches the type.
For currency, look for the “$” button. For percentage, look for the “%” button.
To increase the number of decimals, look for the button with the “.00” and an arrow pointing to the LEFT.
Set the Formatting for Each Cell
For cell B1, choose currency.
For cell B2, choose percentage. AND increase the number of decimals showing.
For cell B3, there’s no need to change anything.
For cell B4, choose currency.
You can choose the different value types for each of the cells by clicking in the cell, and then clicking the button in the ribbon that matches the type.
For currency, look for the “$” button. For percentage, look for the “%” button.
To increase the number of decimals, look for the button with the “.00” and an arrow pointing to the LEFT.
The colors you see in the image above will appear as you type in the formula.
You’ll see #NUM! in the cell at first after you press Enter. It’s OK. That will all change in the final step.
Enter the Formula to Calculate a Mortgage Payment
=PMT(B2/12, B3*12, -B1)The formula here needs to go into cell B4, right next to the Loan Payment label you entered.
The colors you see in the image above will appear as you type in the formula.
You’ll see #NUM! in the cell at first after you press Enter. It’s OK. That will all change in the final step.
In this example, the monthly principal plus interest payment for a $450,000 mortgage at 4.5% over 30 years is $2,280.
Add Your Info
The Loan Amount is the amount you would finance. Or to make it simple let’s just say it’s the cost of the home.
The Interest Rate is the annual interest rate. The formula you entered above divides it by 12 to get to a monthly cost.
The Loan Duration is the number of years for the loan. The formula you entered above multiplies the number by 12 to get to the number of months for the loan.
In this example, the monthly principal plus interest payment for a $450,000 mortgage at 4.5% over 30 years is $2,280.
After you have gone thru the above you have the basic mortgage payment calculation for a fixed interest rate loan.
Other Considerations for How to Calculate a Mortgage Payment in Excel
It Really Needs to be PITI
So far, the calculator you have set up is calculating the monthly principal plus interest payment. This is the “PI” of “PITI“. Most mortgage payments consist of the principal and interest, plus property taxes and homeowner’s insurance. These last two items are the “TI” of “PITI”: taxes and insurance.
Most often, the principal and interest payments are the majority of the overall monthly payment. But say you’re getting a smaller loan on a home that has a high value. In that case you may find that your property taxes and homeowner’s insurance are a much larger part of your mortgage payment.
Look Out for PMI
Another acronym! PMI stands for Private Mortgage Insurance. Basically, PMI comes into the picture if the amount of your loan is going to be more than 80% of the value of the home you are buying. From the example above if the home’s value was $450,000, any loan for more than $360,000 would require PMI. The PMI component is in addition to any PITI part of your mortgage payment. If you start out with a mortgage that includes PMI in the payment, and the home you bought appreciates in value over time enough you may be able to remove the PMI part of your mortgage payment.
Part II
In an upcoming post we’ll extend how to calculate a mortgage payment with Excel to factor in your guestimate of the annual property taxes as well as homeowner’s insurance. We’ll add in PMI too.
