The Excel PMT Function returns the regular and constant repayments for a loan or mortgage required to reduce the balance to zero, or an amount you specify. It’s based on a constant interest rate.
Excel PMT Function Syntax
| Syntax: | =PMT( rate, nper, pv, [fv], [type]) |
Excel PMT Function Arguments
| rate | Interest Rate | Per Payment Period. e.g. if interest is calculated monthly then this would be, roughly*, the annual interest rate (APR) divided by 12. | |
| nper | Number of Payments | e.g. for a 20-year loan with monthly repayments there would be 240 payments | |
| pv | Loan Amount | Principal or loan amount i.e. the total amount borrowed, excluding interest. | |
| [fv] | Final Balance (target) | Optional argument. e.g. if you want to pay off the loan you would enter zero. If omitted, zero is assumed. | |
| [type] | Payments Due | Optional argument. 0 = end of period, 1 = beginning of period. If omitted, zero is assumed. |
*For a more accurate calculation of the interest rate you can use the EFFECT Function, which takes into account compounding of interest when interest is calculated daily or monthly etc.
Excel PMT Function Example
Let’s say we borrowed $500,000 over 20 years at 12% interest per annum and we make monthly repayments at the end of each month. Our aim is to repay the loan in full at the end of 20 years.
Notice that the Excel PMT function returns a negative value because this represents payments being made from you to your lender. Alternatively, if you prefer the PMT function return a positive value you can enter the Loan Amount as a negative figure.
Excel PMT Function Calculator
Enter your own values into the PMT Function calculator (grey cells below) to try it out.
Tip: Remember, to enter interest rates with the percentage symbol, or their decimal equivalent. And if payments are monthly, divide your annual Interest Rate by 12.
Limitations
Caution: Banks and other lenders are notoriously complex in the way they calculate interest and repayments on loans. As a result, the repayment amount returned may differ from your bank. For example, there may be additional fees and taxes levied by your bank. The timing of the interest calculation and compounding may not be in line with the repayments etc.
Therefore, use the PMT function as an indicator, but your lender has the final say on what you owe them 😊
Related Functions
| EFFECT | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. |
| ACCRINT | Returns the accrued interest that gets paid periodically. |
| FV | Calculate the future value of an investment. |
| NPER | Calculates the number of periods to reach an investment goal. |