Build a Better Process

# Stock Portfolio Risk Decomposition into Systematic and Specific Risk

Here we create a portfolio and decompose risk into two uncorrelated components. Conveniently, we also separate portfolio return.
1. Active Portfolio - Set stock weights to build an active portfolio and compare it to a Benchmark.
2. Returns - Pull forward a short window of returns.
3. Scatter plot - Create a visual with Excel's scatterplot.
4. Decomposition - Perform a return and risk decomposition an analyze portfolios with different weights.
5. Next: Data Analysis - Walk through a regression using Excel's Data Analysis method.
Updated: February 19, 2021
Separating systematic risk from specific risk is no easy task, but by the end here we will have a fun risk analysis tool to play with, so keep reading.

/ factorpad.com / fin / quant-101 / portfolio-risk-decomposition.html

An ad-free and cookie-free website.

## Learn how to decompose risk for stock portfolios in Excel

Intermediate

### Video

Videos are available at one organized Quant 101 Playlist on YouTube.

Portfolio risk decomposition into systematic risk and specific risk (16:58)

### Video Script

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.

#### Outline

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.

#### Step 1 - Create an Active Portfolio

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`.

##### b. Create excess returns for the portfolio

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.

#### Step 2 - Pull Forward Returns

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.

#### Step 3 - Create a Scatter Plot

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.

#### Step 4 - Analyze the Return and Risk Decomposition

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.

##### a. Collect regression measures

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.

##### Decompose return

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.

• Excess Return on Portfolio =
(Beta * Excess Return on Benchmark) + Error

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.

##### Decompose risk for a portfolio of stocks

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.

• Portfolio Variance =
(Beta^2 * Benchmark Variance) + Standard Error^2

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.

##### d. Change stock weights and monitor specific risk

And now the fun begins. Let's run through a few examples to see what's going on here.

###### Reallocate the portfolio like an index fund

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.

###### Reallocate the portfolio like a gambler

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.

###### Go back to original weights

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.

#### Summary

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.

#### Step 5 - Next: Excel's Data Analysis Regression

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.

## What's Next?

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.

• To access all tutorials in Quant 101, click Outline.
• For a stock's risk decomposition and risk contribution, click Back.
• One kind tip removes thousands of annoying ads from existence. Click Tip.
• For the 8 most important measures from Excel's Data Analysis method, click Next.

/ factorpad.com / fin / quant-101 / portfolio-risk-decomposition.html

specific risk
portfolio systematic risk
systematic risk
unsystematic risk
diversifiable risk
firm specific risk
financial risk management
non systematic risk
risk analysis
risk decomposition
risk management tools
company specific risk
financial risk analysis
decomposition of risk
portfolio analysis tool
portfolio risk in excel

A newly-updated free resource. Connect and refer a friend today.