(Use the attached Excel sheet): https://1drv.ms/x/s!ApqhKu0FkK8Xhk_98nkttuNduVNV?e=tfYs2W

1) Pick the top 12 stocks from your investments and treat it as your portfolio. DO NOT PICK funds if

you have invested in funds. Pick individual stocks. If you do not have individual stock

investments, pick the top 12 holdings of the funds you are invested in. This information should

be available from the fund website. These 12 investments will be your "Universe" of investable

assets.

a. Replace the "Name" row in the "Price Data" worksheet with the names of your

investments

b. Replace the "Ticker" row in the "Price Data" worksheet with the ticker of your

investments

c. Find the Market Capitalization data for each of your 12 stocks and update the "Equity

Value" row in the "Price Data" worksheet (see the note below for how to get the

Marketcap numbers from Yahoo finance.). If you cannot find the Market Cap for a

particular firm, estimate it using a comparable firm for which data is available. You can

do this by using the Yahoo stock screener (https://finance.yahoo.com/screener/) and

finding similar firms to the one you are searching for.

d. The "Benchmark Proportions" row in the "Price Data" worksheet will update

automatically

e. Replace the "Your Initial $ Investment" row in the "Price Data" worksheet with the

approximate $ amounts you invested in each of the 12 assets.

f. Replace the "Price on date of purchase" row in the "Portfolio Returns" worksheet with

the purchase price of 1 stock of each of the 12 assets.

g. Note: For ease of calculations, I am going to assume that you invested on 1/1/2021 and

we will track the value of the different portfolios for February, March, and April. This is

calculated for you in Columns Q and R

h. Download price data from Yahoo finance for your 12 investments and update the

columns C through N, rows 9 - 69. I have already downloaded the data for Apple Inc

(AAPL) and the S&P 500 (^GSPC). If AAPL is part of your portfolio you do not need to

update this. Please use the "adjusted close price from Yahoo finance".

i. Replace the "Risk Free Rate" in cell B17 in the "Var-Covar" worksheet with the risk-free

rate (see: https://www.treasury.gov/resource-center/data-chart-center/interest-

rates/pages/textview.aspx?data=yield ). Justify which rate you pick. Note that the rates

shown here are already in %, i.e. the number 0.02 means 0.02% ANNUALLY. Be sure to

convert to the monthly rate by dividing it by 12.

2) The Unconstrained Markowitz Optimal Risky Portfolio will be computed for you in the "Var-

Covar" worksheet in the range: F20:F31. (Copy paste these into your writeups)

a. Discuss how realistic the Unconstrained Markowitz Optimal Risky Portfolio is with the

recommendations obtained.

b. Replace cells C7:N7 in the "Portfolio Returns" worksheet to obtain the performance of

the Unconstrained Markowitz Optimal Risky Portfolio. (Copy-paste values obtained into

your writeups as these will update as you move along)

3) Calculate the Constrained Optimal portfolio (no shorting) on the worksheet "Var-Covar -

Constrained" using Excel's Solver function. Please play around with different constraints to

obtain the best possible Sharpe ratio. Use the weights obtained using this optimization (also try

different algorithms - GRG NonLinear, Simplex LP, Evolutionary from "Choose a Solving

Method" option). I show below and example of the solver function with the following

constraints:

a. No-Shorting (P20:P31 >=0)

b. Maximum 20% invested in 1 stock (P20:P31 <=0.2)

c. Minimum 5% invested in each stock (P20:P31 >=0.05)

d. Fully invested (P33 = 1)

4) Once you are satisfied with the composition of your constrained portfolio, input these

proportions into the "Portfolio Returns" worksheet to examine the portfolio performance.

5) Examine the effect of choosing Alternate Var-Covar matrices by replacing the traditional Var-

Covar matrix in the "Var-Covar" worksheet with the SIM, Constant-Correlation, and Shrinkage

Var-Covar Matrices. Play around with the weightage (cells W20 and V37) till you get the best

possible answer. You can simply point to the Var-Covar matrices on the "Alternate Methods"

worksheet and maximize the Sharpe ratio changing the weightages, using Excels Solver. As

before, examine the portfolio performance for each optimized method.

6) Pick the portfolio you are satisfied with, explain your rationale.

7) This portfolio composition represents the investment across risky assets. Use the method shown

in Part 3 of the "An Introduction to Portfolio Management" video to decide how you will

allocate your money across risky and risk-free assets. (Note: You can change your risk appetite

by changing the A value)

Summarize your actual performance and compare and contrast it with what it would have been if you

had invested according to recommendations from the various models. Discuss the results and comment

on the strengths/weaknesses of this approach. Also, compare these to "investing in the market" i.e. the

benchmark proportions obtained initially. Based on the results, comment on what you think about

active investment management vs passive investments (invest in the whole market and forget about it).

Answer rating: 100% (QA)

Portfolio management refers to the process of making decisions and taking actions to optimize the performance of an investment portfolio It involves c... View the full answer