Multiple Discount rates in NPV?

Free Preview

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.

To follow along go to 17.12

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 (let’s assume this is a construction phase).

Multiple Discount Rates

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 riskiness of 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 (so it is better to get money in year 3 at a factor of 0.66 than in year 2 at 0.64)

The correct way is to take the cumulative effect of the various discount rates into account. 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. So what we are doing is changing the FV to be the result of the previous year’s calculation.

In period 1 we are asking what is $1 worth if it takes a year to get it (0.80). For period 2 we ask what is 0.80 worth if it takes 1 year to get it at that years discount rate.

Multiple Discount Rates

So you will see that year 3 is worth less than year 2 which makes more sense (and is more correct). Note the answers are totally different. The wrong way gives a value of 1 422 in cell C7 and the correct way gives 932 in cell C13.

Back to: Financial Calculations and compound interest in Excel > Time Value of Money- Debt, NPV, IRR