Build a Better Process
Build a Better Process
/ factorpad.com / fin / quant-101 / calculate-stock-returns.html
An ad-free and cookie-free website.
Beginner
Videos are available at one organized Quant 101 Playlist on YouTube).
Calculate monthly returns on stocks in Excel (23:27)
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.
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.
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.
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.
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.
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.
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.
First, we will be looking for total monthly return as our output from this exercise, as opposed to average.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
I've incorporated parenthesis here so we don't forget to do the calculations within the parenthesis first, then the rest.
OK, let's move on to the details with four Exercises.
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.
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.
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.
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.
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.
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.
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.
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. Every subscription helps.
/ factorpad.com / fin / quant-101 / calculate-stock-returns.html
A newly-updated free resource. Connect and refer a friend today.
by