Economics Chapter 11 Homework Irrs Can Solved For Using Excels Mirr

11 Case model 9/12/2022 17:19 12/09/2018

You recently went to work for Allied Components Company, a supplier of auto repair parts used

in the after-market with products from Daimler AG, Ford, Toyota, and other automakers.

Your boss, the chief financial officer (CFO), has just handed you the estimated cash flows for two

Here are the projects’ cash flows (in thousands of dollars):

Year

CFLCFS

0 ($100) ($100)

1$10 $70

2$60 $50

3$80 $20

Depreciation, salvage values, net operating working capital requirements, and tax effects are all

included in these cash flows. The CFO also made subjective risk assessments of each project,

and he concluded that both projects have risk characteristics that are similar to the firm’s average

project. Allied’s WACC is 10%. You must determine whether one or both of the projects should

PART C

(1) What is each project’s NPV?

Chapter 11. The Basics of Capital Budgeting

This spreadsheet model is designed to be used in conjunction with the chapter’s integrated case and

the related PowerPoint slide presentation.

proposed projects. Project L involves adding a new item to the firm’s ignition system line; it would

take some time to build up the market for this product, so the cash inflows would increase over

time. Project S involves an add-on to an existing line, and its cash flows would decrease over

time. Both projects have 3-year lives because Allied is planning to introduce entirely new models

after 3 years.

PART D

(1) What is each project’s IRR?

The internal rate of return (IRR) is that discount rate which forces the NPV of a project to equal

The solution to this equation can be found using Excel’s IRR function.

PART E

(1) Draw NPV profiles for Projects L and S. At what discount rate do the profiles cross?

WACC L S

$18.78 $19.98

0% 50.00 40.00

5% 33.05 29.29

10% 18.78 19.98

Year

CFDifference

0$0

1 ($60)

Projects

-20

0

10

20

50

60

NPV ($)

WACC

NPV Profiles

Project L

zero.

PART G

(1) Find the MIRRs for Projects L and S.

MIRR is that discount rate which equates the present value of the terminal value of the inflows,

compounded at the cost of capital, to the present value of the costs. The projects’ modified IRRs

can be solved for by using Excel’s MIRR function, by entering the project’s cash flows and using the

WACC as both the discount rate and the reinvestment rate.

PART H

(1) Find the paybacks for Projects L and S.

Payback Calculations

Project L Years 0 1 2 3

| | | |

Cash Flow -100 10 60 80

Project S Years 0 1 2 3

| | | |

Cash Flow -100 70 50 20

PART I

As a separate project (Project P), the firm is considering sponsoring a pavilion at the upcoming

World’s Fair. The pavilion’s initial outlay at t = 0 is $800,000, and it is expected to result in $5

million of incremental cash inflows during its one year of operation. However, it would then take

another year and a $5 million cash outflow to demolish the site and return it to its original

condition. Thus, Project P’s expected cash flows (in millions of dollars) look like this:

Year Cash Flow

0 ($0.8)

1$5.0 WACC = 10%

2 ($5.0)

The project is estimated to be of average risk, so its WACC is 10%.

By the MIRR criteria, Project S is preferred to Project L, which is consistent with the NPV decision.

(1) What is Project P’s NPV? What is its IRR? Its MIRR?

NPV = ($386,776.86)

IRR = 400%

Indeed, it is revealed that two IRRs exist. Because there are two sign changes in the cash flow

stream, we can be sure that there are two and only two IRRs.

(2) Draw Project P’s NPV profile. Does Project P have normal or nonnormal cash flows? Should

this project be accepted?

Project P has nonnormal cash flows; that is, it has more than one change of signs in the cash flows.

Without this nonnormal cash flow pattern, we would not have multiple IRRs.

that the project should be rejected.

($1,000,000)

($800,000)

($600,000)

($400,000)

$600,000

NPV

WACC

NPV Profile

IRR = 25.00%

MIRR = 5.60%

However, since this project has nonnormal cash flows, we must be aware of the possibility of

multiple IRRs. Therefore, we will perform the IRR calculation again, but this time a guess of 300%

will be entered to test for another IRR.

($386,776.86)

0% ($800,000.00)

25% $0.00

225% $265,088.76

250% $220,408.16

275% $177,777.78

300% $137,500.00

325% $99,653.98

375% $31,024.93

100% $450,000.00

125% $434,567.90

150% $400,000.00

175% $357,024.79

200% $311,111.11