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
may 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 may 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.
- 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
- At least one cost stream and at least one benefit stream is created
- 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.
- Click .
- Select the module that contains the time grid attribute that you
have configured.
- Click Add Attribute and select Float as
the attribute type.
- Enter the title as IRR, Alias as NP_IRR,
Suffix as % and click OK.
The attribute IRR is listed in the list of attributes.
- Click Add Attribute and select Integer
as the attribute type.
- Enter the title as ROI, Alias as NP_ROI,
and click OK. The attribute ROI is listed in
the list of attributes.
- Click and
select your module that contains the time grid attribute, IRR and
ROI attributes.
- Enter the values in USD for benefit and cost streams as mentioned
in Table 1.
Table 1. A financial model with a negative ROI and
a positive IRR| Benefit |
Cost |
| 750.00 |
0.00 |
| 1000.00 |
2000.00 |
| 2000.00 |
7000.00 |
| 3000.00 |
13000.00 |
| 5000.00 |
30000.00 |
- Click the Stats tab, and check the value
of ROI. The ROI is shown as a negative value.
- Click the Calculator tab and 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.