How to Calculate Rates of Return in Excel
Are you ready to kick it up a notch?
In this article, we will calculate the average and geometric average return of a stock, using Excel.
Suppose you are interested in buying Apple stock. Your first task is to calculate the stock return of Apple in the last 2 years.
First, we are going to obtain stock prices.
For this, we will use Yahoo Finance. We already know a thing or two about it, right?
We begin by typing the stock ticker. Do you remember what it is? “AAPL”!
Afterward, we select historical data. We download the stock prices over the last 2 years from July 1st, 2018 to July 1st, 2020.
Then, we need to set the data frequency.
There are 3 options to choose from – daily, weekly, or monthly.
Basically, the choice of data frequency depends on the return you want to calculate.
When you estimate daily returns, you go for daily frequency. Weekly returns require weekly frequency. And so on.
Let’s say we are interested in calculating monthly returns. This gives us 24 data points.
We press ‘apply’ and then ‘download’, in order to retrieve the needed numbers.
Time to check out what we’ve got…
We open the downloaded file and come across the date and a whole bunch of information about the stock price, such as the opening, as well as the highest and lowest value during the period we’ve specified earlier.
Which are the figures our calculations require, though?
Well, we want to use either the close or the adjusted close price. The former corrects for splits, whereas the adjusted one does so for both dividends AND splits.
Hence, we will use the adjusted close price. For conciseness, we get rid of all the information we won’t need and format the table in a more presentable way, based on the guidelines provided previously.
To save you some time, I have already done that.
Our next task is to calculate the holding period return for the stock.
What’s the formula we should be working with?
HPR = frac{Ending~value-Beginning~value}{Beginning~value}It is the ending value of an investment minus its beginning value, divided by the beginning value.
In our example, we have $55.29 minus $46.22 divided by $46.22 which gives us approximately 19.6%.
Finally, we go ahead and drag this formula all the way down.
Don’t forget to convert the values to percentages. Actually, you can get away with a simple shortcut.
Magicians never reveal their secrets, but we will willingly tell you ours: Excel is all about shortcuts! Be smart and use them as much as possible! This can save you a lot of time that you will eventually need for analyzing your data.
Here is one shortcut we can opt for, at this stage. Select the range of values, and then press and hold control plus shift, plus 5.
The last piece of the puzzle is to estimate the simple and geometric mean.
To obtain the former, we use the average function of Excel. We select the first argument and, after that, drag the range down to the last term.
Alternatively, we can use another useful shortcut. Press and hold Control plus shift plus the down arrow. This function marks the entire row of values below the cell you initially selected.
So, we estimate the mean return to be 3.49%.
Now, let’s calculate the geometric mean return.
For this purpose, we will use the geometric function. Basically, it gives us the geometric mean of an array or a range of positive data.
We type “GEOMEAN” and we pick the data range we will use. Don’t forget to add 1 to the expression before closing the parentheses.
The last step is to subtract 1 from the total.
Excel interprets the expression in the following way: Add one to each of the returns and then take the geometric mean.
For those of you using earlier versions of Excel (2016 or older), you need to press Control, Shift and Enter after you key in the formula. This command makes Excel convert the expression to an array formula. In other words, it performs multiple calculations on one or more items in an array. If you have done that right, you will see braces that are added to the formula.
The geometric mean comes to 2.99%.
In our next article, we will learn how to measure the standard deviation of the stock’s returns.
Keep calm and invest on!
