Excel PMT Function

The Excel PMT function has the syntax shown below.

PMT(Rate, Nper, PV, FV, Type)
Arguments used in the Excel PMT function
Pmt = Payment
Rate = Discount rate per period (i)
Nper = Number of periods (n)
PV = Present Value
FV = Future Value
Type = 0 or 1 depending on whether Pmt is at the end or start of a period

Use of the Excel PMT Function

The Excel PMT function is used to calculate the payment (Pmt) in time value of money calculations. For example, it can calculate the payments needed to clear a loan balance, the deposits to a savings account to grow to a future value, or annuity and annuity due payments from a lump sum investment

Payments Needed to Clear a Loan Balance

The Excel PMT function can be used to calculate the periodic payments necessary to clear a loan balance.

If a loan of 30,000 is to be repaid in equal monthly installments at the end of each month over a period of 5 years at a discount rate of 6%, then the monthly repayments are given by the Excel PMT function as follows:

Rate = 6%/12 = 0.5% per month
NPer = 5 x 12 = 60 months
PV = 30,000
FV =  0 (not used)
Type = 0 (payment at the end of each month)
Payment = PMT(Rate, Nper, PV, FV, Type)
Payment = PMT(6%/12, 60, 30000, 0, 0)
Payment = -579.98

It should be noted that the payment is negative as it represents a cash flow out.

The Excel PMT function shows that at a discount rate of 6%, it will take a payment of 579.98 at the end of each month for 5 years to clear the loan balance.

A similar calculation can be made if the payments are at the beginning of each month by setting the Type argument to 1. In this case the payment is given by PMT(6%/12, 60, 30000, 0, 1) = 577.10.

Loans with a Balloon Payment at the end

Sometimes loans are made on the basis that regular periodic payments will be made together with a final balloon payment at the end of the term.

For example, if the loan terms are the same as the previous example except that a balloon payment of 8,000 is made at the end of the term, then the Excel PMT function gives the regular periodic payment as follows.

Rate = 6%/12 = 0.5% per month
NPer = 5 x 12 = 60 months
PV = 30,000
FV =  -8,000 (negative as a cash flow out)
Type = 0 (payment at the end of each month)
Payment = PMT(Rate, Nper, PV, FV, Type)
Payment = PMT(6%/12, 60, 30000, -8000, 0)
Payment = -465.32

The effect of the balloon payment is to reduce the monthly payments to 465.32.

Again if the regular payments are at the beginning of each month the Type argument can be set to 1. Setting the Type to 1 has no effect on the balloon payment, this is always deemed to have been made at the end of the term.

Regular Deposits to Grow to a Given Future Value (FV)

The Excel PMT function can be used to calculate the monthly deposits needed to grow a savings account to a given future value.

For example, suppose you wished to have 1,000,000 in 20 years time and the discount rate on a savings account is 7%. The regular monthly payment needed to achieve this is given by the Excel PMT function as follows:

Rate = 7%/12
NPer = 20 x 12 = 240 months
PV = 0
FV =  1,000,000
Type = 0 (payment at the end of each month)
Payment = PMT(Rate, Nper, PV, FV, Type)
Payment = PMT(7%/12, 240, , 1000000, 0)
Payment = -1,919.66 

The payments are paid out by the investor to the savings account, they are therefore cash flows out and shown as negative. The 1,000,000 will be paid from the savings account to the investor so it is shown as a cash flow in (positive).

Again, if the payments are at the beginning of the month the Type argument should be set to 1.

If the savings account already had a balance before the payments started, then the PV argument can be used to include this. For example, if the account started with a balance of 50,000 then the payment needed would be given by the Excel PMT functions as PMT(7%/12, 240, -50000, 1000000, 0) = 1,532.01.

It should be noted that as the 50,000 is an amount which needs to be paid out by the investor to the savings account it is a cash flow out and is therefore negative,

Annuity Payments

The Excel PMT function can be used to calculate the annuity payments due from a given investment. An annuity is a series of regular payments at the end of each period.

If a lump sum of 500,000 is available to purchase an annuity for 15 years, then at a discount rate of 3%, the monthly annuity payment is given by the Excel PMT function as follows:

Rate = 3%/12
NPer = 15 x 12 = 180 months
PV = -500,000
FV =  0
Type = 0 (payment at the end of each month)
Payment = PMT(Rate, Nper, PV, FV, Type)
Payment = PMT(3%/12, 180, -500000,0, 0)
Payment = 3,452.91 

The lump sum to purchase the annuity is shown as a cash flow out, and the payment received from the annuity over 15 years are cash flows in and are therefore positive.

Annuity Due Payments

An annuity due is a series of regular payments made at the start of each period. The Excel PMT function can be used to calculate annuity due payments by setting the Type argument to 1. In the above example the annuity due payments are given as follows:

Rate = 3%/12
NPer = 15 x 12 = 180 months
PV = -500,000
FV =  0
Type = 1 (payment at the start of each month)
Payment = PMT(Rate, Nper, PV, FV, Type)
Payment = PMT(3%/12, 180, -500000,0, 1)
Payment = £3,444.30

Using the Excel PMT Function with Variable Arguments

Excel is at its most useful when the arguments can be varied without having to enter the Excel function each time a calculation is made. To do this a simple spreadsheet can be set up as shown below.

excel pmt function
Excel PMT Function

In this example spreadsheet, the variable arguments are entered in cells B4 to B8, and the Excel PMT function is entered at cell B11 as =PMT(B4,B5,B6,B7,B8). By changing any of the variables in B4 to B8, the payments can be recalculated without having to enter the Excel PMT function each time.

The Excel PMT function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

You May Also Like