+1(978)310-4246 credencewriters@gmail.com
Select Page

Description

Discuss an efficient portfolio and the relevance of the capital asset pricing model.

Evaluate the success of portfolio managers in diversifying their portfolios as measured against benchmarks.

Explain how the portfolio effect works to reduce the risk of an individual security.

In this assignment, you will measure the level of risk using the Sharpe method and then analyze the risk-adjusted returns in your original portfolio of 5 stocks.

Review the original portfolio you developed in

3-3: Portfolio Project (Part 1) – Initial Selection

.

Analyze the risk-adjusted returns using the Sharpe model on the stocks in your original portfolio.

Use the

Risk Analysis Guide

and the associated

Excel file

to assist you in setting up your analysis. (I’ve attcahed those documents to the files section)

The original stocks I chose to use are

AT&T (Ticker Symbol: T)

Coca-Cola (Ticker Symbol: KO)

Tesla (Ticker Symbol: TSLA)

Logistics XPO (Ticker Symbol: XPO)

Apple (Ticker Symbol: AAPL)

Chevron (CVX)
UPS (UPS)
Joy Global (JOY)
Walgreens (WAG)
Whole Foods (WFM)
Total
Shares
1,710
2,413
3,222
4,840
2,331
My Original Cost
\$
116.90
\$
82.87
\$
62.06
\$
41.32
\$
85.79
Original Value Original Weight
\$ 199,899.00
20%
\$ 199,965.31
20%
\$ 199,957.32
20%
\$ 199,988.80
20%
\$ 199,976.49
20%
\$ 999,786.92
Current Price
\$
118.64
\$
83.55
\$
58.15
\$
48.31
\$
83.68
Do this simple exercise: use the closing price as of the day that you turned in your assign
near then for the “My original cost” value. Once you have that, you’ll be able to calculat
shares you purchased at the beginning of this assignment. Just take what you allocated
companies, and then divide it by the “My Original Cost” value to determine the shares.
Then collect the “current price and input those values. Pretty much everything else will
this worksheet once you do that. The only other thing that you want to input here are th
that you would have received during the period that you held the stock.
On this worksheet, we’re going to calcualte your portfolio return, and you’ll also determn
portfolio weights, which you need in order to determine the portfolio return and standa
tab “4. Portfolio return and SD”
Current Value
Current Weight
\$ 202,874.40
19.9%
\$ 201,606.15
19.8%
\$ 187,359.30
18.4%
\$ 233,820.40
22.9%
\$ 195,058.08
19.1%
\$ 1,020,718.33
Change in Value % Change In value
\$
2,975.40
1.5%
\$
1,640.84
0.8%
\$
(12,598.02)
-6.3%
\$
33,831.60
16.9%
\$
(4,918.41)
-2.5%
\$
20,931.41
2.1%
that you turned in your assignment or some e date
that, you’ll be able to calculate the number of
Just take what you allocated to each of your
ue to determine the shares.
tty much everything else will take care of itself on
you want to input here are the dividends per share
eld the stock.
eturn, and you’ll also determne your current
e portfolio return and standard deviation over on
Dividends
\$
\$
\$
\$
\$
\$

Return, including dividends
1.5%
0.8%
-6.3%
16.9%
-2.5%
2.1%
No dividends were received during the holding
s were received during the holding period
The average Risk Free Rate
Date
3 month T-Bill
Jan-06
4.24
Feb-06
4.43
Mar-06
4.51
Apr-06
4.6
May-06
4.72
Jun-06
4.79
Jul-06
4.95
Aug-06
4.96
Sep-06
4.81
Oct-06
4.92
Nov-06
4.94
Dec-06
4.85
Jan-07
4.98
Feb-07
5.03
Mar-07
4.94
Apr-07
4.87
May-07
4.73
Jun-07
4.61
Jul-07
4.82
Aug-07
4.2
Sep-07
3.89
Oct-07
3.9
Nov-07
3.27
Dec-07
3
Jan-08
2.75
Feb-08
2.12
Mar-08
1.26
Apr-08
1.29
May-08
1.73
Jun-08
1.86
Jul-08
1.63
Aug-08
1.72
Sep-08
1.13
Oct-08
0.67
Nov-08
0.19
Dec-08
0.03
Jan-09
0.13
Feb-09
0.3
Mar-09
0.21
Apr-09
0.16
May-09
0.18
Jun-09
0.18
The instructions for the assignment suggest determining the risk free rate first. This is how
I approached calculating the average risk free rate. I’ve pulled 60 months of data and
calculated the average. This illustrates data through December 2010. Please pull the most
current information.
You’re given links in the assignment. If these (or any you are given) don’t work, please let
For more recent data your risk free rate will probably be lower than what’s shown here.
You’ll be pulling in 60 data points, and at the start of those 60, the rate will be lower and
toward the end of the data set, the rate will likely be even lower. (May 2014)
Jul-09
Aug-09
Sep-09
Oct-09
Nov-09
Dec-09
Jan-10
Feb-10
Mar-10
Apr-10
May-10
Jun-10
Jul-10
Aug-10
Sep-10
Oct-10
Nov-10
Dec-10
Average
0.18
0.17
0.12
0.07
0.05
0.05
0.06
0.11
0.15
0.16
0.16
0.12
0.16
0.16
0.15
0.13
0.14
0.14
2.146333
Make sure that you use the number that you calculate as the average as a Percent. If I
used 2.14 in my calculation of the sharpes ratio, I would be using a risk free rate of 214%
this would make my analysis useless.
risk free rate first. This is how
d 60 months of data and
ber 2010. Please pull the most
er than what’s shown here.
0, the rate will be lower and
wer. (May 2014)
s the average as a Percent. If I
be using a risk free rate of 214%–
Returns
2008
2009
2010
2011
2012
Chevron UPS
Joy Global Walgreens Whole Foods
-18.0%
-19.5%
-64.2%
-34.1%
-75.4%
7.7%
7.3%
128.4%
50.9%
190.8%
22.2%
29.8%
69.6%
7.8%
84.3%
20.0%
3.7%
-12.8%
-13.1%
38.3%
4.9%
3.9%
-14.0%
15.0%
34.7%
Average
7.4%
5.0%
21.4%
5.3%
54.5%
Standard Deviation
16%
17%
77%
32%
96%
Range of Returns based on Standard deviation
High
23%
23%
Low
-9%
-12%
98%
-55%
37%
-27%
151%
-42%
Note, returns are pulled f
1. You might comment o
measurements of risk and
in return, and whether it
historical reputations. Th
2. You might comment o
example, it probably does
in the retail space. It may
two very differnet compa
together doesn’t create a
seeking diversification.
Correlation Tables
Chevron / UPS
0.870381
Chevron / Joy Global
0.523774
Chevron / Walgreens
0.389199
Chevron / Whole Foods 0.587598
UPS / Joy Global
0.67499
UPS / Walgreens
0.512551
UPS / Whole Foods
0.635245
Joy Global / Walgreens 0.885016
Joy Global / Whole Foods0.962634
Walgreens / Whole Foods0.938383
This tab of my spreadsheet mimics the set up of the table in the word docum
includes the instructions for this assignment on the course website for this a
This version illustrates how you would use it. Feel free to use this format.
Note, returns are pulled from the Morningstar site, rather than calculating them out.
1. You might comment on the average return and standard deviations that you’re seeing. These are good
measurements of risk and reward. Perhaps make a comment relating to any that are within or possibly outside of
your risk tolerance. Also consider whether any reputationally “solid” or “stable” companies have had really big swings
in return, and whether it makes sense to try to debunk any biases we hold about certain companies because of their
historical reputations. This can be eye opening.
2. You might comment on the correlations that result from this exercise. Are there any that surprise you? For
example, it probably doesn’t surprise you that Walgreens and Whole Foods are highly correlated because they’re both
in the retail space. It may surprise you that there’s a high correlation between Joy Global and Walgreens. These are
two very differnet companies with highly correlated returns, so this exercise points out that holding these companies
together doesn’t create a lot of diversity, as measured by correlated returns. That’s a troubling conclusion if you are
seeking diversification.
up of the table in the word document that
t on the course website for this assignment.
it. Feel free to use this format.
ossibly outside of
ies because of their
prise you? For
because they’re both
algreens. These are
ing these companies
onclusion if you are
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Correlations
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Covariance
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Portfolio
Weights
19.9%
19.8%
18.4%
22.9%
19.1%
1
Portfolio
mean
sigma
risk free rate
Sharpe’s Measure
Mean
7.4%
5.0%
21.4%
5.3%
54.5%
Chevron
1
0.870381
0.523774
0.389199
0.587598
Chevron
0.025767
0.02441
0.064463
0.019927
0.090699
Chevron
19.9%
0.001018
0.000958
0.002352
0.000907
0.003445
0.00868
Sigma
16.1%
17.5%
76.7%
31.9%
96.2%
UPS
0.870381
1
0.67499
0.512551
0.635245
UPS
0.02441
0.030524
0.090417
0.028562
0.106721
UPS
19.8%
0.000958
0.001191
0.003278
0.001292
0.004028
0.010748
The Mean and Sigma should match the
summary sheet info for average return
and standard deviation
This tab of the spreadsheet is
assignment. Again, feel free t
Joy Global Walgreens
Whole Foods
0.523774 0.389199 0.587598
The numbers here in blue should match t
0.67499 0.512551 0.635245
Sheet”; the numbers in black are linked t
1 0.885016 0.962634 the other way, for example Whole Foods
0.885016
1 0.938383 Foods in blue.
0.962634 0.938383
1
Joy Global Walgreens
Whole Foods
0.064463 0.019927 0.090699
0.090417 0.028562 0.106721 Here you don’t have to do anything, the s
0.587847 0.216428 0.709707
0.216428 0.101732 0.287804
0.235409 0.287804 0.92464
Joy Global Walgreens
Whole Foods
18.4%
22.9%
19.1%
0.002352
0 0.003445
Enter your current weights from the “1. M
0.003278 0.001292 0.004028
vertical column A, beginning at row 21.
0.019806
0.0091 0.024895
0.0091 0.005338 0.012599
0.008258 0.012599 0.033767
0.042794 0.02833 0.078733
18.0%
41.1%
2.2%
0.38559
calculation of the Sharpe measures. You will have to include this yourself.
All that work for these tw
actuallly calculate the Sha
deviation
incorporate the risk free r
Standing on its own the S
the risk that you’re takin
large cap mutual fund (if
Sharpe ratio), compare it
something. The ratio’s u
Further, another way to
consider weigthing it diff
Scenario 1… and 6. Scen
Scenario 1… and 6. Scen
profile of the portfolio.
assignment. Again, feel free to use this format.
mbers here in blue should match the correlations you set up on “3. Summary
the numbers in black are linked to the numbers in blue to show the correlation
er way, for example Whole Foods / Walgreens in black, vs. Walgreens / Whole
u don’t have to do anything, the spreadsheet is set up to calculate covariance
ur current weights from the “1. My Portfolio and Weights” here and they’ll flow down the
olumn A, beginning at row 21.
All that work for these two numbers–and you’re not quite done yet!!! You need to
actuallly calculate the Sharpe Ratio: (portfolio return – risk free rate)/ portfolio standard
deviation–please note in your worksheet, this formula isn’t set up,. You need to
incorporate the risk free rate.
Standing on its own the Sharpe ratio won’t tell you that much. You have to use it to
compare your portfolio to others to determine whether you’re getting a good return for
the risk that you’re taking.
Please comment on your Sharpe ratio: compare it to the results here, or to some other
large cap mutual fund (if you pick mutial funds on Morningstar.com, they’ll have a
Sharpe ratio), compare it to some average Sharpe ratio–whatever you do, compare it to
something. The ratio’s usefulness is in the comparison of one to another.
Further, another way to create context for your ratio is to use your own portfolio, but
consider weigthing it differently to determine a better potential allocation. Tabs 5.
Scenario 1… and 6. Scenario 2… in this workbook attempt to illustrate what happens
when you change your allocation–which will inevitably change your risk and return
Scenario 1… and 6. Scenario 2… in this workbook attempt to illustrate what happens
when you change your allocation–which will inevitably change your risk and return
profile of the portfolio.
from the course website for this
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Correlations
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Covariance
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Portfolio
Weights
60.0%
5.0%
30.0%
2.0%
3.0%
1
Portfolio
mean
sigma
risk free rate
Sharpe’s Measure
Mean
7.4%
5.0%
21.4%
5.3%
54.5%
Chevron
1
0.870381
0.523774
0.389199
0.587598
Chevron
0.025767
0.02441
0.064463
0.019927
0.090699
Chevron
60.0%
0.009276
0.000732
0.011603
0.000239
0.001633
0.023484
Sigma
16.1%
17.5%
76.7%
31.9%
96.2%
UPS
0.870381
1
0.67499
0.512551
0.635245
UPS
0.02441
0.030524
0.090417
0.028562
0.106721
UPS
5.0%
0.000732
7.63E-05
0.001356
2.86E-05
0.00016
0.002354
Joy Global Walgreens
Whole Foods
0.523774 0.389199 0.587598
0.67499 0.512551 0.635245
1 0.885016 0.962634
0.885016
1 0.938383
0.962634 0.938383
1
Joy Global Walgreens
Whole Foods
0.064463 0.019927 0.090699
0.090417 0.028562 0.106721
0.587847 0.216428 0.709707
0.216428 0.101732 0.287804
0.235409 0.287804 0.92464
Joy Global Walgreens
Whole Foods
30.0%
2.0%
3.0%
0.011603
0 0.001633
0.001356 2.86E-05 0.00016
0.052906 0.001299 0.006387
0.001299 4.07E-05 0.000173
0.002119 0.000173 0.000832
0.069283 0.001541 0.009185
12.8%
32.5%
2.2%
0.328056
Comment on how or whether your shar
allocation
ent on how or whether your sharpes measure changed when you changed your
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Correlations
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Covariance
Chevron
UPS
Joy Global
Walgreens
Whole Foods
Portfolio
Weights
0.0%
10.0%
0.0%
45.0%
45.0%
1
Portfolio
mean
sigma
risk free rate
Sharpe’s Measure
Mean
7.4%
5.0%
21.4%
5.3%
54.5%
Chevron
1
0.870381
0.523774
0.389199
0.587598
Chevron
0.025767
0.02441
0.064463
0.019927
0.090699
Chevron
0.0%
0
0
0
0
0
0
27.4%
58.0%
2.2%
0.435463
Sigma
16.1%
17.5%
76.7%
31.9%
96.2%
UPS
0.870381
1
0.67499
0.512551
0.635245
UPS
0.02441
0.030524
0.090417
0.028562
0.106721
UPS
10.0%
0
0.000305
0
0.001285
0.004802
0.006393
Joy Global Walgreens
Whole Foods
0.523774 0.389199 0.587598
0.67499 0.512551 0.635245
1 0.885016 0.962634
0.885016
1 0.938383
0.962634 0.938383
1
Joy Global Walgreens
Whole Foods
0.064463 0.019927 0.090699
0.090417 0.028562 0.106721
0.587847 0.216428 0.709707
0.216428 0.101732 0.287804
0.235409 0.287804 0.92464
Joy Global Walgreens
Whole Foods
0.0%
45.0%
45.0%
0
0
0
0 0.001285 0.004802
0
0
0
0 0.020601 0.05828
0 0.05828 0.18724
0 0.080166 0.250322
FINA 405
Guide to Portfolio Project (Part III)
In this individual assignment you are asked to measure the level of risk of your original portfolio
of five stocks using the Sharpe ratio. In preparation for the assignment and the discussion about
it, you might review the following in addition to the discussion in the moduleÃ¢â‚¬â„¢s Key points
section:
http://www.investopedia.com/articles/07/sharpe_mpt.asp
For the Sharpe index you will need three numbers: mean return on your portfolio, standard
deviation on your portfolio, and the average risk-free return. It is proposed that everyone rely
upon monthly data for the last 5 years or 60 data points. A sample Excel document has been
created by Professor Jeff Racz and is similarly attached here to help you with this assignment.
The following walks you through the Excel document:
In the first tab there is a sample portfolio–yours will look different and the holdings should end
up at about \$1,000,000, as this is what you were starting with for your allocation. Please create a
similar document with your original initial portfolio with the date from the due date of that
assignment. What becomes important from this tab are the CURRENT weights of each company
in the portfolio. You’ll use these weights in the “Portfolio weights and SD” tab to follow.
The second tab is the average risk-free rate. This is where you’d pull the 3 month Treasury bill
data in and take an average. In this example there is old data. Please collect more current data
You can find the data that you’ll need to determine the risk-free rate at this address:
513ab14cf2e5389&lastObs=&from=&to=&filetype=csv&label=include&layout=seriescolumn
Further, make sure that when you use this number to calculate your Sharpe Ratio, you use this
number as a percent. In other words, if you determine that the average you determine is 2.15 as
in the sample, if you donÃ¢â‚¬â„¢t convert it to a percent, your spreadsheet will think that the risk free
rate is 215%. Your results will be incorrect to the point that the Sharpe ratio that you determine
will be useless.
1
The “3. Summary Sheet” tab shows 5 years of annual returns for the 5 stocks, their averages and
standard deviations, and their correlations with each other. Here you can simply replace
company names with your own and input the returns.
One way to calculate annual returns is as follows:
Ã¢â‚¬Â¢
Go to Yahoo! Finance at http://finance.yahoo.com/, enter the ticker symbol to go to your
stockÃ¢â‚¬â„¢s main page, and then click under Ã¢â‚¬Å“QuotesÃ¢â‚¬Â on Ã¢â‚¬Å“Historical Prices.Ã¢â‚¬Â There you can
set the dates for the past 5 years going up to the present and select monthly prices. Use
the prices in the last column titled Adj Close to get end-of-month prices that reflect
dividends and stock splits. Notice that at the bottom of the Ã¢â‚¬Å“Historical PricesÃ¢â‚¬Â page you
standard deviations for each of the 5 stocks in your portfolio based on 5 years of monthly
stock price data that is 60 data points going back 5 years.
How do we calculate monthly returns and annualize them? Assume that we are currently
price for May 2004. Divide this difference by the May 2004 price. In Excel, for example,
= (G3-G2)/G2 . This is the monthly return on the stock for June 2004. The precise way to
transform this into an annual return is as follows: Annual Return = (1 + Monthly
Return)^12 Ã¢Ë†â€™ 1 Or in Excel, assuming the monthly return is in cell H3: =((1+H3)^12) Ã¢â‚¬â€œ 1.
Alternatively, a less tedious approach, and one probably preferable all the way around, is to go to
Morningstar, type in the ticker and then click the Ã¢â‚¬Å“performanceÃ¢â‚¬Â tab, and youÃ¢â‚¬â„¢ll see returns on
the stock for the last 5 years. For
example: http://performance.morningstar.com/stock/performancereturn.action?t=WMT&region=USA&culture=en-US
Here, youÃ¢â‚¬â„¢ll see the returns for the last five years for WMT.
Once you have the returns, the worksheet should calculate the various correlations. ItÃ¢â‚¬â„¢s probably
worth commenting on any interesting correlations that you see, strong or weak, or something that
you might not have expected. YouÃ¢â‚¬â„¢ll notice that text boxes are included right on the
worksheets. This is a totally acceptable approach for your analysis. ItÃ¢â‚¬â„¢s worth commenting on
what youÃ¢â‚¬â„¢re seeing, and how youÃ¢â‚¬â„¢re interpreting it, and how youÃ¢â‚¬â„¢re thinking about it.
The “4. Portfolio Return and SD” provides the tools to calculate your portfolioÃ¢â‚¬â„¢s return and
standard deviation. It is suggested that you first save the original and then enter your own data on
a second file.
In creating your own version of this Excel file here is what it is suggested that you do:
2
1. Mean and Sigma: In cells 2-6 enter your average returns for each stock in Column B and
standard deviations for each stock in Column C.
2. Correlation. Enter your correlations on the upper right of the matrix and the same
numbers should also appear in the lower left as mirror images. Notice the diagonal row of 1Ã¢â‚¬â„¢s
down the middle: the correlation of any stock returnÃ¢â‚¬â„¢s with itself is one
3. Covariance. You need do nothing in cells 14 through 18.
4. Portfolio Weights. In Row 20 enter your weights summing to 1.0 in the 5 columns for
your 5 stocks. In the template they are approximately evenly weighted. You should then get
both the return on the portfolio as well as the portfolioÃ¢â‚¬â„¢s standard deviation (SD) below.
Please note that you have to calculate the Sharpe ratio.
Notice too, in Tabs 5 and 6 are a couple of different scenarios on the portfolio weights to see if
the Sharpe ratio changes. This provides context to say whether the sharpes ratio is good or
bad. Again, the number’s just a number. With context it can start to mean something.
As for what to deliver:
1. Please do complete the worksheets given to you in tabs 1-4. through the course site, or use
this as a model. whichever you’re most comfortable with.
2. Determine a Sharpe ratio.
3. Please comment on the Sharpe ratio: relevant things to include might be a restatement or
definition of what the Sharpe ratio tells you, and what you make of the ratio that you
determine. Compare your ratio to something. A ratio on its own doesnÃ¢â‚¬â„¢t tell you much. For
example you might find a large cap mutual fund and use that as a benchmark. See how your
Sharpe ratio stacks up against some fund that you have in your 401k plan or IRA. That could be
interesting and could be a useful exercise to see what youÃ¢â‚¬â„¢ve got.
4. If you run scenarios, make comments on those, comparing your results across different
portfolios.
The comments that you make don’t have to be long or involved: a paragraph, two at the
most. You can even include them in text boxes in Excel if thatÃ¢â‚¬â„¢s easier for you. We want to see
you do more than just produce numbers. We want to understand how youÃ¢â‚¬â„¢re thinking about them.
3