Question

Obi, Juan, Ken & Obi’s brother, Michael, started their own light saber business. They have decided to invest some of their money in only 1 of the following projects. All the projects have a required rate of return of 8% and a 5 year-useful life. Using rate of return analysis, which option should be selected?

	A	B	C	D	E
Initial Cost	$100	$130	$200	$330	$0
Annual Benefits	$150	$130.78	$185	$184.55	$0
Yearly Cost	$123.62	$92	$137.52	$93	$0

Answer 1

The initial base alternative is E as it has the lowest initial cost

MARR = 8%

We need to perform incremental IRR analysis

In order to calculate incremental IRR, first, we arrange the options in the increasing value of their initial cost

Then we need to find Net cash flow in each year for every alternative.

We need to find incremental cash flow between lowest and second-lowest option first, then use the formula of IRR in excel on the incremental cash flow, if incremental IRR is greater than MARR then select the second-lowest option if it is less than the MARR then select the lowest option.

We keep repeating the above until the last option is evaluated

For option A,

Net Cash flow in EOY 0 = -100

Net cash flow from EOY 1 to 5 = 150 - 123.62 = 26.38

For option B,

Net Cash flow in EOY 0 = -130

Net cash flow from EOY 1 to 5 = 130.78 - 92 = 38.78

For option C,

Net Cash flow in EOY 0 = -200

Net cash flow from EOY 1 to 5 = 185 - 137.52 = 47.48

For option D,

Net Cash flow in EOY 0 = -330

Net cash flow from EOY 1 to 5 = 184.55 - 93 = 91.55

option E is basically Do Nothing alternative

Now we arrange alternatives in increasing order of initial cost

E < A < B < C < D

using Excel

Year	E	A	B	C	D	A-E	B-C	C-B	D-B
0	0	-100	-130	-200	-330	-100	-30	-70	-200
1	0	26.38	38.78	47.48	91.55	26.38	12.4	8.7	52.77
2	0	26.38	38.78	47.48	91.55	26.38	12.4	8.7	52.77
3	0	26.38	38.78	47.48	91.55	26.38	12.4	8.7	52.77
4	0	26.38	38.78	47.48	91.55	26.38	12.4	8.7	52.77
5	0	26.38	38.78	47.48	91.55	26.38	12.4	8.7	52.77
IRR						10.00%	30.35%	-14.02%	10.01%

We compare first E and A since E (DN) has the lowest cost, and the second lowest option is A, incremental IRR > MARR so we select A, and reject E (DN)

Now we find incremental cash flow between A & B, we find incremental IRR>MARR, so we select B and reject A

Now we find incremental cash flow between B & C, we find incremental IRR<MARR, so we select B and reject C

Now we find incremental cash flow between D & B, we find incremental IRR>MARR, so we select D and reject B

D option should be selected as per the incremental analysis

Showing formula in excel

Year	E	A	B	C	D	A-E	B-C	C-B	D-B
0	0	-100	-130	-200	-330	=D337-C337	=E337-D337	=F337-E337	=G337-E337
1	0	26.38	38.78	47.48	91.55	=D338-C338	=E338-D338	=F338-E338	=G338-E338
2	0	26.38	38.78	47.48	91.55	=D339-C339	=E339-D339	=F339-E339	=G339-E339
3	0	26.38	38.78	47.48	91.55	=D340-C340	=E340-D340	=F340-E340	=G340-E340
4	0	26.38	38.78	47.48	91.55	=D341-C341	=E341-D341	=F341-E341	=G341-E341
5	0	26.38	38.78	47.48	91.55	=D342-C342	=E342-D342	=F342-E342	=G342-E342
IRR						=IRR(H337:H342)	=IRR(I337:I342)	=IRR(J337:J342)	=IRR(K337:K342)