The tutorial shows how to use PMT function in Excel to calculate the annuity for a loan or the cash flows for investments based on a fixed interest rate, number of payments, and the total loan or investment amount.
This applies to the following Microsoft Excel versions: Excel for Microsoft 365, Excel 2021, 2019, 2016, 2013, 2010, 2007, and 2003.
Before you begin, I recommend downloading the Excel spreadsheet I've used to create this tutorial. It makes it much easier to follow along.
PMT is a financial function that returns the periodic payment for a loan based on a constant interest rate, the number of periods, and the loan amount.
PMT is the abbreviation for payment, which makes the function name easy to remember.
The PMT function uses the following syntax:
=PMT(rate, nper, pv, [fv], [type])
Note: If omitted, the fv and type arguments use the default value of 0.
Let's look at a simple PMT formula and calculate the future payments for a loan, assuming constant payments and a constant interest rate.
We'll use an example for a house that costs $200,000, with a down payment of $30,000 and a loan amount of $170,000. The mortgage loan has a period of 30 years and an annual interest rate of 5.00%, and payments are due monthly.
Using the Excel file provided for this tutorial, you can write the PMT formula in 6 steps:
Since the fv and type arguments were omitted, Excel used their default value of zero. The minus sign at the beginning of the formula is used to convert the amount to a positive figure.
The same function can be used to write formulas that calculate periodic payments for a mortgage loan, a car loan, or a student loan. As long as the interest rate is constant, the PMT function can be used to determine the loan payment.
Depending on the payment frequency, you will have to convert the rate and nper arguments. The following table shows how to easily convert from annual interest rate and annual payments to weekly, monthly, quarterly, and semi-annual payments:
|weekly||annual interest rate / 52||years * 52|
|monthly||annual interest rate / 12||years * 12|
|quarterly||annual interest rate / 4||years * 4|
|semi-annually||annual interest rate / 2||years * 2|
To calculate the amount of a periodic payment for a $10,000 loan with a 6.00% annual interest rate and duration of 5 years, you can use one of the following PMT formulas:
=-PMT(6%/52, 5*52, 10000)
=-PMT(6%/12, 5*12, 10000)
=-PMT(6%/4, 5*4, 10000)
=-PMT(6%/2, 5*2, 10000)
=-PMT(6%, 5, 10000)
Note: The balance after the last payment (i.e., the fv argument) is assumed to be $0. Payments are due at the end of each period, so the type argument is omitted.
If you want your PMT formula to work properly you need to take into account the following:
If your Excel PMT formula is not working or returns an error message, there are a few things to investigate:
The formulas from this Excel tutorial return the total value of a payment (includes principal and interest). If you are looking for a way to split the interest and principal, two financial functions can help you achieve this: the PPMT function and the IPMT function.
As always, if you are having difficulties using the PMT function, please leave a comment, and I'll reply as soon as possible.
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?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.