PMT Function Excel – How to Use it (With Examples)

If you’re creating a loan amortization schedule or allocating a personal budget, you might find the need to calculate the payment amount of a certain principal. For this, Excel has an amazing financial function called “PMT.”

While Excel users mostly opt for the PMT function to calculate the payment due on a loan, you could also use it to evaluate the savings amounts for future expenses. One of my favorite parts about using this function is you can create a payment calculator with it. 

PMT Function Arguments

PMT function returns the payment amount for a loan in a fixed interest rate and period. The function arguments are:

=PMT(rate, nper, pv, [fv], [type])

• Rate: Loan Interest Rate
• Nper: Number of Loan Payment
• Pv: Present Value or Principal amount
• [Fv]: Future Loan Value
• [Type]: Payment Due
  • 0: Payment Due at End of Period
  • 1: Payment Due at the Start of the Period

The PMT function arguments in the [] brackets are optional. 

Examples of PMT Function in Excel

Example 1: PMT Function With Present Value

If you need to find out the payment due for loans, mortgages, timely credit card debts, etc., use the Present Value (PV) in the PMT function arguments. 

Let’s say Amanda took a $25,000 personal loan from Jade with an annual interest rate of 5.7% for 3 years. Amanda wants to find out the payment amount on a different timely basis weekly, monthly, quarterly, semi-annual, and yearly so that she can estimate her budget.

To find out, first let us input the information in the cell below. 

Since all of the information is in yearly annual, it’s pretty simple to calculate the annual payment amount. 

For this, we will enter the formula as

=PMT(E4, E5, E3, 0)

In the above formula, E4 is our annual interest rate, E5 nper, E3 principal amount. Since we have passed down 0 as a formula argument, it’ll calculate the payment due at the end of the period. The formula returned (9,300.88) in cell E3. So, Amanda needs to pay 9,300.88 amount yearly for the loan.

Now, let us find out the payment amount for different periods. Before you begin, quickly check out how we calculate the Rate and Nper value for each period first. 

It’s solely up to you if you wish to calculate the nper and rate value before using the formula. But, here we will be calculating them in the PMT formula itself below. 

Example 2: PMT With Future Value

You can also use the PMT function to calculate the payment of future value (FV). I use the PMT function with the FV argument whenever I want to allot a sum of money in my savings account for upcoming expenses or events. 

Suppose, I want to save money for a family vacation which is four years from now. The vacation will cost me around $10500. The annual interest rate for savings is 1.8%. I want to find out how much money should I allocate semi-annually (every 6 months) to save a total of $10,500. 

For this, I’ll first input this information in the cell value. Then, to calculate, I will enter the PMT function as

=PMT(B3/2, B4*2, 0, B2)

In the formula, I have entered 0 as the Present value. So, the formula resulted in ($1,271.71). This means that I need to accumulate that amount semi-annually for four years to save $10,500 for family vacations. 

Create a Payment Calculator Using the PMT Function

We’ve covered the examples of using the PMT function above. But, did you know that you could also create a PMT calculator?

PMT calculator is especially helpful if you find yourself calculating the Loan Payment more often. This way you wouldn’t have to keep using the PMT formula and calculate everything from scratch.

Step 1: Add Drop-down List

Here, we will add two drop-down lists for the Payment Due and Payment Period.

1. Firstly, enter all the information in cells(Loan, Year, Interest Rate).
   
2. Now, enter Payment Due in a new cell. Then, select the cell next to it. Navigate to Data Tab > Data Validation.
   
3. On the Data Validation window, expand the drop-down for Allow and choose List.
   
4. On Source, enter End of Period, Beginning of Period and hit OK.
   
5. Again, enter the Payment Period in another cell. Select a cell next to it and insert a drop-down list. From the Data Tab, click Data Validation.
   
6. On the window, stay on the Settings tab. Pick List for the Allow menu. 
   
7. Then, type in Weekly, Monthly, Quarterly, and Semi-Annual in the Source field. Click OK.
   

Step 2: Create a Lookup Table

Firstly, let us create a Lookup Table for the Payment Due. Enter a heading in the new cell. Then, enter the Payment Due type in the cells like in the given picture. My lookup table is from G4:H5.

Now, insert the lookup table of the Payment Period heading. Here, I have entered the Period name and the time period in numbers. My lookup table is from G8:H12. 

Step 3: Calculate Payment Due and Payment Period

To return the value of periods and payments due, let us use the VLOOKUP and IFERROR functions nested together. As an example, I will return end of the period and Quarterly. 

Step 4: Calculate Payment Using PMT

Finally, in this step, I will calculate the PMT. But, before that, I will enter =C8 & " " &"Payment" in cell B10 to link it with the Payment Period cell. I did this to make the heading in cell B10 dynamic.  

Now, to calculate Quarterly Payment, I will enter the PMT formula. 

=IFERROR(-PMT(C5/D8, C6*D8, C4, D7), "")

The formula returned 2281.30539. In the formula, we’ve entered the – (minus) sign before the PMT formula to return the value in a positive number. 

Now, whenever you choose a different Payment Period in the drop-down list, you’ll automatically have that value. 

Avoid These Errors in the PMT Function