Build a Better Process
Build a Better Process
/ factorpad.com / fin / quant-101 / charting-portfolios.html
An ad-free and cookie-free website.
Beginner
Videos are available at one organized Quant 101 Playlist on YouTube.
Charting stock portfolios with a scatter plot in Excel (15:42)
Welcome. Today's goal is to create 11 portfolios with allocations to two different stocks and then chart the results on a risk and return plot.
I'm Paul, and if you're like me, visualizing relationships make the tough concepts easier to understand, especially when it comes to portfolio theory.
So here we will get the visual of a portfolio possibilities curve in this culmination of previous work in Chapter 3 of Quant 101, where our focus is on portfolio optimization, risk analysis and performance measurement.
Subscribers suggested that I pair video tutorials with text, so if you are interested, the first link in the video description goes to the full transcript with the key Excel formulas.
Let's run through the plan for the day.
First, we will review data and how calculations for stock and portfolio risk and return work.
Second, we create eleven portfolios and use named ranges in Excel to create a table with resulting risk and return figures.
Third, we chart this data on a risk and return plot and make observations.
Fourth, we discuss the portfolio possibilities curve and where we go from here.
And in our next episode we discuss the scholars who brought these discoveries to investors nearly 60 years ago.
To visualize portfolio return and risk for 11 portfolios we will complete a table by altering weights in 10% increments to two stocks over a historical time period.
From there we will see the free benefit of diversification take shape.
If you are new to how Quant 101 works and where we get the data for all 30 exercises our tutorial called System Setup walks through how to set up Excel and download a free data set of monthly returns that sit on the Returns data tab.
For this and other tutorials referenced here we have been using a two-stock portfolio, with 6 monthly returns. Starting with this small subset ensures that we focus on the concepts and later we can increase the number of stocks and the complexity.
Earlier, we found three of the points on the risk and return plot. We calculated individual stock risk, as measured by standard deviation of monthly returns, and average arithmetic return for both Microsoft and eBay.
So a portfolio that is 100% invested in these two stocks has the same characteristics as the individual stock, so cells L10 and M10 represent a Microsoft-only portfolio. And L20 and M20 represent the eBay-only portfolio.
We also had a portfolio that was allocated 50% to Microsoft and 50% to eBay.
We saw that the return was the weighted-average of the return for each stock. We also saw that risk wasn't the weighted average of the individual stock risk, but a number much less. We called this the free benefit of diversification.
As a review let's do a quick flyby, which will set us up for what we need to complete the table in Exercise 1.
In Chapter 2, we covered stock return topics like the difference between arithmetic and geometric returns.
Next, we saw how tricky it is to calculate an individual stock's return accurately taking into consideration the effects of dividends, splits and price changes. We learned first-hand why these tricky calculations are commonly left to third-party data providers.
So instead of getting lost there, the monthly returns are provided for you, and sit on the Returns data tab.
In the tutorial called
Calculate Stock Risk
we generated demeaned returns and squared them for each
individual stock over each period, summed them up and divided by
observations to derive each indivdual stock's variance. This
calculation is automatically done for us if we use the Excel function
=VAR.P(), which we will repeat here.
From there, taking the square root of variance we arrived at the individual stock's standard deviation, but that won't be required here.
Then for each pair of stocks we multiplied the demeaned returns, performed the same average calculation to arrive at the covariance between the pair. From there, we divided by the product of the two standard deviations to arrive at the interpretable correlation figure between the two stocks.
Here we only need one covariance figure and will simplify this with the
Excel function
=COVARIANCE.P().
Next, we calculated portfolio returns and saw how the portfolio return for the 50/50 portfolio was the simple weighted average of the individual stock returns.
Here we calculate this on the fly for each of our 11 portfolios
using the Excel function =AVERAGE() to
arrive at the average arithmetic portfolio return, the y-axis value on
our chart.
Then we saw the more complicated portfolio risk calculation for a two-stock portfolio and how it isn't the weighted average of the variances, but a longer formula. We also saw the covariance matrix come into play, with a vision towards how to calculate risk measures for large portfolios in the real world.
As risk is always more tricky than return, here we focus on the portfolio variance calculation for two stocks and then take the square root of the result to find the portfolio standard deviation which will be the x-axis value on our chart.
So if you have been with us this has been a review. If you are new and some of these concepts are not clear, then you have a roadmap for catching up and solidifying your understanding before moving on.
So here we will calculate metrics for the remaining 8 missing portfoios we haven't seen, then we can visualize the results and make observations.
It is a visual world after all, and the graphic helps to demonstrate the most important concept of all, the tradeoff between return and risk. It demonstrates how historically different allocations performed and as we look forward it will help us optimize portfolios to maximize a return-to-risk ratio.
Okay, let's get after those missing 8 portfolios by completing the table for Exercise 1.
The term portfolio allocation refers to the weight of each stock in a portfolio. For our two-stock portfolio here, unlike in the real world, it will be easy will be easy to calculate portfolio return and risk using our most basic portfolio math.
Start by creating eleven portfolio allocations in cells E10 to F20, altering portfolio weights in 10% increments. Input this data in decimal format, and you can visualize it using the percent format.
| E | F | G | H | |
|---|---|---|---|---|
| 8 | Portfolio Weights | Stock Returns | ||
| 9 | MSFT | EBAY | MSFT | EBAY |
| 10 | 100% | 0% | 2.38% | 3.98% |
| 11 | 90% | 10% | 2.38% | 3.98% |
| 12 | 80% | 20% | 2.38% | 3.98% |
| 13 | 70% | 30% | 2.38% | 3.98% |
| 14 | 60% | 40% | 2.38% | 3.98% |
| 15 | 50% | 50% | 2.38% | 3.98% |
| 16 | 40% | 60% | 2.38% | 3.98% |
| 17 | 30% | 70% | 2.38% | 3.98% |
| 18 | 20% | 80% | 2.38% | 3.98% |
| 19 | 10% | 90% | 2.38% | 3.98% |
| 20 | 0% | 100% | 2.38% | 3.98% |
All portfolios should add up to 100%.
Next, we want stock returns in columns G and H. Before proceeding, let's use the timesaver in Excel that is common across many programming languages and name ranges of data so we don't have to key them in each time.
Start by going to the Returns data tab and select
the range from C7:C12 and in the
Name Box type MSFT6
followed by Enter.
This saves the range of 6 monthly returns for Microsoft under that name.
While we're here, do the same for eBay by selecting the range
D7:D12 and naming it
EBAY6.
If you make a mistake and want to edit or view the names, click on Formulas, then Name Manager. These are saved across the whole spreadsheet, or Workbook, so the four already in there we created in the last tutorial.
Earlier when we discussed portfolio return calculations we said we were using the arithmetic return assumption and this technically rebalances the portfolio back to the original weight at the end of each month. If we didn't have this assumption, it would be much more work. Head back to the Portfolio Returns tutorial for further clarifications on that point.
So here, in cell G10 use the formula
=AVERAGE(MSFT6), using our newly named
6-month range for Microsoft. In cell H10 use
=AVERAGE(EBAY6).
The other benefit of using named ranges is that you don't have to worry about updating cell references when you copy data. So here you can copy cell G10 and H10 down 10 rows to populate the same average arithmetic returns for each stock.
Next, we need stock risk data and specifically each individual stock's variance and the covariance between the pair. Of course this data would sit in a covariance matrix, but here since we're only using three elements for all portfolio calculations, we will collect them using the same named ranges.
We know that each stock has its own variance, and for this, in cell
I10 type =VAR.P(MSFT6).
| E | F | I | J | K | |
|---|---|---|---|---|---|
| 8 | Portfolio weights | Stock variances and covariance | |||
| 9 | MSFT | EBAY | MSFT | EBAY | MSFT|EBAY |
| 10 | 100% | 0% | 0.0010 | 0.0018 | -0.0006 |
| 11 | 90% | 10% | 0.0010 | 0.0018 | -0.0006 |
| 12 | 80% | 20% | 0.0010 | 0.0018 | -0.0006 |
| 13 | 70% | 30% | 0.0010 | 0.0018 | -0.0006 |
| 14 | 60% | 40% | 0.0010 | 0.0018 | -0.0006 |
| 15 | 50% | 50% | 0.0010 | 0.0018 | -0.0006 |
| 16 | 40% | 60% | 0.0010 | 0.0018 | -0.0006 |
| 17 | 30% | 70% | 0.0010 | 0.0018 | -0.0006 |
| 18 | 20% | 80% | 0.0010 | 0.0018 | -0.0006 |
| 19 | 10% | 90% | 0.0010 | 0.0018 | -0.0006 |
| 20 | 0% | 100% | 0.0010 | 0.0018 | -0.0006 |
Do the same thing in H10 by pointing to the named range for eBay and copy those cells down 10 rows.
We know that each pair of stocks has one covariance, so we only need one measure for the pair.
For covariance in cell K10 we will simplify the
calculation for the pair using
=COVARIANCE.P(MSFT6,EBAY6). We can
also copy that down 10 rows.
Here we present variances and covariance in four decimal places and not as percent because they are in units of percent-squared, and we won't try to interpret them anyway.
With that, we are ready to move on to the standardized version of risk, standard deviation, that is in units we can interpret.
Next, to simplify charting we will start with portfolio risk on the x-axis first, and second portfolio return for the y-axis.
And finally, we will plug all of that in to the portfolio variance formula and calculate portfolio variance first, then take its square root for standard deviation all in one cell. This sits in column L.
Recall that for two stocks there are 3 terms, however the last one is basically doubled, which means that there are actually four but the last two are identical.
The long formula for portfolio variance is in cell L10
(=(E10^2*I10+F10^2*J10+2*E10*F10*K10)^0.5).
It is the weight of Microsoft squared times its variance plus
the weight of eBay squared times its variance plus two times the
weight of Microsoft times the weight of eBay times the covariance of
the pair of stocks. We wrap this all in parentheses and then take that
to the one-half power, or 0.5, which is the same as the square root.
This gives us the portfolio standard deviation of 3.20% and you can copy this formula down ten rows as all cell references will update for us.
| E | F | L | M | |
|---|---|---|---|---|
| 8 | Portfolio weights | Portfolio Risk | Portfolio Return | |
| 9 | MSFT | EBAY | Std. Dev | Average |
| 10 | 100% | 0% | 3.20% | 2.38% |
| 11 | 90% | 10% | 2.72% | 2.54% |
| 12 | 80% | 20% | 2.31% | 2.70% |
| 13 | 70% | 30% | 2.02% | 2.86% |
| 14 | 60% | 40% | 1.91% | 3.02% |
| 15 | 50% | 50% | 2.01% | 3.18% |
| 16 | 40% | 60% | 2.29% | 3.34% |
| 17 | 30% | 70% | 2.69% | 3.50% |
| 18 | 20% | 80% | 3.17% | 3.66% |
| 19 | 10% | 90% | 3.70% | 3.82% |
| 20 | 0% | 100% | 4.26% | 3.98% |
I could be more explicit here and put parentheses around each term, or rely on the fact that Excel uses the PEMDAS rules and automatically performs exponent first, then the multiplication and finally the addition.
Now that we completed the difficult part, we move to portfolio return in column M for this historical period.
In cell M10 we take the weight in Microsoft in column E times its average return in column G plus the column F times column H for eBay.
So the formula in cell M10 is
=E10*G10+F10*H10.
So the portfolio return for the portfolio invested 100% in Microsoft had an average return of 2.38%, which we saw earlier. From there, copy this down 10 rows and we have the return for each portfolio.
Okay, with that we're through with Exercise 1 and can generate the scatter plot.
In Exercise 2, we simply highlight columns L and M and create an X Y scatter chart.
Our data is aligned properly, with the x-axis presented first in column L and the y-axis data for portfolio return in column M. From the menu bar click on Insert then select the one with Scatter with only markers and there you have it a parabola showing our 11 portfolios.
You will need to play with the settings here and I'm always reluctant to demonstrate the finer points of editing graphs because it doesn't make for very compelling content. So I suggest playing with the options and fine tune it to your liking, including adding axis labels.
The one piece I added manually was the line extending from the points Microsoft to eBay.
Before we go, let's do a little analysis. The chart overall looks like we might expect, meaning the returns are positive and with a low covariance there is a benefit to owning a balance between the two stocks.
Depending on the measurement period, the rate of return could be negative of course when using a historical period, especially for a period as short as 6-months. A better measurement period statistically would be 60 months. Recall these are stocks and they do go down in value. Even for periods as long as 5 years stocks have posted negative rates of return.
At the same time, I should caution you that some teachers stop here and use past occurances, kind of assuming the future will be like the past. But in the real world practitioners use two other timeframes beyond historical.
Later we will see the expected and forecast timeframes and make this more realistic.
With that we nailed down the fundamentals with two stocks and now we can press on to portfolios that in theory could cover thousands of stocks.
By the end of this series we will show the mechanics of how investors maximize, or optimize, the return-to-risk relationship when selecting portfolios. Here we could see this by dividing return by risk, but remember this is for the period looking back, so we have a bit of work to do.
As you will see in the next tutorial this curve is often called the portfolio possibilities curve because it shows the possible portfolios.
Harry Markowitz, in the 1950s, was responsible for quantifying return and risk like this. His work was eventually worthy of a Nobel Prize in Economics, and sometimes we take this for granted when describing the benefits of diversification.
Rather than have a long conversation about this now I will just say that in the next tutorial we will further expand on the theoretical applications of a curve such as this.
By way of summary, we put together all of our learnings in the Chapter to paint a nice picture that, not only shows how diversification works, but also demonstrates our progress. Congratulations!
We used a two-stock example and have now seen the calculation of portfolio return and risk a few times. What we added here was the visual of the magic of diversification and how owning stocks with lower than perfect correlation reduces risk.
We didn't focus on the covariance matrix in this tutorial, but all of our future calculations will require the matrix, so don't forget that.
And, with that, we finished the main material of this Chapter.
In the next episode we will be able to relax a bit as we don't have any Exercises, but will cover a topic that comes up on exam day, Portfolio Theory.
I hope you had fun and learned something. If so, please tell a friend or colleague. Join us any time and have a nice day.
This and other tutorials that lean on graphics are best followed with our video tutorials.
I encourage you to check out our YouTube Channel and subscribe to learn institutional-level processes.
/ factorpad.com / fin / quant-101 / charting-portfolios.html
A newly-updated free resource. Connect and refer a friend today.
by