With that in mind, you decide to put an Excel spreadsheet together that values the firm's stock and bonds. The company's stock trades for US$35 per share, with an annual dividend payment of US$1.50, expected to grow to US$1.58 next year. The required return on stocks is 10%, and the dividend is expected to increase by 6% for the foreseeable future. The bonds you are valuing have 3 years to maturity, a 4% annual coupon, and a face value of US$1,000. The market interest rates are 5%.
Use Microsoft Excel to calculate the value of the stocks and the bonds. In addition, answer these questions:
- At the current trading price of US$35 per share, is the dividend discount model value of the stock equal to the market value? If they are different, explain why.
- At the face value of US$1,000, is the bond trading price greater than, equal to, or less than its face value? Explain your answer.
Your submission should include the Excel spreadsheet.© BrainMass Inc. brainmass.com October 16, 2018, 9:27 pm ad1c9bdddf
Valuation of Stock:
P0= $39.50 (Div1/(ke-g)
Div1= Expected dividend per share next year.
G = Growth 6%
Ke = Cost of equity 10%
This solution provides a step by step response illustrating how to compute the valuation of both a stock and a bond. An Excel spreadsheet is attached which illustrates how to derive the appropriate values.
Valuation of stocks and bonds (coupon rates, maturity, face value and more...)
** See ATTACHED file(s) for complete details **
Assume that a bond will make payments every six months as shown as shown on the following timeline
(using six month periods)
0 1 2 3 ..............
$20 $20 $20
What is the maturity of the bond (in years)?
what is the coupon rate in percent?
what is face value?
Suppose the current zero-coupon yield curve for risk-free bonds is as follows:
Maturity(years) 1 2 3
YTM 5% 5.50% 5.75%
a) What is the price per $100 face value of a two year, zero coupon risk free bond?
b)what is the price per $100 face value of a four-year zero coupon risk free bond?
c)What is the risk-free interest rate for a five year maturity?
3)Suppose a seven year $1000 bond with an 8% coupon rate and semiannual coupons is trading with a yield to maturity of 6.75%.
a)is this bond currently trading at a discount, at par,or at a premium? Explain.
b)IF the yield to maturity of the bond rises to 7%(APR with semiannual compounding), what price will the bond trade for?
Maturity(years) 1 2 3
YTM 4% 4.30% 4.50%
Assume zero-coupon yields on default-free securities.
Consider a five-year default-free bond with annual coupons of 5% and a face value of $1000
a) without doing any calculations, determine whether this bond is tradeing at premium or at a discount. Explain
b) What is the yield to maturity on this bond?
c) if the yield to maturity on this bond increased to 5.2%, what would the new price be?
5) Corporate bond question
The following table summarizes the yields to maturity on several one-year, zero-coupon securities:
Security Yield (%)
AAA corporate 3.2
BBB corporate 4.2
B corporate 4.9
a) What is the price of a one-year zero coupon corporate bond with a AAA rating? (expressed as a percentage of the face value.
b)what is the credit spread on AAA rated corporate bonds?
c)What is the credit spread on B-rated corporate bonds?
c) How does the credit spread change with the bond rating? Why?
Krell Industries has a share price of $22.oo today. If Krell is expected to pay a dividend of $0.88 this year, and its stock price is expected to grow to $23.53 at the end of the year;
what is Krell's dividend yield and equity cost of capital?
7) Dorpac Corp. has a dividend yield of 1.5%.
Dorpac equity cost of capital is 8% and its dividends are expected to grow at a constant rate.
a) what is the expected growth rate of Dorpac's dividends?
b) what is the expected growth rate of Dorpac's share price?
Suppose Cisco Systems pays no dividends but spent $5 billion on share repurchases last year.
If Ciscos equity cost of capital is 12% and if the amount spent on repurchases is expected to grow by 8% per year, estimate Cisco's
market capitalization. If Cisco has 6 billion shares outstanding, what stock price does this correspond to?
In Mid 2006, CocaCola had a share price of $43. its dividend wa $1.24 and you expect CocaCOla to raise this dividend by approximately 7% per year in perpetuity.
a) If Coca Cola equity cost of capital is 8%, what share price would you expect based on your estimate of the dividend growth rate?
b) Given cocacola share price, what would you conclude about your assessment of CocaCola's future divident growth?