Need to create an Excel spreadsheet to organize and answer to the following problem:
Spinkbottle, Inc. currently has the following sources of financing on 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, 7% coupon bonds that mature in 25 years.
What is the value of one such bond if investors require a rate of return of 5%? ___________
2. An issue of $1,000 face value, 6.4% coupon bonds that mature in 20 years.
Calculate the price of these bonds 16 years from now if market rates at that time are at 7%. ____________
3. An issue of $1,000 face value, zero coupon bonds which mature in 20 years.
What is the value of one such bond if investors require a rate of return of 5%? ____________
4. An issue of $1,000 face value, 7% coupon bonds which mature in 14 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 7%. ___________
6. An issue of $5 preferred stock with a par value of $50.
Calculate the yield on such stock if its market price is $42. __________
7. An issue of common stock that paid a dividend yesterday of $2.80.
Calculate the value of one share of this stock to an investor who requires a 10.4% 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 $60 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.10, and is currently priced at $79 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 6%. _____________
10. An issue of common stock that paid a dividend yesterday of $4.90, and is currently priced at $60 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.6%. _____________