Fin 406 – Fall 2024 Security Analysis & Portfolio Management PROJECT 1SQL

Java Python Fin 406 – Fall 2024

Security Analysis & Portfolio Management

PROJECT 1

(Due October 27, 2024, Sunday)

Last updated: 10/11/2024

As indicated on the syllabus, students are strongly encouraged to work on this project as a group (up to six students in each group) and can form. a group with students from other sections.

Each group needs to submit ONE report for this project:

•    Only one member from each group needs to upload a report for the project.

• Please also include the names of all the group members and their section numbers on the title page of the report.

•    The report should be ONE single well-organized document with clearly labeled answers to each question, as well as with included tables and graphs where necessary.

• The format of the report can be in Excel, Microsoft Word, Google document, PDF, etc. (as long as the report is a single document).

•    GenAI is permitted in this assignment. If GenAI is used, please cite it properly (see GenAI course policy in the syllabus) in the report and, in addition, include screen shots of specific AI generated contents incorporated into the report.

•    Please submit your report to Canvas by the due date.

This project consists of five questions:

1.   Estimate the correlation between SPY and a few other ETFs.

2.   Construct the efficient frontier of SPY and LQD using real data.

3.   Determine an investor’s optimal asset allocation among SPY, LQD, and cash using the standard asset allocation model discussed in Fin406.

4.   Estimate betas of certain funds against the S&P 500 index.

5.   Analyze the performance of the mutual fund LMVTX (once managed by the well-known fund manager Bill Miller)

Files posted on Canvas under “Projects” include the following:

•    “data needed for Project 1.xls”

•    “example-correlation-beta-slope.xls” (for Questions 1, 2, 4 and 5)

This file shows how to first calculate monthly returns of XOM and S&P500 from historical prices and then estimate the correlation of these two monthly return series using function CORREL.

• “example-efficientFrontier.xls” (for Question 2)

This is an example on how to construct an efficient frontier.

• “example-assetAllocator.xls:” (for Question 3)

This is an example on how to determine the optimal asset allocation.

1.   Estimate the correlation (coefficient) between two assets based on real data.  Note that for our purpose, correlation means return correlation not correlation between prices of two assets.

In this exercise, consider six ETFs: SPY, LQD, SH, SDS, SSO, and SHV.  You may do this question in the following three steps.

First, get monthly prices of these six ETFs over the period 08/01/2010-07/01/2018.  The data are available in “data needed for Project 1.xls” posted on Canvas under folder “Projects.”

Next, compute monthly returns for Aug. 2010, Sep. 2010, …, May 2018, and June 2018 using the monthly prices, for each of the six ETFs. For example, to compute the return for Aug. 2010, you use

the prices for 09/01/2010 and 08/01/2010.  Note that while the monthly price series begins on 8/1/2010, the monthly return series begins on 9/1/2010.

Note: Return = (Final Price – Initial Price)/Initial Price.

You may want to double check the dates of the "final" and "initial" prices used in your calculations of returns. Make sure to use the price for the earlier date as the initial price and the price for the later date as the final price.

Then, estimate the correlation coefficients between SPY and each of the other five ETFs using Excel function CORREL.  See Example 6.1 in Chapter 6 in the textbook (p. 153 in the 12th edition) on how to use excel function CORREL to compute the correlation.

Things to turn in (Please organize your work appropriately and label each tab clearly):

(i)  a brief description of each of the six ETFs (one or two sentences per ETF would be sufficient)

(ii) the monthly returns (not prices) of the six ETFs (it is better to place all six monthly return series in one spreadsheet)

(iii) the estimates of the six correlation coefficients specified in part (d) of Question 1; and

(iv) a brief discussion of whether your estimates of the six correlations are consistent with your intuition.  E.g., should SPY and SSO be highly correlated? Is your estimate of their correlation consistent with your expectation?

2.   Construct the efficient frontier of SPY and LQD using real data.

(a)  First, you need to estimate the following five input parameters: the expected returns and standard deviations of SPY and LQD, and their correlation.

Here you use monthly returns of the SPY and LQD over the period 09/01/2010−07/01/2018, which are obtained in Question 1, to estimate those five parameters.   Estimate the average monthly returns and standard deviation of the monthly returns for both SPY and LQD. Recall that the correlation between SPY and LQD is already estimated in Question 1.

(b) Annualize the average returns and standard deviations of SPY and LQD obtained in part (a) using monthly data.  You can use the following formulas to do that

annualized avg. return = (1 + average monthly return)^12 - 1;

annualized std. dev. = monthly std. dev. * sqrt(12).

See also “example-correlation-beta-slope.xls” on how to annualize the average monthly returns and monthly standard deviations. Correlation need not be annualized.

The  annualized  average  returns  and  standard  deviations  obtained  here  in  part (b) are assumed to be the same as the expected returns and standard deviations of SPY and LQD that will be used in parts (c) and (d).

(c)  Tabulate the investment opportunity set of these two  ETFs.   Namely,  select, say, 20 different portfolios (of SPY and LQD), with SPY’s weight ranging from -0.5 to 1.5 with an incremental of 0.1.   Then  calculate the  expected return and standard deviation for each of the 20 portfolios. Spreadsheet 6.5 in Chapter 6 in the textbook (p.156 in the 12e) provides a similar example.

(d) Plot the expected returns of 20 portfolios (the y-variable) vs. these portfolios’ standard deviations (the x-variable) to generate a curve (whose upper branch is the efficient frontier).

If you want, you can use “example-efficientFrontier.xls” posted on Canvas as a template.

Things to turn in (Please organize your work appropriately and label each tab clearly):

(i)         a brief description of investment objectives of SPY and LQD (one or two sentences per ETF would be sufficient);

(ii)        the average monthly returns and monthly standard deviations of SPY and LQD;

(iii)       the estimates of annualized average returns and standard deviations of these two ETFs;

(iv)       the estimate of the correlation between SPY and LQD;

(v)        the tabulated investment opportunity set (say, 20 different portfolios of SPY and LQD) and the corresponding portfolio expected returns and standard deviations; and

(vi)       a properly labeled graph of the investment opportunity set (the curve) – namely, labeling the x-axis and y-axis of your plot at least.

3.   Determine an investor’s optimal asset allocation through a mix of SPY, LQD, and cash using the standard asset Fin 406 – Fall 2024 Security Analysis & Portfolio Management PROJECT 1SQL allocation model discussed in Fin406.

In this exercise you essentially redo Example 14 in lecture notes lecs05-11-portfolioTheory- part1.pdf with real data.

Consider an investor who plans to allocate $10,000 to SPY, LQD, and cash.

(a)  First, need to get the risk and return characteristics of these three assets:

• Use the annualized average returns, annualized standard deviations, and the correlation estimated earlier for SPY and LQD in Question 2 (part b).

• The risk-free rate is assumed to be 5% for this question.

(b) Next, determine the tangency portfolio using Eq. (6.10) in Chapter 6 in the textbook.

(c)  Then, consider three investors whose risk aversion coefficients (coefficient A) are 6.0, 8.0, and 10.0, respectively.  Determine these three investors’ optimal complete portfolios.

You can use “example-assetAllocator.xls” posted on Canvas as a template for this exercise.

Things to turn in (Please organize your work appropriately and label each tab clearly):

(i)         the annualized average returns and annualized standard deviations of SPY and LQD; the correlation coefficient between SPY and LQD; and the risk-free interest;

(ii)        the portfolio weights of SPY and LQD in the tangency portfolio;

(iii)       the expected return and standard deviation of the tangency portfolio;  and

(iv)       the three investors ’ optimal complete portfolios (including both portfolio weights and

dollar amounts invested on the three asset classes).

4.   You are asked to estimate betas of the following four funds against the S&P 500 index (e.g., Vanguard 500 index ETF (ticker: VOO)) in this question: the Vanguard Market Neutral Fund (ticker: VMNFX), the ProShares Ultra S&P500 ETF (ticker: SSO), Consumer Staples Select Sector SPDR ETF (ticker: XLP), and Consumer Discretionary Select Sector SPDR ETF (ticker: XLY).

Intuitively, the ranking of these four funds by beta should look like this: VMNFX < XLP < XLY < SSO (why?).

Now estimate these four funds’ betas and see if your estimates are consistent with the above ranking.

(a)  Get monthly prices of these four funds and the Vanguard 500 Index ETF over the period October 2019--September 2024. The data are available in “data needed for Project 1.xls” posted on Canvas under folder “Projects.”

Compute monthly returns of the four funds and VOO using their monthly prices.

Note: Return = (Final Price – Initial Price)/Initial Price.

You may want to double check the dates of the "final" and "initial" prices used in your calculations of returns. Make sure to use the price for the earlier date as the initial price and the price for the later date as the final price.

(b) Beta can be estimated using either returns or excess returns.   Please estimate each fund’s beta (against the S&P 500) using the Excel function SLOPE (see below on how to use this function).

Note:

(i)         Function  SLOPE  has  two  arguments:  slope(y-array,  x-array),  where  the  1st    argument

represents the y-variable (returns of a fund) and the 2nd   argument represents the x- variable (returns of the S&P 500).

For example, XLP’s beta = slope(XLP returns, VOO returns).

(ii)        You may also refer to “example-correlation-beta-slope.xls” posted on Canvas on how to estimate beta.

Things to turn in (Please organize your work appropriately and label each tab clearly):

(i)         a brief description of investment objectives of VMNFX, SSO, XLP, and XLY (one or two sentences per ETF would be sufficient);

(ii)        monthly returns of VMNFX, SSO, XLP, XLY and the Vanguard 500 index ETF over the sample period considered in this question;

(iii)       your estimates of the four funds’ betas against the Vanguard 500 index ETF; and

(iv)       the ranking of these four beta estimates

5.   Analyze the performance of the mutual fund LMVTX (once managed by the well-known fund manager Bill Miller), discussed in Example 7 in notes lecs05-11-portfolioTheory-part2.

(a)  Get monthly prices or levels of LMVTX, the SPDR S&P 500 ETF (ticker: SPY), and the Russell

2000 index (ticker: ^RUS) over the period 10/02/2000-09/30/2003. The data are available in “data needed for Project 1.xls” posted on Canvas under folder “Projects.”

Compute the monthly returns of LMVTX, SPY, and ^RUS using their monthly prices.

Note:

(i)  The monthly return series start from November 1, 2000, although the prices of the funds and

the index on 10/02/2000 are needed for the calculation of their returns for October 2000.

(ii) Return = (Final Price – Initial Price)/Initial Price.

You may want to double check the dates of the "final" and "initial" prices used in your calculations of returns. Make sure to use the price for the earlier date as the initial price and the price for the later date as the final price.

(b)  Calculate the monthly excess returns of LMVTX, SPY, and ^RUS, where the excess return = return – risk free rate. Although the risk-free rate is assumed to be constant in portfolio theory covered in Fin 406, the risk-free rate itself is not a constant and varies over time in the real world. The data on the risk-free rate are available in “data needed for Project 1.xls.”

(c) Estimate the beta of LMVTX using the Excel function SLOPE.

Note: use slope(y-array, x-array), where the  1st   argument represents the y-variable (the fund LMVTX’s excess returns) and the 2nd   argument represents the x-variable  (the  S&P’s excess returns).  Also, refer to Question 4 of this project on how to estimate beta using function Slope.

(d) Estimate LMVTX's beta, alpha, and R-squared against the S&P500 using regression in Excel. The fund beta estimated using regression should be close to the estimate obtained in part (c).

Here are two methods for running a regression in Excel:

(1) Following Example 6.3 in Chapter 6 in the textbook (p. 173 in the 12th edition);

(2) Following the instructions given below on page 7.

(e) Replace the S&P500 index by the Russell 2000 index, and then redo the regression analysis to obtain the alpha, beta, and R-squared of LMVTX against the Russell 2000 index.

Note:

•          “example-correlation-beta-slope.xls”  posted  on   Canvas  illustrates  how  to   estimate XOM’s beta against the S&P500 using Function SLOPE.

•          If you do it correctly, your estimates of alpha and beta should be close to (not necessarily the same as) those shown in Example 7 in notes lecs05-11-PortfolioTheory-part2 (see also the example in the appendix below on page 8).

Things to turn in (Please organize your work appropriately and label each tab clearly):

(i)         the monthly returns of LMVTX, SPY, and ^RUS, and the monthly risk-free interest rate;

(ii)        a  graph  of LMVTX vs. the  S&P 500 that includes the regression line, the regression equation and the R-squared (similar to the example shown on page 8 of this document);

(iii)       a graph of LMVTX vs. the Russell 2000 that includes the regression line, the regression equation and the R-squared