Finance Application: Portfolio Analysis with a Market Index as a Leading Indicator in Simple Linear Regression

Abstract

Simple linear regression of stock rates of return with a Market index provides an estimate of beta, a measure of Market specific risk, which is central to finance investment theory.

5.1 Electronic Supplementary Material(s)

Stocks master (XLSX 101 kb) (XLSX 101 kb)

Three Portfolios master (XLSX 25 kb) (XLSX 25 kb)

Appendices

Excel 5.1: Estimate Portfolio Expected Rate of Return and Risk

Three Portfolios with Apple, Microsoft and Tesla

Monthly rates of return for each of the three stocks and the S&P500 index of the Market are in Three Portfolios.xlsx.

Monthly Portfolio Returns Formula

Insert new columns, F, G and H, for each of the three portfolios containing equally weighted pairs of the three stocks, which will be the average of rates of return of each pair of stocks in a portfolio. In row 1, enter labels to identify the combinations, AAPL+MSFT, AAPL+TSLA, MSFT+TSLA. In the second row of each new column enter a formula for the average of two stocks.

• =average(C2,D2)

• =average(C2,E2)

• =average(D2,E2)

Select the three new cells and down fill the three new columns. From F2, Cntl+Shift+Right (for a PC) OR.

Cmnd+Shift+Right (for a Mac).

Then double click the lower right corner to fill down the columns.

Expected Monthly Rates of Return

Find the expected monthly return for the three portfolios in the first row following the data:

• =average(F2:F60)

• Shift+right right

• Cntl+R (for PCs) OR Command+R (for Macs)

Estimate Betas from Simple Regression

To find the Market specific risk, beta, find the simple regression slope of each of portfolio rate of return (the Y range) with S&P500 (the X range). Below is the beta of the AAPL+MSFT portfolio:

Excel 5.2: Plot Return by Risk to Identify Dominant Portfolios and the Efficient Frontier

To compare the expected rates of return and estimated risk of the three portfolios, first move the S&P500 column B to column E. Then, create a summary of the portfolio betas and expected returns below the data. Copy row 1 containing the portfolio labels and paste into row 62. Below the expected rates of return, used the Excel slope function to product the portfolio betas.

• =slope(F2:F6,$B2:$B61)

• Shift+right right

• Cntl+R (on a PC) OR Cmnd+R (on a Mac)

• Set the S&P500 beta to one.

  

Change the ERRs to percents. Plot the portfolio rates of return against their betas (one portfolio at a time) to identify the Efficient Frontier. Select the S&P label, ERR and b in rows 61–63 the insert a scatterplot of the point:

When plotting a single point, Excel will read both b and ERR as a single series plotting two points. To correct this, edit the series. In the Chart Design menu, Select Data. Edit the Series, changing the X values to E63 and the Y values to E62.

Add three additional series, one for each of the three portfolios:

Add chart and axes titles, adjust axes, and set font size. Add the legend, as shown in Fig. 5.5:

Fig. 5.5

The Efficient Frontier

Case 5.1: Portfolio Risk and Return

14 stocks.xlsx contains a 5 year time series of monthly prices for 14 stocks and the S&P500 Market index for months November 2017 through October 2022. Stocks include “blue chips,” as well as technology stocks and others that have been in recent news.

Find the rate of return and beta for each of the stocks. Use logic to choose two stocks to combine in an equally weighted portfolio.

• 1. The expected rate of return of my portfolio:

  Use regression to find the 95% confidence interval for beta of your portfolio.

• 2. The Market specific risk, beta, of my portfolio: ___ to ___

• 3. My portfolio ___ dampens, ___ mirrors, OR ___ exaggerates Market swings.

  Create an alternative portfolio with two other stocks. Find the expected rate of return and beta.

• 4. Plot E(RR) and b for the S&P500 and your portfolios to see the Efficient Frontier, then compare the two portfolios:

  Portfolio ___ dominates Portfolio ___ OR neither dominates the other.