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.

Excel PMT Function November 6th, 2016Team

You May Also Like


Related pages


journal entries examples accountinggross profit margin formula excelhow to rectify errors in accountingformula for a perpetuityoverhead spending varianceformula for diminishing value depreciationfifo costing methodexample of single entry bookkeepingshipment terms fobexamples of revenue and capital expenditureaccrual journal entrycheque meaning and definitionleverage ratio examplesyield to maturity excel formuladefine amortization expenseaccounts excel templateformula of accounting equationobjectivity principle accountingcompany bookkeeping templateswhat is accounting cycle and stepsdepreciation slm methodadjusting entry for unearned revenuetrial balance exercise with answernon perpetual inventorychange in accounting estimate depreciationcost of good sold statement formatcalculate future value of annuitydebit and credit cheat sheetwhat is the normal balance of saleswhat is the normal balance of accounts payabledouble entry bookkeeping examplea company that receives an interest bearing note receivable willfifo perpetual inventory exampleformula for installment loanaverage number of days to collect accounts receivabledebt equity ratio formula with exampleshould i be an accountant quizhow to post journal entries to t accountsreorder point calculatordividends debit or creditpayroll quiz questions and answersaccounting cycle steps explanationunearned subscription revenuecalculate a perpetuitydouble entry of depreciationirr ratio formularoce ratio definitionaccounting for revolving credit facilitydirect labor efficiency varianceexcel cash receipt templateaccounts receivable collection period formulacapital lease journal entries lesseeoutstanding checks definitiongeneral ledger chart of accounts examplescash received from customers journal entryadjusting entries exampleprovision for bad debts income statementbad debt journal entrysmall business general ledgernet income to sales ratioaccounting adjusting entries rulescalculate the price of a zero coupon bonddebit credit journal entry examplessample nonprofit chart of accountsadvantages of cash bookjournal entry for invoicetax effect accounting journal entriesdouble entry ledger templateformula for continuous compoundingformula for cost of goods manufacturedtrade receivable turnoveraccounting objectivity principleis merchandise inventory a debit or creditbad debts journal entriesdouble declining balance depreciation exampleformula of leverage ratiocreditors turnover ratio formulabills receivable and bills payable