Fervent | Finance Courses, Investing Courses

Rigorous Courses, Backed by Research, Taught with Simplicity.

How to Calculate Portfolio Beta Manually & In Excel®

How to Calculate Portfolio Beta Manually & In Excel®

By Leave a Comment

In this article, you’ll learn how to calculate Portfolio Beta from scratch – both manually as well as on Excel®. Let’s get into it.

Table of Contents hide
1 What is Portfolio Beta?
1.1 How to Interpret Portfolio Beta
2 Why is the Portfolio Beta Important?
3 How is the Portfolio Beta Used?
4 How To Calculate Portfolio Beta Manually
4.1 Portfolio Beta Example
4.1.1 Calculating Portfolio Beta (Solution)
5 How To Calculate Portfolio Beta on Excel®
5.1 Portfolio Beta Excel Calculation Example
6 What is a Good Portfolio Beta?
7 Wrapping Up

TL;DR

You can calculate Portfolio Beta using this formula:

    \[\beta_p = \sum_{i=1}^k \beta_i \omega_i\]

Where:

  • \beta_p represents the Beta of the portfolio
  • \beta_i reflects the Beta of a given stock / asset i, and
  • \omega_i denotes the weight or proportion invested in stock / asset i

Now, if this equation is freaking you out, please don’t let it freak you out. You’ll understand it inside out by the time you finish reading this article.

If you’d like to learn more about why this formula for portfolio beta works the way it does, and if you want to understand how exactly it works, then keep reading.

NOTE: This article assumes you know how to calculate individual stock betas. If you don’t know how to do this, we recommend starting with our sister article on How to Calculate Beta of a Stock.

What is Portfolio Beta?

Firstly, what exactly is the ‘Portfolio Beta’?

Put simply, it represents the systematic risk of your investment portfolio as a whole.

The interpretation of the portfolio beta is largely identical to the interpretation of the beta of a stock or asset.

The only real difference is in how it’s used. More on that in a bit.

How to Interpret Portfolio Beta

If we think about how to interpret stock beta, we essentially refer to it as the stock’s exposure to market risk.

On a similar note, then, the portfolio beta represents your portfolio’s exposure to the market risk.

Thus, if your portfolio beta is equal to 1, then your investment portfolio is as risky as the overall market portfolio.

If, on the other hand, your portfolio beta is greater than 1, then your investment portfolio is riskier vis-a-vis the market portfolio.

And finally, if your portfolio beta is lesser than 1, then your investment portfolio is less risky in comparison to the overall market portfolio.

The comparison to a value of 1 is ultimately because the Market Beta (i.e., the beta of the market portfolio) is always equal to 1.

We go over a simple proof of why the market beta is always equal to 1 in our course on Investment Analysis & Portfolio Management (with Excel®) and Investment Analysis & Portfolio Management (with Python) course.

For now, let’s think about why the portfolio beta is important.

Why is the Portfolio Beta Important?

The portfolio beta helps investors better understand their portfolio’s exposure to market risk as a whole.

For those wishing to actively manage their portfolios (i.e., conduct active investing vs passive investing), this is a crucial metric to monitor and optimise.

Suppose your investment portfolio’s beta is equal to 1. This would mean that it’s as risky as the market portfolio.

Adding a stock with a beta of say, 2, to your portfolio will result in increasing the portfolio beta, and therefore, an increase in the exposure to market risk.

Similarly, if you were to add a stock with a beta of say, 0 to your portfolio, then the portfolio beta will decrease, meaning your exposure to the market risk will also decrease.

Naturally, the relationship between expected returns and risk means that your overall expected return will also decrease as a result.

It’s the portfolio beta that allows you as an investor to determine your exposure to market risk.

Put simply, the portfolio beta is your primary way of identifying your exposure to the one risk you simply cannot control.

Let’s now explore how the use of the portfolio beta differs from that of the stock beta.

How is the Portfolio Beta Used?

The portfolio beta is used to manage the exposure to the systematic risk of an already diversified portfolio.

As we demonstrate in our Investment Analysis and Portfolio Management course, one of the effects of diversification is that all the unsystematic risk is eliminated.

All you’re left with, then, is the systematic risk (aka market risk).

Adding or removing stocks on the portfolio thereafter only changes the exposure to the market risk (since all the firm-specific risk has already been eliminated).

The changes in exposure to market risk are thus measured and managed by optimising the portfolio beta.

This idea fundamentally relies and builds on the Capital Asset Pricing Model.

How To Calculate Portfolio Beta Manually

Okay, let’s now think about how to calculate Portfolio Beta.

As highlighted earlier, the formula for Portfolio Beta is as follows…

    \[\beta_p = \sum_{i=1}^k \beta_i \omega_i\]

Where:

  • \beta_p represents the Beta of the portfolio
  • \beta_i reflects the Beta of a given stock / asset i, and
  • \omega_i denotes the weight or proportion invested in stock / asset i

Expanding the equation above results in…

    \[\beta_p = \beta_1 \omega_1 + \beta_2 \omega_2 + \cdots + \beta_k \omega_k\]

From this expanded form, it’s clear that the portfolio beta is nothing but the weighted average of the individual betas that make up the portfolio.

Indeed, this is in a similar vein to how to calculate portfolio returns in that we’re literally just taking a weighted average, just like we do to calculate the portfolio return!

Now, if you’re wondering how to calculate portfolio weights, then check out the linked article which goes over the estimation of portfolio weights in great detail in the context of portfolio returns.

For the purpose of this particular article, knowledge of how to calculate portfolio weights isn’t essential (but it certainly is helpful!).

It’s important that you understand how the portfolio beta essentially is just a weighted average of individual stock betas.

This is perhaps clearer if we consider a simplified case of 2 assets.

In a 2 asset case, the portfolio beta is calculated as…

    \[\beta_p = \beta_1 \omega_1 + \beta_2 \omega_2\]

And since the sum of weights is always equal to 1 (\sum_{i=1}^k \omega = 1), we can rewrite the equation above as…

    \[\beta_p = \beta_1 \omega_1 + \beta_2 (1 - \omega_1)\]

Let’s consider an example now.

Portfolio Beta Example

Consider an investor Jo, who’s evaluating 2 stocks Royal Drones Plc (RD) and Joy Sonic Inc. (JS). Jo’s considering investing 60% of her money in RD and 40% in JS. Analysts estimate RD’s beta to be 1.35 and that of JS to be 0.79. Calculate Jo’s portfolio beta assuming these are the only two assets she will hold.

Try solving this on your own using the formula for portfolio beta from above.

We’re going to assume that you did that, so let’s try solving this together now.

Calculating Portfolio Beta (Solution)

We know that the portfolio beta is calculated as…

    \[\beta_p = \sum_{i=1}^k \beta_i \omega_i\]

Where:

  • \beta_p represents the Beta of the portfolio
  • \beta_i reflects the Beta of a given stock / asset i, and
  • \omega_i denotes the weight or proportion invested in stock / asset i

For a 2 asset case, the portfolio beta formula simplifies to…

    \[\beta_p = \beta_1 \omega_1 + \beta_2 \omega_2\]

Using notations from the question, we can write the formula for portfolio beta as…

    \[\beta_p = \beta_{RD} \omega_{RD} + \beta_{JS} \omega_{JS}\]

We’re told that Jo wishes to invest 60% and 40% in RS and JS respectively.

Thus, \omega_{RD} = 0.6 and \omega_{JS} = 0.4 meaning the equation can now be written as…

    \[\beta_p = \beta_{RD} 0.6 + \beta_{JS} 0.4\]

Plugging in the values for the individual betas, we have…

    \[\beta_p = 1.35 \times 0.6 + 0.79 \times 0.4\]

Solving for this using BODMAS yields…

    \[\beta_p = 0.81 + 0.316 = 1.126 \approx 1.13\]

Jo’s portfolio beta is therefore approximately equal to 1.13 meaning it is slightly riskier than the market portfolio.

How To Calculate Portfolio Beta on Excel®

Alright, now that you know how to calculate portfolio beta manually, let’s see how we can calculate it on Excel.

You can calculate portfolio beta on Excel using the “SUMPRODUCT” function.

Excel’s “SUMPRODUCT” essentially takes the sum of multiple products (being the value of 2 numbers multiplied by one another).

Let’s take a look at an example.

Portfolio Beta Excel Calculation Example

Suppose you know of five stocks and their individual stock betas and portfolio weights as follows:

Example question data on how to calculate portfolio beta on Excel

You can calculate portfolio beta for these 5 stocks using Excel’s SUMPRODUCT function.

To do so, you would call the SUMPRODUCT function and then pass in B6:F6, and B7:F7 as the two arrays required in the function like so:

Image showcasing the use of SUMPRODUCT to calculate portfolio beta on Excel

Next, just hit Enter and you’re done!

The Beta of this particular portfolio is equal to 0.7845, or approximately 0.79.

Image showcasing the value for portfolio beta calculated using Excel

Notice that this particular set of stocks has a large variety in terms of their individual exposures to market risk.

Mindle Plc has a negative beta equal to -0.04, which is very close to 0, meaning it’s the least risky of the 5 stocks (relative to the market portfolio).

Ann Berkshire Inc on the other hand, is the most risky stock (relative to the market portfolio) given a Beta of 2.84.

Remember, higher beta stocks tend to be riskier vis-a-vis low beta stocks.

By combining a range of different market exposures (Betas), you can better your exposure to market risk.

And this brings us to the next point – identifying a ‘good’ portfolio beta.

What is a Good Portfolio Beta?

A ‘good’ portfolio beta really depends on what your investment goals are and what your risk preferences are.

If you’re a risk-loving investor, then a portfolio beta of 0 would be quite terrible for you. That’s because a portfolio beta of 0 technically means the portfolio is risk-free (at least in terms of exposure to market risk). Don’t confuse this with the risk-free rate which is a whole other topic in and of itself!

If, on the other hand, you’re a risk-averse investor (i.e., someone who prefers not to take risk), then a portfolio beta of say, 1, might be far too high.

Note that, although academics tend to label and classify investors as either “risk-averse” or “risk-neutral” or “risk-loving”, in reality, people are more likely to be a mix of the types.

Do you know what your risk preference / tolerance is? If not, take the Risk Tolerance Quiz and find out!

Investor risk preferences can vary with time and context.

The changing nature of investor risk preferences naturally means that the definition of a “good” portfolio beta will also vary with time and context.

In a nutshell, though, if you want to ensure that your investment portfolio’s market risk is no more than the overall stock market portfolio, then a “good” portfolio beta would be anything that’s lower than or equal to the market beta of 1.

If, on the other hand, you want to expose yourself to greater market risk, then a good portfolio beta would be some “reasonable” value that is greater than 1.

What’s “reasonable”? Well, that depends on your risk preferences!

Wrapping Up

Alright, hopefully, this all makes sense and you now know how to calculate portfolio beta both manually as well as in Excel.

If any part of this article isn’t quite clear, please do give it another read.

If you’re looking to learn how to manage a portfolio in excel, do check out our linked course on Investment Analysis & Portfolio Management (with Excel®). We start from the very basics and gradually build you up to become a PRO at investment analysis and portfolio management.

Alternatively, if you want to learn how to invest like a pro and rely on the data, maths, and stats to drive your investment decisions, then take a look at our rigorous courses on investing.

That’s a wrap from us for now though.

Keep learning and loving Finance!

Reader Interactions

Leave a Reply