What is the excel spreadsheet solution for this exercise? Finding the WACC. Give
ID: 1176154 • Letter: W
Question
What is the excel spreadsheet solution for this exercise? Finding the WACC. Given the following information for Gerken Power Co., find the WACC. Assume the company’s tax rate is 35 percent. Debt: 15,500 6.4 percent coupon bonds outstanding, $1,000 par value, 25 years to maturity, selling for 108 percent of par; the bonds make semiannual payments. Common stock: 495,000 shares outstanding, selling for $81 per share; beta is 1.20. Preferred stock: 20,000 shares of 4.2 percent preferred stock outstanding, currently selling for $92 per share. Market: 7 percent market risk premium and 3.1 percent risk-free rate.
Explanation / Answer
1-
cost of debt
Using rate function in MS excel
rate(nper,pmt,pv,fv,type) nper = 25*2 = 50 PMT = 32 pv = -1080 fv= 1000 type = 0
3%
2.90%
After tax cost of debt (Yearly)
2.90*2*(1-.35)
3.77
2-
cost of preferred stock
preferred dividend/market price
4.2/92
0.045652
4.57%
3-
cost of common stock
risk free return+(market return-risk free rate)*beta
3.2+(7)*1.2
11.6
WACC
source
value
weight
cost
weight*cost
debt
16740000
0.2862762
2.9
0.830201
preferred
1640000
0.0280462
4.57
0.128171
common stock
40095000
0.6856776
11.6
7.953861
total
58475000
WACC
sum of weight*cost
8.91
1-
cost of debt
Using rate function in MS excel
rate(nper,pmt,pv,fv,type) nper = 25*2 = 50 PMT = 32 pv = -1080 fv= 1000 type = 0
3%
2.90%
After tax cost of debt (Yearly)
2.90*2*(1-.35)
3.77
2-
cost of preferred stock
preferred dividend/market price
4.2/92
0.045652
4.57%
3-
cost of common stock
risk free return+(market return-risk free rate)*beta
3.2+(7)*1.2
11.6
WACC
source
value
weight
cost
weight*cost
debt
16740000
0.2862762
2.9
0.830201
preferred
1640000
0.0280462
4.57
0.128171
common stock
40095000
0.6856776
11.6
7.953861
total
58475000
WACC
sum of weight*cost
8.91
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.