/ factorpad.com / fin / quant-101 / return-calculations.html
An ad-free and cookie-free website.
Beginner
Videos are available at one organized Quant 101 Playlist on YouTube.
Stock Return Calculations: arithmetic and geometric (18:31)
Welcome. Today's topic is how to calculate rate of return on investments like stocks.
I'm Paul, and I'll admit, it is difficult to retain all of the math without a periodic reminder.
So here we will reinforce the concepts with a few exercises, while picking up a few new Excel functions. Our goal isn't to just memorize, but to understand when to use each formula and how to interpret the results.
For context, this is the first video in Chapter 2 of a series of 30 financial modeling tutorials, called Quant 101. By the end of the chapter we will create a histogram of returns, but this tutorial is more of a standalone, not requiring the data set we installed in the last tutorial.
For those watching the video, if I go too fast, everything I say is printed on a web page that can be found at the first link in the video's Description. There you will find the long formulas too.
Here we will introduce three return calculation methods, but spend our time on two: arithmetic and geometric.
First, we will review our problem set for this tutorial which covers two stocks over three periods.
Second, we talk about totals and averages.
Third, we are interested in measuring historical returns here, but will mention other timeframes for financial models.
Fourth, we will walk through arithmetic and geometric returns because they are most important for us now and touch on log return.
And in our next episode we will calculate monthly returns for stocks in Excel, taking into consideration all corporate actions.
Let's move on to the one problem set we will use for this tutorial.
Here we will be using a fictitious and round-number scenario with two stocks: ABC and XYZ. This is a clean and simple example but has a nice little trick in it, that will reinforce a few takeaways for you.
We are going to share this data across Exercises 1 and 2, illustrating the difference between arithmetic and geometric returns.
Also, much like a coding exercise, I suggest typing this straight in your copy of Excel, as it will help you reinforce the tricky parts.
Imagine we have $1,000.00 US dollars invested at the beginning of the first period, then returns for three periods after that.
We have the formatted data in cells G9 through I10 in percent format, while the original data is stored in cells J9 through L10 (see video).
E | F | G | H | I | |
---|---|---|---|---|---|
8 | Stock | Invested | % Period 1 | % Period 2 | % Period 3 |
9 | ABC | $1,000 | 10% | -11% | 10% |
10 | XYZ | $1,000 | 100% | -50% | 0% |
So cell G9 is a reference to J9,
except that its format is set to percent with
right click
, then
Format Cells
. So
we can view it the way we want, to suit the presentation, but the
stored data doesn't change.
That is a good opening for the second point on decimal place convention. Here we prefer to store the data in decimal format, meaning 10%, is stored as 0.10.
For those sticking around for the Quant 101 series, if we don't keep the source data in decimals, it will cause headaches down the road.
So if a data set is given in percent, like 10 for
10%, it can be changed to decimal form by either
dividing by 100, =10/100
, or
multiplying by 0.01 =10*.01
.
Second, use caution when using 1 to refer to 1%, because 1 is also 100%, right?
The first thing to consider is, what type of return are we talking about? Meaning, when calculating return over distinct time periods a problem or scenario will require an answer to be presented using one of three different types of return.
Here, we will focus on the first two.
Also, later we will start to make a distinction between return in the past, present and future. For financial modeling we try to keep what is expected in the present and what is forecasted in the future separate by giving them different names.
In our problem set here, we are looking at past returns, and here we call that the historical period, just to be clear.
Later, when we want to know what return is baked in to current expectations, we will use the expected timeframe to refer to the present.
And finally, when referring to the future we call these returns forecast returns. Many times in the financial literature, there is no distinction made between expected and forecast, which leads to confusion, so here we will stick with these three terms.
Now in step 4, let's cover three methods for calculating return: arithmetic, geometric and logarithmic, or log.
The distinction refers mostly to how returns are linked together. Let's first talk about how they are calculated and when they are used.
After that, we will walk through the math in the exercises because that's how we will remember the finer points.
Arithmetic return is the simplest type of return to calculate.
It is often described as time uncertain, meaning the order in which the returns arrive does not matter. They could be random, for example.
So returns of 10%, -11% and 10% would yield the same result for arithmetic return as the same returns in a different order, -11%, 10% and 10%. Basically you add each periodic return together.
To me, total arithmetic return by itself doesn't have much value, except as a starting point for calculating average arithmetic return.
You find the average arithmetic return by adding each periodic return together, then dividing by the number of observations or periods. Sometimes you simply see letter n when being more formal.
Arithmetic returns ignore the compounding impact on investing, but still do serve a purpose.
In a forward-looking context, because you can't expect to know the order of returns in the future, practitioners and academic researchers often use arithmetic average returns when making estimates about the future. This makes arithmetic returns suitable for volatility studies where you want to account for each individual return occurrence but the compounding effect is not a concern.
This is also commonly known the arithmetic mean return.
Geometric returns, on the other hand, refer to a specific period of time and the order of returns matters.
To account for this, returns are multiplied, instead of added. Here, compounding is incorporated and you might see daily, monthly or annual periods linked together.
Here we add 1 to the first period return, in decimal form that is, and multiple that times 1 added to the second period return, then subtract 1.
Geometric returns are best for analyzing periods when looking back. And because you know the time frame, and the order of returns, you can calculate a return that matches the investor's experience. This is similar to what you might find in a growth of a dollar chart.
There is an average version of geometric return as well.
Here, calculating the average is a bit trickier. We have to raise the result to the power of 1 over the number of observations. I've used 'obs' here as an abbreviation. You also see 1 over n frequently.
At the end, we subtract 1.
This is also commonly called the geometric mean return.
Log returns won't be a focus here, so I'm reluctant to confuse matters now, but I wanted you to know they exist.
They are used for very short time frames, like daily or even hourly. Also, with respect to compounding, log returns are continuously compounding.
Let's look at three Excel functions we'll use here.
The Excel function =SUM()
adds a range
of cells together while the function
=PRODUCT()
multiplies them together,
and
=AVERAGE()
is self-explanatory.
Excel offers pointers if you type the first part including the first
parenthesis. So for example, if you type
=SUM(
a help box pops up which
will guide you to the inputs this function requires. This is a very
nice feature and is common for programming as well.
Let's move on to the Exercises.
For Exercise 1, we are asked to calculate total arithmetic return and average arithmetic return for both stocks ABC and XYZ. Again, I suggest duplicating this in your spreadsheet.
E | F | G | H | I | |
---|---|---|---|---|---|
14 | Stock | % Period 1 | % Period 2 | % Period 3 | |
15 | ABC | 10% | -11% | 10% | |
16 | XYZ | 100% | -50% | 0% |
I carried over the original returns in decimal notation, but have labeled them in Excel using the percentage format. You can see this by examining cells G15 to I16. Here you can see the link to our data in decimal format pointing to the range J9 to L10.
Now let's move on to the calculation of total arithmetic return. Recall that with arithmetic returns, the order of returns doesn't matter, there's no compounding effect, so we're simply adding the total of the three returns.
E | J | K | L | |
---|---|---|---|---|
14 | Stock | Arith Return % | Obs | Arith Avg % |
15 | ABC | 9.00% | 3 | 3.00% |
16 | XYZ | 50.00% | 3 | 16.67% |
In cell J15 I did it the manual way with
=G15+H15+I15
, pointing to
and adding each cell. Then for stock XYZ, in J16, I
used the =SUM()
function with
=SUM(G16:I16)
.
Now, to calculate the average, divide by the number of data points or observations, in this case 3, and you have the average arithmetic return. That's it.
In cell L15 as an example I performed the division by
hand with =J15/K15
. In cell
L16, I used the
=AVERAGE()
function, because it is
a simple average (=AVERAGE(G16:I16)
).
So the total and average arithmetic return for ABC was 9.00% and 3.00%, respectively. For stock XYZ, the same measures were 50.00% and 16.67%.
Now let's see how that same return stream compares when we use the geometric method.
In Exercise 2, we will get experience with the geometric total and average geometric method. We will include a growth of a dollar example so you can see a different interpretation. Recall, this is a better calculation for the impact on money invested prior to the start of Period 1 and compounded for three periods.
Again, I pulled down the returns in decimal format from the top. We start by adding 1 to the decimal return, which you can see in cells G22 to I22.
E | F | G | H | I | |
---|---|---|---|---|---|
20 | Stock | Invested | % Period 1 | % Period 2 | % Period 3 |
21 | ABC | 10% | -11% | 10% | |
22 | 1 + Return | 1.1000 | 0.8900 | 1.1000 | |
23 | Dollars | $1,000.00 | $1,100.00 | $979.00 | $1,076.90 |
24 | XYZ | 100% | -50% | 0% | |
25 | 1 + Return | 2.0000 | 0.5000 | 1.0000 | |
26 | Dollars | $1,000.00 | $2,000.00 | $1,000.00 | $1,000.00 |
For the total geometric return multiply these together, then subtract
1 as done in cell J22, resulting in 7.69%, using
=G22*H22*I22-1
.
E | J | K | L | |
---|---|---|---|---|
20 | Stock | Geometric Return % | Obs | Geometric Avg % |
22 | ABC | 7.69% | 3 | 2.50% |
25 | XYZ | 0.00% | 3 | 0.00% |
If you notice, I'm being lazy here and not using parentheses, knowing
that PEMDAS order of operations for math apply in Excel. So the order
goes Parentheses, Exponents, Multiplication, Division, Addition and
Subtraction. I could have put parentheses around multiplications for
better readability. In other words, it may be easier to visualize this
as =(G22*H22*I22)-1
.
Even better yet, I could use the
=PRODUCT()
function because many times
we are taking the product of 30, 60 or hundreds of cells.
To calculate the average geometric return, take the total to the
power of 1-over the number of data points or observations, in this
case 3, then subtract 1. So the formula in cell L22
might look like
=(PRODUCT(G22:I22))^(1/3)-1
.
Again geometric returns reflect the impact of compounding. In cells G23:I23 I went through an example of the change in dollars so you can see my point about how geometric returns match the true client experience. Start with $1,000.00 in cell F23, then multiply that by 1 plus the return for the first period, next multiply that by the second period and the third. Again, the order of returns matters here as the value from the previous period is used for the next period.
To illustrate that, in cell G23 you can see the
formula =F23*G22
. So these returns are
linked together, which is why order matters.
You can see how the dollars change from $1,000.00, to $1,100.00 after a 10% return, then to $979.00 after an 11% loss, then to $1,076.90 after another 10% gain.
I encourage you to work your way through all of the same calculations for the XYZ stock on your spreadsheet so your answers match mine here.
Let's take a moment to summarize this so it sinks in. I hand-picked this return series to make a few points.
First, how is it that the arithmetic returns and geometric returns can differ so materially?
For ABC, the average arithmetic return is 3% while the average geometric return is 2.5%, for the same data set. These are fairly close but indicate that differences do occur.
Next, let's take a look at the average arithmetic and geometric returns for XYZ. Here we have a drastic difference between 16.67% and 0.00%. How is that possible?
Again, the order of returns matters for geometric returns. Look at cells G26:I26 to see what happens to the initial $1,000.00 investment. The XYZ investment increases to $2,000.00 after the first 100% return, then drops back to $1,000.00 after losing 50% the next period. And after a flat return in period 3 the investment remains at $1,000.00.
If that makes you scratch your head, it's totally normal. I selected an extreme return series example to highlight an issue that many investors do not understand. Also, the reason I take so much time to discuss the two rate of return calculations is because the results can be materially different, as you see.
This highlights why we need to know when each method is used. So geometric returns are best used to match the true client experience for backward-looking periods in particular.
Arithmetic returns, again, are best for forward looking data and risk estimations.
Before we go, let me share a quick tip for calculating total geometric return. It is something I use all the time and is worth remembering.
If you divide the ending value of a portfolio, a stock holding, or other asset value, by the beginning value and subtract 1, it will give you the total geometric return. You can do this even though you don't have each individual sub-period return.
So for example, the value $1,076.90 divided by $1,000.00, minus 1 gives you 0.0769, or 7.69%. This is certain to come up on test day and in many contexts in your lifetime. It helps to answer the always important question, "What was the rate of return on my investment?"
The calculation of return is an essential building block in Finance, so knowing the distinction between arithmetic and geometric returns is critical. If you can recite the differences from memory, you will certainly differentiate yourself.
And if you need a periodic reminder, as we all do, this tutorial will sit here waiting for us to visit.
By way of summary, we've now run through two methods for calculating return and discussed when each is applicable. We also introduced a third method which is used to continuously compound returns for shorter time frames.
We also talked about totals and averages, plus different timeframes. So if you hear someone talking about return, in your mind, ask: "are we talking about geometric or arithmetic returns?", "historical, expected or forecast?", "total or average?". If you don't know, then ask. Just knowing what to ask will often set you apart.
From here forward, for those sticking around to learn faster in Quant 101, it will be important to understand these concepts as I will ask you to perform calculations using one method or the other, depending on the context.
In the next episode we will get back to the Returns data tab we created in the last tutorial and see how those returns were generated. There I'll introduce corporate actions and we will see why those big data providers like Bloomberg and Factset, have such a big and loyal following in the Institutional investment space.
In the meantime, think about which method we will use arithmetic or geometric?
Please join us any time and have a nice day.
To learn faster make sure you are watching the videos because unlike our more code-based tutorials, it will help to follow along in Excel.
Never miss another helpful tutorial released on our YouTube Channel. Subscribe now.
/ factorpad.com / fin / quant-101 / return-calculations.html
A newly-updated free resource. Connect and refer a friend today.