Making a mortgage calculator is key to your financial success. No one wants to calculate their monthly payments because they feel manually calculating is a boring task. Anyone who has taken out a mortgage loan knows very well that the monthly payment is one of the most important factors.
But for some people calculating monthly mortgage payments is a mystery. But if you are one of them then don’t get stressed! We are here to help you out.
Excel can do the calculation for you. if you are trying to manage your finances and figure out exactly how much your mortgage payment will cost then this guide is for you.
Excel is a great application software for calculating monthly payments. Once, you get to know how you can perform this task then you can realize it is not so much difficult.
If you can handle the task in Excel then you can come in handy in various situations.
What is the PMT function in Excel?
A PMT function is an in-built function in Excel and it is categorized as a financial function. This function is used for calculating the payment of loans, and mortgages, based on the constant interest rate, and constant payment.
The syntax of the PMT function is:
PMT(rate, per, pv, fv, type)
The arguments of the functions are as follows:
- Rate: The interest rate of the mortgage
- Nper: the total number of payments period of the mortgage
- Pv: The present value, the value of the mortgage or loan
- Fv: future value, this value is optional, and most of the time the value is=0
- Type: Whether the payment calculation is done at the beginning or end of the period.
How to use the PMT function in Excel for calculating mortgage payments?
Now, you get to know what the PMT function is and what the arguments referred to. Let’s take a look at how you can use the PMT function in Excel for calculating mortgage payments.
Here, we are going to explain to you how to calculate the mortgage payment using Excel, the most used method with the help of an example.
Suppose, you have taken out a loan of £10,000 with terms of 60 months and at an interest rate of 5%. In this case, the monthly payment will be £188.71.
You can calculate the value in Excel using the PMT function as follows:
The formula is:=PMT(5%/12,60,-10000,0,0)
The result will be -188.71. That is the monthly payment of your mortgage.
You can note that the interest rate is entered as 5%/12, because the interest rate we have put as an annual percentage rate (APR). We want to calculate the monthly interest rate.
The negative sign in front of the result determines that the payment is an outgoing payment. In simpler words, you are making a payment on the loan every month.
Mortgage calculator by month:
To calculate the amount that you have to pay each month on the mortgage loan, you will have to convert the interest rate and value of the period.
Convert the period into months:
The conversion of the period into the month is very easier. You only need to put the period and multiply it by 12.
=Number of years*12
Convert the annual interest rate in months:
This conversation is a little harder part of the calculation. There are 2 situations:
- The interest rate is calculated at the end of the period
- The interest is running throughout the period.
If the interest rate is calculated at the end of the period then the calculation is easier:
=annual interest rate/12
If the interest is calculated over the entire period then the formula is a little complex.
= (1+annual interest rate)^(1/12)-1
Result of the monthly value:
We are replacing the previous formulas in the arguments of the function:
=PMT ((1+5%)^(1/12)-1;60*12;10,000)
Benefits of PMT function in Excel:
The PMT function is an easier way to calculate the monthly payment for a loan or a mortgage in Excel. This function is also useful function to create an effective budget and make a financial plan.
If you want to calculate how much you can afford on a house then you can use this PMT function for calculating the payment amounts of different loans, and mortgages based on interest rate and loan terms.
Conclusion:
Here we have prepared a step-by-step guide for you on advanced techniques for calculating monthly mortgage payments in Excel. These steps are simple and easy to follow.
So, if you are worried about figuring out the value of your monthly mortgage payment then Excel have you covered.
KWD- Calculating Mortgage Payments in Excel, mortgage calculator excel
