{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

FM11_Ch_10_Tool_Kit - 6 7 8 9 10 11 12 13 14 15 16 17 18 19...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
Ch 10 Tool Kit 6/7/2003 Chapter 10. Tool Kit for The Basics of Capital Budgeting: Evaluating Cash Flows Expected after-tax Project S Year (t) Project S Project L 0 1 2 3 4 0 ($1,000) ($1,000) (1,000) 500 400 300 100 1 500 100 2 400 300 Project L 3 300 400 4 100 600 0 1 2 3 4 (1,000) 100 300 400 600 Capital Budgeting Decision Criteria Payback Period Project S Time period: 0 1 2 3 4 Cash flow: (1,000) 500 400 300 100 Cumulative cash flow: (1,000) (500) (100) 200 300 Click fx > Logical > AND > OK to get dialog box. 0.00 0.00 0.00 1.00 0.00 Use Logical "AND" to determine Then specify you want TRUE if cumulative CF > 0 but the previous CF < 0. 0.00 0.00 0.00 2.33 0.00 the first positive cumulative CF There will be one TRUE. Payback: 2.33 Use Logical IF to find the Paybac Click fx > Logical > IF > OK. Specify that if true, the payback is the previous year plus a fraction, if false, then 0. Use Statistical Max function to Click fx > Statistical > MAX > OK > and specify range to find Payback. Alternative calculation 2.33 display payback. Project L Time period: 0 1 2 3 4 Cash flow: (1,000) 100 300 400 600 Cumulative cash flow: (1,000) (900) (600) (200) 400 Payback: 3.33 Uses IF statement. Discounted Payback Period WACC = 10% Project S Time period: 0 1 2 3 4 Cash flow: (1,000) 500 400 300 100 Disc. cash flow: (1,000) 455 331 225 68 Cash Flows Discounted back at 10%. Disc. cum. cash flow: (1,000) (545) (215) 11 79 Discounted Payback: 2.95 Uses IF statement. Project L Time period: 0 1 2 3 4 Cash flow: (1,000) 100 300 400 600 Disc. cash flow: (1,000) 91 248 301 410 Disc. cum. cash flow: (1,000) (909) (661) (361) 49 Discounted Payback: 3.88 Uses IF statement. Net Present Value (NPV) WACC = 10% Project S Time period: 0 1 2 3 4 Cash flow: (1,000) 500 400 300 100 Disc. cash flow: (1,000) 455 331 225 68 NPV(S) = $78.82 = Sum disc. CF's. or $78.82 = Uses NPV function. Project L Time period: 0 1 2 3 4 Cash flow: (1,000) 100 300 400 600 Disc. cash flow: (1,000) 91 248 301 410 NPV(L) = $49.18 $49.18 = Uses NPV function. Internal Rate of Return (IRR) Expected after-tax Year (t) Project S Project L 0 ($1,000) ($1,000) The IRR function assumes 1 500 100 14.49% payments occur at end of 2 400 300 11.79% periods, so that function does 3 300 400 not have to be adjusted. 4 100 600 Multiple IRR's Consider the case of Project M. Project M: 0 1 2 (1.6) 10 (10) 25.0% 400% Project M: 0 1 2 (1.6) 10 (10) r = 25.0% NPV = 0.00 NPV r $0.0 0% (1.60) 25% 0.00 50% 0.62 75% 0.85 100% 0.90 Max. 125% 0.87 150% 0.80 175% 0.71 200% 0.62 225% 0.53 250% 0.44 275% 0.36 300% 0.28 325% 0.20 350% 0.13 375% 0.06 400% 0.00 425% (0.06) 450% (0.11) 475% (0.16) 500% (0.21) 525% (0.26) 550% (0.30) NPV Profiles Net Cash Flows Year Project S Project L WACC = 10.0% 0 -$1,000 -$1,000 Project S Project L 1 $500 $100 NPV = $78.82 $49.18 2 $400 $300 IRR = 14.49% 11.79% 3 $300 $400 Crossover 7.17% 4 $100 $600 Data Table used to make graph: Project NPVs S L WACC $78.82 $49.18 0% $300.00 $400.00 5% $180.42 $206.50 7.17% $134.40 $134.40 10% $78.82 $49.18 11.79% $46.10 $0.00 14.49% $0.00 -$68.02 15.0% -$8.33 -$80.14 20% -$83.72 -$187.50 25% -$149.44 -$277.44 Points about the graphs: 1. In Panel a, we see that if WACC < IRR, then NPV > 0, and vice versa. 2. Thus, for "normal and independent" projects, there can be no conflict between NPV and IRR rankings. 4. Summary: a. For normal, independent projects, conflicts can never occur, so either method can be used. b. For mutually exclusive projects, if WACC > Crossover, no conflict, but if WACC < Crossover, then there will be a conflict between NPV and IRR. Expected after-tax Cash flow Alternative: Use Tools > Goal Seek to find WACC when NPV(S) = Year (t) Project S Project L differential NPV(L). Set up a table to show the difference in NPV's, which we 0 ($1,000) ($1,000) 0 want to be zero. The following will do it, getting WACC = 7.17%.
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}