How to calculate a mortgage payment in Excel
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.
What is the formula for calculating monthly mortgage payments?
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).
Calculate the monthly payments in Excel
Now let's look at an example. To calculate the monthly payments, I have used the following arguments for the PMT function:
rate = C3/12 (to obtain the monthly interest rate)
nper = C4*12 (we need the total number of payments)
pv = C6 (the present value is the principal that we borrow from the bank)
fv = 0 or omitted (by default, the future value argument is set to zero)
type = 0 or can be omitted (by default, the type argument is set to zero)
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.
Calculate the principal and interest payments separately
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.
Calculate the principal payment of a mortgage
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:
rate = C3/12
per = 1 (we want the principal paid for the first period)
nper = C4*12
pv = C6
I have omitted fv and type.
Note: the minus sign at the beginning of the formula is needed to return a positive number.
Calculate the interest payment of a mortgage
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:
rate = C3/12
per = 1 (we want the principal paid for the first period)
nper = C4*12
pv = C6
I have omitted fv and type.
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.
Things to remember when you calculate monthly payments
There are a few things to watch out for when working with Excel functions like PMT, PPMT, or IPMT:
be consistent when setting your arguments;
use the negative sign to convert the result to a positive number;
if your payments are made monthly, make sure to divide the annual rate by 12;
if your loan term is expressed in years and the payments are made monthly, make sure to multiply the loan period by 12;
the amounts returned by PPMT and IPMT are different based on the period number
What to do next?
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.