Build a Better Process
Build a Better Process
/ factorpad.com / fin / quant-101 / portfolio-risk-decomposition.html
An ad-free and cookie-free website.
Intermediate
Videos are available at one organized Quant 101 Playlist on YouTube.
Portfolio risk decomposition into systematic risk and specific risk (16:58)
Welcome. Today's goal is to walk through a decomposition of risk for a portfolio summarizing calculations found in risk management software.
I'm Paul, and there are so many ways to get lost when analyzing risk of portfolios.
So here we will take a huge step and walk through a very important process that is given a very odd name: risk decomposition. In the end we will have a fun little risk analysis tool that you can build into your financial models.
If it would help you, we have a web page with the entire video transcript and key Excel formulas, found at the first link in the video's Description.
Here is the plan for the day.
First, we will create an active portfolio to compare with a Benchmark portfolio.
Second, we pull forward a short window of time to study.
Third, we create a scatter plot in Excel to visualize the regression.
Fourth, we perform a return and risk decomposition and analyze a variety of portfolios by altering weights.
And in our next episode we will walk through each component from Excel's Data Analysis regression report.
Okay, for Step 1, let's create an active portfolio for Acme Fund with weights to four stocks in row 8 and for this study we will assume the portfolio is rebalanced monthly back to these original weights.
Our Benchmark was constructed with 25% weights to these same four stocks and rebalanced monthly as well.
In other tutorials we refer to this as the Market portfolio, but here because we are using it for a comparison for an active portfolio, it takes the name Benchmark, as is common in practice.
| E | F | G | H | I | J | |
|---|---|---|---|---|---|---|
| 7 | MSFT | EBAY | ABT | MRK | Total | |
| 8 | Acme Weight | 10% | 40% | 10% | 40% | 100% |
| 9 | Benchmark Weight | 25% | 25% | 25% | 25% | 100% |
| 10 | Relative Weight | -15% | 15% | -15% | 15% | 100% |
Having a Total column helps us ensure that weights always add up to 100%, which will help us later.
The investment industry terms relative weight and active weight refer to how far the active portfolio weight is from the Benchmark weight.
While we're here, because we want to change stock weights later without having to update cell references, which is a hassle and may lead to errors, let's name these weights so we can use the name in the formula instead.
Click on F8 and in the Name Box type
MSFT_weight for that stock's
allocation in the Acme Fund portfolio. For eBay in G8
use EBAY_weight, then in
H8 type
ABT_weight and finally for Merck in
I8 enter
MRK_weight.
Now let's head to the Returns data tab where we already have the returns on the Benchmark built.
You can download the stream of Raw Returns sign-up free as spelled out in the System Setup tutorial.
The Excess Returns table was created by subtracting
the risk-free return for each month, labeled rf from
each monthly return, for four stocks and the Market. So for example,
cell K7 is =C7-H7.
Again, the Market here takes the name Benchmark.
In column Q we need to build that active portfolio by pointing to the weights we already set up, and we can do it for the whole 60-month period while we're at it.
The formula in cell Q7 sums the weights times the
excess returns for each period for each stock held in the portfolio.
It reads
=K7*MSFT_weight+L7*EBAY_weight+M7*ABT_weight+N7*MRK_weight.
From there you can copy the formula down 59 rows and there you have it, monthly returns for an active portfolio that will change as we modify the weights in row 8 of the table we set up in Exercise 1.
Okay, for Step 2, let's pull forward returns.
Because we want to see everything right in front of us, we'll accept a little statistical insignificance for ease of visualization by using a very short 15-month period. The point is to understand the concept, right?
In column D we will pull forward dates. The easy way
is to grab the first date of 11/30/06 in D17 by
pointing to =Returns!J50.
Similarly, we can pull the Benchmark return in cell E17
with =Returns!O50.
Finally we pull the Acme Fund return forward in F17
with =Returns!Q50.
Once those are populated you can copy them down 16 rows to complete the table.
| D | E | F | |
|---|---|---|---|
| 16 | Date | Benchmark Return | Acme Return |
| 17 | 11/30/06 | -0.55% | -0.88% |
| 18 | 12/29/06 | -0.93% | -3.08% |
| 19 | 1/31/07 | 5.26% | 4.95% |
| 20 | 2/28/07 | -2.31% | -1.86% |
| 21 | 3/30/07 | 0.92% | 1.40% |
| 22 | 4/30/07 | 6.56% | 8.01% |
| 23 | 5/31/07 | -0.35% | -1.02% |
| 24 | 6/29/07 | -4.01% | -3.50% |
| 25 | 7/31/07 | -1.98% | -0.92% |
| 26 | 8/31/07 | 1.62% | 2.28% |
| 27 | 9/28/07 | 5.70% | 7.56% |
| 28 | 10/31/07 | 7.76% | 4.48% |
| 29 | 11/30/07 | -2.43% | -2.75% |
| 30 | 12/31/07 | 0.00% | -0.91% |
| 31 | 1/31/08 | -12.15% | -16.88% |
| 32 | 2/29/08 | -6.83% | -4.57% |
| 33 | 3/31/08 | 1.57% | 0.41% |
With that we have all of the data we need.
So moving on the Step 3, let's create a scatter plot in Excel for the 15-month period ending 1/31/08.
The data is aligned properly with the independent x-variable, the Benchmark return, listed first and the dependent y-variable, the Acme Fund return, second. So we can highlight the range from E17:F31, then click Insert followed by Scatter and Scatter with only Markers.
We are only going to row 31 at this point, and later I'll return to rows 32 and 33 and show you how a rolling regression works.
We like to see the formula on the chart, so to do that, click Layout, then Trendline and More Trendline Options.... From there click on Display Equation on chart then Close and customize as you like.
The Excel default formula for a line is in y = mx + b format and in Finance we prefer the rearranged form y = a + bx + e because it includes the error term, which we will need later.
That said, the slope of this line or beta is 1.148 and the intercept or alpha is -0.004.
The equation for the line estimates the dependent variable y given changes in the independent variable x. So the line estimated that for a -12.15% Benchmark return, the Acme Fund would have declined by -14.35% instead of the -16.88% that it actually declined by in January of 2008. This difference is picked up in the error term.
I found the estimate of -14.35% for y by multiplying 1.148 times -12.15% minus 0.004.
Okay, let's move on to Step 4 and walk through the regression together with the return decomposition. After that we'll walk through the risk decomposition and play with our new portfolio risk management tool.
In cell G31 we use Excel's
SLOPE() function with
=SLOPE(F17:F31,E17:E31) and the
resulting 1.148 figure matches what we saw on the chart.
Next, while we don't need the standard error to decompose return we will need it later to decompose risk, so let's pull it now.
One option is to use the STEYX()
function which, if you survived the last tutorial on
Risk Contribution
then you are painfully aware that I like numbers to tie, and let me
explain.
Excel's default calculation for STEYX()
divides by the degrees of freedom so the standard error is like a
sample measure instead of a population measure.
And because I don't want someone later, when looking at my risk decomposition, to say "hey buddy, your risks don't add up", so since this is an exercise anyway, I'd like to show you a more intricate way to pull the standard error, so the numbers tie.
| D | G | H | I | |
|---|---|---|---|---|
| 30 | Acme Beta | Acme Std. Error | Systematic Return | Specific Return |
| 31 | 1.148 | 0.0157 | -13.95% | -2.93% |
| 32 | 1.086 | 0.0180 | -7.51% | 2.84% |
| 33 | 1.077 | 0.0175 | 1.69% | -1.28% |
The formula in cell H31 nests a
LINEST() function inside of an
INDEX() function inside a
SQRT() function.
It looks like:
=SQRT(INDEX(LINEST(F17:F31,E17:E31,TRUE,TRUE),5,2)/15).
If you want an introduction or refresher on
LINEST() and
INDEX() zip back to our
LINEST function
tutorial.
The modification I made, so the numbers tie, was the division by 15 instead of the degrees of freedom which would have been 13. You will see in a moment how this impacts the decomposition of risk.
To decompose return is relatively easy, we have the excess return on the portfolio already and all we need is the systematic piece and through subtraction we can find the specific return using this formula.
The left side is systematic return and the right side is specific return.
Now with the beta of 1.148, we can split up the Acme return of -16.88%,
on January 31st, the date of our regression, into a piece that is
correlated with the movements in the market in cell
I31 using
=G31*E31, for a figure of -13.95%.
A rationale for not including the alpha in the equation here was given in the tutorial on Expected Return. This to me is a point of personal preference for the financial modeler.
Now for the specific return in cell J31 we simply
subtract the systematic return from the original Acme Fund excess
return in column F using
=F31-I31 for a figure of -2.93%.
The systematic return of -13.95% can be interpreted as the portion of return attributed to the exposure to the market in general. The specific piece is associated with firm-specific events for the Acme Fund holdings for that month. This includes events like press releases, investors reacting to financial statements, information about competitors, and the like.
Let's tackle the second step here and walk through the risk decomposition using the formula for risk that is similar to the one for return from earlier.
From way back in our Stats class who can forget how many times we heard that risks are not additive? So how is it that we can decompose risk with addition here? The answer is that uncorrelated sources of variance are additive.
Of course, we prefer to interpret standard deviation instead of variance as well, but for risk decomposition it is most common to use variance as the math for standard deviation gets a little tricky.
Also risk, unlike return, covers many periods and we normally square the terms as we are doing here. Once again, the left side is the systematic piece, or systematic variance.
Systematic risk is derived by squaring the beta and multiplying that by the Benchmark Variance. This is the piece that is correlated with movements in the Benchmark portfolio.
It is important to review terms you will hear in different settings. Systematic risk is often called Market risk, common factor risk, non-diversifiable risk, common source risk or undiversifiable risk. I've even heard it called systemic risk.
The specific risk is related to company-specific factors and is the standard error squared. We already have both of these from the regression earlier, so let's plug and play.
| D | K | L | M | N | O | |
|---|---|---|---|---|---|---|
| 30 | Benchmark Variance | Systematic Variance | Specific Variance | Acme Variance | Specific % | |
| 31 | 1/31/08 | 0.00231 | 0.00304 | 0.00025 | 0.00329 | 8% |
| 32 | 2/29/08 | 0.00262 | 0.00309 | 0.00032 | 0.00341 | 9% |
| 33 | 3/31/08 | 0.00263 | 0.00306 | 0.00031 | 0.00336 | 9% |
In cell K31 we have the Benchmark variance using
=VAR.P(E17:E31).
In cell L31 we square the beta and multiply it by
the Benchmark variance, so =G31^2*K31.
For the specific variance for the Acme Fund we square the standard
error, with =H31^2 in cell
M31.
Because we took the effort to customize the regression earlier, now
the total of these two should equal the total Acme variance using
=VAR.P(F17:F31) in cell
N31. We have a match at 0.00329, and that makes me
feel better.
The way we set these formulas up, they should be good to copy down two rows so you can see how a partial rolling regression works. This demonstrates a historical risk decomposition over multiple periods.
Again, the timeframes we use here are far too short for a real study and I also used population variances throughout, but the point was to show you how this works and let you modify your own financial models from there.
Don't forget we are operating in what we call the Historical Timeframe, so looking back like in a performance attribution application. Be mindful, this whole procedure elsewhere may be called ex-post risk decomposition or realized risk decomposition. In the last tutorial titled Risk Contribution we talked about how to do this for the Expected Timeframe.
Also, I mentioned how we normally don't try to interpret variance because it is in units of returns-squared and also takes 4 or 5 decimal places to even show up. So in column O, we made this interpretable by dividing the Acme Fund specific variance by the Acme total variance so we can tell what percentage of total variance is specific to the stocks, with the remainder being systematic risk. That way it is interpretable.
And now the fun begins. Let's run through a few examples to see what's going on here.
For our first example, let's act like a closet index fund manager and align our weights like the Benchmark.
In the range from F8:I8 let's input 25% in each stock. Notice how all points on the scatter plot sit on the line. This makes sense. Also, the beta is 1.000 and there is no intercept. The specific variance is zero so the systematic variance matches the total variance and the Benchmark variance. No huge surprises here, at this point the active fund matches the Benchmark.
Now why don't we act like a gambler and allocate the whole portfolio to
eBay, our highest risk stock, if you recall. So input
100% in cell G8,
zeroing out the other three stocks and notice how the picture changes.
The points on the chart are now scattered far away from the line. The
portfolio specific variance now makes up 56% of the total variance for
Acme.
I'll let you calculate it on your end to confirm, but since the portfolio held 100% of eBay, its total variance should match eBay's.
Granted those were two extreme cases, but now you have a portfolio risk analysis tool that you can use to build something more useful for portfolios with a larger number of holdings.
Go ahead and play with this by altering weights, but remember to make sure the total of portfolio weights in cell J8 equals 100% and you should be good to go.
What I will do is set this back to the original weights of 10% for Microsoft and Abbott Labs and 40% to eBay and Merck, and get this table ready for a very detailed deep dive into Excel's Data Analysis output in the next tutorial.
By way of summary, we walked through a risk decomposition and created a fun little risk analysis tool for a portfolio of four stocks.
The concepts of return and risk decomposition for portfolios follows what we saw earlier for the Expected Timeframe, with some subtle differences.
Typically, Institutional invesment practitioners don't build these calculations in a spreadsheet; however, knowing how they are sourced and presented in portfolio analysis programs is helpful for the advancing financial model builder.
In the next episode of Quant 101 we will run the exact regression used to generate this scatter plot and all of the figures in row 31 using Excel's Data Analysis output. So if you are a little fuzzy on some of the numbers then stick with us for that.
After that we move on Chapter 7 where we optimize a portfolio using a bunch of that array math we learned in Chapter 5.
If you don't want to miss future tutorials on portfolio risk analysis and statistics related to stock portfolios make sure you subscribe. If you learned something here today referrals are a great way to give back too.
Please feel free to join us at any time and have a nice day.
For this tutorial in particular it is important to watch the demonstration in Excel because many of the visuals could not be reproduced here.
I encourage you to check out our YouTube Channel for more on portfolio management in Excel.
/ factorpad.com / fin / quant-101 / portfolio-risk-decomposition.html
A newly-updated free resource. Connect and refer a friend today.
by