Personal Finance

Understanding NPER function in Excel

Anand Kalyanaraman | Updated on November 20, 2019 Published on November 20, 2019

This picture used for representational purpose only

Short for ‘Number of Periods’, NPER can help one know how long it will take for an investment to grow to a desired corpus

Until now, we have seen how the RATE function in Microsoft Excel can be used to calculate the return on investment, how the FV function can help find out how much your investment will grow to, how the PV function tells us the lumpsum to invest to accumulate a desired corpus, and how the PMT function can arrive at the amount to invest regularly to accumulate a desired corpus.

Now, what if you want to know how long it will take for an investment — lumpsum or regular — to grow to a desired corpus? The NPER function can be useful here. NPER is short for ‘Number of Periods’.

On the Excel screen, this function can be found using the command ‘fx’ and under the category ‘Financial’.

Here’s an example of how NPER can be used. Say, you plan to invest ₹1 lakh in a cumulative (compounded annually) fixed deposit that offers 8 per cent per annum and want to redeem it when it becomes ₹2 lakh. How long should you hold the investment?

Screenshot 1: Lumpsum investment, annual compounding

 

Lumpsum investment

The manual way of finding out would be to fit in the numbers in the compound interest formula: Amount = Principal*[(1 + Rate)^Period], and work out the period. So, 200,000 = 100,000*[(1+8%)^Period)]. The answer is 9 years, but it is a cumbersome calculation.

The easier way of figuring out the ‘period’ would be to use the NPER function. The NPER function has five arguments or fields (Screenshot 1).

Rate is the interest rate per period. Here, we enter the interest rate that the investment offers. In this case, the fixed deposit gives 8 per cent which is compounded annually, so we enter ‘8%’.

Pmt is the payment or investment made in each period. Since the investment in the example is a lumpsum and is not made at regular intervals, we can leave this field blank or enter ‘0’. Pv is the Present Value. Here, we enter the sum invested, that is, ‘100000’. Note that since the money invested is an outflow, we put the minus sign before 100000. Fv is the Future Value. Here, we enter the expected value of the investment, that is, ‘200000’. As this is in the nature of an inflow, we can put the plus sign or do without one. Finally, in Type, enter ‘1’ if the investment is made at the beginning of the period. If the payment is made at the end of the period, we can enter ‘0’ or leave the field blank. Here, we enter ‘1’ though it does not matter in case of an lumpsum investment.

That’s it.

The formula result gets readily displayed as 9.00. That is, it will take 9 years for ₹1 lakh to grow to ₹2 lakh at 8 per cent per annum compounded annually.

Now, in the above example, what if the annual interest rate is 8 per cent but is compounded every quarter, as is the case with most bank fixed deposits? In this case, in the Rate column, we must enter the effective annual interest rate (8.24 per cent), taking into account the quarterly compounding. The effective annual rate of 8.24% is arrived at thus: ((1+8%/4)^4)-1). There is no change in the other inputs. The formula result is 8.75 years. Note that with the increase in the frequency of compounding and the effective annual interest rate, the period to grow the money to the desired corpus reduces. Alternatively, in the Rate column, enter the quarterly rate, that is 2% (8%/4). The other fields remain the same. The formula result will be displayed as 35 quarters, which is the same as 8.75 years.

Rules of thumb

Along with the NPER function, it’s handy to know about some interesting rules of thumb.

The Rule of 72, for instance, is a quick way to estimate the approximate number of years in which your money will double at a given rate of return; just divide 72 by the annual rate to arrive at the approximate number of years.

For instance, at 8 per cent annually, your investment should double in about 9 years (72/8).

Likewise, if your money doubles in nine years, the effective annual rate is about 8 per cent.

This will help you not get carried away by advertisements claiming ‘doubling of money’.

Similarly, the rules of 114 and 144 help you find the approximate period in which your money will triple and quadruple, at a given rate of return.

For instance, at 8 per cent annually, your investment should triple — that is, grow from ₹1 lakh to ₹3 lakh — in about 14.25 years (114/8), and it should quadruple — that is, grow from ₹1 lakh to ₹4 lakh — in about 18 years (144/8). These answers are very close to the NPER answers, which will be accurate to the decimal.

Screenshot 2: Regular investment, monthly compounding

 

Regular investments

The NPER function can also help us find the period in which regular, constant investments such as a recurring deposit or a mutual fund SIP would grow to a desired corpus at an expected rate of return. Say, you invest ₹5,000 at the beginning of each month in an SIP that is expected to earn about 8 per cent per annum and you want to accumulate ₹2 lakh. How long will it take?

Using the NPER function (Screenshot 2), enter ‘8%/12’ that is, the expected monthly return in the Rate field; enter ‘-5000’, which is the monthly SIP outflow amount in the Pmt field, put ‘0’ in the Pv field or leave it blank; enter ‘200000’ in the FV field, and ‘1’ in the Type field since the investment is made at the beginning of each month.

The formula result is 35.366, that is, it would take these many months. Divide this by 12 to arrive at the number of years (2.94 years). That is, it will take 35.36 months (2.94 years) to accumulate ₹2 lakh with a monthly investment of ₹5,000 that is expected to earn 8 per cent per annum.

In the coming weeks, we will see the use of the IRR and XIRR functions. These can help us find out returns when the regular investment sums and/or periodicity of regular investments are different.

Published on November 20, 2019
  1. Comments will be moderated by The Hindu Business Line editorial team.
  2. Comments that are abusive, personal, incendiary or irrelevant cannot be published.
  3. Please write complete sentences. Do not type comments in all capital letters, or in all lower case letters, or using abbreviated text. (example: u cannot substitute for you, d is not 'the', n is not 'and').
  4. We may remove hyperlinks within comments.
  5. Please use a genuine email ID and provide your name, to avoid rejection.