Get Started for FREE
Sign up with Facebook Sign up with Twitter
I don't have a Facebook or a Twitter account
Your new post is loading...
Your new post is loading...
Russell Meade's curator insight,
May 10, 2015 1:13 AM
Model for Capital Budgeting First, you will analyze Projects S and L. Their cash flows are shown immediately below in both tabular and timeline formats. Spreadsheet analyses can be set up vertically, in a table with columns, or horizontally, using timelines. For short problems, with just a few years, it is normal to use the timeline format because rows can be added and you can set the problem up as a series of income statements. For long problems, it is often more convenient to use a tabular layout. Expected aftertax Project S net cash flows (CFt) Year (t)Project SProject 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 Here are the five key methods used to evaluate projects: (1) payback period, (2) discounted payback period, (3) net present value, (4) internal rate of return, and (5) modified internal rate of return. Using these criteria, financial analysts seek to identify those projects that will lead to the maximization of the firm's stock price. Payback Period The payback period is defined as the expected number of years required to recover the investment, and it was the first formal method used to evaluate capital budgeting projects. First, identify the year in which the cumulative cash inflows exceed the initial cash outflows. That is the payback year. Then, take the previous year and add to it the unrecovered balance at the end of that year divided by the following year's cash flow. Generally speaking, the shorter the payback period, the better the investment. 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. FALSEFALSEFALSETRUEFALSEUse Logical "AND" to determineThen 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 Payback.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 toClick fx > Statistical > MAX > OK > and specify range to find Payback. Alternative calculation:2.33 Alternative: Use nested IF statements to display payback. find payback. Fx > Logical > IF > OK, statements. 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 The discounted payback period uses the project's cost of capital to discount the expected cash flows. The calculation of the discounted payback period is identical to the calculation of regular payback period, except you must base the calculation on a new row of discounted cash flows. Note that both projects have a cost of capital of 10%. 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 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. The inherent problem with both paybacks is that they ignore cash flows that occur after the payback period mark. While the discounted method accounts for timing issues (to some extent), it still falls short of fully analyzing projects. However, all else equal, these two methods do provide some information about projects' liquidity and risk. Net Present Value (NPV) To calculate the NPV, find the present value of the individual cash flows and find the sum of those discounted cash flows. This value represents the value the project adds to shareholder wealth. 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 The NPV method of capital budgeting dictates that all independent projects that have positive NPV should be accepted. The rationale behind that assertion arises from the idea that all such projects add wealth, and that should be the overall goal of the manager in all respects. If strictly using the NPV method to evaluate two mutually exclusive projects, you would want to accept the project that adds the most value (such as the project with the higher NPV). Hence, if considering the above two projects, you would accept both projects if they are independent, and you would only accept Project S if they are mutually exclusive. Internal Rate of Return (IRR) The internal rate of return is defined as the discount rate that equates the present value of a project's cash inflows to its outflows. In other words, the internal rate of return is the interest rate that forces NPV to 0. The calculation for IRR can be tedious, but Microsoft® Excel® software provides an IRR function that merely requires you to access the function and enter the array of cash flows. The IRR's for Project S and L are shown below, along with the data entry for Project S. Expected aftertax net cash flows (CFt) Year (t)Project SProject L 0 ($1,000)($1,000) The IRR function assumes 1 500 100 IRR S =14.49% payments occur at end of 2 400 300 IRR L =11.79% periods, so that function does 3 300 400 not have to be adjusted. 4 100 600 The IRR method of capital budgeting maintains that projects should be accepted if their IRR is greater than the cost of capital. Strict adherence to the IRR method would further dictate that mutually exclusive projects should be chosen on the basis of the greatest IRR. In this scenario, both projects have IRRs that exceed the cost of capital (10%) and both should be accepted, if they are independent. If, however, the projects are mutually exclusive, you would chose Project S. Recall that this was the determination using the NPV method as well. The question that naturally arises is whether or not the NPV and IRR methods will always agree. When dealing with independent projects, the NPV and IRR methods will always yield the same accept/reject result. However, in the case of mutually exclusive projects, NPV and IRR can give conflicting results. One shortcoming of the internal rate of return is that it assumes that cash flows received are reinvested at the project's internal rate of return, which is not usually true. The nature of the congruence of the NPV and IRR methods is further detailed in a latter section of this model. Multiple IRR's Because of the mathematics involved, it is possible for some (but not all) projects that have more than one change of signs in the set of cash flows to have more than one IRR. If you attempted to find the IRR with such a project using a Financial Calculator, you would get an error message. The HP10B says "Error  Soln" and the HP17B says "Many/No Solutions; Key in Guess" when such a project is evaluated. The procedure for correcting the problem is to store in a guess for the IRR, and then the calculator will report the IRR that is closest to your guess. You can then use a different guess value, and you should be able to find the other IRR. However, the nature of the mathematics creates a scenario in which one IRR is quite extraordinary (often, a few hundred percent). Consider the case of Project M. Project M: 0 1 2 (1.6)10 (10) You will solve this IRR twice; the first time using the default guess of 10%, and the second time you will enter a guess of 300%. Notice, that the first IRR calculation is exactly as it was above. IRR M 1 =25.0% IRR M 2 =400% The two solutions to this problem tell you that this project will have a positive NPV for all costs of capital between 25% and 400%. This point is illustrated by creating a data table and a graph of the project NPVs. Project M: 0 1 2 (1.6)10 (10) k =25.0% NPV =0.00 NPV k$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 NPV profiles graph the relationship between projects' NPVs and the cost of capital. To create NPV profiles for Projects S and L, create data tables of NPV at different costs of capital. Project S Project L WACC$78.82 WACC$49.18 0%300.00 0%400.00 2%249.74 2%317.63 4%202.77 4%242.00 6%158.79 6%172.44 8%117.55 8%108.35 10%78.82 10%49.18 12%42.39 12%(5.53) 14%8.08 14%(56.20) 16%(24.27) 16%(103.21) Previously discussed was the fact that, in some instances, the NPV and IRR methods can give conflicting results. First, you should attempt to define what you see in this graph. Notice, that the two project profiles (S and L) intersect the xaxis at costs of capital of 14% and 12%, respectively. Not coincidently, those are the IRRs of the projects. If you think about the definition of IRR, remember that the internal rate of return is the cost of capital at which a project will have an NPV of 0. Looking at the graph, it is a logical conclusion that the IRR of a project is defined as the point at which its profile intersects the xaxis. Looking further at the NPV profiles, you see that the two project profiles intersect at a point called the crossover point. Observe that at costs of capital greater than the crossover point, the project with the greater IRR (Project S, in this case) also has the greater NPV. But at costs of capital less than the crossover point, the project with the lesser IRR has the greater NPV. This relationship is the source of discrepancy between the NPV and IRR methods. By looking at the graph, you see that the crossover appears to occur at approximately 7%. Luckily, there is a more precise way of determining crossover. To find crossover, find the difference between the two projects' cash flows in each year, and then find the IRR of this series of differential cash flows. Expected aftertax net cash flows (CFt) Cash flowAlternative: Use Tools > Goal Seek to find WACC when NPV(S) = Year (t)Project SProject LdifferentialNPV(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%. 1 500 100 400 Look at B57 for the answer, then restore B57 to 10%. 2 400 300 100 NPV S = $ 78.82 3 300 400 (100) NPV L = $ 49.18 4 100 600 (500) S  L = $ 29.64 IRR =Crossover rate =7.17% The intuition behind the relationship between the NPV profile and the crossover rate is as follows: (1) Distant cash flows are heavily penalized by high discount ratesthe denominator is (1 + k)^t, and it increases geometrically, hence gets very large at high values of t. (2) Longterm projects like L have most of their cash flows coming in the later years, when the discount penalty is largest, hence they are most severely impacted by high capital costs. (3) Therefore, Project L's NPV profile is steeper than that of S. (4) Since the two profiles have different slopes, they cross one another. Modified Internal Rate of Return (MIRR) The modified internal rate of return is the discount rate that causes a project's cost (or cash outflows) to equal the present value of the project's terminal value. The terminal value is defined as the sum of the future values of the project's cash inflows, compounded at the project's cost of capital. To find MIRR, calculate the PV of the outflows and the FV of the inflows, and then find the rate that equates the two. Or, you can solve using the MIRR function. WACC =10% MIRRS =12.11% Project S MIRRL =11.33% 10% 0 1 2 3 4 (1,000)500 400 300 100 Project L 0 1 2 3 4 (1,000)100 300 400 600 440.0 363.0 133.1 P V :(1,000) Terminal Value:1,536.1 The advantage of using the MIRR, relative to the IRR, is that the MIRR assumes that cash flows received are reinvested at the cost of capital, not the IRR. Since reinvestment at the cost of capital is more likely, the MIRR is a better indicator of a project's profitability. Moreover, it solves the multiple IRR problem, as a set of cash flows can have but one MIRR . Note that if negative cash flows occur in years beyond Year 1, those cash flows would be discounted at the cost of capital and added to the Year 0 cost to find the total PV of costs. If both positive and negative flows occurred in some year, the negative flow should be discounted, and the positive one compounded, rather than just dealing with the net cash flow. This makes a difference. Also note that the MIRR function allows for discounting and reinvestment to occur at different rates. Generally, MIRR is defined as reinvestment at the WACC, though Microsoft® Excel® software allows the calculation of a special MIRR where reinvestment occurs at a different rate from WACC. Finally, it is stated in the text, when the IRR versus the NPV is discussed, that the NPV is superior because (1) the NPV assumes that cash flows are reinvested at the cost of capital whereas the IRR assumes reinvestment at the IRR, and (2) it is more likely, in a competitive world, that the actual reinvestment rate is more likely to be the cost of capital than the IRR, especially if the IRR is quite high. The MIRR setup can be used to prove that NPV does indeed assume reinvestment at the WACC, and IRR at the IRR. Project S WACC =10% 0 1 2 3 4 (1,000)500 400 300 100 330.0 484.0 Reinvestment at WACC = 10% 665.5 PV outflows$1,000.00 Terminal Value:1,579.5 PV of TV $1,078.82 NPV $ 78.82 Thus, we see that the NPV is consistent with reinvestment at WACC. Now repeat the process using the IRR, which is G118 as the discount rate. Project S IRR =14.49% 0 1 2 3 4 (1,000)500 400 300 100 343.5 524.3 Reinvestment at IRR = 14.49% 750.3 PV outflows$1,000.00 Terminal Value:1,718.1 PV of TV $1,000.00 NPV $0.00 Thus, if compounding is at the IRR, NPV is zero. Since the definition of IRR is the rate at which NPV = 0, this demonstrates that the IRR assumes reinvestment at the IRR.


QRB 501 Week 6 Sander’s Woodworks – Balance Sheet Analysis & Recommendations