Cost Benefits Analysis Example - Free Download | Page 8
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
8
Data
Description
-
150
,
000
Initial cost
12,000
Return first year
25,000
Return second year
25,000
Return third year
35,000
Return fourth y
ear
40,000
Return fifth year
3.0%
Annual interest rate for the
£
150,000 loan
2.0%
Annual interest rate for the reinvested profits (likely scenario)
5
.0%
Annual interest rate for the reinvested profits (hopeful scenario)
-
1
.2
%
Investment's MIRR after f
ive years (likely)
-
0
.3
%
Investment's MIRR after five years (hopeful)
In Excel, here’s what you would enter for the data column:
A
1
Data
2
-
150000
3
12000
4
25000
5
25000
6
35000
7
40000
8
3.0%
9
2.0%
10
5.0%
11
=MIRR(A2:A7, A8,A9)
12
=MIRR
(A2:A7, A8, A10)
You can, of course, do “what-if” scenarios by varying the percentage amounts in cells A8, A9 and
A10, or by giving different values for the return for each year. According to our current example, this
would be quite a risky project in terms of cash return (though of course there may be other reasons
why we want to do it).
Bruce Levitan
Business Improvement Manager
October 2010 (v2)
