Cost Benefits Analysis Example - Free Download | Page 7
4.5,
2613
votes
Please vote for this template if it helps you.
M M U
C o s t B e n e f i t A n a l y s i s t o o l k i t ( v 2 )
Page
7
Now, using IRR, we assume the 3% discount rate might well increase in the future so here’s the same
project using a 5% rate:
Year
Cash Flow (£)
5% Discount Rate
Present Value
(£)
0
-
15
0,000
1.000
-
150,000
1
12,000
0.952
11,429
2
25,000
0.907
22,676
3
25,000
0.864
21,596
4
35,000
0.823
28,795
5
40,000
0.784
31,341
Net Present Value
-
34,164
The negative result shows that 5% will be too high a rate, and the IIR will be somewhere between 3%
and 5%.
IRR = 3
% + Difference between the two discount rates *
Positive NPV
Range of
+
/ve to
-
/ve NPVs
IRR = 3% + (2
% *
26298
)
60462
IRR = 3.87%
IRR Summary
The value to a business of calculating the IRR is that its decision-makers are able to see the level of
interest that a project can withstand. In the case where a number of projects are competing for
selection, the one that is most resilient can be chosen.
IRR should not be used to compare mutually exclusive projects, however. For example a project with
a lower IRR may in fact have a higher NPV so the potential income (or saving) could be higher.
Also IRR should not be used to compare project of different durations because it doesn’t consider
cost of capital (expected return on capital).
Another problem with IRR appears with projects that have irregular cash flows alternating between
positive and negative values several times. Numerous IRRs can be identified for such projects
potentially leading to confusion and the wrong investment decisions being made.
3.5 Modified Internal Rate of Return (MIRR)
This is usually used to rank various choices. As the name implies, MIRR is a modification of the
Internal Rate of Return (IRR). MIRR adds up the negative cash flows after discounting them to time
zero, adds up the positive cash flows after factoring in the proceeds of reinvestment at the final time
period, then works out what rate of return would equate the discounted negative cash flows at time
zero to the future value of the positive cash flows at the final time period. This rate of return is the
MIRR.
Luckily there is an Excel formula to calculate MIRR which takes three arguments: the range of values
of payments / income over the period of the project, the interest rate, and the reinvestment interest
rate. Here’s a worked example using the £150,000 project and the same cash flow as in the above
example:
