If you have a mortgage loan or if you are planning to get one then calculating the mortgage payment might become a routine for you. Calculating monthly mortgage payments is not an easy task and there are many ways you can calculate the monthly payment. You will be happy to know that you can easily calculate the monthly mortgage payment with an excel formula. Below I will be demonstrating how you can calculate the monthly payment in Excel using the formula. Let’s explore!
How to Calculate Mortgage Payment Using Excel Formula
The process of calculating monthly mortgage payments using an excel formula is very simple. First, you have to list some information and data on the excel sheet. Check the below sheet –
Now, if the value of column B is changed then the monthly mortgage payment will be changed and it will recalculate automatically. Here the PMT function is used and with this function, you can calculate any monthly payment of mortgage that has fixed periodic payments, fixed interest rate, and equal cash flows spaced equally in time. So, in order to calculate the monthly payment with PMT, you have to provide an interest rate, loan term, loan amount, etc. Without these values, you won’t be able to calculate the monthly payment of a mortgage.
How to Create a Mortgage Calculator with Excel
Creating a mortgage calculator with Excel is not that difficult. If you have basic knowledge of Excel then you can easily create a mortgage calculator. Moreover, you can also follow the below steps and create a calculator for yourself.
Step – 1: Open Microsoft Excel
If you don’t have Excel installed on your computer then you have to install Microsoft Excel on your computer first. Moreover, you can also use Outlook’s online Excel extension instead of Microsoft Excel. But in that case, you have to create an Outlook account. Once you have Excel on your computer move to the next step.
Step – 2: Open a Blank Workbook
First, open the Excel on your computer. Then from the available options click on the New and then click on the “Blank Workbook.” It will open a new Excel spreadsheet.
Step – 3: Create the “Categories” Column
In the “A” column of the spreadsheet, you have to insert the categories. You have to add the following categories:
- Loan Amount $
- Annual Interest Rate
- Life Loan (in years)
- Number of Payments per Year
- Total Number of Payments
- Payment per Period
Step – 4: Insert the Value
In the “B” column of the spreadsheet, you have to enter the values in the respective field.
- Loan Amount $: The total amount of the loan
- Annual Interest Rate: Percent of interest arte that you have to pay as interest
- Life Loan (in years): Total amount of the time to pay off the loan
- Number of Payments per Year: Total number of times you have to make payments in a year
- Total Number of Payments: Total number of payments you have to make in the loan term
- Payment per Period: This is the value of the amount that you have to pay per month
Step – 5: Calculate the Monthly Payment
In this step, we will use the PMT formula to calculate the monthly mortgage payment. You have to use this formula –
“=PMT(Interest Rate/Payments per Year, Total Number of Payments, Loan Amount,0)”
According to the above screenshot, the formula would be –
=PMT(B2/B4,B5,B1,0)
So, this is the calculator that you can use to calculate your monthly mortgage payment. When you change the value of the loan amount, interest rate, and loan term, you will have a different result in the “Payment per Period” field. With this calculator, you will be able to calculate the monthly payment of a mortgage that has a fixed interest rate, fixed loan amount, and term.
FAQs about Mortgage Payment Excel Formulas: How to Create a Mortgage Calculator with Excel
What is the Excel formula for calculating mortgage payments?
The excel formula that you have to use to calculate the mortgage payments is “=PMT(Interest Rate/Payments per Year, Total Number of Payments, Loan Amount,0)”
What is the mathematical formula for a mortgage payment?
The mathematical formula for mortgage payment is: M = P [ i(1 + i)^n ] / [ (1 + i)^n – 1]. Here –
- M = Total monthly payment
- P = The total amount of your loan
- I = Your interest rate, as a monthly percentage
- N = The total amount of months in your loan term
What are the 10 formulas in Excel?
The 10 formulas in excel are –
- =SUM
- =MAX & =MIN
- =IF
- =TRIM
- =CONCATENATE
- =TODAY
- =PROPER
- =EVEN & =ODD
- =EOMONTH
- =RANDBETWEEN
How to calculate the total interest cost of a mortgage loan?
You can easily calculate the total interest cost of a mortgage loan. What you have to do is subtract the initial loan amount from the total cost of your loan. The remaining value is your total interest cost of the mortgage loan.
References:
https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20
https://www.wikihow.com/Create-a-Mortgage-Calculator-With-Microsoft-Excel
Last Updated on August 7, 2022 by Ana S. Sutterfield
Magalie D. is a Diploma holder in Public Administration & Management from McGill University of Canada. She shares management tips here in MGTBlog when she has nothing to do and gets some free time after working in a multinational company at Toronto.
