please answer all question in excel format
Module 9 (Ch 9) Homework Module 9 (Ch 9) Homework Due Sunday by 11:59pm Points 10 Submitting a file upload Available until Mar 22 at 11:59pm Submit Assignment Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. Finknautle, Inc. currently has the following sources of financing for its balance sheet. After analyzing each source of financing, answer the question associated with each. (Please carry all answers out to two decimal places.) 1. An issue of $1,000 face value, 6% coupon bonds that mature in 22 years. What is the value of one such bond if investors require a rate of return of 6%? ___________ 2. An issue of $1,000 face value, 3% coupon bonds that mature in 25 years. Calculate the price of these bonds 15 years from now if market rates at that time are at 6%. ____________ 3. An issue of $1,000 face value, zero coupon bonds which mature in 25 years. What is the value of one such bond if investors require a rate of return of 4.5%? ____________ 4. An issue of $1,000 face value, 7% coupon bonds which mature in 19 years. Calculate the bond's yield-to-maturity if its current market price is $1,260. ___________ 5. An issue of 6% preferred stock with a par value of $50. Calculate the price of one share of such stock when investors require a rate of return of 8%. ___________ 6. An issue of $5 preferred stock with a par value of $30. Calculate the required rate of return on such stock if its market price is $57. __________ 7. An issue of common stock that paid a dividend yesterday of $2.40. Calculate the value of one share of this stock to an investor who requires a 10% rate of return and who forecasts that the company's dividends will grow at a constant annual rate of 4%. ___________ 8. An issue of common stock that paid a dividend yesterday of $3.70, and is currently priced at $50 per share. Calculate this stock's current dividend yield for the coming year if investors anticipate the company's dividends to grow for the foreseeable future at a rate of 5%. _____________ 9. An issue of common stock that paid a dividend yesterday of $2.15, and is currently priced at $70 per share. Calculate this stock's capital gains yield if investors anticipate the company's dividends to grow for the foreseeable future at a rate of 4%. _____________ 10. An issue of common stock that paid a dividend yesterday of $4.71, and is currently priced at $62 per share. Calculate this stock's total rate of return if investors anticipate the company's dividend to grow for the foreseeable future at a rate of 5%. _____________ Microsoft Word - Chapter 9 Study Guide (2).doc 1 Fin 310 Study Guide Chapter 9 The Value of Securities Summary of Key Concepts of the Chapter Chapter 9 of the textbook is concerned primarily with putting a monetary value on the cash flows leaving the firm. That being the case, the focus in this chapter is from the perspective of the buyers of the financial instruments issued by the firm. In short, we are talking here about determining the prices of stocks and bonds. In general, we will find that the value all securities is the present value of the future cash flows offered by the security, discounted at the required rate of return. The Traditional Bond. A bond is a promise to pay a fixed annual amount each year plus a fixed par value (usually $1,000) upon maturity. The price of a bond (often quoted in percentage terms rather than actual dollars) then, is typically the present value of two kinds of cash flows: An annuity (a series of equal payments at regular intervals) for a set number of years. A terminal value (usually $1,000) to be received at the end of a set number of years. Using the HP10BII calculator, the calculation is rather straightforward. For example, let’s say we have a $1,000 bond that matures in 20 years that pays a 7% coupon rate. To calculate the price of such a bond for an investor that requires a return of 5%, we have the following keystrokes on the calculator: 1,000 FV 20 N 70 PMT 5 I/YR PV Answer: $1,249.24 The bond in the example above is selling at a premium (greater than $1,000) because the discount rate (5%) is less than the coupon rate (7%). As the time of maturity grows closer, the price of the bond will move gradually toward the $1,000 face of the bond. Bonds don’t always pay interest annually, and such a bond that does not pay interest is called a zero-coupon bond. In such a case, the value of the bond is simply the present value of the maturity value (face value) discounted at the required rate of return. For example, the value of 2 a $1,000 zero-coupon bond maturing in 20 years when the required rate of return is 7% is calculated as follows: 1,000 FV 20 N 7 I/YR PV Answer: $258.42 Notice that the zero-coupon bond sells at a deep discount because the “interest” earned on the investment is not realized until the time of maturity. When we know the present value of a bond, and we want to know the discount rate that equates this value to the cash flows to be obtained on the bond, we can calculate the “yield-to- maturity” on the bond. Extending the previous example of the 20-year 7% coupon bond priced at $1,249.24, we can calculate the yield-to-maturity (YTM) as follows: 1,000 FV 20 N 70 PMT -1,249.24 PV I/YR Answer: 5% Preferred Stock Valuation. The valuation of preferred stock is very much like the calculation for a traditional bond, except that the preferred stock does not mature. In practice, in order to be conceptually consistent with our calculations in dealing with bonds, we can discount the fixed dividend payments to the present using a very high number of years such as 999, and this will get us within a penny or so of the theoretically “correct” value of the stock. For example, let’s take a 7% preferred stock with a par value of $50. For a person who requires a return of 5%, the present value of the stock would be calculated as follows: 3.50 PMT 999 N 5 I/YR PV Answer: $70 Our textbook abbreviates the above calculation by presenting a somewhat shorter formula for calculating the present value of a preferred stock: PV = D/r For our example above, using this abbreviated formula we get a present value of 3.50/.05 = $70. While this abbreviated formula is obviously a quicker way of calculating the value of a preferred stock, I recommend using the first approach used above in order to more fully understand and appreciate the nature of the discounting concept. As can be seen, both methods yield the same result. 3 Common Stock Valuation. While the valuation of common stock is consistent with the idea that the value of any security is the present value of the future expected cash flows, common equity is different than bonds or preferred stock in that the annual dividends are not fixed in amount. In dealing with that reality, the dividend growth model is often used to calculate the value of common stock. The dividend growth model assumes that the dividends will grow by a constant rate of growth (g) each year, and is given mathematically by the following formula: PV = D1 / (r – g) Please note that the dividend (D1) in the formula is the dividend that is expected one year from the date that the calculation is being made. For example, if we have a common stock that paid a dividend of $3 yesterday, and an investor requires 8% is his investment, we can calculate the price of the stock as follows if we assume a 3% constant growth rate: 3.09 / (0.08 – 0.03) = $61.80 Using this same formula, we can obviously do a little algebra and determine the rate of return on a common stock if we know the dividend, the price, and the growth rate. Thus we would have: r = (D1 / PV) + g With the formula presented in this manner, we can see that the required rate of return on a common stock (the total yield) is equal to the dividend yield (D1/PV) plus the capital gains yield (the growth rate). This being the case, if we know the price, the dividend, and the growth rate, we can solve for the required rate of return as follows: (3.09/61.80) + 0.03 = 0.08 End-of-Chapter Problems 1. The Chevron Corporation has outstanding an issue of $1,000 face value, 8 1/2% coupon bonds that mature in 15 years. Calculate the value of one bond to an investor who requires a rate of return of: a. 7% 1,000 FV 85 PMT 15 N 7 I/YR PV Answer: $1,136.62 4 b. 8.5% 1,000 FV 85 PMT 15 N 8.5 I/YR PV Answer: $1,000.00 Please note that, on this one, we did not really need to go through the calculation at all to get the correct answer. If the coupon rate is the same as the required rate of return, then obviously the price of the bond will be exactly the same as the maturity value. c. 10% 1,000 FV