FIN 2000
Personal Finance
Portfolio Diversification Analysis
Part I: The Exercise
1. Pick two stocks that are of “different risk characteristics.”
2. Find their tickers (i.e., stock exchange ID) and locate them on Yahoo! Finance (or any other reliable source of data based on your preference).
3. For both companies:
a. Download their monthly price data for five years (be sure to use the same date range for both). Use the adjusted close prices, as they adjust for dividends, splits, etc. You should have 60 prices for each stock (5 years × 12 months per year).
b. In Excel (or any spreadsheet program of your choice, such as Calc in Apache OpenOffice), do the following:
i. Calculate the annualized average return for each stock over the five-year period.
1. Every month t, using adjusted close prices, calculate the monthly returns as (Pt-Pt-1)/Pt-1. You should have 59 of these monthly returns, since for each return you need two prices:
a. The 2nd month’s return in the series would be MRET2 = (P2P1)/P1.
b. The 60th month’s return in the series would be MRET60 =
(P60-P59)/P59.
c. Calculating MRET2 through MRET60 requires using up all 60 months of returns.
d. If you wish to have 60 months of returns, you’ll need 61 prices.
2. Use the Excel function AVERAGE(•) to calculate the average of the monthly returns; • denotes the monthly returns series.
3. Annualize this average by multiplying 12.
4. The resulting number is the annualized average return of the stock over the five-year period.
ii. Calculate the annualized standard deviation of the returns of each stock over the five-year period.
1. Use the Excel function STDEV.S(•) to calculate the standard deviation on a monthly return basis; again, • denotes the monthly returns series.
2. Annualize this by multiplying √12. The square root of 12 is not a typo. We’re annualizing a square-rooted second-moment statistic.
3. The resulting number is the annualized standard deviation of the stock over the five-year period.
4. Calculate the annualized average return and annualized standard deviation of a 50-50 portfolio over the five-year period. The 50-50 portfolio consists of 50% of the portfolio value allocated to the first stock and the remaining 50% allocated to the second stock.
a. For each month in your series, calculate the weighted average monthly return as 0.5 × MRET1st STK + 0.5 × MRET2nd STK.
b. By now you should have three series of 59 monthly returns: One for your first stock, one for you second stock, and now one more for your 50-50 portfolio.
c. As in part 3.b.i and 3.b.ii above, calculate the annualized average return and annualized standard deviation using the Excel functions AVERAGE(•) and
STDEV.S(•) and multiplying the function outputs by 12 and √12, respectively.
Part II. Discussions
After the above exercise, submit your answers to the following questions on Canvas:
1. What are the two stocks of your choice? In what way do they have different risk characteristics? Explain.
2. What are the annualized average return and the annualized standard deviation of the first stock?
3. What are the annualized average return and the annualized standard deviation of the second stock?
4. Find two numbers:
a. What is the average of the annualized average returns of the two stocks?
b. What is the annualized average return of the 50-50 portfolio?
c. Which one is larger? Or are they the same?
5. Find two numbers:
a. What is the average of the annualized standard deviations of the two stocks?
b. What is the annualized standard deviation of the 50-50 portfolio?
c. Which one is larger? Or are they the same?