FactorPad
Faster Learning Tutorials

Accurately calculate monthly stock returns in Excel

We provide a data set for Quant 101 and this financial modeling tutorial shows you how it was generated.
  1. Data set - Discuss the data that sits behind our set of monthly returns.
  2. Calculation - Review considerations for which return calculation method should be used.
  3. The process - Introduce the daily process including prices, regular and special dividends plus splits.
  4. Pricing sources - Cover pricing service providers, including Yahoo Finance.
  5. Next: Return distributions - Create a frequency distribution and histogram of returns.
by Paul Alan Davis, CFA, March 29, 2018
Updated: July 21, 2018
Return is not price alone. Corporate actions have a meaningful impact. Read more and do it the right way.

Outline Back Next

~/ home  / finance  / quant 101  / calculate stock returns


Learn how to calculate monthly stock returns from daily or monthly data

Beginner

Watch the Video

Calculate monthly returns on stocks in Excel (23:27)

Videos are available at one organized Quant 101 Playlist on YouTube (opens in a new browser window).

Video Script

Welcome. Today's goal is to review the calculation of monthly return in all of its detail, so we can generate an accurate figure that matches what you would get from a data vendor.

I'm Paul, and when I went through the process of calculating return manually, I was baffled by how complicated the process was.

So here I will simplify it for you, because many people can't afford expensive data vendors who offer returns that incorporate all intricacies associated with corporate actions, like splits, spinoffs, regular dividends and special dividends.

For context, this is one of 30 financial modeling tutorials in the Quant 101 Playlist. You don't have to participate in the whole Playlist to get value from this tutorial though.

For those who use Yahoo Finance, I will show you how to create your own accurate data set using daily or monthly data, or you can freely download the one that comes with Quant 101.

Some people prefer video and text, so we have both, a web page with all of the text and a YouTube video for the audio and visual. Those watching the video can find the web page at the first link in the Description.

Outline

First, we will discuss our end product, or what we are looking for. In our example, we have four stocks and 5 years worth of daily data.

Second, we cover the type of return calculation, of the two we covered earlier: arithmetic or geometric return.

Third, we cover the process, going from the tricky daily data here, while showing you how to simpify things with monthly data including all corporate actions.

Fourth, we discuss pricing services, both paid and free, like the most commonly used free service Yahoo Finance.

And in our next episode we will visualize these returns and create a frequency distribution and histogram.

Step 1 - Our Sample Data Set

If you are new to Quant 101, we are using a four stock example to create a series of financial modeling tutorials for quantitative equity portfolio management, including some portfolio theory and optimization.

Most people are put off by the details of calculating an accurate rate of return so they take shortcuts, but here we won't.

a. The end result: monthly returns

The end result sits on the Returns tab. Here we have monthly returns, in decimal format, for each of four US stocks: Microsoft, eBay, Abbott Labs and Merck.

Date MSFT EBAY ABT MRK Market Rf
4/30/2003 0.056175 0.089087 0.083536 0.062066 0.072716 0.00100
5/30/2003 -0.037544 0.094070 0.096480 -0.044689 0.027079 0.00090

The number 0.056175 in cell C7 refers to the return on Microsoft for April 2003, so just over 5.6 percent. In an earlier tutorial (System Setup) we went over how to download this data and import it to Excel. Understanding how it was put together is important and that is the point here.

The two other columns here are specific to our needs for this Quant 101 series. Because we want the math to work out later, our Market column is an equally-weighted and monthly rebalanced portfolio made up of the four stocks. And Rf refers to the risk-free rate of return, which here is the return on US Treasury Bills.

b. A five-year period of actual data

Our data set covers a five year historical period starting in March 2003, which is dated and really just serves as a sample data set to learn from.

What goes into 60 monthly return calculations is 1,259 daily periods for each stock plus a few nasty corporate actions we can't ignore.

c. Daily versus monthly

I should also mention that if you are doing historical research, like we are, then monthly returns are often sufficient.

For monthly returns you can always take a slighly-less-than accurate step of lumping corporate actions that occur mid-month into the monthly calculation.

However, if your goal is to manage money, then linking daily returns is required, and in this case a third-party data provider will really be worth looking into, as we will cover later.

Step 2 - Which Return Calculation Is Appropriate?

So let me start by bouncing a question back to you: which return calculation method should we use?

In the previous tutorial, we introduced the two methods, arithmetic and geometric. We left off wondering which method, meaning the formal name, of the calculation type we should use here.

Let's go over the clues.

a. Output: total monthly returns

First, we will be looking for total monthly return as our output from this exercise, as opposed to average.

b. Input: daily returns plus adjustments

Next, as far as inputs go, we will be using daily returns plus any necessary adjustments that come in the form of dividends, price splits and spin-offs.

c. Linking: daily returns over a backward-looking timeframe

Third, and this clue should clinch it. We are looking backward, so if you guessed the formal name to be "total historical geometric return" then you were right.

Notice how it has all of the components to it, timeframe, method and whether it is total or average? We're not using the average because that would imply we were interested in the average daily return, which we are not.

We won't need to be formal all of the time, but it's a good exercise to check in once in a while to make sure everybody is speaking the same language.

Step 3 - The Daily Process

Next, let's look at those adjustments.

The calculation of daily returns requires four items from the daily stream of data: prices, dividends, splits and spin-offs. I will review the tabs below, with 1,259 daily prices, but let me say a few words about each one first.

a. Daily prices

Daily prices are easy to access online at many financial websites such as Yahoo Finance, Google Finance and many brokerage firm websites. But this is only the first piece of the puzzle, and it's the easy part.

b. Regular and special dividends

Second, dividends come in two flavors, regular and special.

Regular dividends represent a reliable, steady and consistent stream of cash flows from a company. You can think of dividends like the fruit produced from a tree.

Dividends are normally paid quarterly. Most large and established public firms in the United States pay dividends in this form. Some investors put a lot of weight, emphasis and importance on the steadiness of the stream of dividends. Corporate boards and management are very aware of this. Reducing dividends may be interpreted as a very bad sign, which explains why they rarely occur.

During the financial crisis of 2008 and 2009, many firms reduced their dividends due to suffering cash flows, especially in the Financial sector. Also, historically, during periods of falling commodity prices, we have seen stocks in the Energy and Materials sectors decrease or discontinue their dividends as well.

To help you gauge how common dividends are, our own measure of 100 large-cap US companies, 90, or 90%, pay regular quarterly dividends.

Special dividends occur when companies reward long-term shareholders with extra cash that has built up on the balance sheet. Special dividends are also a way to account for the value of a spinoff of a publicly-traded company. We will visualize one of these shortly.

Special dividends are rare, occurring for less than 1 percent of companies annually.

Spin-offs are more frequent than you might imagine. In the sample of 100 stocks over the past 10 years, between 2 and 3 percent of companies enacted a spinoff each year.

In some pricing sources, such as Yahoo Finance, special dividends and spinoffs are treated the same way. Shareholders may in practice receive stock of the spinoff company, but Yahoo Finance translates this into a value as if it were a cash dividend. From there a return can be calculated as you will see shortly.

c. Splits

The next topic is a stock split. A split is nothing more than the change of the price of a stock through an accounting procedure. It's like cutting a pie into 8 slices instead of 4. If you add it all up, you still have 1 pie, regardless of whether each piece is one quarter or one eighth of the pie.

Now, let's say you have a pie and cut it into 4 slices and with the goal of charging $2 per slice, the total value of the pie is $8, right?

Now imagine you cut that pie into 8 slices and charge $1 per slice, the value is still $8. This is exactly what is happening in a split, the number of shares doubles and the price is cut in half.

Splits are most common in a factor of 2, which is quoted as 2 colon 1, which is the same as a 2-for-1 split. Sometimes you see splits in ratios of 3:1 or 3:2 and in the odd case that management wants to increase the price of their stock you may see a 1:10 split, which is called a reverse-split, or reverse-share-split, much like you saw with Citigroup, after the Financial Crisis a decade ago. Let's move on to a non-pie example and be a little more technical.

Say the price of a stock is $100 per share. The Board of Directors in their desire to make the stock price palatable to more shareholders, at $50, initiates this mathematical operation called a split.

Here they instruct the corporate finance department to double the number of shares outstanding, giving each shareholder who owns 100 shares another 100 shares, making a total of 200 shares. In the meantime, the price is cut in half to $50 per share. Think about it this way. You own the same percentage of the pie, they just cut it up in smaller pieces.

There is some evidence in academic studies that shareholders reward companies for splitting their shares even though it is just optics.

So back to the example above, with the pie, the argument would be that people might be willing to spend one dollar and five cents for a slice of pie, instead of one dollar, thereby increasing the whole pie value to eight times a dollar-five, or $8.40. For many, a five percent increase in value is worth the tradeoff for a little administrative work, especially if you can delegate it.

Of course this is based on an academic study, and has been out for a while, so likely the market has already priced in a premium to stocks that could split.

I'll let you chew on all of that, while saying, splits occur for about 1-2 percent of companies per year in the US large cap space. And in our data set, one of our companies, eBay, split twice during our 5-year study period, which I will show you shortly.

d. The importance of corporate actions

Before moving on, I want to make a few general comments about these corporate actions so you can gain an appreciation for why Institutional investors pay a steep price to third-party pricing providers.

Adjustments for splits, special dividends and spin-offs can alter the calculations people use in their financial models. Institutional investors move a lot of money based on assumptions so they need to ensure that calculations are accurate and reliable.

As an example, imagine if you didn't notice a stock split, and had an automated process built into a financial model that sold stocks with significant price drops. In this case you may have inadvertently sold a stock due to a split instead of an actual price drop. Oops! That's called a trading error, and an oversight like this can be embarrassing and costly.

To avoid this, Institutional investors, who subscribe to constituent-level data provided by a benchmark provider, receive notice of pending corporate actions. Most individual investors though are left in the dark.

And last, as you can imagine, corporate actions do impact indexes and benchmarks, which we are all measured against, so they are important, but we will wait until later to dig into that.

Step 4 - Pricing Sources

Let's run through where you can gain access to the data, should you want to buy it instead of build it. Many of these sources do all of this work for us, but of course, you have to pay up for this.

a. Paid services

First, paid services. It is good to know the names in the field, so you can see if a subscription fits your needs. Many Institutional firms have Bloomberg terminals which provide pricing as well as other fundamental and analytic capabilities. FactSet, Thomson Reuters, S&P Capital IQ are other reliable and long-standing firms professionals use. Some professionals also utilize pricing from their brokerage and custody firms or portfolio accounting software programs.

b. Free services

Since the late 1990s, pricing has become a ubiquitous service over the Internet for personal use. Free services typically used by individuals include Yahoo Finance, Google Finance, brokerage firms and the stock exchanges themselves.

c. The quick and dirty approach: Yahoo Finance

Let's run through the free, but quick and dirty approach.

Yahoo Finance is reliable, at least in my experience with pricing in the US. That is, if you can stand all of the advertising that resembles Times Square. Yahoo Finance, unlike others I have found, incorporates corporate actions for you.

Many people use this, so it is good to learn, and here are the steps as of this writing.

  1. Navigate - First, navigate to Yahoo Finance.
  2. Quote Lookup - In the Quote Lookup box input your ticker symbol or company name.
  3. Historical Data - Next, click on the link to Historical Data and you will see three drop-downs: Time Period, Frequency and Show.
  4. Time Period - Using Time Period input the time period you are looking for.
  5. Frequency - Using the drop-down for Frequency you can select daily, weekly or monthly periods.
  6. Show - The Show dropdown gives you options for Historical Prices, Dividends Only and Splits. The Historical Prices section includes dividends and splits, but keep in mind, sometimes the dates aren't aligned properly.
  7. Apply - After making your selections hit the Apply button.
  8. Download Data - Finally, use the Download Data button and you will be prompted to save a file in a comma-delimited CSV format which is easily imported into a spreadsheet like Excel.

Now let me expand on what I meant by quick and dirty. Remember, Yahoo Finance is free and with that, the burden of cleaning the data is the price you pay.

What I suggest is pulling prices, splits and dividends and creating a tab for each company, as I will show you. It is also a good idea to use monthly periods to save time.

Alternatively, Yahoo Finance provides a column titled "Adj. Close". This is the field that incorporates corporate actions and splits and is used to derive the charts shown on Yahoo Finance.

This Adjusted Close ("Adj. Close") field is included in the download. You can use this to calculate monthly returns, although it has a strange method of adjusting the price depending on the period selected, which is beyond our scope here.

As a side note, earlier I mentioned how programming is often a faster. In Python for example, several modules have been written to grab this "Adj. Close" field and calcualate returns for you, which I think are fairly reliable, so reach out to me for further information on that.

Two takeaways about Yahoo Finance. First, relative to paid services, you still have issues with precision and details about corporate actions, but this gets you most of the way there.

Second, like most things in life, you get what you pay for.

The Math

a. Daily return without dividends

Let's take a quick look at The Math section. First is a formula for daily return with no dividends or corporate actions. In this simple calculation you take today's stock price and divide it by yesterday's stock price, then subtract 1. We saw that in the previous tutorial.

  • Daily return without dividends = (Price (Today) / Price (Yesterday)) - 1
b. Daily return with dividend

Next, to calculate the return with a dividend, you add the dividend to today's price and divide the total by yesterday's price, then subtract 1. That's it. Think of it as just adding all forms of return that occur on the day to the ending price.

  • Daily return with dividends = ((Price (Today) + Div (Today)) / Price (Yesterday)) - 1

I've incorporated parenthesis here so we don't forget to do the calculations within the parenthesis first, then the rest.

Exercises

OK, let's move on to the details with four Exercises.

Exercise 1: Review daily prices for each of our four stocks

Let's summarize our data set again and then I'll give a peek at the Tabs in my spreadsheet that you don't have.

Ticker Name Frequency Start End Count
MSFT Microsoft Daily 3/31/03 3/31/08 1,259
EBAY eBay Daily 3/31/03 3/31/08 1,259
ABT Abbott Labs Daily 3/31/03 3/31/08 1,259
MRK Merck Daily 3/31/03 3/31/08 1,259

The table shows Ticker, Name, Frequency, which is daily, the start and end dates plus a count of the number of daily prices which is 1,259 rows. Let's look at daily prices using the Microsoft tab.

B C D E F G H I J
MSFT
Date
Price
Today
Price
Yesterday
Dividend
Ex-date
Split
Ratio
Daily
Return
Daily
Return+1
Cumulative
Return
Monthly
Return
3/31/2003 24.21           1.0000  
4/1/2003 24.35 24.21     0.0058 1.0058 1.0058  
...
4/30/2003 25.57 25.80     -0.0089 0.9911 1.0562 0.056175

I will go through the daily prices so you can see the geometric return calculation for the first monthly return of 0.056175 that matches the first cell of our Returns tab. Here we have the Price Today and the Price Yesterday. We also have columns for our two corporate action types, dividends and splits.

So if we divide 24.35 by 24.21, prices from April Fool's Day and March 31st, respectively, then subtract 1, we get a daily return of 0.0058 in decimal format or 0.58%. In Column H, we add 1 so we can multiply these for geometric return.

The Cumulative Return, in Column I, takes the day's return and multiplies it by the previous day's return. This is the compounding part of geometric returns.

So over time you can see the impact of a dollar (1.0000) invested on 3/31/03 in Microsoft. And on the last day of the month, we find the cumulative return of 1.0562, and then subtract 1 to get a return of 0.0562, or 5.62%. The number to six decimal places (0.056175) matches what we have on the Returns tab. So that's what goes into each monthly return, based on prices alone.

You might be asking, why compound daily returns instead of just taking the month-end price and dividing it by the previous month's price?

You are technically correct, $25.57, the price on April 30th divided by the price on March 31st of $24.21 generates the same return of 5.62%. However, this only works when there are no corporate actions in columns E and F.

So for months where there are no corporate actions, it is perfectly acceptable to calculate returns using prices alone.

Now, let's complicate things a bit with regular dividends.

Exercise 2: Review regular dividends and understand the adjustments for performance

Let's review the background of dividends for our four stocks and see how many adjustments we had. Three of our companies paid regular dividends, however Microsoft, after paying a $0.16 dividend in October 2003, didn't begin regular dividends payments until 2004.

eBAY didn't pay dividends over the entire 5-year period. Abbott Labs and Merck paid regular quarterly dividends over the whole period and this impacted return calculations for 20 of the 1,259 trading days.

Let's go back to that tab for Microsoft and see how the adjustment is made for dividends. Ok, as you see on 8/23/04 Microsoft paid a dividend of $0.08.

B C D E F G H I
MSFT
Date
Price
Today
Price
Yesterday
Dividend
Ex-date
Split
Ratio
Daily
Return
Daily
Return+1
Cumulative
Return
8/23/2004 27.24 27.20 0.08   0.0044 1.0044 1.1347

As you recall from our formula, take the ending price, so $27.24, add the dividend of 8 cents and divide the total by the previous day's price of $27.20, then subtract 1, for a daily return 0.0044, in decimal format.

Now you know how to generate a daily return with a dividend.

If you only wanted monthly data, you could simplify that daily table to monthly, removing a few columns. Details are provided here.

B C D E F G
MSFT
Date
Price
Today
Price
Last Month
Dividend
Ex-date
Split
Ratio
Monthly
Return
7/30/2004 28.49        
8/31/2004 27.30 28.49 0.08   -0.0390

Recall I said how daily is accurate when you need that level of precision, but for return studies often monthly data is adequate.

At four decimal places the return for both daily and monthly calculations match at -0.0390, or -3.90%, however, there is a slight difference from -0.038955 using daily returns and -0.0389610 for monthly due to the compounding of shorter periods daily.

Let's now cover special dividends.

Exercise 3: Inspect the list of special dividends and monitor adjustments

For the third Exercise, let's inspect the special dividends and see how the adjustments occur. There were only three special dividends. First, Microsoft had a special $3.08 cash dividend in 2004.

As I recall, Microsoft had a lot of cash building up on its balance sheet, and instead of making a big acquisition, they rewarded shareholders with cash. This was material in that it represented roughly 10% of the market value of the company at the time. As you can imagine, a dividend of this type is very uncommon.

What is more common is a spinoff, which as mentioned above we are treating as a special dividend for accounting purposes. As an example, Abbott Labs spun-off Hospira on May 3rd 2004. The value of that was $1.36 per share. Let's take a look at that one on the ABT_D tab, and there you have it.

Again, if you ignored these, your returns calculations would be off materially.

Exercise 4: Evaluate the impact of splits in our data set

Next, let's talk about stock splits, which are a bit tricky.

Over our 5-year historical period, there were two instances of splits. As you can see, eBay split twice, and the ratio for each was 2:1.

Let's see how the accounting works on tab EBAY_D for row 113.

B C D E F G
EBAY
Date
Price
Today
Price
Yesterday
Dividend
Ex-date
Split
Ratio
Daily
Return
8/29/2003 55.41 109.52   2 0.0119

The calculation in cell G113 incorporates all of the adjustments at once. In fact, the whole column G, for daily return, uses the same formula structure, and here it is.

  • Formula in Cell G113 =(C113+E113)/(D113/IF(ISBLANK(F113),1,F113))-1

As you can see, there are two Excel functions here. The =ISBLANK() function returns TRUE if a cell is blank. That is wrapped inside of the =IF() function.

The =IF() function provides conditional logic that basically says, if the cell is blank, or TRUE then ignore the impact of the split, by dividing by 1, otherwise include it in the calculation, with the split ratio in F113, thus dividing the previous day's price of 109.52 by 2. And at the end we subtract 1.

Yes, that is a complicated formula and I think now you can see why I calculated returns and provided the sample data. At the same time, I wanted to show you how I did it so you can build your own spreadsheet with your own stocks. Keep in mind, this is more tricky for daily data, but can be simplified for monthly data by summing up all of the dividends for the month in column E.

Also, as mentioned before, this should shed some light on why Institutional investors subscribe to third-party data providers for accurate daily and monthly returns.

Summary

In summary, now we've seen all of the details behind what goes into a stream of accurate monthly returns.

As you can see, it takes a bit of work but most of it is automated by service providers. You just have to decide for yourself whether it is better to buy or build returns yourself.

That said, it is good to understand splits, spinoffs and other corporate actions as you advance in financial model building. This is a level of detail many people do not understand, which is why some model builders take shortcuts, resulting in inaccurate data.

Step 5 - Next: Return Distributions

In the next episode we will use the data on the Returns data tab and create a frequency distribution and histogram.

And if you forget how this is all calculated, then this tutorial will sit here offering a reminder.

Please join us any time, and have a nice day.


What's Next?

I suggest watching the video in conjunction with the text here as the senses you use while learning will make the process go faster.

If you learned something today, please check out our YouTube Channel and Subscribe straight from here.

  • To access all tutorials, click Outline.
  • To see return calculation methods, click Back.
  • To create a frequency distribution and histogram, click Next.

Outline Back Next

~/ home  / finance  / quant 101  / calculate stock returns



 
 
Keywords:
excel tutorial
Yahoo Finance
Yahoo adj close
financial modeing
special dividends
monthly stock return
calculate monthly return
stock returns
regular dividends
stock split
geometric return
total return
corporate actions
monthly return
adjusted close