How to cope with: IRR shortcomings

In order to make a decision, managers are used to the Net Present Value (“NPV”) in Excel. Using the NPV they can easily compare 2 or more project. But NPV is most of the time used in conjunction with the in-famous Internal Rate of Return (or “IRR”).

To make things clear:
- NPV and IRR are related but NOT equivalent.
- Basically the IRR is the rate for which the NPV is equal to 0.

This function is soooooo sexy because:
- It is easily understandable as it gives a percentage…
- It gives the impression that picking the project with the greatest IRR is the best thing to do…

BUT, the IRR knows many flaws making its use is more tricky that just calling the function in Excel…

Just to name a few problems:
- Compared to a normal IRR (1): if the Cash Flows are all positive OR negative over the life of the project you cannot calculate the IRR, (2)
- If the Cash Flows change sign over the life of the project you cannot calculate the IRR as there will be multiple IRRs,(3)
- The result is just a percentage that doesn’t give you an idea of the importance of the project (4)
- IRR takes into consideration that you will reinvest ALL Cash Flows over the life of the project at the Discount Rate AND will not be charged for negative Cash Flows.

So now we know that the IRR does wonders in certain scenarios but that it can be misleading. What’s next?

Modelling made easy using Monte Carlo simulations

A while ago I had to build a model for a Real Estate project. I had spent a bit of time looking for information on the market, on the transaction etcetc. Structuring the deal seemed pretty straightforward until I wanted to compare different scenarios: how would my NPV, IRR… react to an Interest Rates hike and an increase of the cost of the land by 10%? What would happen to the profitability of the project only in the case of an increase of the land cost but with constants Interest Rates. I was doomed! I had to compare scenarios…

Let the nightmare begin:

Whatever financial model you are building in excel you use a deterministic approach. All your assumptions are fixed once and for all like carved in stone. If you want to assume different parameters (let’s say an interest rates hike for instance) you will have to deal with quite a few new worksheets and then compare the results. This leads to a few problems:

- It’s a time consuming task and time is a luxury you don’t have,
- You’ll inevitably duplicate variables and data,
- You’ll get the spreadsheet to be really heavy and slow,
- You’ll be the only one able to read it (well at least for the next 2 weeks or so…),
- You’ll loose track of your variables and data.

So to make it short: it’s not easy and you are more than likely to make mistakes in the process. Think that you’ll at least want to run 3 scenarios and you are going to die of hunger and thirst working on this financial model. Using VBA programming could help but I wish you good luck here…

How can Monte-Carlo save your life?

Now we know that there are problems when you want to compare scenarios using a deterministic model in Excel. What shall we do to make our life easier? Switch from a deterministic model to a stochastic model would be quite a good idea. In other words: introduce uncertainty in your spreadsheets.

For instance:

Deterministic approach using a single variable:
- Interest Rates currently are 7%,
- Create your model using this as a variable,
- Once the model is created assume that the rate can change to 9%,
- Copy/Paste this model assuming the Interest Rates are now 9% (or using some conditional formating to alleviate the pain),
- Do your best not to make mistakes when comparing the results.

Stochastic approach using a single variable:
- Interest Rates are 7%,
- Create the model using this Interest Rates as a variable,
- Once the model is created assume that the Interest Rates follow a Normal Distribution with a Mean of 7% and a Standard Deviation of 10%,
- Run 10,000 simulations
- Get your probability of your model to be successful along with a series of useful charts

What’s the big difference?

- In the first case you assume that the world is either black or white: there are only 2 scenarios possible: Interest Rates are either 7% or 9%,
- You have to build up your own charts
- You multiply the risks or making mistakes in the process

- In the second case you get 10,000 simulations (compared to 2 as mentionned above…),
- You get the probabilities of reaching your targets,
- You get charts,
- It’s easy to understand

The process seems easy and actually it is!

Why is it easy?

For at least two reasons:

- Because you are not required to develop the script that will run the simulations,
- Because you don’t need to be an expert in Statistics and Probabilities to understand the results,

Indeed: you will have to focus mostly on 3 things:

- Build a model as close as possible to reality,
- Define the role played by uncertainty in your model,
- Run the simulations.

In the next article we’ll go through an example using excel and an add-in you can download free of charge.