Calculating Number of Payments, N

In this section, we consider calculating the number of payments (N). The formulas as given in the textbook, can be developed by using logarithm rules (if you are familiar with them). However, Excel has a function that we can use to easily calculate the number of payments.

NPER(rate,pmt,pv,fv,type)

where:

  • Rate is the interest rate per period.
  • Pmt  is the payment made each period.
  • Pv is the present value.
  • Fv is the future value. If fv is omitted, it is assumed to be 0.
  • Type  is the number 0 or 1 and indicates when payments are due.
    • 0 indicates at the end of the period
    • 1 indicates at the beginning of the period
    • If type is omitted, it is assumed to be 0

Example 1

One month from now, Maurice will make his first monthly contribution of $250 to a TFSA. Over the long run, he expects to earn 8% compounded annually. How many months will it take for the end-of-month contributions and accrued earnings to reach $100,000? (Source: Jerome & Worswick, 2020, Example 12.2A).

For this example we are given:

  • Nominal Rate = 8%
  • m_1 (compounding frequency) = 1
  • m_2 (payment frequency) = 12
  • PMT = $250
  • PV = 0
  • FV = $100,000
  • Type = 0

We can set up our spreadsheet to calculate the number of payments for both simple and general ordinary annuities.

Try recreating the spreadsheet above on your own. 

Click this link to see the completed spreadsheet: Calculating NPER (N) for any annuity – Template

For more information about the NPER function, see Microsoft Support

License

Icon for the Creative Commons Attribution-NonCommercial 4.0 International License

Using Excel in Business Math Copyright © by Lisa Koster is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License, except where otherwise noted.

Share This Book