Discounting cash flows with multiple discount rates
As financial models become more sophisticated users are starting to look at discounting the cash flows with multiple discount rates. However, we have noticed during our financial modelling training courses that this is often done incorrectly.
The incorrect way to obtain the discount factor is shown in the first block. In this case the discount rate is applied against the period it is in. The problem here is that the prior periods are ignored. So in period 3 the calculation would effectively assume a 15% discount rate for 3 years instead of a discount rate of 25%, then another 25% and then the 15%. If you look at the first two yellow cells you will see the impossible situation of cash flows being worth more if you receive them a year later.
The correct way is to take the cumulative effect of the various discount rates. The easiest way to do this is simply to use the PV function and make one year calculate based on the prior year’s result. Now you will see that year 3 is worth less than year 2 which makes more sense (and is more correct).