FactorPad
Faster Learning Tutorials

Analyze portfolio performance with linear regression in Excel

Whether it be for the analysis of investment advisor performance or a mutual fund track record, knowledge of risk-adjusted returns is the language spoken at the Institutional level.
  1. Performance - Review performance considerations needed before we get started.
  2. Active portfolio - Create an active portfolio with slight deviations from the benchmark.
  3. Three measures - Review and calculate the Sharpe Ratio, Treynor Ratio and Jensen's Alpha.
  4. Interpret - Interpret results for the active portfolio.
  5. Next: Time-Series Modeling - Learn about array math to scale up analysis in Excel.
by Paul Alan Davis, CFA, May 20, 2018
Updated: July 22, 2018
The world is getting more competitive every day. For the longevity of your investing career make sure you know your way around regression-based portfolio analysis. Keep reading.

Outline Back Next

~/ home  / finance  / quant 101  / portfolio analysis


Learn how to analyze portfolios at the Institutional level

Beginner

Watch the Video

Analyze portfolio performance with linear regression in Excel (21:21)

Videos are available at one organized Quant 101 Playlist on YouTube (opens in a new browser window).

Video Script

Welcome. Today's goal is to review risk-adjusted return measures and evaluate an active portfolio management track record.

I'm Paul, and with all of the choices between mutual funds and investment advisor products we have to be smart about how we analyze portfolios or we may draw incorrect conclusions.

So here we will focus on the main three risk-adjusted return measures Institutional investors use and cover a variety of reports we may be asked to produce as financial analysts.

This is the final tutorial in Chapter 4 of Quant 101 where we shift from correlation and regression of stocks to portfolios in one of the most important tutorials in the Playlist.

If you would like to read the transcript and see the Excel formulas on a web page the first link in the video Description will take you straight to it.

Let's see what we will cover today.

Outline

First, we will review what we need to get started, meaning returns or holdings, two return calculation methods and add the risk-free rate to our analysis.

Second, we build a simple active portfolio to compare against a benchmark.

Third, we calculate the three most common risk-adjusted return measures.

Fourth, we interpret this portfolio's performance and see if it provided alpha after taking risk into consideration.

And in our next episode we will move on to time-series modeling using arrays as we approach portfolio optimization using Excel's Solver.

Step 1 - Portfolio Performance Basics

For Step 1 and before any analysis of performance begins, we must first figure out what we're starting with, meaning the data and the measurement methods. These are often dictated by reporting standards at a mutual fund or investment advisory firm.

In this tutorial we will be wearing two hats. First, as the portfolio manager who generates the track record in an attempt to sell a product, and second as a potential buyer who evaluates the performance.

a. Returns-based versus holdings-based analysis

The first step the portfolio manager takes is how to present the track record for the investment product and what the potential buyer would want to see.

For that let's start with returns-based versus holdings-based analysis.

Returns-based analysis

Returns-based analysis refers to a stream of returns, typically monthly, for periods of 3 or 5 years, and ideally longer if the firm has been in business and managing a product consistently for that period of time.

Second, returns-based data is often found in public disclosures. For example, with mutual funds returns can be sourced from publicly-available data or found on websites at research companies like Morningstar.

Holdings-based analysis

For holdings-based analysis inputs are portfolio holdings and typically are for daily periods instead of monthly. These are often maintained by portfolio managers in-house and are typically not shared. So they are for the internal management of the portfolio.

Because the data here is more granular the level of detail for the analysis can be much more granular as well.

Of course with holdings-based data you need more advanced software than a spreadsheet can provide, like those offered by third-party risk-model providers. This allows the buyer to see the exposures to risk factors, allocations to sectors and styles within the month. Jeez, you can even see if the manager is using cash to mitigate risk or leverage to increase risk.

In Steps 1 and 2 we will put our portfolio manager hat on and generate returns-based data monthly, but in order to do that we need holdings-based data, and here to simplfy things we will use monthly periods that sit on the Returns data tab. If you would like to download this free data set and follow along in your spreadsheet head back to the tutorial on System Setup.

Also, since this tutorial will be a review of much of what we have worked on up to this point, head back to previous tutorials if anything doesn't make sense.

b. Review arithmetic and geometric return calculations

For example, in the tutorial on return calculation methods we covered two ways to generate returns. For risk studies and forecasting we use the average arithmetic return. There, because we aren't compounding returns we use addition and divide by the number of observations.

For geometric returns, on the other hand, because the order of the returns matters and we need to take into consideration the effect of compounding we link returns with multiplication and raise the result to the power of one over the number of periods, then we subtract one.

We will employ both of these in Exercise 2.

c. Active returns versus alpha

Next, let's clarify a point about the terms active return and alpha.

Active return refers to the simple subtraction of the portfolio return minus its Benchmark return, as we will see in Exercise 1.

Alpha, on the other hand, is the regression-derived figure that takes into consideration the risk of the portfolio versus the Benchmark, which is the focus of Exercise 4.

Also to clarify, the terms Benchmark, Market and Index are often used synonymously even though there is a subtle distinction between the three. An active portfolio manager attempts to outperform a Benchmark and that is the term we will use here.

d. Raw returns versus excess returns

The fourth clarifying point is whether the analysis is performed using raw returns or excess returns.

Raw returns typically represent returns net of fees on the mutual fund or investment advisor product.

  • Excess return = Raw Return - Risk-free Return

Excess returns are raw returns minus the risk-free rate of return for each period. It is most common when calculating the Sharpe Ratio, Treynor Ratio and Jensen's alpha to use excess returns because that is how they were specified by the authors Bill Sharpe, Jack Treynor and Michael Jensen.

With those clarifications in mind, we now have enough to proceed.

Having a quick look at the data, we have returns on four stocks for 60 months. (The table below shows the first three rows)

  B C D E F
6 Date MSFT EBAY ABT MRK
7 4/30/2003 0.056175 0.089087 0.083536 0.062066
8 5/30/2003 -0.037544 0.094070 0.096480 -0.044689

We also have a Market portfolio which will be our Benchmark here and in column H we have risk-free returns. (The table below shows the first three rows)

  B G H
6 Date Market Rf
7 4/30/2003 0.072716 0.001000
8 5/30/2003 0.027079 0.000900

Step 2 - Build an Active Portfolio

Let's move on to Step 2 and create an active portfolio and name it the Acme fund.

a. Alter weights and calculate returns

Exercise 1 specifies the allocations to each of four stocks in the Benchmark and in this active portfolio.

  E F G H
7   % Weight % Weight % Weight
8   Benchmark Acme Active
9 MSFT 25% 20% -5%
10 EBAY 25% 40% 15%
11 ABT 25% 20% -5%
12 MRK 25% 20% -5%

Our Benchmark is equally-weighted so 25% invested in each stock on a monthly basis with rebalancing back to 25% every month.

Now with our portfolio manager hat on, let's say an active process guided us to overweight eBay at the expense of each of the other three stocks. On top of that, this view didn't change for the whole 5-year period so the Acme Fund is rebalanced back to these weights monthly. Of course this is highly unrealistic but helpful for a simplied example.

The term active weight is the weight relative to the Benchmark, so eBay had an 'active weight' of +15% and the other three stocks had an 'active weight' of -5%.

Let's go to the Returns tab and create this portfolio.

First, to visualize things better, let's put the Benchmark return in in column J, which is a reference to column G. So in cell J7 the formula is =G7.

We are duplicating data here which you don't typically want to do, but it clarifies the point that active portfolios have benchmarks. Also, later it will be easier to view and grab the data for our regression.

Next, in column K, we create the Acme Portfolio with the following weights, 20% in Microsoft, 40% in eBay, 20% in Abbott Labs and 20% in Merck.

So the formula in cell K7 is =0.2*C7+0.4*D7+0.2*E7+0.2*F7. (The first three rows are shown below)

  J K L
6 Benchmark Acme Active
7 0.072716 0.075990 0.003274
8 0.027079 0.040477 0.013398

Next, copy that formula down and in column L we show active returns for each month, which is column K minus J.

We now have stream of active portfolio returns. As the portfolio manager we can always use this in a report or proposal to paint a picture or tell a story about the track record over time.

In columns N and O, let's create a stream of excess returns for both the Benchmark and Acme Fund, or returns for each month less the risk-free return. So column N is J minus H. Column O is K minus H, and copy that formula down.

  N O
6 Benchmark Acme
7 0.071716 0.074990
8 0.026179 0.039577

Okay, great, now that we are done with that, let's look at Exercise 2.

Exercise 2: Calculate raw monthly returns

The goal here is to calculate arithmetic and geometric returns based on the columns we just created, with output in columns L and M.

Arithmetic returns

For total arithmetic, we sum the totals from columns J and K on our Returns tab, for the whole 5 year period.

  K L M N
8 Metric Benchmark Acme Active
9 Total Arithmetic 42.94% 47.58% 4.65%
10 Average Arithmetic 0.72% 0.79% 0.08%
11 Total Geometric 43.91% 47.59% 3.68%
12 Average Geometric 0.61% 0.65% 0.04%

In the Return Calculations tutorial we saw the difference between arithmetic and geometric methods.

Here because we need returns for two types of analyses, a risk study and in a growth-of-a-dollar context we need both.

Again, we have an 'active' column showing the difference for all four measures, so in all cases column N is column M minus column L.

Total arithmetic return is the sum, so cell L9 is =SUM(Returns!J7:J66). We can copy that over to column M for the active fund.

For the average arithmetic measure we divide by observations, or use the =AVERAGE() function because we rebalanced the portfolio back to original weights monthly. So the Acme Fund provided an average monthly outperformance 0.08% using arithmetic returns.

Geometric returns

Geometric returns are not as easy as they require compounding so we need to create another two columns of data on the Returns tab.

In columns Q and R add one to each return from column J and K.

In the section labeled Cumulative in columns T and U we are building that growth of a dollar example, compounding returns so in cells T6 and U6 we start with 1.00.

Now after the first monthly return of 7.2716%, the dollar grew to $1.07 (with rounding). To calculate that we had =Q7*T6 or the return for the month, plus one, times the value at the end of the previous month. Here it is common to use a starting point of 1.00, which is where the term growth-of-a-dollar came from.

On your end do the same for the Acme Fund and copy those cells down for all remaining rows and the result is the running cumulative return.

The point here is to create a stream that could be used to chart the growth of both the Benchmark and active portfolio as if both had started with $1, and were left to grow for 5 years.

As an illustration highlight the range from T5:U66 and click Insert then Line and select the first chart. While not required in this exercise, you've seen the purpose of geometric returns and how to generate this type of chart.

With all of our data ready, let's put on the other hat. The one of the mutual fund or investment advisor product buyer or performance analyst.

This is called a mountain chart. To the selective buyer or analyst, especially at the Institutional level the mountain chart is likely the most dreaded chart for several reasons.

First, mountain charts almost always make the portfolio manager look good, or they wouldn't show you them.

For, example a manager may cherry-pick the timeframe that highlights when they outperformed and leave off periods when they underperformed. Of course if you can source the data yourself this isn't an issue, but oftentimes the portfolio manager will create a canned report then the analyst needs to take the returns and look at sub-period returns, or rolling-period measures, as we will see in our next tutorial on Time-Series Modeling.

A second reason is that mountain charts don't take risk into consideration, which will be our focus in Steps 3 and 4 here.

Now that we have geometric returns, let's return to the Performance tab and in cell L11 and we use the final cumulative return from =Returns!T66 and then subtract 1 for the Benchmark and we can copy that over one column for the Acme Fund.

To calculate the average monthly geometric return, we take the final product to the power of, one over the number of periods, so here it is 1 divided by 60, then we subtract 1.

Copy that over, and finally we see that the Portfolio outperformed the Market using both the arithmetic and geometric returns.

Next, how will all of these returns look when we evaluate it on a risk-adjusted basis? For this we need to move on to Exercise 3.

Exercise 3: Collect risk measures

Next, we will risk-adjust these returns.

For this we need risk measures, so in cell F17 we have =STDEV.P(Returns!J7:J66) for the Benchmark and in cell G17 we have the standard deviation for the Acme Fund.

So the Acme Fund had higher risk, as measured by standard deviation.

We have been working exclusively with monthly observations up to this point, so here is something new. To annualize standard deviation multiply by the square root of the number of sub-periods in a year.

  • Annualize Standard Deviation = Return for the sub-period * (Square Root of the number of sub-periods in a year)

So to annualize monthly standard deviation we multiply it by the square-root of the number of sub-periods, or 12 here. Cell F18 reads =F17*SQRT(12).

  E F G H
16   Benchmark Acme Difference
17 Std. Dev. (Monthly) 4.61% 5.30% 0.69%
18 Std. Dev. (Annual) 15.98% 18.36% 2.38%

So on an annual basis, the portfolio manager took much greater risk when measured on a standard deviation basis, 2.38% higher.

Exercise 4: Collect regression-based portfolio measures

Next, in Exercise 4, let's run a regression on the excess returns stream by pointing to the Returns tab and columns N and O and pull out alpha using the =INTERCEPT() function and beta using the =SLOPE() function.

First off, the alpha for the x-variable, or the Benchmark by itself, is 0 and the Beta is 1, by definition, so we just populate cell L17 with 0 and L18 with 1. We only need to do the regression for the active portfolio.

Cell M17 is =INTERCEPT(Returns!O7:O66,Returns!N7:N66). That's the y-variable, the active portfolio, first followed by the x-variable, the Benchmark.

The beta calculation uses the same ranges using the =SLOPE() function. It reads =SLOPE(Returns!O7:O66,Returns!N7:N66). (See results in the table below)

  K L M
16 Metric Benchmark Acme
17 Alpha 0 0.0285%
18 Beta 1 1.10

To interpret, we see positive alpha, at 0.0285% per month. While we aren't sure about the significance of that just yet, it is positive and that's the portfolio manager's goal after all.

The beta at 1.10, is the slope of the line of best fit. This line estimates that for every 1% change in the Benchmark, the Acme Fund increased 1.10%. Any deviation from this is incorporated in the error term as we said in the tutorial on interpreting Regression. There you have it for Exercise 4.

Step 3 - Three Portfolio Performance Measures

Let's now take a look at three common measures used to evaluate portfolios on a risk-adjusted basis.

Keep an eye on The Math section, specifically formulas b, c and d. Also, the equation for a line is handy here in the preferred format, because it aligns with the terms alpha and beta in Finance.

Exercise 5: Calculate risk-adjusted return measures

In Exercise 5 it all comes together.

Let's start by calculating the average risk free rate over the period. So in cell G25 calculate the risk-free return using =AVERAGE(Returns!H7:H66) for a result of 0.24% on a monthly basis.

  E F G H
27   Benchmark Acme Active
28 Sharpe 0.102 0.104 0.001
29 Treynor 0.005 0.005 0.000
30 Jensen's   0.00285%  
a. Sharpe Ratio

First, the Sharpe Ratio is likely the most common risk-adjusted return measure. We covered the Sharpe Ratio in our tutorial on Portfolio Theory.

It represents the return per unit of risk. It is raw returns minus the risk-free return. Some people ignore the part about subtracting the risk free return and others calculate the standard deviation using excess returns. Here we will simplify the standard deviation using raw returns.

So in cell F28 we use =(L10-G25)/F17 and for cell G28 it is =(M10-G25)/G17.

In the column called 'Active' we can see that on a monthly basis the Sharpe Ratio was nearly the same for both the Benchmark and the Acme Fund. So on a risk-adjusted basis, the outperformance, while still positive, is less compelling than we saw in Exercise 2 after we risk-adjust the returns, right? Here we need to go to three decimal places and even then the outperformance barely registers.

b. Treynor Ratio

The calculation for the Treynor Ratio is similar, meaning we start with excess returns in the numerator, but here, in the denominator we use beta. The Treynor ratio typically uses the average arithmetic return and divides that by the systematic risk of the active portfolio, or the risk relative to the Benchmark.

So in cell F29 it is =(L10-G25)/L18 and for the Portfolio it is =(M10-G25)/M18.

The difference here using Treynor rounds to 0.000 at 3 decimal places.

c. Jensen's Alpha

Next, for Jensen's Alpha we use regression-based figures calculated earlier. The intercept, or constant, represents the alpha figure. It is where the line of best fit crosses the y-axis.

It shows whether the Portfolio generated returns that compensated for the risk taken relative to the Benchmark and at 0.0285% per month, it shows a very slight outperformance.

Exercise 6: Create a scatterplot of the Portfolio vs. the Market

For our last exercise, let's create a scatter plot using the excess returns stream from the Returns tab, in columns N for the x-variable and O for the y-variable. Also add a trendline and the equation as we walked through in the tutorial on Regression.

So there you have it. Notice how the R-squared figure is 0.919. This is high relative to the stock data we saw in previous tutorials, but isn't necessarily high for active portfolios as they typically fall in the range from 0.80 to 0.95 using R-squared.

The square-root of R-squared would give you a correlation of about 0.96. Recall, this is a 'very strong' relationship when we interpreted correlation, and by looking at the scatterplot you can confirm this.

It doesn't resemble a shotgun pattern at all. Instead, we can see that changes in the Acme Fund were highly correlated with changes in the Benchmark. This makes sense from the standpoint that only small active decisions were made.

Step 4 - Interpret Portfolio Performance

So to summarize these results with our performance analyst hat on, the average active return of 0.08% Exercise 2, started out looking narrowly compelling, however after running this through several risk-adjusted return measures the advantage was not far from zero.

The Sharpe was barely positive. The Treynor was about flat and the Jensen's measure registered a small positive figure.

Without holdings data for each trading day, we just have less information to go by to evaluate portfolio performance and we can't see what the portfolio manager did within the month, right?

With holdings-based information, you would have all of the daily holdings and returns linked together. So you would be able to see the exposures change over time but this also requires more sophisticated software and data sets to analyze the active portfolio properly.

Summary

By way of summary, we shifted our attention to portfolios instead of individual stocks. We put all of our understandings of correlation and regression to good use. We talked about the concept of returns-based and holdings-based analysis, which relates to the inputs, and here we fed returns-based data into the regression.

We resurrected the topics of arithmetic and geometric returns as well and tacked on three new portfolio-related measures so we can evaluate return on a risk-adjusted basis.

We talk often about what goes on at Institutionial asset management firms. Often a portfolio manager will say they outperformed their Benchmark, but under the microscope, one in which risk is taken into consideration, that outperformance may not look as compelling, as we have seen here ourselves with this small 4-stock portfolio.

Step 5 - Next: System Setup

In the next episode we will shift to Chapter 5 and focus on the mechanics of building models, like risk and alpha models, in a more scalable fashion. After that, we will use linear algebra, which is better suited for manipulating large data sets.

This series is about building better financial models and these resources sit here for you to explore and use.

Please feel free to join us at any time and have a nice day.


What's Next?

To learn faster watch the videos because many of the exercises are demonstrated visually in Excel.

If you learned something today please check out our YouTube Channel and subscribe straight from here.

  • To access all tutorials in Quant 101, click Outline.
  • To interpret linear regression, click Back.
  • Learn about rolling regressions for time-series data, click Next.

Outline Back Next

~/ home  / finance  / quant 101  / portfolio analysis



 
 
Keywords:
portfolio analysis in excel
performance analysis in excel
portfolio performance
performance attribution
sharpe ratio
treynor ratio
jensens alpha
portfolio analyzer
portfolio analysis tool
financial advisor performance
mutual fund performance
portfolio management software
mutual fund performance
portfolio management software
data analysis tools
active return