Question

answer using excel

If the cost of capital is 12%, which project should be chosen? If the cost of capital is 18% which project should be chosen? a) To determine the answer to this above questions you are to calculate the NPV of each project using a required rate of return of 12% and then a required rate of return of 18%. Then construct the NPV profiles for Project A and Project B. (Note: plot the NPVs of both projects on the same graph.) The cost of capital ranges from 0% to 30% by increments of 2%. Expected Cash Flows Project A -34.000 -27,000 pe la 11,500 20,500 30,500 35,000 38,000 40,000 Expected Cash Flows Project B -31,500 14,500 15,000 16,500 18,000 19,000 19,500 20,000 b) Calculate each project's IRR and the crossover rate of the two projects. c) Calculate each project's MIRR at a reinvestment rate of 10% and a cost of capital of 18%. d) Calculate each project's profitability index at a cost of capital of 12% e) Calculate each project's regular payback period. f) Calculate each project's discounted payback period with a cost of capital of 12%. Note: All of Calculations should be rounded to one decimal place. Therefore, the IRR should be shown as 10.5% not 10.48378332%

Answer 1

a. Calculation of NPV when rate of return = 12%

Year	Expected Cash Flows		DF @ 12%	Present Value
	Project A	Project B		Project A	Project B
0	-34000	-31500	1	-34000	-31500
1	-27000	14500	0.893	-24107.14	12946.43
2	11500	15000	0.797	9167.73	11957.91
3	20500	16500	0.712	14591.50	11744.37
4	30500	18000	0.636	19383.30	11439.33
5	35000	19000	0.567	19859.94	10781.11
6	38000	19500	0.507	19251.98	9879.307
7	40000	20000	0.452	18093.97	9046.984
NPV				42241.27	46295.44

Calculation of NPV when rate of return = 18%

Year	Expected Cash Flows		DF @ 18%	Present Value
	Project A	Project B		Project A	Project B
0	-34000	-31500	1	-34000	-31500
1	-27000	14500	0.847	-22881.36	12288.14
2	11500	15000	0.718	8259.12	10772.77
3	20500	16500	0.609	12476.93	10042.41
4	30500	18000	0.516	15731.56	9284.2
5	35000	19000	0.437	15298.82	8305.075
6	38000	19500	0.370	14076.40	7223.415
7	40000	20000	0.314	12557.00	6278.501
NPV				21518.48	32694.5

b. IRR of Project A

At IRR, NPV = 0

0 = -34000 + -27000/ (1 + IRR) + 11500/ (1 + IRR)^2 + 20500/ (1 + IRR)^3 + 30500/ (1 + IRR)^4 + 35000/ (1 + IRR)^5 + 38000/ (1 + IRR)^6 + 40000/ (1 + IRR)^7

IRR = 27.4%

IRR for Project B

At IRR, NPV = 0

0 = -31500 + 14500/ (1 + IRR) + 15000/ (1 + IRR)^2 + 16500/ (1 + IRR)^3 + 18000/ (1 + IRR)^4 + 19000/ (1 + IRR)^5 + 19500/ (1 + IRR)^6 + 20000/ (1 + IRR)^7

IRR = 47.7%

At Crossover rate (CRR), NPV of both projects is zero. It is calculated by finding difference between the cash flows of both project and then finding the IRR/ rate at which the NPV is zero.

Year	Expected Cash Flows		Difference in cash flows
	Project A	Project B
0	-34000	-31500	-2500
1	-27000	14500	-41500
2	11500	15000	-3500
3	20500	16500	4000
4	30500	18000	12500
5	35000	19000	16000
6	38000	19500	18500
7	40000	20000	20000

0 = -2500 + -41500/ (1 + CRR) + -3500/ (1 + CRR)^2 + 4000/ (1 + CRR)^3 + 12500/ (1 + CRR)^4 + 16000/ (1 + CRR)^5 + 18500/ (1 + CRR)^6 + 20000/ (1 + CRR)^7

CRR = 9.5%

c. MIRR of Project A

MIRR = [(FV of inflows/ PV of outflows) ^ 1/n] - 1

PV of outflows at 18% = 34000 + 27000/1.18

= 34000 + 22881 = 56881

FV of inflows at 10% = 11500 * (1.1)^5 + 20500 * (1.1)^4 + 30500 * (1.1)^3 + 35000 * (1.1)^2 + 38000 * (1.1)^1 + 40000 *(1.1)

= 213280

MIRR = [(213280 / 56881)^(1/7)] - 1

= [3.75^(1/7)] - 1

= 1.208 - 1

= 0.208 or 20.8%

MIRR of Project B

PV of outflows at 18% = 31500

FV of inflows at 10% = 14500 * (1.1)^6 + 15000 * (1.1)^5 + 16500 * (1.1)^4 + 18000 * (1.1)^3 + 19000 * (1.1)^2 + 19500 * (1.1)^1 + 20000 *(1.1) = 162400

MIRR = [(162400 / 31500)^(1/7)] - 1

= [5.16^(1/7)] - 1

= 1.264 - 1

= 0.264 or 26.4%

d. Profitability index of Project A

Profitability index = ( NPV Initial Investment ) / Initial Investment

= (42241.27 + 34000) / 34000

= 76241.27 / 34000

= 2.25 years

Profitability index of Project B

Profitability index = ( NPV Initial Investment ) / Initial Investment

= (46295.44 + 31500) / 31500

= 77795.44 / 31500

= 2.47 years

Note: As per HOMEWORKLIB POLICY, in case of multiple sub questions, only the first 4 need to be answered.