Scenario: A negative ROI even when the IRR is positive
In Investment analysis you use financial metrics to arrive at a better decision for your business need. You must analyze all of the metrics to determine the ones that works best for your requirement. When using multiple metrics, you might see situations where the two metrics seem to contradict each other. This example describes one such situation, where the Return on Investment (ROI) is negative (suggesting a problematic investment), but the Internal Rate of Return (IRR) is positive (suggesting a good investment). A common scenario where you might see a negative ROI with a positive IRR is in models in which there is a profit in the first time period, but there are losses in subsequent time periods, with the losses becoming higher in each subsequent time period. In such a scenario, you might see a negative ROI with a large, positive IRR.
Prerequisite
IRR is defined as the discount rate at which the Net Present Value (NPV) is zero. To compute NPV, you apply a discount rate to the result of subtracting the costs incurred during a time period from the benefits obtained during that time period:
where n is one minus the number of time periods in your model; r is the discount rate.
In the first time period, where i=0, the discount rate is not applied (because (1+r)^0 is 1). The denominator, (1+r)^i, increases in each successive time period, because as i increases, (1+r)^i increases largely. As a result, even if the numerator is large, the discounted (benefits(i) - costs(i)) is much smaller. In a scenario where there is a profit in the first time period (i=0), with increasingly large losses in subsequent time periods, with a large value of r (the discount rate), the denominator can grow fast enough to diminish what would otherwise be a large loss (in the numerator). If the profit in the first time period (which is not discounted, according to the NPV equation) covers the discounted losses in the subsequent time periods, such that the sum of the discounted values is zero (or close to zero), then that discount rate causes NPV to be zero, which makes it a correct IRR value.
1 A module with a time grid attribute configured with the sheets: High, Likely, Low, Actual with the same start and end dates for a period of five years
2 At least one cost stream and at least one benefit stream is created
3 The option Allow Investment Analysis is selected when configuring the time grid attribute
Example
This example shows how the IRR and ROI are computed. In this example, IRR is the discount rate at which NPV is zero.
1 Click the Settings icon , and then, from the Configure group, click Attributes.
2 Select the module that contains the time grid attribute that you have configured.
3 Click Add Attribute, and then select Float as the attribute type.
4 Enter the title as IRR, Alias as NP_IRR, Suffix as %, and then click OK.
The attribute IRR is listed in the list of attributes.
5 Click Add Attribute and select Integer as the attribute type.
6 Enter the title as ROI, Alias as NP_ROI, and then click OK.
The attribute ROI is listed in the list of attributes.
7 Click Modules and select your module that contains the time grid attribute, IRR and ROI attributes.
8 Enter these values in USD for benefit and cost streams:
Benefit
Cost
750.00
0.00
1000.00
2000.00
2000.00
7000.00
3000.00
13000.00
5000.00
30000.00
9 Click the Stats tab, and then check the value of ROI.
The ROI is shown as a negative value.
10 Click the Calculator tab, and then compute the IRR.
The value of discount rate will be approximately 3.16 (for IRR of 316%).
The high discount rate of 3.16 balances the discounted loss that incurs from the second year onwards. This value of NPV at discount rate of 3.16 is within epsilon of 0 (due to rounding errors) demonstrates that the IRR value is mathematically correct as it produces a NPV of zero.
, and then, from the Configure group, click Attributes.