Question

You are considering an investment in two projects, A and B. Both projects will cost $115,000, and the projected cash flows are as follows:

YEAR PROJECT A PROJECT B

1 $7,188    $51,750

2 $21,562    $38,812

3 $40,250    $28,750

4 $50,315    $21,563

5    $57,500 $14,375

Using Excel and show formulas

a. Assuming that the WACC is 9.4%, calculate the payback period, discounted payback period, NPV, PI, IRR, and MIRR. If the projects are mutually exclusive, which project should be selected?

b. Create an NPV profile chart for projects A and B. What is the exact crossover rate for these two projects?

Answer 1

(a): The answers are:

	Payback	Discounted payback	NPV	PI	IRR	MIRR
Project A	3.91	4.67	12,145.72	1.11	12.50	11.62
Project B	2.85	3.88	10,916.75	1.09	13.95	11.40

Calculations:

Payback:

Year	A's cash flow	Cumulative cash flow of A		B's cash flow	Cumulative cash flow of B
0	-115,000.00	-115,000.00		-115,000.00	-115,000.00
1	7,188.00	-107,812.00		51,750.00	-63,250.00
2	21,562.00	-86,250.00		38,812.00	-24,438.00
3	40,250.00	-46,000.00		28,750.00	4,312.00
4	50,315.00	4,315.00		21,563.00
5	57,500.00			14,375.00

A's payback = 3+(46000/50315) = 3.91 years

B's payback = 2+(24438/28750) = 2.85 years

Discounted payback:

Year	A's cash flow	1+r	PVIF	A's discounted cash flow	Cumulative discounted cash flow of A	B's cash flow	B's discounted cash flow	Cumulative discounted cash flow of B
0	-115,000.00	1.094	1.0000	-115,000.00	-115,000.00	-115,000.00	-115,000.00	-115,000.00
1	7,188.00		0.9141	6,570.38	-108,429.62	51,750.00	47,303.47	-67,696.53
2	21,562.00		0.8355	18,015.84	-90,413.78	38,812.00	32,428.84	-35,267.69
3	40,250.00		0.7637	30,740.71	-59,673.07	28,750.00	21,957.65	-13,310.04
4	50,315.00		0.6981	35,125.96	-24,547.11	21,563.00	15,053.58	1,743.55
5	57,500.00		0.6381	36,692.83	12,145.72	14,375.00	9,173.21

A's discounted payback = 4+(24547.11/36692.83) = 4.67 years

B's discounted payback = 3+(13310.04/15053.58) = 3.88 years

NPV:

Year	A's cash flow	1+r	PVIF	A's discounted cash flow	B's discounted cash flow
0	-115,000.00	1.094	1.0000	-115,000.00	-115,000.00
1	7,188.00		0.9141	6,570.38	47,303.47
2	21,562.00		0.8355	18,015.84	32,428.84
3	40,250.00		0.7637	30,740.71	21,957.65
4	50,315.00		0.6981	35,125.96	15,053.58
5	57,500.00		0.6381	36,692.83	9,173.21
			NPV	12,145.72	10,916.75

PI: PI = present value of cash inflows/present value of cash outflows

A's cash flow	1+r	PVIF	A's discounted cash flow	B's discounted cash flow
-115,000.00	1.094	1.0000	-115,000.00	-115,000.00
7,188.00		0.9141	6,570.38	47,303.47
21,562.00		0.8355	18,015.84	32,428.84
40,250.00		0.7637	30,740.71	21,957.65
50,315.00		0.6981	35,125.96	15,053.58
57,500.00		0.6381	36,692.83	9,173.21
		Present value of inflows	127,145.72	125,916.75
		Present value of outflow	115,000.00	115,000.00
		PI	1.11	1.09

IRR: it is the rate that makes NPV as nil:

A's cash flow	1+r	PVIF	A's discounted cash flow
-115,000.00	1.1250402	1.0000	-115,000.00
7,188.00		0.8889	6,389.11
21,562.00		0.7901	17,035.43
40,250.00		0.7023	28,265.83
50,315.00		0.6242	31,406.92
57,500.00		0.5548	31,902.72
		NPV	0.00

B's IRR:

Year	1+r	PVIF	B's cash flow	B's discounted cash flow
0	1.139537	1.0000	-115,000.00	-115,000.00
1		0.8775	51,750.00	45,413.18
2		0.7701	38,812.00	29,888.84
3		0.6758	28,750.00	19,429.09
4		0.5930	21,563.00	12,787.79
5		0.5204	14,375.00	7,481.10
			Total	0.00

MIRR: Present value of cash outflow = Terminal value/(1+MIRR)^5

Terminal value of A = 7188*1.094^4 + 21562*1.094^3 + 40250*1.094^2 + 50315*1.094^1 + 57500 = 199,245.43

or 115,000 = 199245.43/(1+MIRR)^5. Solving this we get MIRR = 11.62%

For B: Terminal value = 51750*1.094^4+38812*1.094^3 + 28750*1.094^2 + 21563*1.094^1 + 14375 = 197,319.56

or 115,000 = 197319.56/(1+MIRR)^5. Solving we get MIRR = 11.40%

(b) Exact cross over rate is the rate at which NPV is same for both the projects. The cross over rate is = 10.1883% or 10.19% (rounded off to 2 decimal place)

Graph:

60,000.00 50,000.00 As NPV 30,000.00 10,000.00 0.00 0.00 2.00 4.00 6.00 8.00 10.00 12.00 14.00