Week 4 Final Ass

Cost of Capital
WACC Problems

Input boxes in tan

Output boxes in yellow
Given data in blue
Answers in green

NOTE: Some functions used in these spreadsheets may require that

the "Analysis ToolPak" or "Solver Add-in" be installed in Excel.
To install these, click on "Tools|Add-Ins" and select "Analysis ToolPak"
and "Solver Add-In."
nal Assignment
Long-Term Financing
Case Study

lysis ToolPak"
 Problem 1 Finding the WACC

Given the following information for Lightning Power Co., find the WACC. Assume the company’s ta

Debt: 10,000 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 $63 per share; beta is 1.15.

Preferred Stock: 35,000 shares of 3.5 percent preferred stock outstanding, currently selling
for $72 per share.

Market: 7 percent market risk premium and 3.2 percent risk-free rate.

Input Area:

Tax rate 21%

Debt
Bonds outstanding 10,000
Settlement date 01/01/00
Maturity date 01/01/25
Annual coupon rate 6.40%
Coupons per year 2
Bond price (% of par) 108 <--This quoted price is not listed as a rate, bu
Redemption value (% of par) 100 <--This quoted price is not listed as a rate, bu
Par value $ 1,000

Common stock
Shares outstanding 495,000
Beta 1.15 =b E

Share price $ 63

Preferred stock outstanding

Shares outstanding 35,000
Dividend percentage 3.50% <---Also referred to as 'Coupon rate'
Share price $ 72

Market
Market risk premium 7.00% = (RM - Rf) = (Expected return on market - ris
Risk-free rate 3.20% = (Rf)
Complete the following analysis. Do not hard code values in your calculations. Leave the "Basi
input blank in the YIELD function. You must use the built-in Excel function to answer this
question.

Output Area:

Market value of debt ? = Bond Price (% of par)/Redemption value (

Market value of equity ? = Shares outstanding of common stock * Sh

Market value of preferred ? = Shares outstanding of preferred stock * Sh

Market value of firm ? = Total of all financing components (Total As

Market value weight of debt ? = MV of debt/MV of firm

Market value weight of equity ? = MV of equity/MV of firm

Market value weight of preferred ? = MV of prefered/MV of firm

Pretax cost of debt ? <--Must be calculated using YIELD fx for cre

Aftertax cost of debt ? <---Pretax cost of debt * (1-Tax rate)

Cost of equity ? <---Do we use DGM or SML formula here?

Cost of preferred ? <---RP = D/P0 Hint: Par value of preferr

Calculate dividend paym
WACC ? <---WACC formula = WACC=(E/V)×RE+(P/V)
sume the company’s tax rate is 21 percent.

ears to maturity,

is 1.15.

urrently selling

e is not listed as a rate, but reporting 10% of the bond price

e is not listed as a rate, but reporting 10% of the par value

as 'Coupon rate'

cted return on market - risk-free rate)

lations. Leave the "Basis"
ion to answer this

f par)/Redemption value (% of par)*Par Value*Bonds Outstanding

ing of common stock * Share price

ing of preferred stock * Share price

cing components (Total Assets = Total debt + Total equity + Total Preferred)

ed using YIELD fx for credit cosideration

ebt * (1-Tax rate)

M or SML formula here? SML = Rf + B * (RM - Rf)

DGM = D1/P0 + g
Hint: Par value of preferred stock has a stated value of $100.
Calculate dividend payment based on the coupon rate = coupon rate*par value
= WACC=(E/V)×RE+(P/V)×RP+(D/V)×RD×(1−TC)
 Problem 2 Calculating the WACC [LO 3]

You are given the following information concerning Parrothead Enterprises:

Debt: 9,700 7.2 percent coupon bonds outstanding, with 23 years to maturity
and a quoted price of 105.75. These bonds pay interest semiannually.

Common Stock: 260,000 shares of common stock selling for $65.20 per
share. The stock has a beta of .97 and will pay a dividend of $3.40 next year.
The dividend is expected to grow by 5.2 percent per year indefinitely.

Preferred Stock: 8,700 shares of 4.6 percent preferred stock selling at $94.70
per share.

Market: 11.3 percent expected return, risk-free rate of 3.95 percent, and a 22
percent tax rate.

Input Area:

Debt
Bonds outstanding 9,700
Annual coupon rate 7.20%
Settlement date 01/01/00
Maturity date 01/01/23
Bond price (% of par) 105.75 <--This quoted price is not listed as a rate, but reporting at w
Coupons per year 2
Face value (% of par) 100 <--This quoted price is not listed as a rate, but reporting at w

Common stock
Shares outstanding 260,000
Share price $ 65.20 = P0
Beta 0.97 =b E

Dividend next year $ 3.40 =D1

Dividend growth rate 5.20% =g

Preferred stock outstanding

Shares outstanding 8,700
Coupon rate 4.60%
Share price $ 94.70

Market
Market expected return 11.30% = (RM)
Risk-free rate 3.95% = (Rf)
Tax rate 22%
Complete the following analysis. Do not hard code values in your calculations. Leave the "Basis"
input blank in the YIELD function. You must use the built-in Excel function to answer this question.

Output Area:

Market value of debt ? = (Bond Price (% of par)*10) * Bonds Outstanding

Market value of equity ? = Shares outstanding of common stock * Share price
Market value of preferred ? = Shares outstanding of preferred stock * Share price
Market value of firm ? = Total of all financing components (Total Assets = Total de

Pretax cost of debt ? <--Must be calculated using YIELD fx for credit cosideration
Aftertax cost of debt ? <---Pretax cost of debt * (1-Tax rate)

Cost of equity - SML/CAPM ? SML/CAPM = Rf + bE * (RM - Rf)

Cost of equity - DGM ? DGM = D1/P0 + g
Cost of equity - Average ? <---Cost of Equity to use in WACC formula (Connect Co

Cost of preferred ? <---RP = D/P0 Hint: Par value of preferred stock has a s
Calculate dividend payment based on the
WACC ?
as a rate, but reporting at what percentage the bond price is of the par value

as a rate, but reporting at what percentage the redemption value is of the par value
eave the "Basis"
swer this question.

onds Outstanding
stock * Share price
d stock * Share price
ts (Total Assets = Total debt + Total equity + Total Preferred)

D fx for credit cosideration

CC formula (Connect Cost of Equity Answer)

of preferred stock has a stated value of $100.

end payment based on the coupon rate = coupon rate*par value
 Chapter Case
Stephenson Real Estate Recapitalization

Stephenson Real Estate Company was founded 25 years ago by the current CEO, Robert
Stephenson. The company purchases real estate, including land and buildings, and rents the property
to tenants. The company has shown a profit every year for the past 18 years, and the shareholders are
satisfied with the company’s management. Prior to founding Stephenson Real Estate, Robert was the
founder and CEO of a failed alpaca farming operation. The resulting bankruptcy made him extremely
averse to debt financing. As a result, the company is entirely equity financed, with 8.7 million shares of
common stock outstanding. The stock currently trades at $46.50 per share.

Stephenson is evaluating a plan to purchase a huge tract of land in the southeastern United States for
$65 million. The land will subsequently be leased to tenant farmers. This purchase is expected to
increase Stephenson’s annual pretax earnings by $14 million in perpetuity. Kim Weyand, the
company’s new CFO, has been put in charge of the project. Kim has determined that the company’s
current cost of capital is 12.5 percent. She feels that the company would be more valuable if it included
debt in its capital structure, so she is evaluating whether the company should issue debt to entirely
finance the project. Based on some conversations with investment banks, she thinks that the company
can issue bonds at par value with a coupon rate of 8 percent. From her analysis, she also believes that
a capital structure in the range of 70 percent equity/30 percent debt would be optimal. If the company
goes beyond 30 percent debt, its bonds would carry a lower rating and a much higher coupon because
the possibility of financial distress and the associated costs would rise sharply. Stephenson has a 21
percent corporate tax rate (state and federal).

Input area:

Shares outstanding 8,700,000

Share price $ 46.50
Purchase price of land $ 65,000,000
Perpetual earnings increase $ 14,000,000
Current cost of capital 12.50%
Cost of new debt 8%
Optimal equity weight 70%
Optimal debt weight 30%
Tax rate 21%

Output area:

1) If Stephenson wishes to maximize its total market value, would you recommend that it issue debt or
equity to finance the land purchase? Explain.

2) Assets ? Equity ?
Total assets ? Debt & Equity ?

3) a) Perpetual aftertax earnings ?

NPV of purchase ?

b) Balance Sheet
Old assets ?
NPV of project ? Equity ?
Total assets ? Debt & Equity ?

New share price ?

Shares to issue ?
 c) Balance Sheet
Cash ?
Old assets ?
NPV of project ? Equity ?
Total assets ? Debt & Equity ?

Total shares outstanding ?

Share price ?

d) PV of earnings increase ?

Balance Sheet
Old assets ?
PV of project ? Equity ?
Total assets ? Debt & Equity ?

4) a) Value of levered company ? <---VL = VU+TC*D Note*: TC = Tax rate

b) Balance Sheet
Value unlevered ? Debt ?
Tax shield value ? Equity ?
Total assets ? Debt & Equity ?

Stock share price ?

5) Which method of financing maximizes the per share stock price of Stephenson's equity?

Ross, S. A., Westerfield, R. W., & Jordan, B. D. (2023). Essentials of Corporate Finance (11th ed.). McGraw Hill LLC. P. 462
ISBN13: 9781264101573 (bound edition); ISBN 9781265412548 (ebook)