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.

Different discount rate each year

Below is a series of cash flows with different WACC rates per year. The early years have higher WACC’s as there is more risk in this period (let’s 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 rate is applied against the period it is in. The problem here is that the cumulative impact of the prior periods (higher risk 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.

Correct way to model multiple discount rates

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

Finding errors in NPV Calculations

Financial models generally make use of NPV or XNPV functions. But NPV in particular has an issue that users regularly misunderstand and as a result the NPV can be more than 10% wrong. Have a look at the risk of NPV calculations.

XNPV and effective rate

Auditing Financial models