Problem 11-18 Problem 11-19 Problem 11-22
Spreadsheet Templates
Cost of Capital
Spreadsheet Templates by Block, Hirt and Danielsen
Copyright © 2011 McGraw-Hill/Irwin and ANSR Source India Pvt Ltd. (www.ansrsourceindia.com)
MAIN MENU – CHAPTER 11
Foundations of Financial Management
Problem 11-18
Objective: Growth rates and common stock valuation
Student Name:
Course Name:
Student ID:
Course Number:
O’Neal’s Men’s Shops, Inc., specializes in large-size clothing. Business has been good, as indicated by the
six-year growth in earnings per share. The earnings have grown from $1.00 to $1.87.
a. Use Appendix A at the back of the text to determine the compound annual rate of growth in earnings (n = 6).
b. Based on the growth rate determined in part a, project earnings for next year (E1). (Round to two places to the
right of the decimal point.)
c. Assume the dividend payout ratio is 40 percent. Compute (D1). (Round to two places to the right of the
decimal point.)
d. The current price of the stock is $18. Using the growth rate (g) from part a and (D1) from part c, compute Ke.
e. If the flotation cost is $1.5, compute the cost of new common stock (Kn).
Block, Hirt and Danielsen
Foundations of Financial Management
Problem 11-18
Instructions
Using the assumptions below, enter formulas and functions to solve the requirements of this problem.
Information provided:
Year 1 earnings $1.00
Year 6 earnings $1.87
Dividend payout 40%
Current stock price $18
Flotation costs $1.50 per share
Instruction: ( Round intermediate and final values to two decimal places, except in requirement ‘a’. )
a. Use the MS Excel RATE function to determine the compound annual rate of growth in earnings (n = 6).
Annual growth rate 11%
b. Based on the growth rate determined in part a, project earnings for next year (E1). (Round to two places to the
right of the decimal point.)
Next year’s earnings, E1$2.08
c. Assume the dividend payout ratio is 40 percent. Compute (D1). (Round to two places to the right of the
decimal point.)
Next year’s dividend, D1$0.83
d. The current price of the stock is $18. Using the growth rate (g) from part a and (D1) from part c, compute Ke.
Ke15.61%
e. If the flotation cost is $1.5, compute the cost of new common stock (Kn).
Kn16.03%
Solution
Problem 11-19
Objective: Weighted average cost of capital
Student Name:
Course Name:
Student ID:
Course Number:
United Business Forms’ capital structure is as follows:
Debt 35%
Preferred stock 15%
Common equity 50%
The aftertax cost of debt is 7 percent; the cost of preferred stock is 10 percent; and the cost of common
equity (in the form of retained earnings) is 13 percent.
Calculate United Business Forms’ weighted average cost of capital in a manner similar to Table 11-1 on page 332.
Block, Hirt and Danielsen
Foundations of Financial Management
Problem 11-19
Instructions
Complete the table below by entering formulas.
Weights
Debt (Kd)7.0% 35% 2.45%
Preferred stock (Kp)10% 15% 1.50%
Common equity (Ke) (retained earnings) 13.0% 50% 6.50%
Weighted average cost of capital (Ka)10.45%
Cost
(aftertax)
Weighted
Cost
Solution
United Business Forms
Problem 11-22
Objective: Weighted average cost of capital
Student Name:
Course Name:
Student ID:
Course Number:
Given the following information, calculate the weighted average cost of capital for Hamilton Corp. Line up the
calculations in the order shown in Table 11–1 on page 332.
Percent of capital structure:
Debt 30%
Preferred stock 15%
Common equity 55%
Additional information:
Bond coupon rate 13%
Bond yield to maturity 11%
Dividend, expected common $3.00
Dividend, preferred $10.00
Price, common $50.00
Price, preferred $98.00
Flotation cost, preferred $5.50
Growth rate 8%
Corporate tax rate 30%
Block, Hirt and Danielsen
Foundations of Financial Management
Problem 11-22
Instructions
Enter formulas to calculate the after-tax cost of capital.
Weights
Debt (Kd)7.70% 30% 2.31%
Preferred stock (Kp)10.81% 15% 1.62%
Common equity (Ke) (retained earnings) 14.00% 55% 7.70%
Weighted average cost of capital (Ka)11.63%
Cost
(aftertax)
Weighted
Cost
Solution
The Hamilton Corp.