Spreadsheeting  Home Page

Using the NPER function in Excel

This page calls on Excel Help and a knowledge of finance!

 

The purpose of this page is to demonstrate the application of the NPER function that we find in Excel (and other?) spreadsheet packages. I wrote this page in response to a short dialogue I had with a gentleman on an Excel discussion list. I hereby acknowledge the help of Excel’s Help screen in providing the syntax and definitions that follow. However, I believe my explanation of how the function works is better than that found in the Help screen! Well, judge for yourself.

 

NPER tells you the number of periods needed for an investment to grow from x to y where x is the amount you have now (which could be zero or even negative!) and y is the amount you want to have in "n" periods' time.

                  

The Syntax of the function is                                                                                     

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

 

rate is the interest rate PER PERIOD

pmt is the periodical payment amount

pv is the present value of your investment: that is, what you have now                                                                            

fv is the future value of the investment: that is, what you want to have at the end of the final period                                           

type tells the function whether interest is paid at the beginning or end of a period: NORMALLY, in the real world, that is, this should be 0 and can safely be omitted

                                                                                                                            

There are 2 key elements to this function that should be highlighted                                                                       

                                                                                                                            

Firstly, the rate needs to be the interest rate per period.  So, if the investment is for a year and interest is paid monthly you should enter, for a 10% rate, 0.1 /12.  If you are dealing with an interest rate of 15% you would enter 0.15 /12 , assuming monthly interest settlements.  If you are dealing with daily interest payments, the normal case for banks for example, we would use 0.1 ÷ 365 for a 1 year investment.  (OK, so banks might really use 360 days, so use 360 if that's the case).                                                                   

 

Secondly, the payment needs to be entered as a NEGATIVE value; and if the pv is also negative, if you are starting out with a loan, for example, then it needs also to be a negative.

 

Let's imagine we have interest rates of 10% per year with a monthly payment amount to be made of £10,000, a present value of –£10,000 and an fv of £60,000                            

 

=NPER(0.1/12,-10000,10000,60000) = 6.8875 periods                               

                                                                                                                            

meaning that we need to make 6.8875 payments of £10,000 both to clear the debt of £10,000 and leave £50,000 for us to enjoy.

 

Can we prove this formula is working properly? Yes, let’s build up a table showing the opening balance and then add all of the transactions that follow: interest charges, repayment amounts and closing balances.

 

 

period

 

balance

at beginning

interest

factor

10% pa

amount

plus interest

payment

balance

at end

1

-10,000.00

1.0083

-10,083.33

10,000.00

-83.33

2

-83.33

1.0083

-84.02

10,000.00

9,915.98

3

9,915.98

1.0083

9,998.61

10,000.00

19,998.61

4

19,998.61

1.0083

20,165.25

10,000.00

30,165.25

5

30,165.25

1.0083

30,416.62

10,000.00

40,416.62

6

40,416.62

1.0083

40,753.41

10,000.00

50,753.41

7

50,753.41

1.0083

51,176.34

10,000.00

61,176.34

                                                                  

Working down the amount plus interest column, we can see that we need to make more than 6 but fewer than 7 payments of £10,000 to get to our target of a final positive balance of £60,000.

 

By interpolation, we can "prove" the value of 6.8875 periods: the result we get here is 6.8871 periods, calculated as follows:

 

6 periods  +

£60,000 – £50,753.41

= 6.8871 periods

£10,422.9282

 

Where does the 10,422.9282 come from? This is the total income for the period = 51,176.34 – 50753.41 + 10,000: that is the interest earned in the period plus the monthly investment of 10,000.

 

The value we get, of 6.8871 is very close to the value we found by using the NPER formula because we have used LINEAR INTERPOLATION to estimate it: so, it gives an approximate value ... it’s very close, though.

 

If you want greater accuracy than the linear interpolation method, plot the above table on a chart and have a close look! Like this … plot the period and balance at end columns on a line chart and see where the curve cuts the £50,000 future value point and you'll see how near it is to 6.8875 periods!

 

 

© Duncan Williamson

August 2000 revised August 2001

Revised 7 August 2002

with grateful thanks to Simon and Jan Tonge for helping me to improve this page

© Webmaster Duncan Williamson 2002