Page 4 of 7
Irregular time periods
The NPV function as supplied in most spreadsheets will only work with equal intervals.
It assumes that each cash sum specified is received at the end of a regular time period.
If you want to calculate the PV of a cashflow at unequal intervals you can always use the basic formula on each payment - that is discount each sum and find the total.
For example, if you receive payments of:
- $500 after 6 months
- $1000 at the end of one year
- $1500 at the end of the second year
- $2000 at the end of the third year
then the NPV can be calculated as shown in Figure 2.
Figure 2 - and irregular cash flow
The cashflow and the time received are entered normally in column A with fractional years used to express 6 months i.e. 0.5 of a year.
The present value of each cashflow is calculated by entering:
into C3 and then copying this down the column. Notice the the discount formula uses the interest rate stored in B3.
Finally to get the NPV enter the formula:
This first principles method can always be used to calculate the NPV. As long as you record the time that each sum is received using fractions if necessary then the NPV calculated will be correct.
An alternative to the direct approach is to reduce all of the payments to the smallest interval of time and use the NPV function. For example, in the cashflow used earlier the smallest interval is 6 months so we could record all of the cashflows in terms of a basic 6 month period.
Of course some of these cashflows would be zero but this doesn’t alter the calculation of the NPV.
You can see the result of doing this in Figure 3. You can see that the annual payments are simply the consequence of no payment half way through the year! The cashflow can now be reduced to its NPV using the NPV function in the usual way.
Converting irregular cash flows into regular.
The only question is what interest rate is appropriate.
You might think that as the rate is 6% per annum the appropriate rate for a half yearly cashflow is 6%/2.
This isn’t the case because it doesn’t take into account the different compounding periods.
The correct interest rate to use is the effective interest rate calculated from the annual rate. As explained in Chapter 3, the effective rate gives you an interest rate that can be applied over a different compounding period while still giving the same result as the original rate. The formula relating the two is simply
where m is the new compounding period measured in units of the original period. In this case m=.5 and the formula for the effective rate is:
which should be entered into B2. Now the NPV formula can be entered into B10:
and you can see that the result is the same as the first calculation.
Whenever you alter the period of an NPV calculation you must make sure that the interest rate that you are using is the appropriate one. As the NPV calculation is based on compound interest rates should be converted using the nominal to effective formula.