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.