How to calculate beta in Excel

First, we will calculate the raw statistical data or Bloomberg’s “Raw” Beta. This “Raw” Beta is before Bloomberg’s proprietary adjustment to “move” beta calculation in excel each particular company’s Beta toward the market, i.e. towards a value of 1. We will illustrate three methodologies to calculate the “Raw” Beta.

Copy the data in the column named Adj Close (cells G2 to G45) from the .csv file. Let us calculate Apple Inc’s beta in relation to the S&P 500 benchmark index. Rs is the return on the stock, and rb is the return on a benchmark index. Some may argue that the slope function is the quickest, but ultimately use what you are comfortable with. With the variety of statistical backgrounds the three options give you some flexibility in choosing a method that works for you.

How to Calculate Stock Beta in Excel

Beta is very volatile as it depends on the stock market, and we know it very well. Beta helps determine the risk and expected rate of returns for a specific stock, as well as aids in Discounted Cash Flow evaluation. 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).

In Figure 4, you can see the equation of the trendline (or the linear regression line) in the top-left corner of the scatterplot. To do so, we first add two columns to our spreadsheet; one with the index return r (daily in our case), (column E in Excel), and with the performance of Apple stock (column D in Excel). Yarilet Perez is an experienced multimedia journalist and fact-checker with a Master of Science in Journalism. She has worked in multiple cities covering breaking news, politics, education, and more. Her expertise is in personal finance and investing, and real estate.

Beta Formula in Excel (With Excel Template)

Stock beta is a measurement of the risk of a particular stock relative to the risk of the entire stock market. Before choosing a particular stock for your portfolio, you can check the beta of that particular stock to decide how risky it is. Alright, hopefully, this all makes sense and you now know how to calculate portfolio beta both manually as well as in Excel. The changing nature of investor risk preferences naturally means that the definition of a “good” portfolio beta will also vary with time and context.

• You can easily calculate the Beta using the formula in the template provided.
• Whereas stocks with a lower beta are less volatile and thus have the potential for smaller returns — but also smaller losses.
• The cash flow statement shows how a company generated and spent cash throughout a given timeframe.
• It’s the portfolio beta that allows you as an investor to determine your exposure to market risk.
• Stock beta is a measurement of the risk of a particular stock relative to the risk of the entire stock market.
• We will start with the slope method because it is easier and faster.

Calculate Jo’s portfolio beta assuming these are the only two assets she will hold. This tutorial on how to calculate beta in Excel is part of our series on analyzing stock returns. Therefore, incorporating low beta stocks versus higher beta stocks could serve as a form of downside protection in times of adverse market conditions. Low beta stocks are much less volatile; however, another analysis must be done with intra-industry factors in mind.

Method 2 – Calculating covariance in Excel

In the other column, enter the index’s daily closing prices for the same period. A company gave a risk-free return of 5%, the stock rate of return is 10%, and the market rate of return is 12% now; we will calculate Beta for the same. To calculate Beta, you can use the above formula, where you divide the standard deviation of asset returns by the standard deviation of benchmark returns. After that, multiply the result by the correlation between asset returns and benchmark returns.

Interpreting betas is a core component in many financial projections and investment strategies. Change the value in the cells C4, D47, and E47 to 0 as #NA will create problems when we create beta value. Though beta is not the only factor to consider before investing in a stock, you can make a wise decision by considering beta as well. In this guide we will approximate the calculation from the below Bloomberg terminal view for Mondelez International Inc. as of February 27th, 2014. The value of the Beta vs. the Standard and Poor’s index/SPX we will calculate is 0.82 (circled in red below).

How to use the SLOPE Function in Excel?

On a similar note, then, the portfolio beta represents your portfolio’s exposure to the market risk. Multiple factors, such as the duration of the period taken into account, are included in the computation of the beta, which creates various results that could portray a different picture. For example, some calculations base their data on a three-year span, while others may use a five-year time horizon. Those two extra years may be the cause of two vastly different results. Therefore, the idea is to select the same beta methodology when comparing different stocks. Then enter the range of stock returns and market returns in the brackets.

Open the .csv file which contains the historical data of AAPL stock. Copy the values in the column named Date (cells A2 to A45) and paste them into the cells A4 to A47 of beta.xlsx. If the beta of a particular stock is one, then that stock has the same risk as the market. If the beta value is greater than one, then that particular stock is riskier than its benchmark and vice versa.

What is Portfolio Beta?

The beta (β) of an investment security (i.e. a stock) is a measurement of its volatility of returns relative to the entire market. It is used as a measure of risk and is an integral part of the Capital Asset Pricing Model (CAPM). A company with a higher beta has greater risk and also greater expected returns. 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. 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). It’s the portfolio beta that allows you as an investor to determine your exposure to market risk.

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

Things to remember about the SLOPE Function

They wish to turn this volatility into profit, albeit with higher risks. Such investors would select stocks with a higher beta, which offer more ups and downs and entry points for trades than stocks with lower beta and lower volatility. At first, we only consider the values ​​of the last three years (about 750 days of trading) and a formula in Excel, to calculate beta.

You can calculate portfolio beta on Excel using the “SUMPRODUCT” function. Jo’s portfolio beta is therefore approximately equal to 1.13 meaning it is slightly riskier than the market portfolio. Consider an investor Jo, who’s evaluating 2 stocks Royal Drones Plc (RD) and Joy Sonic Inc. (JS).

The cash flow statement shows how a company generated and spent cash throughout a given timeframe. From time to time you might want to hide source data in your worksheet. Below is a break down of subject weightings in the FMVA® financial analyst program. As you can see there is a heavy focus on financial modeling, finance, Excel, business valuation, budgeting/forecasting, PowerPoint presentations, accounting and business strategy.