Homework for Chapter 8: Problem # 2 in the text (Chapter 8)NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 8_Excel” TO SOLVE THE FOLLOWING PROBLEM.
You are planning to buy a new house. You currently have $35,000 and your
bank told you that you would need a 15% down payment plus an additional
4% in closing costs. If the house that you want to buy costs $250,000 and you
can make a 7% annual return on your investment, determine the following:
a) When will you have enough money for the down payment and closing costs,
assuming that the $15,000 is the only investment that you make? (20 points)
b) You decide that you want to buy the house in 3 years. How much do you need
to save every month to achieve your goal? (20 points)
c) Assume that three years later the house still has the same price and that you can
get a 15-year mortgage from your bank at a fixed rate of 4.5%:
1. What are the monthly payments on the loan? (12 points)
2. How much will you have to pay the bank each year? (12 points)
3. What is the total interest over the term of the loan? (12 points)
4. How much do you pay on interest and principal the first monthly payment?
(12 points)
5. How much in the 50th month? (Hint: use the IPMT and PPMT functions) (12
points)
Given Information
Money to Invest
Home Price
Downpayment %
Closing Costs %
Rate of Return
Number of Years Until Purchase
Loan Amount
Total Amount Needed at Closing
a) Time Until Having Amount Needed
b) Monthly Savings Needed to Buy in 3 Years
c) Mortgage Term in Years
Mortgage Rate (annual)
Monthly Payment
Total Annual Payment Amount
Total Interest Over Life of Loan
First Month’s Interest
First Month’s Principal
50th Month Interest
50th Month Principal
50th Month Total Payment
$
$
35,000.00
250,000.00
15.00%
4.00%
7.00%
3.00 years
Homework for Chapter 9: Problem # 4 in the text (Chapter 9)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 9_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Financial information for four companies is provided in the following table:
Company A Company B Company C Company D
Last Dividend
$0.50
$0.75
Expected Dividend
$1.25
$0.90
Required rate of Return
15%
14%
17%
12%
Growth Rate #1
10.00%
9.00%
7.00%
8.00%
Growth Rate #2
5.00%
3.00%
4.00%
2.00%
Growth Rate #1 Time
5.00 years
3.00 years
4.00 years
2.00 years
Transition Period
3.00 years
2.00 years
4.00 years
3.00 years
Quoted Price
$6.88
$8.00
$10.50
$9.00
a) If you expect that the dividend of each company will grow at rate #2 into the
foreseeable future (g is constant), at what price would you be willing to buy
each of these stocks? (25 points).
b) Assume that the companies will grow at rate #1 for the amount of time
indicated in the table above. After that, assume that the companies will grow at
rate #2 forever. Under these assumptions, what is the price at which you would
be willing to buy these companies’ stocks using the two-stage dividend growth
model? Calculate your solution twice, first using equation 9-5 on page 260 and
then using the FAME_TwoStageValue user-defined function (25 points).
c) Assume that the transition between growth rates #1 and #2 will be gradual
rather than instantaneous. Using the transition period given in the table, what is
the price at which you would be willing to buy these companies’ stocks using
the H Model dividend growth model? Calculate your solution twice, the first
time using equation 9-8 on page 264 and then using the FAME_HModelValue
user-defined function (25 points).
d) How does the calculated intrinsic value compare to the quoted price of the
stock? Use an IF statement to display whether the stock is undervalued,
overvalued, or fairly valued (25 points).
Homework for Chapter 10: Problem # 1 in the text (Chapter 10)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 10_Excel” TO SOLVE THE FOLLOWING PROBLEM.
You are considering the following bonds to include in your portfolio:
Bond 1
Bond 2
Bond 3
Price
$900.00 $1,100.00 $1,000.00
Face Value
$1,000.00 $1,000.00 $1,000.00
Coupon Rate
7.00%
10.00%
9.00%
Frequency
1
2
4
Maturity (Years)
15
20
30
Required Return
9.00%
8.00%
9.00%
a) Determine the highest price you would be willing to pay for each of these bonds
using the PV function. Also find whether the bond is undervalued, overvalued,
or fairly valued (25 points).
b) Determine the yield to maturity on these bonds using the RATE function
assuming that you purchase them at the given price. Also calculate the current
yield of each bond (25 points).
c) Determine the yield to call of each bond using the RATE function if the time to
first call and the call premium are the following (25 points):
Bond A Bond B Bond C
Call Premium % 3.00% 4.00% 5.00%
Years to first call
5
4
3
d) Assume the following settlement dates for each bond:
Bond 1 Bond 2 Bond 3
Settlement Date 1/1/2018 6/1/2018 9/1/2018
Use the PRICE and YIELD functions to recalculate your answers on parts (a), (b),
and (c) (25 points).
Homework for Chapter 11: Problem # 2 in the text (Chapter 11)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 11_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Black Diamond, Inc., a manufacturer of carbon and graphite products for the
aerospace and transportation industries, is considering several funding alternatives
for an investment project. To finance the project, the company can sell 1,000 15year bonds with a $1,000 face value, 7% coupon rate. The bonds require an
average discount of $50 per bond and flotation costs of $40 per bond when being
sold. The company can also sell 5,000 shares of preferred stock that will pay a $2
dividend per share at a price of $40 per share. The cost of issuing and selling
preferred stocks is expected to be $5 per share. To calculate the cost of common
stock, the company uses the dividend discount model. The firm just paid a
dividend of $3 per common share. The company expects this dividend to grow at a
constant rate of 3% per year indefinitely. The flotation costs for issuing new
common shares are 7%. The company plans to sell 10,000 shares at a price of $50
per share. The company’s tax rate is 40%.
a) Calculate the company’s after-tax cost of long-term debt.
b) Calculate the Company’s cost of preferred equity.
c) Calculate the company’s cost of common equity.
d) Calculate the company’s weighted average cost of capital.
e) What is the company’s weighted average cost of capital without flotation costs?
Given Information
Money to Invest
Home Price
Downpayment %
Closing Costs %
Rate of Return
Number of Years Until Purchase
Loan Amount
Total Amount Needed at Closing
a) Time Until Having Amount Needed
b) Monthly Savings Needed to Buy in 3 Years
c) Mortgage Term in Years
Mortgage Rate (annual)
Monthly Payment
Total Annual Payment Amount
Total Interest Over Life of Loan
First Month’s Interest
First Month’s Principal
50th Month Interest
50th Month Principal
50th Month Total Payment
$
$
35,000.00
250,000.00
15.00%
4.00%
7.00%
3.00 years
Homework for Chapter 9: Problem # 4 in the text (Chapter 9)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 9_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Financial information for four companies is provided in the following table:
Company A Company B Company C Company D
Last Dividend
$0.50
$0.75
Expected Dividend
$1.25
$0.90
Required rate of Return
15%
14%
17%
12%
Growth Rate #1
10.00%
9.00%
7.00%
8.00%
Growth Rate #2
5.00%
3.00%
4.00%
2.00%
Growth Rate #1 Time
5.00 years
3.00 years
4.00 years
2.00 years
Transition Period
3.00 years
2.00 years
4.00 years
3.00 years
Quoted Price
$6.88
$8.00
$10.50
$9.00
a) If you expect that the dividend of each company will grow at rate #2 into the
foreseeable future (g is constant), at what price would you be willing to buy
each of these stocks? (25 points).
b) Assume that the companies will grow at rate #1 for the amount of time
indicated in the table above. After that, assume that the companies will grow at
rate #2 forever. Under these assumptions, what is the price at which you would
be willing to buy these companies’ stocks using the two-stage dividend growth
model? Calculate your solution twice, first using equation 9-5 on page 260 and
then using the FAME_TwoStageValue user-defined function (25 points).
c) Assume that the transition between growth rates #1 and #2 will be gradual
rather than instantaneous. Using the transition period given in the table, what is
the price at which you would be willing to buy these companies’ stocks using
the H Model dividend growth model? Calculate your solution twice, the first
time using equation 9-8 on page 264 and then using the FAME_HModelValue
user-defined function (25 points).
d) How does the calculated intrinsic value compare to the quoted price of the
stock? Use an IF statement to display whether the stock is undervalued,
overvalued, or fairly valued (25 points).
Homework for Chapter 10: Problem # 1 in the text (Chapter 10)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 10_Excel” TO SOLVE THE FOLLOWING PROBLEM.
You are considering the following bonds to include in your portfolio:
Bond 1
Bond 2
Bond 3
Price
$900.00 $1,100.00 $1,000.00
Face Value
$1,000.00 $1,000.00 $1,000.00
Coupon Rate
7.00%
10.00%
9.00%
Frequency
1
2
4
Maturity (Years)
15
20
30
Required Return
9.00%
8.00%
9.00%
a) Determine the highest price you would be willing to pay for each of these bonds
using the PV function. Also find whether the bond is undervalued, overvalued,
or fairly valued (25 points).
b) Determine the yield to maturity on these bonds using the RATE function
assuming that you purchase them at the given price. Also calculate the current
yield of each bond (25 points).
c) Determine the yield to call of each bond using the RATE function if the time to
first call and the call premium are the following (25 points):
Bond A Bond B Bond C
Call Premium % 3.00% 4.00% 5.00%
Years to first call
5
4
3
d) Assume the following settlement dates for each bond:
Bond 1 Bond 2 Bond 3
Settlement Date 1/1/2018 6/1/2018 9/1/2018
Use the PRICE and YIELD functions to recalculate your answers on parts (a), (b),
and (c) (25 points).
Homework for Chapter 11: Problem # 2 in the text (Chapter 11)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 11_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Black Diamond, Inc., a manufacturer of carbon and graphite products for the
aerospace and transportation industries, is considering several funding alternatives
for an investment project. To finance the project, the company can sell 1,000 15year bonds with a $1,000 face value, 7% coupon rate. The bonds require an
average discount of $50 per bond and flotation costs of $40 per bond when being
sold. The company can also sell 5,000 shares of preferred stock that will pay a $2
dividend per share at a price of $40 per share. The cost of issuing and selling
preferred stocks is expected to be $5 per share. To calculate the cost of common
stock, the company uses the dividend discount model. The firm just paid a
dividend of $3 per common share. The company expects this dividend to grow at a
constant rate of 3% per year indefinitely. The flotation costs for issuing new
common shares are 7%. The company plans to sell 10,000 shares at a price of $50
per share. The company’s tax rate is 40%.
a) Calculate the company’s after-tax cost of long-term debt.
b) Calculate the Company’s cost of preferred equity.
c) Calculate the company’s cost of common equity.
d) Calculate the company’s weighted average cost of capital.
e) What is the company’s weighted average cost of capital without flotation costs?
WACC WITH FOLTATION COSTS
Source
Debt
Preferred
Common
Totals
Price
Units
Total Market Value
Weight
After-tax Cost
(a)
(b)
(c)
WACC =
(d)
Additional Bond Data
Additional Preferred Data
Additional Common Data
Tax Rate
40% Dividend
$2.00 Dividend 0
$3.00
Coupon Rate
7% Flotation
$5 Growth Rate
3%
Face Value
$1,000
Flotation
7%
Maturity
15
Flotation
$40
WACC WITHOUT FLOTATION COSTS
(e)
Source
Debt
Preferred
Common
Price
Units
Total Market Value
Weight
After-tax Cost
Totals
WACC =
Additional Bond Data
Additional Preferred Data
Additional Common Data
Tax Rate
40% Dividend
$2.00 Dividend 0
$3.00
Coupon Rate
7% Flotation
$5 Growth Rate
3%
Face Value
$1,000
Flotation
7%
Maturity
15
Flotation
$40
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
