Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Principles of Finance

15.5 Using Excel to Make Investment Decisions

Principles of Finance15.5 Using Excel to Make Investment Decisions

Learning Outcomes

By the end of this section, you will be able to:

  • Calculate the average return and standard deviation for a stock.
  • Calculate the average return and standard deviation for a portfolio.
  • Calculate the beta of a stock.

Average Return and Standard Deviation for a Single Stock

Excel can be used to calculate the average returns and the standard deviation of returns for both a single stock and a portfolio of stocks. It can also be used to calculate the beta for a stock. Historic stock price data for stocks you are interested in analyzing can easily be downloaded from sites such as Yahoo! Finance into Excel. The examples in this section use monthly stock data from December 2017 to December 2020 from Yahoo! Finance.

Monthly price data for AMZN (Amazon) is shown in column B of Figure 15.4. To begin, monthly returns must be calculated from the price data using the formula

Monthly Return = Ending Price - Beginning PriceBeginning PriceMonthly Return = Ending Price - Beginning PriceBeginning Price

The ending prices shown in Figure 15.4 are the last price the stock traded for each month. Each month, the return is calculated under the assumption that you purchased the stock at the last trading price of the previous month and sold at the last price of the current month. Thus, the return for January 2018 is calculated as

Monthly Return = Jan2018 Price - Dec2017 PriceDec2017 Price = $1,450.89 - $1,169.47$1,169.47 = 0.2406 = 24.06%Monthly Return = Jan2018 Price - Dec2017 PriceDec2017 Price = $1,450.89 - $1,169.47$1,169.47 = 0.2406 = 24.06%

This is accomplished in Excel by placing the formula =(B3-B2)/B2 in cell C3. This formula can then be copied down the spreadsheet through row C38. Now that each monthly return is in column C, you can calculate the average of the monthly returns in cell C39 and the standard deviation of returns in cell C40.

An Excel screenshot the ending price and monthly return of Amazon’s stock price for 13 months. The average monthly return is 0.0330. The standard deviation is 0.0933
Figure 15.4 Calculating the Average Return and the Standard Deviation of Returns for AMZN (data source: Yahoo! Finance)

Over the three-year period, the average monthly return for AMZN was 3.3%. However, this return was highly volatile, with a standard deviation of 9.33%. Remember that this means that approximately two-thirds of the time, the monthly return from AMZN was between −6.03% and 12.63%.

Download the spreadsheet file containing key Chapter 15 Excel exhibits.

Average Return and Standard Deviation for a Portfolio

The Excel screenshot in Figure 15.5 shows the return and standard deviation calculation for a portfolio. This sample four-stock portfolio contains AMZN, CVS, AAPL (Apple), and NFLX (Netflix). This portfolio is constructed as an equally weighted portfolio; because there are four stocks in this portfolio, each has a weight of 25%.

The Excel screenshot shows the Average Return and Standard Deviation for a Portfolio containing four-stocks: Amazon, CVS, Apple, and Netflix. The monthly average return and standard deviation is calculated for the individual stocks, as well as the entire portfolio. It shows that the portfolio has a Monthly Average Return of 0.0269 and a Standard Deviation of 0.0710.
Figure 15.5 Calculation of the Average Return and Standard Deviation for a Portfolio (data source: Yahoo! Finance)

The monthly returns for each stock are recorded in their respective columns. The portfolio return for each month is calculated as the weighted average of the four monthly individual stock returns. The formula for the portfolio return is

Portfolio Return = 0.25 × RAMZN + 0.25 × RCVS + 0.25 × RAAPL + 0.25 × RNFLXPortfolio Return = 0.25 × RAMZN + 0.25 × RCVS + 0.25 × RAAPL + 0.25 × RNFLX

The formula =$B$1*B3+$C$1*C3+$D$1*D3+$E$1*E3 is placed in cell F3. The formula is then copied down column F to calculate the portfolio return for each month. After the monthly portfolio return is calculated, then the average monthly portfolio return is calculated in cell F39. The average monthly portfolio return is 2.69%.

Because this is an equally weighted portfolio, with each of the four stocks impacting the portfolio return in the same way, the average monthly portfolio return of 2.69% is the same as the sum of the average monthly returns of the four stocks divided by four, or 0.0330 + 0.0021 + 0.0380 + 0.03474 = 0.0269 = 2.69%0.0330 + 0.0021 + 0.0380 + 0.03474 = 0.0269 = 2.69%.

The standard deviation of the monthly portfolio returns is calculated in cell F40. This four-stock portfolio has a standard deviation of 7.10%. Unlike the average return, this standard deviation is not equal to the average of the standard deviations of returns of the four stocks. In fact, the standard deviation for the portfolio is less than the standard deviation for any one of the four stocks. Remember that this occurs because the stock returns are not perfectly positively correlated. The high return of one of the stocks in one month is dampened by a lower return in another stock during the same month. Likewise, a negative return in one stock during a month might be offset by a positive return in one of the other three stocks during the same month. This is the risk reduction benefit of holding a portfolio of stocks.

Calculating Beta

The standard deviation of a stock’s returns indicates the stock’s volatility. Remember that the volatility is caused by both firm-specific and systematic risk. Investors will not be rewarded for firm-specific risk because they can diversify away from it. Investors are, however, rewarded for systematic risk. To determine how much of a firm’s risk is due to systematic risk, you can use Excel to calculate the stock’s beta.

To calculate a stock’s beta, you need the monthly return for the market in addition to the monthly market return for the stock. In column B in Figure 15.6, the monthly return for SPY, the SPDR S&P 500 Trust, is recorded. SPY is an ETF that was created to mimic the performance of the S&P 500 index by State Street Global Advisors and is often used as a proxy for the overall market performance. The monthly returns for AMZN are visible in column C. It is important that these returns be lined up so that the returns for a particular month for both securities appear in the same row number. Also, you want to place the returns for SPY in the column to the left of the returns for AMZN so that when you create your graph, SPY will automatically appear on the horizontal axis.

You will use a scatter plot to create a graph. In Excel, go to the Insert tab, and then from the Chart menu, choose the first scatter plot option.

An Excel screenshot shows how to create a scatterplot using excel. The data for the monthly return for two stocks, SPY and AMZN are placed in columns B and C, respectively. These columns are selected. On the insert tab, the scatterplot icon is selected, revealing the different types of scatterplots available for this data.
Figure 15.6 Excel Format for Calculating Beta (data source: Yahoo! Finance)

Selecting the scatter plot option will result in a chart being inserted that looks like the chart in Figure 15.7. Each dot represents one month’s combination of returns, with the return for SPY measured on the horizontal axis and the return for AMZN measured on the vertical axis. Consider, for example, the dot in the furthest upper right-hand section of the figure. This dot is the plot of returns for the month of April 2020, when the return for SPY was 13.36% (measured on the horizontal axis) and the return for AMZN was 26.89% (measured on the vertical axis).

Hover your mouse over one of the dots, and right-click the dot to pull up a chart formatting menu. This menu will allow you to add labels to your axis and polish your chart in additional ways if you would like. Select the option Add Trendline.

A screenshot of scatter plot created in excel. The scatterplot is over the data that was entered into the Excel sheet.
Figure 15.7 Creating a Scatter Plot in Excel (data source: Yahoo! Finance)

When the trendline is inserted, a formatting box will appear on the right of your screen (see Figure 15.8). If it is not already selected, choose the Linear trendline option. Scroll down and select the “Display Equation on chart” option. You will see the equation y = 1.1477x + 0.0186y = 1.1477x + 0.0186 appear on the screen. This is the equation for the best-fit line that shows how AMZN moves when the market moves. The slope of this line, 1.1477, is the beta for AMZN. This tells you that for every 10% move the overall market makes, AMZN tends to move 11.477%. Because AMZN tends to move a little more than the broader market, it has a little more systematic risk than the average stock in the market.

A screenshot of an Excel sheet that shows the formatting box used to format a trendline for a scatter plot. Different trendline options are available to select.
Figure 15.8 Inserting a Trendline to Determine Beta (data source: Yahoo! Finance)
Order a print copy

As an Amazon Associate we earn from qualifying purchases.


This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at
Citation information

© Jan 8, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.