Question

Please show how to answer this step by step in Excel

A company is considering two mutually exclusive expansion plans. Plan A requires a $40 million expenditure on a large-scale integrated plant that would provide expected cash flows of $6.39 million per year for 20 years. Plan B requires a $12 million expenditure to build a somewhat less efficient, more labor-intensive plant with an expected cash flow of $2.69 million per year for 20 years. The firm's WACC is 11%.

1. Calculate each project's NPV. Enter your answers in millions. For example, an answer of $10,550,000 should be entered as 10.55. Do not round intermediate calculations. Round your answers to two decimal places.

   Plan A:     $    million

   Plan B:     $    million

   Calculate each project's IRR. Round your answers to one decimal place.

   Plan A:       %

   Plan B:       %

2. By graphing the NPV profiles for Plan A and Plan B, determine the crossover rate. Round your answer to one decimal place.

     %

3. Calculate the crossover rate where the two projects' NPVs are equal. Round your answer to one decimal place.

Answer 1

Here, there are two mutually exclusive projects to be evaluated. We have been given following information:

		Plan A	Plan B
Expenditure(in millions)		40	12
Cashflows per year		6.39	2.69
Term in years		20	20
WACC		11%

Ans. a Net Present Value:

NPV = Present value of cash inflows - Present value of cash outflows

1-(1+r) - Present value of cash inflows = Present value of annuities = = A*|

Here, A = Periodic cashflows paid per period

r = rate of interest per period

n = number of periods

Also, present value of cash outflows is same as initial expenditure amount, this amount need not be discounted as it is already in present value terms.

We will be calculating the NPVs of the projects as shown in the excel file images below:

Plan A Plan B 3 4. Expenditure(in millions) 5 Cashflows per year 6 Term in years 7 WACC 40 R 6.39 20 11% 12 2.69 20 10 NPV Plan A Plan B 11 a) Present value of cash inflows: 50.88567 21.42135 14 b) Present value of cash outflows: =D$5*((1-((1+$C$7)^(-D$6))}/$C$7) - 40 12 16 17 NPV = a) - b) = 18 NPV (in millions, rounding off): 10.88567 9.421353 10.9 9.4

Now, we will calculate the IRR of the projects. Kindly know that IRR or the internal rate of return is the rate of interest at which the NPV of a project is zero i.e. when the project's present value of cash inflows is same as the present value of its cash outflows. We will use the excel function "IRR(values, [guess])" to find out the IRR for each project. But for this we will need to create a tabular representation of the cashflows of the projects.

B C D O IRR 1 Year 2 Cash outflow: 3 Cash inflows: 0 1 00 O-Nm 7 Plan A Plan B -40 -12 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 2.69 6.39 2.69 00 O- 1963 6.39 4 IRR: 14.999% 21.996% =IRR(C22:042)

Ans. b Graphing the NPV profiles and the crossover rate.

Crossover rate is the rate at which NPVs of both the projects are equal.

B C D F G H I | J K L M N. 8 NPV Profiles = NPV taking r = 1% (other values remaining same as given) oooonmoo000 NPV Rate of interest Plan A Plan B 1% 75.3111 36.5425 5.00% 39.6335 21.5233 10% 14.4017 10.9015 15.00% -0.00287 4.8376 20% -8.88339 1.09917 25.00% -14.7347 -1.36405 30% -18.8121 -3.08051 35.00% -21.788 -4.3333 40% -24.0441 -5.28304 45.00% -25.8084 -6.02576 50% -27.2238 -6.62162 55.00% -28.3836 -7.10985 60% -29.3509 -7.51704 NPV in millions Crossover rate= 11.8% 0% 10% 20% 30% 40% 50% 60% 70% Rate of discount

Ans. c Calculation of crossover rate:

Crossover rate is the internal rate of return of the differential cashflows between the two projects, I.e. when the NPV of the differential cashflows between the two projects is zero.

F G H I 72 -28 A B C D E 69 Calculation of crossover rate: 70 71 Differential Plan A Plan B cashflows 73 Cash outflow: -40 -12 74 Cash inflows: 6.39 2.69 3.7 6.39 2.69 3.7 6.39 2.69 3.7 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 3.7 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 6.39 2.69 3.7 6.39 2.69 2.69 17 6.39 2.69 3.7 6.39 2.69 3.7 6.39 2.69 3.7 201 6.39 2.691 3.7 Crossover rate = IRR= 11.8% 3.7 PREB. 00 ww 3.7 3.7 3.7 =IRR(E73:E93)