One of the most commonly used financial functions is the payment function – the PMT() function.
This formula replicates the mathematical equation below:
Instead of using the mathematical equation, the Excel formula uses a much simpler list of arguments.
=PMT(interest rate, number of periods, loan principal, [future value], [start or end])
Required PMT() Formula Arguments
Number of Periods – This is the number of periods that the loan is compounding over. This is not the number of years unless the loan is one that compounds on an annual basis.
So, if you have a 5-year loan that compounds monthly, the correct number of periods is 60.
Interest Rate – The rate that is paid every period. Just like with the number of years, you can’t put an annual rate on a loan that compounds monthly! If you had a loan that was at 6% APR and you used 6% with monthly installments, this rate would effectively be 72%!
To correct for this, we divide the annual rate by the number of annual installments. This means that a 6% APR monthly is 0.5%, but normally this should be reflected as rate/periods – just in case you have a rate that is more difficult to calculate.
Loan Principal – This is the amount that all future cash flows are worth today. In the case of a loan, it is how much you are borrowing.
Optional PMT() Formula Arguments
Most loans don’t use optional arguments. These arguments are more often used in other scenarios where payments are being used to target a specific value in the future (like a retirement nest egg).
Future Value – This represents a target value; the amount that will be available after making payments over time.
Beginning or End – This argument is used to override in case payments are made at the beginning of a period instead of at the end of a period. This is more common in a savings scenario as opposed to a loan.
Using the PMT() Formula
Mortgage Payments
In this case, there is a 30 year, $500,000 loan at 4% interest rate paying and amortizing monthly.
The payment formula breaks down as follows:
=-PMT(B2/12,B3*12,B1)
There are 2 things to notice on this PMT() formula:
- There is a negative sign before the PMT() formula. This is because the formula is saying that you need to pay $2,387.08 per month for 30 years to equal $500,000 today. By adding the negative, it shows the payment as a positive.
- The annual interest rate is divided by 12, and the number of years is multiplied by 12. There is no way to prorate interest rates and periods by month, and you’ll almost always see the term in years and rate as an annual figure.
Retirement Savings
When calculating retirement savings, you will start with zero and attempt to determine how much needs to be saved at the beginning of each year for 40 years with a 6% interest rate to reach $1 million.
This formula breaks down a bit differently:
=-PMT(B2,B3,-B1,B4,B5)
Some of the things to notice on the savings scenario:
- There is still a negative needed before the PMT() formula. You are still paying $6,095.79 at the beginning of every year to reach the $1 million objective.
- The starting amount has a negative in the formula. In the PMT() formula, it would treat that number as an amount that needs to be paid back over time (like a loan). By negating this number, it allows a positive number to be added as a starting balance.
- The term TRUE is used instead of using the number 1 for the beginning of the period. This is more readable when working with the formula.
The PMT() formula is one of the more powerful and useful formulas in the Excel financial formula set. Enjoy using PMT()!