Knowing the Beta of your portfolio can be a really powerful way to manage the overall risk of your investments. You may have some idea of the level of risk of each individual stock in your portfolio but do you know what the overall systematic risk of your portfolio is?
Knowing the overall risk of your portfolio can be very useful, especially when you buy any additional shares or sell any of your positions. You will be quickly how this affects the overall composition of risk in your portfolio.
Calculating the Beta of a portfolio may sound like something you need to be a math genius to be able to work out, but as you will see it is very easy to do with Google Sheets.
In this blog post, we will briefly describe what Beta is before going on to show you how you can calculate it step by step all by yourself.
What is Beta and Why is it Important for your Investments?
Beta is a way that you can measure a stock’s volatility compared with the overall volatility of the market. It is a historical measure and can be calculated over any period of time, but usually the longer the time frame the better.
The market is a hypothetically all available stocks on planet earth but often the S&P 500 is used as a proxy for this.
Most importantly what you need to know is that the market will always have a Beta value = 1.
Any stocks that have a Beta value greater than 1 have historically been more volatile than the market and any stock with a Beta value less than 1 has historically been less risky than the market.
Therefore a stock with a Beta value of 1.5 would typically have risen 15% when the overall market increased 10%. Conversely, if the market dipped 10% then the stock in question would have dipped 15%.
Let’s briefly look at a few examples of the Betas of popular stocks that everyone knows.
| STOCK | BETA VALUE |
|---|---|
| Apple | 1.18 |
| AirBnb | 1.46 |
| Fiverr | 1.82 |
| 1.06 | |
| IBM | 1.10 |
| Shopify | 1.63 |
| McDonalds | 0.61 |
| Proctor and Gamble | 0.44 |
As we can see from the above stocks like Fiverr and Airbnb have been historically much more volatile investments to hold compared to McDonald’s and Proctor and Gamble.
Unsystematic vs Systematic Risk
Here is another reason why paying attention to your portfolio’s beta is important.
When you invest in any stock you are faced with two risks; unsystematic risks and systematic risks.
Unsystematic risk: these are risks that only specifically apply to the individual stock. For example a risk the the factory burning down, a CEO acting fraudulently etc.
Systematic risk: these risks are pervasive and effect all company, such as macro factors like interest rates, inflation and economic activity.
The more stocks you add to your portfolio the more the unsystematic risk is diversified away and the only material risk you face is systematic/market risks. If you hold 15 stocks in your portfolio you will unlikely notice any negative events that only affect one stock impacting your overall performance.
So How Do You Calculate the Beta of Your Portfolio on Google Sheets
Google Sheets have several inbuilt functions and databases that you can import into your spreadsheet – Google Finance formulae.
For example, you can use any of the below to pull the relevant data into your sheet =GOOGLEFINANCE(A2,”inserthere”)
"price"– Real-time price quote, delayed by up to 20 minutes."priceopen"– The price as of market open."high"– The current day’s high price."low"– The current day’s low price."volume"– The current day’s trading volume."marketcap"– The market capitalization of the stock."tradetime"– The time of the last trade."datadelay"– How far delayed the real-time data is."volumeavg"– The average daily trading volume."pe"– The price/earnings ratio."eps"– The earnings per share."high52"– The 52-week high price."low52"– The 52-week low price."change"– The price change since the previous trading day’s close."beta"– The beta value.
If the company you are trying to pull the beta value for is relatively new (e.g Coinbase IPO’d in 2021) there may not be a beta value on the Google Finance database as there is not enough data.
In these cases, you can still find beta values from other sources such as Yahoo and Bloomberg that calculate beta over a shorter time frame than Google.
Once you have imported your beta values into your portfolio tracker (using the formula =GOOGLEFINANCE(A2,”beta”)) the next step is to calculate the percentage allocation of each position in your portfolio.
For example in the below screenshot you can calculate the allocation of Apple stock as follows: Apple Equity $3,328/Total Equity $33,248 = 10.01%.
The next step is to calculate the weighted average beta.
This can be done by multiplying the individual stock beta by the allocation percentage.
Finally, you can then sum the column of the weighted beta values and the total will equal your portfolio’s beta value. In the screenshot above for example the portfolio had a beta value of 1.085.
If you would like to access the spreadsheet to copy and use for yourself then you can find it here.
When you know the beta of your portfolio you will be easily able to see how any stocks that are bought or sold increase or decrease the overall risk of your portfolio.
Disclaimer: This blog post is for informational and educational purposes only and should not be construed as financial advice.
