Build a Better Process
Build a Better Process
/ factorpad.com / fin / quant-101 / portfolio-analysis.html
An ad-free and cookie-free website.
Beginner
Videos are available at one organized Quant 101 Playlist on YouTube.
Analyze portfolio performance with linear regression in Excel (21:21)
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.
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.
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.
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 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.
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.
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.
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.
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 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 |
Let's move on to Step 2 and create an active portfolio and name it the Acme fund.
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.
The goal here is to calculate arithmetic and geometric returns based on the columns we just created, with output in columns L and M.
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 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.
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.
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.
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.
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.
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% |
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.
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.
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.
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.
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.
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.
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.
To learn faster watch the videos because many of the exercises are demonstrated visually in Excel.
If you learned something today please consider subscribing to our YouTube Channel and (no spam) mailing list.
/ factorpad.com / fin / quant-101 / portfolio-analysis.html
A newly-updated free resource. Connect and refer a friend today.
by