How to Use Excel PMT Function (4 Quick Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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])

Introduction to Excel PMT Function

  • 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.

How to Use Excel PMT Function: 4 Examples

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)

Calculate Annual Payment Using the PMT Function

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.

Calculate Annual Payment Using the PMT Function

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)

Calculate Annual Payment Using the PMT Function

➤ Consequently, you will get a positive annual payment.


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.

Apply Excel PMT Function to Get Weekly, Monthly, Quarterly, and Semi-Annual Payments


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)

Use PMT Function to Determine Periodic Loan Payment (In case of Down Payment)

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.


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).

Create Loan Payment Calculator Applying PMT Function in Excel

  • Secondly, prepare a drop-down list for the periods (weekly, monthly, quarterly, semi-annual).

Create Loan Payment Calculator Applying PMT Function in Excel

  • Thirdly, make a drop-down list for type (when the payments are due).

Create Loan Payment Calculator Applying PMT Function in Excel

  • 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), "")

Create Loan Payment Calculator Applying PMT Function in Excel

  • 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),"")

Create Loan Payment Calculator Applying PMT Function in Excel

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.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo