To calculate a mortgage payment in Excel with a formula, you can use the PMT function. If you want to get the interest and principal amounts separately, you can use IPMT (interest component) and PPMT (principal component) functions.
Before we begin, I recommend that you download the Excel spreadsheet. It includes all the formulas used in this tutorial.
PMT is a built-in function that calculates an annuity's monthly or annual payment based on constant payments and a constant interest rate. An annuity is defined as a series of equal cash flows that occur at fixed intervals (monthly, quarterly, yearly). A mortgage is an example of an annuity.
The Excel formula to calculate mortgage payments can be written as:
=-PMT(annual interest rate/12, loan term*12, loan amount)
Note: If omitted, the future value and type arguments are set to 0 by default.
Using the annual interest rate, the principal, and the loan term, we determine the sum to be paid monthly. The formula, as shown above, is written in the following order:
The minus sign before the PMT function is needed since the formula returns a negative number. For the interest rate, we use the monthly rate (annual rate divided by 12), then we calculate the number of periods (360 months which is 30 years multiplied by 12 months). Finally, we insert the principal borrowed (the difference between the cost of the house and the down payment).
Now let's look at an example. To calculate the monthly payments, I have used the following arguments for the PMT function:
Note: the PMT function works only for a fixed-rate mortgage.
The total amount returned in cell F4 is $912.60 and includes the principal amount and the interest amount.
The example above only calculates the monthly payment amount, but you get no breakdown of principal and interest. If you want to build an amortization schedule, you need to use Excel formulas that calculate the monthly payment of interest and principal separately. You can achieve this by using IPMT and PPMT functions.
PPMT works a bit differently. Since the amount of principal paid changes based on the payment number, the function takes an additional argument (per). This is the number of the monthly payment. For example, if we calculate the principal payment for the first month of the second year, we will use 13 as the [per] argument.
I've used the following formula in cell F3 to calculate the principal payment for the first month:
=-PPMT(C3/12, 1, C4*12, C6)
The arguments used in the formula are:
Note: the minus sign at the beginning of the formula is needed to return a positive number.
As shown above, how much interest you pay actually depends on the payment number. The formula used in cell F5 to calculate the interest payment for the first month is:
=-IPMT(C3/12, 1, C4*12, C6)
The arguments used in the formula are identical to the ones used for the PPMT formula:
Note: the minus sign at the beginning of the formula is needed to return a positive number.
As you can see, the sum of principal and interest payments is the same as the amount obtained using the PMT function.
There are a few things to watch out for when working with Excel functions like PMT, PPMT, or IPMT:
The formulas from this Excel tutorial can be adjusted to also work for a personal loan with equal installments. Simply change the term of the loan and the down payment, and you should be good to go. In one of my next articles, I will also teach you how to create a loan amortization schedule in Excel.
If you find any info from this article confusing, write a comment, and I'll help you out as soon as possible. Also, I strongly suggest that you take the time to learn more about PMT, IPMT, and PPMT functions since they are often used in finance.
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?