Question

A company has a 13% WACC and is considering two mutually exclusive investments (that cannot be repeated) with the following cash flows:

0

1

2

3

4

5

6

7

Project A	-$300	-$387	-$193	-$100	$600	$600	$850	-$180
Project B	-$405	$134	$134	$134	$134	$134	$134	$0

The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.

Spreadsheet data :

Capital budgeting criteria
WACC	13.00%
		0	1	2	3	4	5	6	7
Project A		-$300	-$387	-$193	-$100	$600	$600	$850	-$180
Project B		-$405	$134	$134	$134	$134	$134	$134	$0
Project MIRR Calculations:
Alternatively, MIRRA can be calculated as:
		0	1	2	3	4	5	6	7
Project A		-$300	-$387	-$193	-$100	$600	$600	$850	-$180
	Formulas
N	7	Formulas
PV	$0.00
PMT	0
FV	$0.00
I/YR = MIRRA		#N/A
MIRRB		#N/A
Alternatively, MIRRB can be calculated as:
		0	1	2	3	4	5	6	7
Project B		-$405	$134	$134	$134	$134	$134	$134	$0
Sum of Outflow PVs	#N/A									#N/A
N	7	Formulas
PV	$0.00
PMT	0
FV	$0.00
I/YR = MIRRB		#N/A
Project Acceptance:
WACC	13.00%
Accept		#N/A
WACC	18.00%
NPVA	$2.66
NPVB	$63.68
Accept		#N/A
NPV Profiles:
Discount Rates	NPVA	NPVB			Discount Rates	NPVA	NPVB
	$2.66	$63.68				$2.66	$63.68
0%					0%	#N/A	#N/A
5.00%					5.00%	#N/A	#N/A
10.00%					10.00%	#N/A	#N/A
12.00%					12.00%	#N/A	#N/A
15.00%					15.00%	#N/A	#N/A
18.10%					18.10%	#N/A	#N/A
23.97%					23.97%	#N/A	#N/A
Calculation of Crossover Rate:
		0	1	2	3	4	5	6	7
Project A		-$300	-$387	-$193	-$100	$600	$600	$850	-$180
Project B		-$405	$134	$134	$134	$134	$134	$134	$0
Project Delta
		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
Crossover Rate = IRRΔ		#N/A
Project MIRR Calculations at WACC = 18%
WACC	18.00%
MIRRA		#N/A
MIRRB		#N/A

1. What is each project's NPV? Round your answer to the nearest cent. Do not round your intermediate calculations.

   Project A: $  

   Project B: $  

2. What is each project's IRR? Round your answer to two decimal places.

   Project A: %

   Project B: %

3. What is each project's MIRR? (Hint: Consider Period 7 as the end of Project B's life.) Round your answer to two decimal places. Do not round your intermediate calculations.

   Project A: %

   Project B: %

4. From your answers to parts a-c, which project would be selected?

   _________Project AProject B

   If the WACC was 18%, which project would be selected?

   _________Project AProject B

5. 
   

6. Construct NPV profiles for Projects A and B. Round your answers to the nearest cent. Do not round your intermediate calculations. Negative value should be indicated by a minus sign.

   Discount Rate	NPV Project A	NPV Project B
   0%	$	$
   5	$	$
   10	$	$
   12	$	$
   15	$	$
   18.1	$	$
   23.97	$	$

7. Calculate the crossover rate where the two projects' NPVs are equal. Round your answer to two decimal places. Do not round your intermediate calculations.

   %

8. What is each project's MIRR at a WACC of 18%? Round your answer to two decimal places. Do not round your intermediate calculations.

   Project A: %

   Project B: %

Answer 1

We have the project cash flows

a.) We use NPV function in excel to calculate NPV of projects

Since WACC=13%, we use a discount rate=0.13

NPV = Initial investment + NPV(0.13,Cash-flows from year 1 to end-year)

Year	Project A	Project B
0	-300	-405
1	-387	134
2	-193	134
3	-100	134
4	600	134
5	600	134
6	850	134
7	-180	0
NPV	$162.48	$130.67
NPV Formula	D3+NPV(0.13,D4:D10)	E3+NPV(0.13,E4:E10)
IRR	18.10%	23.97%
IRR Formula	IRR(D3:D10)	IRR(E3:E10)
MIRR	15.60%	17.61%
MIRR Formula	MIRR(D3:D10,0.13,0.13)	MIRR(E3:E10,0.13,0.13)

Project A NPV : $162.48

Project B NPV : $130.67

b)

Referring to the table in part a), we use IRR function in excel => IRR(All cash-flows)

Project A IRR : 18.10%

Project B IRR : 23.97%

c)

Referring to the table in part a), we use MIRRfunction in excel => MIRR(All cash-flows,0.13,013)

Here, we assume that finance rate=reinvestment rate = 13%

Project A MIRR : 15.60%

Project B MIRR : 17.61%

d)

There is a conflict between IRR and NPV as IRR and MIRR analysis suggests project B should be selected and NPV analysis suggests Project A should be selected as it has higher NPV.

We select Project A, because we have a preference of NPV over IRR as in IRR it is assumed that cash-flows are reinvested at the IRR which is a difficult practically.

e)

If the WACC was 18%, we again calculate the NPV

Here, we observe that Project B has higher NPV as well as higher IRR and MIRR

Hence, we select Project B if the WACC is 18%

f)

NPV Formula	Initial Investment + NPV(0.13, Cashflows from year 1 to 7)
Discount rate	NPV Project A	NPV Project B
0%	$890.00	$399.00
0.50%	$849.81	$385.12
1.00%	$810.89	$371.59
1.50%	$773.19	$358.42
2.00%	$736.67	$345.59
2.50%	$701.29	$333.09
3.00%	$667.00	$320.90
3.50%	$633.77	$309.03
4.00%	$601.57	$297.45
4.50%	$570.35	$286.15
5.00%	$540.09	$275.14
5.50%	$510.74	$264.40
6.00%	$482.29	$253.92
6.50%	$454.70	$243.70
7.00%	$427.94	$233.72
7.50%	$401.98	$223.98
8.00%	$376.80	$214.47
8.50%	$352.37	$205.18
9.00%	$328.67	$196.11
9.50%	$305.66	$187.26
10.00%	$283.34	$178.60
10.50%	$261.68	$170.15
11.00%	$240.64	$161.89
11.50%	$220.23	$153.82
12.00%	$200.41	$145.93
12.50%	$181.16	$138.21
13.00%	$162.48	$130.67
13.50%	$144.33	$123.30
14.00%	$126.71	$116.08
14.47%	$110.60	$109.44
14.50%	$109.59	$109.02
14.53%	$108.61	$108.62
15.00%	$92.96	$102.12
15.50%	$76.81	$95.37
16.00%	$61.11	$88.75
16.50%	$45.86	$82.28
17.00%	$31.05	$75.95
17.50%	$16.65	$69.75
18.00%	$2.66	$63.68
18.10%	-$0.09	$62.48
23.97%	-$137.13	$0.03

g)

The cross-over rate is where NPV of Project A= NPV of Project B

Referring to the table and chart in part f)

Here, NPV of project A = NPV of project B at a discount rate = 14.53%

h)

Referring to part e), we have

Project A MIRR : 18.05%

Project B MIRR : 20.49%