ExcelInsider Team
Taking loans is a tough financial choice that a lot of people have to make these days. Before deciding on which loan to select, it is mandatory to calculate the mortgage payment and whether it will be possible to pay it on time. In this article, we will learn how to calculate a monthly mortgage payment in Excel.
➤ Insert the formula in your desired cell:
=PMT(B2/100/12, C2*12, -A2)
➤ Replace B2 with the annual interest rate (without percentage %), C2 with the loan term, and A2 with the loan amount.
➤ Press Enter to complete the calculation.
That was an oversimplification of the formula. If you want a full explanation and want to do the calculation even without the help of a dedicated function, read the whole article. Download the workbook we used for this tutorial so that you can follow the methods with us.
Using the PMT Function to Calculate Monthly Mortgage Payment
To demonstrate the calculation of monthly mortgage payments, we have a workbook here with different amounts of loans, interest rates, and loan terms. We will calculate the monthly mortgage payment using the PMT function. This function is built into Microsoft Excel, and you can use this function without having to understand the underlying calculation steps. Here is how we can use the function:
➤ Create another heading for Monthly Payment in column D.
➤ Write the PMT formula in D2 cell and press Enter:
=PMT(B2/100/12, C2*12, -A2)
➥ The second parameter is the loan terms in years. We have to multiply it (C2) by 12 to get the monthly rate. Finally, the third parameter is the loan amount (A2), and we have to put a minus (-) sign before it because it is a loan.
➤ Select D2:D13 cells. From the Home tab, go to Editing > Fill > Down to automatically fill all of the rows in the D column.
Manually Calculating Mortgage Payment
If you truly want to learn how to calculate a mortgage payment, you should learn the actual formula for the calculation. This time, we have written the interest rate in actual decimals for percentage so that you can learn both of the methods on how to calculate this.
➤ Take a helper column for the mortgage payment.
➤ Write this formula in the D2 cell:
=A2*(B2/12)*(1+(B2/12))^(C2*12) / ((1+(B2/12))^(30*12)-1)
= P{r(1 + r)^n} / {(1 + r)^n – 1}Here, we replaced P with A2 as the loan amount, r with B2/12 as we have to divide the yearly rate by 12 to get the monthly rate. Finally, n is replaced with C2*12 because we have to multiply the years by 12 for monthly calculation.
➤ Find the handle that looks like the plus (+) sign at the bottom right of D2 cell, and drag it to the D13 cell to autofill the column.
Frequently Asked Questions
Is a mortgage calculated monthly or yearly?
Mortgages are calculated monthly. Wherever you take the loan will require a monthly payment from you to complete the repayment.
Are mortgages calculated daily?
Mortgages are not paid daily. However, the interest might be calculated on a daily basis, depending on what institute/person you are taking the loan from. You can pay the mortgage on a monthly basis, with the interest calculated for the whole month. If you become late, the daily interest might increase if they use a compounding rate.
What is the formula for calculating a mortgage rate?
When you take a loan, the lender will provide you with the mortgage rate on a yearly basis. To get the monthly rate, use this formula in Excel:
=A1/12
Replace A1 with the yearly rate in decimal format. We are dividing the yearly rate by 12 to obtain the monthly rate.
Is the mortgage rate compounded monthly?
Whether the rate will be compounded or not depends on your lender. If they are using compounded interest, the amount you have to pay might increase on a daily/monthly/quarterly/yearly basis. Check the frequency with your lender to know the exact information.
What is the formula for simple interest?
Here is the formula for simple interest in Excel:
=A2*B2*C2
In this formula, A2 is the reference to the principal, B2 is the reference to the annual interest rate (in decimal), and C2 is the reference to the time in years.
How to calculate monthly compound interest?
You can use the following formula to calculate monthly compound interest:
= (B1 * (1 + (B2/100)/12)^(12*B3)) – B1
Replace B1 with the loan amount, B2 with the interest rate ( in plain numbers ), and B3 with the number of years.
Wrapping Up
Mortgage payments can be tough to pay. To plan properly for the payments, it is important to calculate the monthly mortgage payment. In this article, we have learned how to calculate the monthly mortgage payment in excel. We hope that we were able to help you a bit. If you have any questions, leave them below for us to review.
