Hosne Ara
The PMT function is a Financial function in excel. Interestingly, the name PMT stands for ‘payment’. We can use this function to get the periodic payment for a loan. Besides, you can use this function to get the loan payment for different payment frequencies (weekly, monthly, quarterly, etc.). In this article, I will discuss the PMT function in detail along with some examples.
Download the Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Introduction to Excel PMT Function
- Summary
The PMT function calculates the payment for a loan based on a constant interest rate. For instance, you have applied for a 10- year home loan of $110,000 with an interest rate of 3%. Now, using the PMT function, you can calculate the periodic payment for the loan.
- Syntax
PMT(rate, nper, pv, [fv], [type])
- Arguments
| Argument | Requirement | Explanations |
|---|---|---|
| rate | Required | The interest rate of the loan. |
| nper | Required | This is the total number of payments per loan. |
| pv | Required | This is the present value i.e. the total value of all the loan payments at present. In other words, it is simply the original amount borrowed. |
| fv | Optional | The future value i.e. the cash balance one wants to have after the last payment is done. If this parameter is omitted, the future value is assumed to be zero (0). |
| type | Optional | This specifies when the payment is due. If this parameter is 0 or omitted, the payments are due at the end of each period. If it is 1, the payments are due at the beginning of each period. |
- Return Value
The PMT function returns the loan payments as a number.
- Available in Version
Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.
4 Examples to Use Excel PMT Function
1. Calculate Annual Payment Using the PMT Function
You can calculate the annual loan payment by applying the PMT function. For example, you have taken a 5-year home loan with an interest rate of 3% and the loan amount is $110,000. Now, if you want to calculate the annual loan payment for the home, here are the steps involved.
Steps:
- First, type the following formula in Cell C9.
=PMT(C4,C5,C6,C7)
Here, in this formula, we did not mention the argument [type]. Previously, in the function argument section we have mentioned that if this argument is omitted, excel assumes that the payment will be due at the end of the period. Moreover, most of the loans are of these type. In case your loan payment is due at the beginning of the period, put 1 as the argument.
- As a result, you will get the annual payment for your home loan.
The result of the formula will be in the currency format, red color, and rounded to two decimal places. By default, the result will be enclosed in parenthesis, which means the loan amount is negative (As the loan payment will be subtracted from your bank account).
Note:
➤ If you want to see the loan amount as a positive number, simply put a minus (–) sign at the beginning of the PMT formula. Such as:
=-PMT(C4,C5,C6,C7)
➤ Consequently, you will get a positive annual payment.
Read More: How to Use FV Function in Excel (4 Easy Examples)
2. Apply Excel PMT Function to Get Weekly, Monthly, Quarterly, and Semi-Annual Payments
Using the PMT function you can calculate periodic loan payments. Such as, you can calculate a loan payment on a weekly, monthly, quarterly, or semi-annual basis. However, to do that, you have to adjust the rate and nper arguments, depending on the number of payments per year. More elaborately,
➤ In the case of rate, divide the annual interest rate by the number of payments per year.
➤ On the other hand, for nper, multiply the number of years with the number of payments per year.
Now, assume a 7-year home loan with an annual interest date of 5% and a loan amount of $250,000. According to the above-mentioned discussion, we will follow the below steps to calculate loan payments for different periods.
Steps:
- Initially, type the below formulas in Cell C10 to Cell C13.
The formula for the weekly payment:
=PMT(C4/52,C5*52,C6)We have used 52 here because there are 52 weeks in a year.
Similarly, the formula for the monthly payment:
=PMT(C4/12,C5*12,C6)Here, 12 is used as there are 12 months in a year.
=PMT(C4/4,C5*4,C6)While doing payments on a quarterly basis, we do pay 4 times per year.
Finally, the formula for semi-annual payment is:
=PMT(C4/2,C5*2,C6)This is because we do pay twice a year on a semi-annual payment period.
- As a consequence, you will get the following results.
Read More: How to Use PV Function in Excel (3 Examples)
Similar Readings
- Use IRR Function in Excel (4 Examples)
- How to Use XIRR Function in Excel (3 Methods)
- Use YIELD Function in Excel (4 Effective Examples)
- How to Use AGGREGATE Function in Excel (13 Examples)
- Excel Financial Function Tips – NPV vs XNPV
3. Use PMT Function to Determine Periodic Loan Payment (In case of Down Payment)
Often we buy things on down payments. In that case, a certain percentage of the price is paid initially. So, in such cases, to calculate the loan payment you have to subtract the paid price from the original price. For instance, you have bought a car for $30000 and as a down payment, you have paid 10% of the $30000. Subsequently, you have to pay the loan within 5 years at a 5% annual interest rate. Now, we will calculate the monthly payment for this car using the PMT function.
Steps:
- First, type the following formula in Cell C10.
=PMT(C6/12,C7*12,C4*(1-C5),C8)
Here, the price of the car is multiplied by (1-C5) which is C4*.9. That means you have to pay 90% of the price as a loan (as you paid 10% of the price as the down payment).
- Finally, you will get the amount of monthly payment from the formula.
Read More: How to Use RATE Function in Excel (3 Examples)
4. Create Loan Payment Calculator Applying PMT Function in Excel
Till, now in this article, we have calculated loan payments individually for a certain period. But, if you have several options of loans and you want to compare those loans, then you can create your own loan payment calendar. Let’s assume a 5-year loan amount of $110,000 with a 3% interest rate. So, here are the steps involved to make a loan calendar.
Steps:
- Firstly, enter the loan details in Cell C4 to Cell C7 and prepare two lookup tables: Period (F5:G9) and Type (F11:G12).
- Secondly, prepare a drop-down list for the periods (weekly, monthly, quarterly, semi-annual).
- Thirdly, make a drop-down list for type (when the payments are due).
- Next, using the combination of the IFERROR and VLOOKUP functions type the below formula in Cell D8.
=IFERROR(VLOOKUP(C8, F5:G9, 2, 0), "")
Here the VLOOKUP function finds the value of C8 in the lookup table Period and brings the corresponding period value according to the drop-down selection. Later, the IFERROR function hides any error if it occurs.
- Similarly, type the following formula in Cell D9.
=IFERROR(VLOOKUP(C9, F11:G12, 2, 0), "")
- Now, the calendar is ready. Then, to find the semi-annual payment where the loan will be due at the end of the period, type the below formula in Cell C12.
=IFERROR(-PMT(C4/D8,C5*D8,C6,0,D9),"")
Here, the PMT function calculates the loan payment. And the IFERROR function hides errors if any input values are not defined.
- After that, upon entering the above formula, you will get the semi-annual loan payment for the loan.
- In conclusion, you can calculate the monthly payment for the same loan using the calendar.
Things to Remember
While using the PMT function, keep the below points in mind.
- The payment returned by the PMT function includes only principal and interest rate. However, it does not include any fees, taxes, or reserved amounts that might be associated with the loan sometimes.
- Be certain that you are consistent about the units you use for specifying rate and nper. For example, if you make monthly payments on a five-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 5*12 for nper. On the other hand, if you make annual payments on the same loan, use 12 percent for rate and 5 for nper.
Common Errors of PMT Function
Although using the PMT function is much simpler, sometimes errors might occur.
#NUM!: This occurs when the rate argument is negative or the nper value is zero (0).
#VALUE!: It occurs when one or more arguments are text values.
Conclusion
In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
