Finance Excel

Problem 1. You have $10,000 to invest in a stock portfolio. Your choices are Stock X with an expectedreturn of 12.4 percent and Stock Y with an expected return of 10.1 percent. If your goal is to create a
portfolio with an expected return of 10.85 percent, how much money will you invest in Stock X? In Stock
Y?
Problem 2. Consider the following information:
1. W hat is the expected return on an equally weighted portfolio of these three stocks?
2. W hat is the expected return of a portfolio invested 20 percent each in A and B and 60 percent in C?
3. Assume there is an equal probability of a Boom and of a Bust. What is the standard deviation of each
stock?
Problem 3. Consider the following information:
1. Your portfolio is invested 30 percent each in A and C, and 40 percent in B. What is the expected return
of the portfolio?
2. Now assume equal probabilities for each State of the Economy. W hat is the variance of this portfolio?
The standard deviation?
Stock X with an expected
your goal is to create a
nvest in Stock X? In Stock
stocks?
d B and 60 percent in C?
standard deviation of each
What is the expected return
e variance of this portfolio?
Problem 1
amount to invest
E(Rp)
E(Rx)
E(Ry)
$10,000
10.85%
12.40%
10.10%
Wx
Wy
32.61%
67.39%
$3,261
$6,739
$10,000
Problem 2
Boom
Bust
Rate of Return if State Ocurs
Probablity
Stock A
Stock B
Stock c
0.75
7%
18%
27%
0.25
12%
-8%
-21%
Part A
E(R)
weights
8.25%
0.33
E(Rp)
11.58%
Probablity
Boom
Bust
8.25%
0.20
E(Rp)
12.95%
Boom
Bust
Variance
Stnd dev
average (mean)
15.00%
0.33
Rate of Return if State Ocurs
Stock A
Stock B
Stock c
0.75
7%
18%
27%
0.25
12%
-8%
-21%
Part A
E(R)
weights
Probablity
11.50%
0.33
12%
0.20
15%
0.60
Rate of Return if State Ocurs
Stock A
Stock B
Stock c
0.5
7%
18%
27%
0.5
12%
-8%
-21%
0.000625
2.5%
9.5%
0.0169
13.0%
5.0%
0.0576
24.0%
3.0%
Problem 3 (Book 10)
State
boom
good
poor
bust
E(R)
W
E(Rp)
Probablity
0.15
0.45
0.3
0.1
1.00
Return if State
A
B
35%
40%
16%
17%
-1%
-3%
-10%
-12%
C
28%
9%
1%
-9%
11.15%
11.55%
7.65% = E(R) for each stock given the probability for each state
0.3
0.4
0.3
10.26% = overall E(R) of the portfolio
Variance and Standard Deviation
var
0.0296
stnd dev
17.2%
0.0400
20.0%
0.0184
13.6%
bability for each state
Assume
Firm total debt + equity oustanding
Debt outstanding
Equity oustanding
Cost of Debt
Cost of Equity
$
10,000,000
4,000,000
6,000,000
12%
10%
Firm weighted average cost of capital
10.80%
generalized cap struc cost
D0
$
4.00
P0
$
g
60.00
6%
RE
13.07%
Greater States
Year
Dividend
2017 $
2018 $
2019 $
2020 $
2021 $
annual
growth
rate
1.10
1.20
1.35
1.40
1.55
9.09%
12.50%
3.70%
10.71%
Average growth rate
9.00%
Estimate Growth Rate
coupon
time to maturity
7%
30
price
96
settlement
maturity
redemption
yield to maturity
1/1/2023
1/1/2045
100
issued a 30-year bond, 8 years ago
7.37%
Cost of Debt YTM
borrowing
interest rate
tax rate
1,000,000
9%
21%
annual interest
reduction to tax bill
90,000
18,900
after-tax interest bill
after-tax interest rate
71,100
7.11%
OR
after-tax interest = RD X (1 – TC) where TC = the corporate tax rate
after-tax interest rate
7.11%
Aftertax cost of D
Assume
debt to equity ratio
Then
E/V
0.75
57.1%