In: Economics
Annual costs of 3 different systems are provided below. If the MARR is 8%, which system is the least costly of the three? Use incremental investment analysis and show all steps.
Year |
System A |
System B |
System C |
0 |
$0 |
$2,000,000 |
$4,000,000 |
1 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
2 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
3 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
4 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
5 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
6 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
7 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
8 |
$9,000,000 |
$3,800,000 |
$1,900,000 |
If you use Excel to solve these problems, please provide details of the functions that you used.
MARR = 8%
We need to perform incremental IRR analysis
Using Excel
System Costs | Incremental IRR Analysis | ||||
Year | A | B | C | B-A | C-B |
0 | 0 | -2000000 | -4000000 | -2000000 | -2000000 |
1 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
2 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
3 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
4 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
5 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
6 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
7 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
8 | -9000000 | -3800000 | -1900000 | 5200000 | 1900000 |
Incremental IRR | 259.99% | 94.54% |
We compare first A and B since A has the lowest cost, and the second lowest option is B, incremental IRR > MARR so we select B, and reject A
Now we find incremental cash flow between C & B, we find incremental IRR>MARR, so we select C and reject B
Option C should be selected as per Incremental IRR analysis
Showing formula in excel
System Costs | Incremental IRR Analysis | ||||
Year | A | B | C | B-A | C-B |
0 | 0 | -2000000 | -4000000 | =O30-N30 | =P30-O30 |
1 | -9000000 | -3800000 | -1900000 | =O31-N31 | =P31-O31 |
2 | -9000000 | -3800000 | -1900000 | =O32-N32 | =P32-O32 |
3 | -9000000 | -3800000 | -1900000 | =O33-N33 | =P33-O33 |
4 | -9000000 | -3800000 | -1900000 | =O34-N34 | =P34-O34 |
5 | -9000000 | -3800000 | -1900000 | =O35-N35 | =P35-O35 |
6 | -9000000 | -3800000 | -1900000 | =O36-N36 | =P36-O36 |
7 | -9000000 | -3800000 | -1900000 | =O37-N37 | =P37-O37 |
8 | -9000000 | -3800000 | -1900000 | =O38-N38 | =P38-O38 |
Incremental IRR | =IRR(Q30:Q38) | =IRR(R30:R38) |