Three Ways to Calculate Beta in Excel - YouTube

Welcome to this tutorial on calculating beta in Microsoft Excel.

Excel allows us three different options of calculating beta.

If you're a finance major you might know what beta is.

It's the measure of volatility, or systematic risk.

In this example I'm going to be using Kinder Morgan and I'm going to be comparing it to

the S&P500.

What I usually do is go to Yahoo Finance and get historical pricing for the two.

In this example I'm going to use the monthly return over the last 3 years just to keep

it simple.

However, I believe you should be using at least 60 points and this is only going to

be bringing up 36.

Probably not a very good sample size, but I want to keep it short.

We are going to click get prices and you can actually download to spreadsheet.

You will do the same thing for the S&P, which is GSPC.

Click Go.

It's going to leave the same date range you already set.

You can download to spreadsheet.

I actually already did that and I'm using the close for the two not the adjusted close.

In Yahoo Finance, whenever you download data, it always goes from newest to oldest.

We need to calculate the monthly returns.

To do that we need to sort this the other way around, so from oldest to newest.

We can go ahead and compute the monthly returns for the two assets.

I'm just going to drag this formula over and drag it all the way down.

The first way to calculate beta I will explain is using a regression.

A regression basically is a scatter plot.

It's going to plot all these returns and it's going to draw a line through that scatter

plot.

That line, the slope, will equal our beta.

So to do that we actually go to the top, click data, then data analysis, and run regression.

Now if you don't have that as an option, you will have to go to your start menu for excel,

click options.

It's going to bring up this window.

Click add-ins, manage excel add-ins, click go.

You're going to highlight this Analysis Toolpak.

I believe this comes with Excel version.

It's just not always enabled right away, so you have to click okay on that.

We are going to go back here, click regression and click okay.

Now it says the Y-range.

This is going to be the return on our stock.

The X-range is going to be our return on the market, which in this case we are using the

S&P.

I'm going to put the output range right here.

Just to show you what I mean about the scatter plot and how it's going to make a line, I'm

going to enable this, but usually I don't.

Click Ok.

This is actually going to be our Beta right here.

The .54.

You can see all these different data points and here is the line that best fits through

there.

The slope of this line is .54.

The second way to compute it is actually to just calculate slope.

To do that we actually just type equal slope.

Known Y is going to be stock return and known X is going to be our market returns.

Close parenthesis out, hit enter.

.54 equals the exact same, so you know we did it right.

The other way to do it is to compute the co-variance divided by the variance.

So we are going to equal covariance.p.

Our array 1 is going to be our stock return.

Our array 2 is going to be our market return which is the S&P.

Close parenthesis.

We will divide it by the var.p which is going to be just the market return.

Close the parenthesis and then do control + shift + enter.

If you scroll back to the top you will see that this also equals .542.

Hope you found these three formulas useful.

Until next time.

Thanks.