Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
BFIN 2145: FINANCIAL MODELING
Fall 2024-25
Homework Assignment 2
Objective
In this problem set, we will try to understand the results of practical application of portfolio theory to asset allocation using real stock data. To this end, we will generate efficient frontiers and observe the performance of optimal portfolios formed on this basis.
Data
We will generate efficient frontiers with four (4) stocks. For this:
a) Download data on adjusted closing prices for four stocks of your choice out of the Dow 30 component stocks (see a list at https://en.wikipedia.org/wiki/Dow_Jones_Industrial_Average). Get these prices on a monthly basis from June 2018 to June 2024. This data will enable you to generate monthly returns for these stocks from July 2018 to June 2024 (72 months).
b) I am providing you data on rm − rf (excess returns on a broad index of the stock market) and rf (risk free rate) for the above months, which will be useful in the exercise. See enclosed spreadsheet named 2145 HW2.xlsx
Methodology
Efficient frontiers
We will use data from July 2018 through June 2023 to generate our efficient frontiers. To generate efficient frontiers with these four stocks, we need as inputs: a) a matrix of variances and covariances for the returns of these stocks, and b) a vector of expected returns for these stocks.
a) For the variance-covariance matrix (we called this Σ in class), simply calculate the sample variances from the stock return data using the =VAR.S() Excel function and covariances between every pair of the four stocks (there should be 6 unique pairs) using the =COVARIANCE.S() Excel function. Assemble the variances and covariances in a square, symmetric matrix in your spreadsheet.
b) For the expected returns of the four stocks, proceed in two ways:
i. Simply use the historical (arithmetic) average or mean returns for each chosen stock between July 2018-June 2023. This can be done with the =AVERAGE.S() Excel function
ii. Use the CAPM at the end of June 2023 to estimate expected returns on each chosen stock. Use the average rm − rf and rf provided to you as inputs into the CAPM. To input into the CAPM, calculate betas for each of the stocks by running regressions with data for the past 60 months (July 2018-June 2023) (like we did for GM stock in class.)
At this point, you have two vectors of expected returns for your stocks and one variance-covariance matrix as inputs. Using these inputs, you should be able to generate two unconstrained efficient frontiers.
Choice of portfolio on the efficient frontier
On each efficient frontier, choose a portfolio with an expected return level such as say, 1% (remember everything is monthly here!). Choose a level such that it is included on both frontiers you generated. (In other words, avoid choosing an extremely high or extremely low expected return level.) Now you have two efficient portfolios with the same expected return formed as of June 30, 2023. Comment on the differences in composition of the portfolio across different frontiers, e.g., which seem sensible or plausible? Which seem extreme?
Performance of the chosen portfolio
We will use data from July 2023 through June 2024 for evaluating our chosen portfolios. You have 12 returns on your chosen portfolio as well as for the market for these months. Comment on the performance of your portfolios over this year. You can evaluate the portfolio performance on the basis of the reward (excess return) to risk (standard deviation) ratio also called the Sharpe Ratio. Or you could run a regression of your portfolio against the market and see if there is any alpha (although with 12 monthly observations a regression may not show much statistical significance) to your portfolio.