Copy of Lagusay_Smart_Zutter_Problem_5.35_Start Done
xlsx
School
Northern Alberta Institute of Technology *
*We aren’t endorsed by this school
Course
FNCE-331
Subject
Finance
Date
Apr 3, 2024
Type
xlsx
Pages
12
Uploaded by JusticeOryx2071
Spreadsheet Exercise: Problem 5.35
To Do
Create a spreadsheet to model and analyze the distribution of two secu
a.
Estimate the beta values for INTC and PG based on the data from t
b.
Using the CAPM, determine the required rates of return for both IN
c.
Solution
a.
Estimate the beta values for INTC and PG based on the data from t
Katie plans to form a portfolio consisting of two securities, Intel (INTC
amount that she invests in each stock. Katie’s professor suggests that s
r
j
= r
f Katie measures r
f
using the current long-term Treasury bond return of 3
that figure to measure r
m
.
Complete the steps below using cell references to given data or previou
down a column, an absolute cell reference or a mixed cell reference m
type in numerical data into a cell or function. Instead, make a referenc
cases, unless otherwise directed, use the earliest appearance of the dat
Katie has decided that the portfolio will be distributed between INT
and betas of the portfolio. Determine the weighted average required
-20.0%
-15.0%
-10.0%
-5.0%
0.0%
5.0%
10.0%
15.
-10.0%
-5.0%
0.0%
5.0%
10.0%
15.0%
20.0%
25.0%
f(x) = − 0.119451918168832 x + 0.007157233989827
f(x) = − 0.161770919471419 x + 0.011431814805318
Stock Return, %.
Beta for INTC
-0.185
Beta for PG
-0.1195
b.
Using the CAPM, determine the required rates of return for both IN
Risk-free rate
3%
Expected market return
11%
Required rate of return for INTC
2%
Required rate of return for PG
2.0%
c.
Weight of INTC in the portfolio
60%
Weight of PG in the portfolio
40%
Weighted average required rate of return
2%
Katie has decided that the portfolio will be distributed between INT
and betas of the portfolio. Determine the weighted average required
war
where
war
= weighted average required rate of retur
w
i
= weight of security
i
in the portfolio
r
i
= required return of security
i
in the portfol
w
j
= weight of security
j
in the portfolio
r
j
= required return of security
j
in the portfol
wab
where
wab
= weighted average beta for the portfolio
w
i
= weight of security
i
in the portfolio
b
i
= beta for security
i
w
j
= weight of security
j
in the portfolio
b
j
= beta for security j
-15.0%
Column D
Linear (Column D)
Column E
Market Return, %
Weighted average beta
-0.159
2/1/2017
-1.7%
3/1/2017
0.4%
4/1/2017
0.2%
5/1/2017
-0.1%
6/1/2017
-5.9%
7/1/2017
5.1%
8/1/2017
-1.1%
9/1/2017
9.4%
10/1/2017
19.5%
11/1/2017
-1.4%
12/1/2017
3.6%
1/1/2018
4.3%
2/1/2018
2.4%
3/1/2018
6.4%
4/1/2018
-0.9%
5/1/2018
6.9%
6/1/2018
-9.4%
7/1/2018
-3.2%
8/1/2018
0.7%
9/1/2018
-1.8%
10/1/2018
-0.9%
11/1/2018
5.2%
12/1/2018
-4.2%
1/1/2019
0.4%
2/1/2019
9.1%
Requirements
Stock Return, %
Intel (INTC)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
1
2
Note: Compare calculated beta value with that obtained in the equa
3
Note: Compare calculated beta value with that obtained in the equa
4
5
6
7
9
Save the workbook. Close the workbook and then exit Excel. Subm
In cells C15:E29
, insert a Scatter Chart to estimate betas for Intel (
Inserting a Chart
On the Insert tab, in the Charts group, click the arrow next to Insert
Selecting Data Series
Then in Select Data Source window, delete any series created autom
Add new series for the security market line using cells F50:F74
for
Add new series for the stocks from your portfolio using cells F50:F
Trendline Options
Select any point of the security market line and right click on it. Ch
name. Choose Series1.
Select any point of the trendline and right click on it. Choose Form
Select any point of the security market line and right click on it. Ch
name. Choose Series2.
Select any point of the trendline and right click on it. Choose Form
Edit Chart Elements
Add axis titles. Replace Axis Title for the horizontal axis with Mar
In cell D31
, by using cell references and the function SLOPE
, calc
In cell D32
, by using cell references and the function SLOPE
, calc
In cell D38
, by using cell references, calculate the required rate of r
In cell D39
, by using cell references, calculate the required rate of r
In cell D45
, by using cell references, calculate the weighted averag
In cell D46
, by using cell references, calculate the weighted averag
urities in the portfolio:
the table far below using the graphic method and Excel SLOPE function.
NTC and PG.
the table far below using the graphic method and Excel SLOPE function.
C) and Procter& Gamble (PG), and she wonders how the portfolio’s return will depend
she use the capital asset pricing model to define the required returns for the two compan
+ [b
j × (r
m
- r
f
)]
3%. Katie determines that the average return on the S&P 500 Index over the last severa
us calculations. In some cases, a simple cell reference is all you need. To copy/paste a may be preferred. If a specific Excel function is to be used, the directions will specify the
ce to the cell in which the data is found. Make your computations only in the blue cells ta in your formulas, usually the Given Data section
.
TC and PG in a 60% and 40% split, respectively. Hence, a weighted average can be cal
d rate of return and the weighted average beta for the portfolio.
.0%
20.0%
25.0%
NTC and PG.
TC and PG in a 60% and 40% split, respectively. Hence, a weighted average can be cal
d rate of return and the weighted average beta for the portfolio using the following mod
r = (w
i
× r
i
) + (w
j
× r
j
)
rn for the portfolio
lio
lio
b = (w
i
× b
i
) + (w
j
× b
j
)
o
Linear (Column E)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Market Return, %
4.8%
-7.5%
-1.3%
-0.3%
-2.8%
-1.9%
1.6%
-2.7%
-1.1%
-1.2%
4.2%
-3.9%
2.4%
-0.5%
-1.4%
-3.8%
-5.1%
-4.4%
5.0%
-5.7%
2.1%
-2.4%
-6.0%
-10.2%
-8.4%
6.6%
1.0%
4.3%
-8.8%
-0.8%
2.1%
-4.5%
6.7%
-1.3%
3.6%
-6.5%
3.5%
-5.8%
0.3%
-1.3%
6.5%
14.9%
7.5%
-3.9%
-2.7%
18.1%
4.9%
-14.1%
4.5%
-5.5%
Stock Return, %
Procter& Gamble (PG)
ation of the corresponding trendline.
ation of the corresponding trendline.
mit the workbook as directed.
(INTC) and Procter& Gamble (PG). t Scatter (X,Y) or Bubble Chart and choose Scatter Chart. matically. r the X values and cells D50:D74
for the Y values. Do not name the series.
F74
for the X values and cells E50:E74
for the Y values. Do not name the series.
hoose Add Trendline. In the Trendline Options window, select Linear with automatic tr
mat Trendline. Tick on Display Equation on chart.
hoose Add Trendline. In the Trendline Options window, select Linear with automatic tr
mat Trendline. Tick on Display Equation on chart.
rket Return, %
and Axis Title for the vertical axis with Stock Return, %
.
culate the beta value for INTC.
culate the beta value for PG.
return for INTC.
return for PG.
ge required rate of return.
ge beta.
d on the
nies.
al years is 11%, so she uses formula across a row or e use of that function. Do not highlighted below. In all lculated for both the returns
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
lculated for both the returns dels for the calculations:
Points
5
1
1
1
1
1
1
0
rendline rendline
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Documents
Related Questions
i need the answer quickly
arrow_forward
Jason Jackson is attempting to evaluate two possible portfolios consisting of the same five assets but held in different proportions. He is particularly interested in using beta to compare the risk of the portfolios and, in this regard, has gathered the following data:
LOADING...
.
a. Calculate the betas for portfolios A and B.
b. Compare the risk of each portfolio to the market as well as to each other. Which portfolio is more risky?
Question content area bottom
Part 1
Data table
(Click on the icon here
in order to copy its contents of the data table below into a spreadsheet.)
Portfolio Weights
Asset
Asset Beta
Portfolio A
Portfolio B
1
1.35
17%
29%
2
0.69
26%
8%
3
1.24
10%
22%
4
1.06
11%
20%
5
0.87
36%
21%
Total
100%
100%
a. The beta of portfolio A is
enter your response here.
(Round to three…
arrow_forward
Jm. 157.
arrow_forward
Question 1 Fill the parts in the above table that are shaded in yellow. You will notice that there are nineline items.
Question 2Using the data generated in the previous question (Question 1);a) Plot the Security Market Line (SML) b) Superimpose the CAPM’s required return on the SML c) Indicate which investments will plot on, above and below the SML? d) If an investment’s expected return (mean return) does not plot on the SML, what doesit show? Identify undervalued/overvalued investments from the graph
arrow_forward
Can you answer please and kindly show detailed human working.
arrow_forward
Can you please answer and kindly show detailed human working.
arrow_forward
Questions:
a. Compute the expected return for stock X and for stock Y
b. Compute the standard deviation for stock X and for stock Y.
c. Determine the best course to take for investing.
arrow_forward
use attachments to answer question
This question relates to Diagrams 6 - 9 from the 9.2 diagrams, each of which shows a set of portfolios plotted on a set of risk/return axes.
Which diagram shows (in red) the set of efficient portfolios in the presence of a risk-free asset?
Select one:
a.
Diagram 6
b.
Diagram 7
c.
Diagram 8
d.
Diagram 9
arrow_forward
Can you accurately answer these, please? show detailed human working out.
It's for financial managment
arrow_forward
Same question in the second picture. Added info box
arrow_forward
Rose Berry is attempting to evaluate two possible portfolios, which consist of the same five assets held in different proportions. She is particularly interested in using beta to compare the risks of the portfolios, so she has gathered the data shown in the following table:
Portfolio weights
Asset
Asset beta
Portfolio A
Portfolio B
1
1.43
15%
20%
2
0.69
40%
5%
3
1.15
10%
35%
4
1.42
5%
20%
5
0.97
30%
20%
Totals
100%
100%
a. Calculate the betas for portfolios A and B.
b. Compare the risks of these portfolios to the market as well as to each other. Which portfolio is more risky?
arrow_forward
9. Jason Jackson is attempting to evaluate two possible portfolios consisting of the same five assets but held in different
proportions. He is particularly interested in using beta to compare the risk of the portfolios and, in this regard, has gathered
the following data: ®.
a. Calculate the betas for portfolios A and B.
b. Compare the risk of each portfolio to the market as well as to each other. Which portfolio is more risky?
Review Only
Click the icon to see the Worked Solution.
a. The beta of portfolio A is
(Round to three decimal places.)
The beta of portfolio B is
(Round to three decimal places.)
b. Portfolio (1)
is slightly less risky than the market (average risk), while portfolio (2).
IS
more risky than the market. Portfolio (3)
given increase or decrease in market risk. (Select from the drop-down menus.)
L's return will move more than portfolio (4)-
's for a
Portfolio (5)-
is the more risky portfolio. (Select from the drop-down menu.)
8: Data Table
(Click on the icon here
in…
arrow_forward
Use attachments to answer questions
This question relates to Diagrams 1 - 4 from the diagrams attached , each of which shows a set of portfolios plotted on a set of risk/return axes.
Which diagram shows (in red) the set of feasible portfolios?
Select one:
a.
Diagram 1
b.
Diagram 2
c.
Diagram 3
d.
Diagram 4
arrow_forward
Finance
arrow_forward
Show detailed steps to solve the following question.
Consider a portfolio comprised of three securities in the following proportions and with the indicated security beta.
a.) What is the portfolios beta?
b.) Wht is the portfolios expected return?
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Related Questions
- i need the answer quicklyarrow_forwardJason Jackson is attempting to evaluate two possible portfolios consisting of the same five assets but held in different proportions. He is particularly interested in using beta to compare the risk of the portfolios and, in this regard, has gathered the following data: LOADING... . a. Calculate the betas for portfolios A and B. b. Compare the risk of each portfolio to the market as well as to each other. Which portfolio is more risky? Question content area bottom Part 1 Data table (Click on the icon here in order to copy its contents of the data table below into a spreadsheet.) Portfolio Weights Asset Asset Beta Portfolio A Portfolio B 1 1.35 17% 29% 2 0.69 26% 8% 3 1.24 10% 22% 4 1.06 11% 20% 5 0.87 36% 21% Total 100% 100% a. The beta of portfolio A is enter your response here. (Round to three…arrow_forwardJm. 157.arrow_forward
- Question 1 Fill the parts in the above table that are shaded in yellow. You will notice that there are nineline items. Question 2Using the data generated in the previous question (Question 1);a) Plot the Security Market Line (SML) b) Superimpose the CAPM’s required return on the SML c) Indicate which investments will plot on, above and below the SML? d) If an investment’s expected return (mean return) does not plot on the SML, what doesit show? Identify undervalued/overvalued investments from the grapharrow_forwardCan you answer please and kindly show detailed human working.arrow_forwardCan you please answer and kindly show detailed human working.arrow_forward
- Questions: a. Compute the expected return for stock X and for stock Y b. Compute the standard deviation for stock X and for stock Y. c. Determine the best course to take for investing.arrow_forwarduse attachments to answer question This question relates to Diagrams 6 - 9 from the 9.2 diagrams, each of which shows a set of portfolios plotted on a set of risk/return axes. Which diagram shows (in red) the set of efficient portfolios in the presence of a risk-free asset? Select one: a. Diagram 6 b. Diagram 7 c. Diagram 8 d. Diagram 9arrow_forwardCan you accurately answer these, please? show detailed human working out. It's for financial managmentarrow_forward
- Same question in the second picture. Added info boxarrow_forwardRose Berry is attempting to evaluate two possible portfolios, which consist of the same five assets held in different proportions. She is particularly interested in using beta to compare the risks of the portfolios, so she has gathered the data shown in the following table: Portfolio weights Asset Asset beta Portfolio A Portfolio B 1 1.43 15% 20% 2 0.69 40% 5% 3 1.15 10% 35% 4 1.42 5% 20% 5 0.97 30% 20% Totals 100% 100% a. Calculate the betas for portfolios A and B. b. Compare the risks of these portfolios to the market as well as to each other. Which portfolio is more risky?arrow_forward9. Jason Jackson is attempting to evaluate two possible portfolios consisting of the same five assets but held in different proportions. He is particularly interested in using beta to compare the risk of the portfolios and, in this regard, has gathered the following data: ®. a. Calculate the betas for portfolios A and B. b. Compare the risk of each portfolio to the market as well as to each other. Which portfolio is more risky? Review Only Click the icon to see the Worked Solution. a. The beta of portfolio A is (Round to three decimal places.) The beta of portfolio B is (Round to three decimal places.) b. Portfolio (1) is slightly less risky than the market (average risk), while portfolio (2). IS more risky than the market. Portfolio (3) given increase or decrease in market risk. (Select from the drop-down menus.) L's return will move more than portfolio (4)- 's for a Portfolio (5)- is the more risky portfolio. (Select from the drop-down menu.) 8: Data Table (Click on the icon here in…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you