Many thanks to those of you who took the time to complete my NPV modelling challenge #4. And special thanks to those that had the bravery to go public with their answers.
I received 40+ completed responses.
I know you will be dying to find out who was selected by the supercomputer for the special prize, but please bear with me for a few moments for a discussion of the results.
I enjoyed going through the solutions and I was surprised at the variation in responses.
The chart below shows the distribution of your answers: NPV along the x-axis; % frequency down the y-axis.
Most results were in the €80m – €100m range with some outliers. The highest NPV was €709m and the lowest was minus €69m.
Reasons for the variation in results:
Base Date – Getting the base date wrong can really mess up an NPV. Applying a whole year’s discounting to the first semi-annual period leads to an understatement of around 4%.
Real vs. Nominal – In calculating an NPV it is important that you understand whether the discount rate you are using includes the impact of inflation (“nominal”) or has general inflation stripped out of it (“real”). Are the cash flows consistent with the rate? In this challenge, the cashflows calculated were nominal (including inflation) and therefore it was appropriate to use a nominal discount rate.
Discount rate period – the rate quoted is an annual rate whilst the cashflows are semi-annual. In this instance, applying an annual rate to semi-annual cashflows gives a very low NPV.
Effective annual rate vs. Simple annual Rate – this was the most interesting issue for me.
You might have reasonably assumed that the 8% discount rate was quoted on an annual basis. But note I was not very helpful here.
The resolution of the model (and therefore the cashflows) was more transparent: semi-annual.
You had to make a decision as to how to convert from an annual rate to an equivalent (semi-annual) periodic discount rate.
I was not very helpful here at all.
There are two bases on which annual rates tend to be quoted:
- 1. Simple Annual; and
- 2. Effective Annual Rate
And I didn’t tell you which one to assume.
Simple rate: To get a semi-annual periodic rate from a rate quoted on a simple annual basis, you can divide by 2. In the debt markets, this convention is known as 30/360.
Lots of people used this approach in answering the challenge.
Effective Rate: With an effective annual rate (known as an EAR), you should decompound to find the equivalent periodic rate.
In my solution, I keep the effective annual rate at 8% annual but apply it to part periods: 0.5, 1, 1.5 etc. Because the discount factor formula (1 / (1 + r) ^ n) is also based on de-compounding, I get the same result as I would if I had de-compounded the rate itself.
And note that my part periods are calculated with reference to day counts. My calculation is based on actual days in a 6 month period.
It’s the debt markets in particular that use simple annual rates as a quotation convention. For NPV calculations, it is more likely that your starting point will be an EAR. The XIRR function in Excel is designed to return an EAR.
The NPV impact of simple vs compound is c. €5m and was the main cause of difference in the solutions submitted.
All of this begs an interesting question: I wonder how many decision makers who use NPV as a key measure appreciate the subtlety of the above and define how they want their NPV calculated with sufficient clarity?
To invest or not to invest?
The project gives a strong positive NPV at the required discount rate, and therefore, on this measure alone, the decision should be to invest. However, as all good modellers know, it would not be appropriate to base a decision on a single metric without a consideration of the wider deal.
Some of you made valid comments and gave some good reasons not to invest. These included:
- 1. Long payback period – Consideration should be given to the investment time horizon. The long payback period was mentioned by some as a reason not to invest. In practice, this kind of deal would often be funded with project finance which, through leveraging cheaper-than-equity sources of debt, might enable equity investors to achieve a return much sooner.
- 2. Risk – In theory, the level of risk of the project should be factored in to the discount rate applied. In practice, this can be hard to achieve and the rate often driven more by investor expectations than a meaningful assessment of an individual project.
It would be sensible to run a range of sensitivities through the model to assess the impact on the NPV of flexing key parameters. For example, in my model, a drop of 25% in production or power prices would soon turn project’s NPV negative. But we don’t know how likely this is to happen.
So I am going to sit on the fence and say ‘insufficient information’ to make an informed investment decision.
I was surprised at the variation in the results submitted. It highlighted to me the scope for misinterpretation and error in the calculation of something as apparently simple as an NPV.
Our suggested answer is €90.96m. Please find our solution here.
The lucky winner, as selected at random by our complex excel based algorithm is Andrew Shiaka of Barclays Africa. Congratulations Andrew, I’ll be in touch to sort out access to your prize.
Thanks again for taking the time, the next challenge will be out soon: be prepared.