Excel NPER Function

The Excel NPER function has the syntax shown below.

NPER(Rate, Pmt, PV, FV, Type)
Arguments used in the Excel NPER function
Nper = Number of periods (n)
Rate = Discount rate per period (i)
Pmt = Periodic payment
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 NPER Function

The Excel NPER function can be used to calculate the number of periods in time value of money calculations including the following circumstances:

  • For a lump sum investment (PV) to grow to a future value (FV)
  • For an annuity payment (Pmt) to grow to a future value
  • For an annuity due payment (Pmt) to grow to a future value
  • For a loan balance (PV) to be cleared with regular payments(Pmt)
  • For a loan balance (PV) to be cleared with regular payments(Pmt) and a balloon payment (FV)

Number of Periods for a Lump Sum to Grow

The Excel NPER function can be used to calculate the number of periods it takes for a lump sum investment to grow from its present value (PV) to a future value (FV).

Number of Periods for a Lump Sum to Grow Example 1

If a lump sum of 50,000 is invested at the start of period 1 at a discount rate of 7%, how many periods will it take to grow to 92,000.

Rate = 7%
Pmt = 0 (not used)
PV = -50,000 (negative as cash flow out)
FV = 92,000 (positive as cash flow in)
Type = 0 (not used)
Number of periods = NPER(Rate, Pmt, PV, FV, Type)
Number of periods = NPER(7%, 0,-50000, 92000, 0)
Number of periods = 9.01

The Excel NPER function shows that it will take 9.01 periods for the investment to grow from 50,000 to 92,000 if the discount rate used is 7%.

It should be noted that PV and FV should have opposite signs. Excel is expecting an amount PV (50,000) to be invested at the start of the term (cash flow out or negative), and then expects the amount FV (92,000) to be returned at the end of the term (cash flow in or positive).

When using the Excel NPER function to calculate periods relating to a lump sum value, the Pmt and the Type arguments are not used, they can be left blank or set to zero.

Number of Periods for a Lump Sum to Grow Example 2

If the discount rate is 5% per year compounded monthly, calculate the number of months it takes for an investment to grow from 30,000 to 60,000.

Rate = 5%/12 monthly
Pmt = 0 (not used)
PV = -30,000 (negative as cash flow out)
FV = 60,000 (positive as cash flow in)
Type = 0 (not used)
Number of periods = NPER(Rate, Pmt, PV, FV, Type)
Number of periods = NPER(5%/12, 0,-30000, 60000, 0)
Number of periods = 166.70 months

Since the rate and compounding are monthly, the period is one month and the answer is 166.70 months.

Number of Periods for an Annuity to Grow

The Excel NPER function can be used to calculate how many periods it will take for an annuity (a series of regular periodic payments at the end of each period) to grow to a given future value.

Number of Periods for an Annuity to Grow Example

If an amount of 600 is invested at the end of each period and the discount rate is 4%, how many periods will it take for the investment to grow to 8,000.

Rate = 4%
Pmt = -600 (negative as cash flow out)
PV = 0 (not used)
FV = 8,000 (positive as cash flow in)
Type = 0
Number of periods = NPER(Rate, Pmt, PV, FV, Type)
Number of periods = NPER(4%, -600, 0, 8000, 0)
Number of periods = 10.90 periods

At a discount rate of 4%, it will take 10.90 periods for the annuity of 600 per period is grow to a value of 8,000.

In this example the argument PV is not used and can be set to 0 or left blank. The argument Type is used and is set to 0 (or can be left blank), and indicates that the annuity payment is at the end of each period.

Number of Periods for an Annuity Due to Grow

The Excel NPER function can be used to calculate how many periods it will take for an annuity due (a series of regular periodic payments at the start of each period) to grow to a given future value.

Number of Periods for an Annuity Due to Grow Example

If an amount of 600 is invested at the start of each period and the discount rate is 4%, how many periods will it take for the investment to grow to 8,000.

Rate = 4%
Pmt = -600 (negative as cash flow out)
PV = 0 (not used)
FV = 8,000 (positive as cash flow in)
Type = 1
Number of periods = NPER(Rate, Pmt, PV, FV, Type)
Number of periods = NPER(4%, -600, 0, 8000, 1)
Number of periods = 10.56 periods

At a discount rate of 4%, it will take 10.56 periods for the annuity due of 600 per period is grow to a value of 8,000.

In this example the argument PV is not used and can be set to 0 or left blank. The argument Type is used and is set to 1, and indicates that the annuity payment is at the start of each period.

The only change made in this example compared to the previous annuity example is that the Type argument has been set to 1 to represent payments at the start of the period. This has reduced the number of periods required since the payments from the annuity (600) are being invested one period earlier.

Number of Periods to Clear a Loan Balance

The Excel NPER function can be used to find the number of periods it takes a regular payment (Pmt) to reduce a loan balance (PV) to zero.

Number of Periods to Clear a Loan Balance Example

If a loan of 40,000 has a discount rate of 6% and periodic loan repayments of 5,000 at the end of each period, how long will it take for the balance to be cleared. The number of periods is given by the Excel NPER function as follows:

Rate = 6%
Pmt = -5,000 (negative as cash flow out)
PV = 40,000 (loan balance)
FV = 0 (not used)
Type = 0
Number of periods = NPER(Rate, Pmt, PV, FV, Type)
Number of periods = NPER(6%, -5000, 40000, 0, 0)
Number of periods = 11.22 periods

At a discount rate of 6%, it will take 11.22 periods for the loan balance of 40,000 to be reduced to zero with payments at the end of each month of 5,000.

In this example the argument FV is not used and can be set to 0 or left blank. The argument Type is used and is set to 0 (or can be left blank), and indicates that the annuity payment is at the end of each period.

Loan Payments made at the Start of a Period Example

The same calculation can be carried out with payments made at the start of each period by setting the Type argument to 1. In this case the Excel NPER function gives

Rate = 6%
Pmt = -5,000 (negative as cash flow out)
PV = 40,000 (loan balance)
FV = 0 (not used)
Type = 1 (payments at the start of the period)
Number of periods = NPER(Rate, Pmt, PV, FV, Type)
Number of periods = NPER(6%, -5000, 40000, 0, 1)
Number of periods = 10.35 periods

Balloon Payments at the end of a Loan Example

By using the FV argument to represent a cash outflow at the end of the term, the Excel NPER function can be used to calculate the number of periods to clear a loan balance taking into account a final balloon payment.

For example, if a loan of 40,000 has a discount rate of 6% and periodic loan repayments of 5,000 at the end of each period, how long will it take for the balance to be cleared assuming a balloon payment of 18,176.02 is to be made on the last day of the term. The number of periods is given by the Excel NPER function as follows:

Rate = 6%
Pmt = -5,000 (negative as cash flow out)
PV = 40,000 (loan balance)
FV = -18,176.02 (negative as cash flow out)
Type = 0
Number of periods = NPER(Rate, Pmt, PV, FV, Type)
Number of periods = NPER(6%, -5000, 40000, -18176.02, 0)
Number of periods = 7 periods

At a discount rate of 6%, it will take 7.00 periods for the loan balance of 40,000 to be reduced to zero with payments at the end of each month of 5,000, and a final balloon payment of 18,176.02.

Using the Excel NPER 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 nper function v 1.0
Excel NPER Function

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

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

Excel NPER Function November 6th, 2016Team

You May Also Like


Related pages


examples of t accounts debits and creditsconvert operating lease to capital leasethe balance of a control account in the general ledgersample general ledger chart of accountsdebt turnover ratio formulaprinciples of bookkeeping and accountingfuture value of lump sum excelreplenishing the petty cash fund requirespresent value in perpetuityhow to calculate manufacturing overhead allocatedjournal entry for accrual of expensesjournal entries for perpetual inventory systemdouble entry bookkeeping meaninghow to compute manufacturing overhead ratedeferred rent journal entryabsorption costing equationinterest receivable adjusting entryaccounting balance sheet calculatorfactory overhead accountingjournal entry for doubtful debtspayroll quizgross profit margin equationprepaid insurance expenseledger debitnotes payable vs accounts payablebad debts entrycost accounting excel templateshow to compute cost of goods manufacturedbeginning inventory equationtreatment of deferred tax assets in balance sheetfinal step in the accounting cyclesuspense accounts explainedvalue of annuity calculatoraccounting practice quizsales ledger double entryjournalizing notes payableaccounting cycle steps exampleoutstanding checks definitionfuture value annuity formulapresent value of bond calculatorhigh liquidity ratiogoodwill accounting treatmentfixed asset turnover definitiona total materials variance is analyzed in terms ofwhat is triple column cash bookgp percentage formulahow to record petty cash expensespetty cash entriesdefine amortization in accountingwhat is included in manufacturing overheadaccrued expense accountingprinciple of conservatism in accountingformula for contribution margin per unithow to treat disposal of fixed assetrecording bad debt expense journal entrybookkeeping spreadsheets for excelbank reconciliation excel templatejournal entry to record deferred revenueexamples of cash disbursementsdirect material price variance formulaexamples of lifoending merchandise inventorydefine accounts receivable ledgerhow to calculate future value of annuityrules of bank reconciliation statementsuspense account in accountingaccrual double entrydepreciation slmmanufactoring overheadaccumulated depreciation accounting entryeffeciency ratiosadjustments for unearned revenueaccounting special journalsaccounting unearned revenuewhich journal would adjusting entries be foundbank statement reconciliation templateintangible fixed assets amortisation policy