Chapter 1, BOC Question 2 (ch01boc-model) 8/1/2005 2. Finance is all about valuation—how to estimate asset values and what to do to increase them. We develop and use Excel models throughout the book. We start this process in this chapter with simple models used to value bonds, stocks, and capital budgeting projects. Working through the model will give you a refresher in valuation plus a refresher on (or preview of) Excel. The model can be accessed on iLrn Finance under the Beginning-of- Chapter Models section, and its filename is ch01-M. If you have never used Excel at all, then you should not attempt to use it to help answer this question, or if you do, you should not get frustrated if you have trouble with it. Click on the tab at the bottom, "Spreadsheet Model," to access the model itself. o do to increase s process in this ting projects. fresher on (or eginning-of- d Excel at all, then you should not get A B C D E F G H I J 1 Worksheet for Chapter 1 BOC Questions (ch01-M) 7/28/2005 a. Use Excel to find the value of an 8%, annual payment, $1,000 par value, 5 year bond if the going rate of interest is 7%. Check your answer with a calculator. 2 3 Principal amount of bond: $1,000 Years to maturity: 5 4 Coupon interest rate: 8.0% Annual interest: $80 5 Market interest rate: 7.0% 6 7 Time line of payments: 1 2 3 4 5 8 Interest $80 $80 $80 $80 $80 9 Principal 1,000 10 Total CF $80 $80 $80 $80 $1,080 11 12 The bond's value can be found in several ways, as shown below: 13 14 Method #1. Find PV of interest and PV of principal, and sum them. 15 16 PV of interest payments: $328.02 Click fx > Financial > PV and then fill in as shown in dialog box below. 17 PV of maturity payment: $712.99 Just use algebra to find PV. 18 Value of bond: $1,041.00 19 20 Notes: 21 1. We could have used numbers, 22 but we used cell references to 23 provide greater flexibility. 24 2. We could have left FV and Type 25 blank, but we put in 0. 26 3. The result shows as a negative 27 number, so we pt a minus sign 28 after the equal sign in cell C16 29 to convert to positive value. 30 31 32 33 34 35 Method #2. Use the same PV function, but insert $1,000 as the FV: 36 37 Click fx > Financial > PV and then fill in as shown in dialog box below. 38 39 Value of bond: $1,041.00 40 41 Notes: 42 1. All the inputs are the same as 43 above except for FV, where we 44 inserted C3. 45 2. Note that this Excel function is 46 identical to the procedure that 47 would be used to find the value of 48 the bond with many financial 49 calculators. 50 3. Again, we inserted a minus sign 51 after the equal sign to convert 52 the answer to a positive number. 53 A B C D E F G H I J 54 55 A B C D E F G H I J 56 Note that if you change any of the input values, the bond's value as shown in cells C18 and C39 will instaneously change to 57 reflect the new conditions. To see this, change C5 to 15% and watch the bond's price fall. As we will see in later chapters, the 58 process for making such changes is called "sensitivity analysis," or, if several variables are changed at the same time, 59 "scenario analysis." Furthermore, Excel has very useful features called "Data Tables" and "Scenario Manager" that 60 automate this process. We will use these features often. 61 62 Note too that in our bond valuation problem we assumed that the bond will make its next interest payment exactly one year from 63 today. We could use the Excel bond "Price" function to find the bond's value between interest payment dates, and we will do so 64 in Chapter 4. 65 66 Stock Valuation b. Use Excel to find the value of a stock if the last dividend was $1, the expected growth rate for the next 3 years is 20%, the long-run growth rate is 5%, and the required rate of return is 12%. Again, you 67 can check your answers with a calculator. 68 Last dividend (D0): 1 Growth rate next 3 years (g1-3): 20% 69 Required rate of return (r): 12% Growth rate thereafter (gn): 5% 70 71 Time line of payments: 0 1 2 3 4 72 Dividend $1.00 $1.20 $1.44 $1.73 $1.81 73 Stock value end of Year 3: $25.92 74 Total cash flow: $1.00 $1.20 $1.44 $27.65 75 76 77 PV of cash flow: $1.07 $1.15 $19.68 78 Value of the stock: $21.90 79 80 Notes: 1. The first dividend that will be received is the $1.20, because the $1 has been paid. 81 2. The growth rate drops to 5% after Year 3. 82 3. The PV of the dividends from Year 4 to infinity is found as D4/(r - gn) = $25.92. 83 The calculation assumes that the stock is, or can be, sold at the $25.92 at the end of Year 3. 84 85 Again, we could change the inputs and see instantly how the stock price varies. For example, if you change r to 10% 86 and gn to 7%, the stock price will more than double, rising from $21.90 to $49.88. Thus, if management could lower 87 the firm's risk (and thus r) and raise the growth rate, this would greatly enhance shareholder value. 88 89 Capital Budgeting c. Use Excel to find the NPV and the IRR of a project that is expected to cost $10,000 and to provide net cash flows of $2,000 per year for the next 10 years. The cost of capital is 11%. 90 91 Excel is excellent for analyzing capital budgeting problems. Here is an example: 92 Project cost: -$10,000 Project life (years): 10 93 Annual net cash flow: $2,000 Cost of capital: 11% 94 95 Time line: 0 1 2 3 4 5 6 7 8 96 97 Cash flow -$10,000 $2,000 $2,000 $2,000 $2,000 $2,000 $2,000 $2,000 $2,000 98 Net Present Value (NPV): $1,778 99 Internal Rate of Return (IRR): 15.1% 100 101 Notes: 1. The completed dialog boxes shown below illustrate how to use Excel to calculate the NPV and IRR. 102 2. You get the dialog boxes by clicking fx > Financial > NPV or IRR. 103 3. Excel's NPV function assumes that the first cash flow occurs at the end of the year. However, the cost 104 occurs at the beginning of the period. Therefore, to use the NPV function we first calculate the NPV of the 105 flows that occur after Period 0, and we add to the value thus calculated the (negative) cost. 106 4. Excel's IRR function assumes that the cash flows occur at the beginning of each period, so when we A B C D E F G H I J 107 calculate the IRR we can simply specify the entire range (B96:L96). Also, we leave "guess" blank and let 108 Excel starts the iterative process with a 10% cost of capital discount rate. 109 110 Again, we could vary the inputs and see how the NPV and IRR change, and we will be doing a lot of this later in the book. 111 As we will see, using Excel to analyze capital budgeting projects is an easy way to evaluate potential projects, and it can 112 also be used by management to help design projects so that they maximize the firm's value.
Pages to are hidden for
"Capital Budgeting End of Chapter Questions"Please download to view full document