代做BFIN 2145: FINANCIAL MODELING Fall 2024-25 Homework Assignment 2调试SPSS

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.