# How to Use the Excel PMT Function – 4 Examples

## Introduction to the Excel PMT Function

• Summary

The PMT function calculates the payment for a loan based on a constant interest rate. For instance, you have a 10- year home loan of \$110,000 with an interest rate of 3%. 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 The total number of payments.
pv Required The present value: the total value of all the loan payments at present (the original amount borrowed).
fv Optional The future value: the cash balance you have after the last payment. If this parameter is omitted, the future value is assumed to be zero (0).
type Optional It 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.

### Example 1 – Calculate the Annual Payment Using the PMT Function

You have a 5-year home loan with an interest rate of 3%. The loan amount is \$110,000. To calculate the annual loan payment:

Steps:

• Enter the following formula in C9.
`=PMT(C4,C5,C6,C7)`

The [type] argument is not declared. By default, the payment will be due at the end of the period. If your loan payment is due at the beginning of the period, enter 1 as argument.

You will get the annual payment:

The result will be in 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.

Note:

To see the loan amount as a positive number, enter a minus () sign at the beginning of the PMT formula:

`=-PMT(C4,C5,C6,C7)`

You will get a positive annual payment.

### Example 2 – Applying the Excel PMT Function to Get Weekly, Monthly, Quarterly, and Semi-Annual Payments

You have to adjust the rate and nper arguments, depending on the number of payments per year:

• Divide the annual interest rate by the number of payments per year.
• For the nper, multiply the number of years by the number of payments per year.

Calculate loan payments for different periods for a 7-year home loan with an annual interest date of 5% and a loan amount of \$250,000.

Steps:

• Enter the following formula in C10:C13.

The formula for the weekly payment is:

`=PMT(C4/52,C5*52,C6)`

52 is used for the 52 weeks in a year.

The formula for the monthly payment is:

`=PMT(C4/12,C5*12,C6)`

12 is used for the 12 months in a year.

`=PMT(C4/4,C5*4,C6)`

For payments on a quarterly basis, calculate 4 times per year.

For a semi-annual payment, the formula is:

`=PMT(C4/2,C5*2,C6)`

This is the output.

### Example 3 – Use PMT Function to Determine the Periodic Loan Payment (In case of Down Payment)

You have bought a car for \$30000 and as a down payment, you have paid 10% of the \$30000. You have to pay the loan within 5 years at a 5% annual interest rate. Calculate the monthly payment:

Steps:

• Enter the following formula in C10.
`=PMT(C6/12,C7*12,C4*(1-C5),C8)`

The price of the car is multiplied by (1-C5) which is C4*.9. You have to pay 90% of the price as a loan (as you paid 10% of the price as the down payment).

This is the output.

### Example 4 – Create a Loan Payment Calculator Applying the PMT Function in Excel

Consider a 5-year loan amount of \$110,000 with a 3% interest rate.

Steps:

• Enter the loan details in C4:C7 and prepare two lookup tables: Period (F5:G9) and Type (F11:G12).

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

• Create a drop-down list for type (when payments are due).

• Use the combination of the IFERROR and VLOOKUP functions and enter the formula in D8.
`=IFERROR(VLOOKUP(C8, F5:G9, 2, 0), "")`

The VLOOKUP function finds the value of C8 in the lookup table Period and returns the periodic value according to the drop-down selection. The IFERROR function hides any error.

• Enter the following formula in D9.
`=IFERROR(VLOOKUP(C9, F11:G12, 2, 0), "")`

• To find the semi-annual payment, enter the formula in C12.
`=IFERROR(-PMT(C4/D8,C5*D8,C6,0,D9),"")`

The PMT function calculates the loan payment. The IFERROR function hides errors.

You will see the semi-annual loan payment.

This is the output.

## Things to Remember

• The payment returned by the PMT function includes only principal and interest rate. It does not include fees, taxes, or reserved amounts.

## Common Errors of PMT Function

#NUM!:  the rate argument is negative or the nper value is zero (0).

#VALUE!:  one or more arguments are text values.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF