Discounting cash flows with multiple discount rates

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.

Below is a series of cash flows with different discount rates per year. The early years have higher discount rates as there is more risk in this period (lets assume this is a construction phase).Using multiple discount rates to value a series of cash flows

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).

Click here to download the multiple discount rate spreadsheet example


XNPV and effective rate