The investment project
A company is deciding whether to invest in new assets that cost $1,000,000. Management expects the investment to generate $250,000 of annual profits over the next 6 years. The cash inflows associated with the first 3 years are certain. Starting from the 4th year, annual profits are subject to an increasing uncertainty: ±10% in Year #4, ±20% in Year #5, and ±30% in Year #6.
The aim of this exercise is to determine the investment’s IRR, defined as the value of the discount rate that results in a Net Present Value (NPV) equal to zero. Another objective is to assess the effect of cash inflows uncertainties on the IRR. The figure below illustrates the steps we will go through in order to achieve these two objectives.
We will start by creating an irr@u workbook, defining the project’s cashflows and calculating its IRR (Point Estimate). We will introduce uncertainties on cashflows, in a second step, and assess their impact on the project’s IRR (Monte-Carlo Simulation).
Creating an irr@u workbook
Pretty straightforward, as a process:
- Login to @LabAix;
- Head to https://labaix.com/app/irr;
- Hit “Create a new workbook”;
- Specify a name for your project, as well as the applicable time and currency units;
- Hit “Create”.
Defining the project’s cashflows
To add a cashflow definition, click on the $+ button of the page’s toolbar, or simply hit the + key of your keyboard.
You will have to define the value of your cashflow (positive, if it is a cash inflow, and negative if it is a cash outflow) and the year at which it is incurred.
You can optionally associate a color with the cashflow (to distinguish it from other cashflows), mark it as uncertain, and document it, by providing information about the cashflow, and describing the underlying assumptions.
Once you have defined your cashflow, you can preview it by unchecking the “Edit” checkbox, and add it to your project’s list of cashflows by clicking on the “Save” button.
⇓ ⇓ ⇓
After defining each of the project’s cashflows, you should be able to list them as indicated in the illustration below. The initial investment is a negative cashflow and its value is put between parentheses. Annual profits are positive. Let’s now calculate the project’s IRR.
Evaluating the project’s IRR (Point Estimate)
To evaluate the project’s IRR, you just need to navigate to the “Point Estimate” tab of the page’s toolbar, as shown in the figure below. Our project’s IRR is 13 % annual, which means that, as long as the discount rate is lower than this value, the project’s NPV is positive.
Now we are going to introduce uncertainties on the three last cashflows, propagate them, and assess their impact on the project’s IRR.
Attaching uncertainties to project’s cashflows
Let’s attach a 10% uncertainty to the cashflow at Year #4. To do that, the corresponding definition has to be modified:
- From the cashflow list’ tab, click on the “Pencil” icon;
- Check the “Edit” checkbox, mark the cashflow as uncertain, set the uncertainty to 10%, and then hit “Save”.
You will see, now, an error bar in the graph illustrating the cashflow.
⇓ ⇓ ⇓
⇓ ⇓ ⇓
⇓ ⇓ ⇓
Follow the procedure described above to introduce a 20% uncertainty on the cashflow at Year #5, and a 30% uncertainty at Year #6.
At the end of this process, the three last cashflows will be marked by a light blue background in the cashflows listing table.
You will also notice the presence of three error bars in the project’s cashflow profile graph, shown in the “Point Estimate” tab.
Propagating uncertainties and assessing their impact on the project’s IRR (Monte-Carlo Simulation)
To propagate uncertainties and assess their impact, you need to:
- Navigate to the app’s “Monte-Carlo Simulation” tab;
- Specify the probability law according to which uncertain input variables will be distributed (between their lower and upper values);
- Run the simulation and explore its results.
In the example below, a neutral scenario was simulated. According to this scenario, the three last cashflows, marked as uncertain, follow a uniform distribution where all values — between the lower and the upper limit — are equally likely. The resulting IRR follows a (quasi) normal distribution centered around ~ 13% with a standard deviation of ~ 1%.
The table below indicates the probability associated with each IRR range.
|IRR Range in % (percent)||Probability in ‰ (per mille)|
Probabilities are calculated by dividing the number of simulations leading to an IRR within a given range — you can get it by hovering over the graph’s bars — by the total number of successful calculations, which is 1000 in our case.
This Monte-Carlo Simulation shows that the impact of cashflow uncertainties on the project’s IRR is a ~ 1% variation around a central ~ 13% value (about two-thirds of the one thousand simulations lead to an IRR between 12 and 14%). In all cases, the IRR cannot take a value lower than 10%.