Calculate Stock Portfolio Returns and Turnover in Excel

Learn how to calculate and interpret portfolio returns

Beginner

Video

Videos are available at one organized Quant 101 Playlist on YouTube.

Calculate stock portfolio returns and turnover in Excel (20:58)

Video Script

Welcome. Today's goal is to calculate portfolio return for monthly periods, then find an average and cover topics related to portfolio drift, rebalancing and portfolio turnover.

I'm Paul, and I'll admit, sometimes it is easy to gloss over details associated with calculating an accurate portfolio return figure. Then someone asks for details and it is easy to stumble.

So here we will walk through what goes on behind the scenes in portfolio accounting software programs so we are prepared to answer the tough questions and become better portfolio managers.

This financial modeling tutorial sits in Chapter 3 of Quant 101 where we are working towards graphing portfolio return and risk on a scatter plot in Excel. You don't need to participate in the series to gain value from this exercise and if you want free sample data head back to the System Setup tutorial to get that.

Our tutorials come in two flavors, first, a webpage with the video transcript with the tricky Excel cell formulas and second a YouTube video Playlist for the visuals.

Okay, let's go over what we will see here today.

Outline

First, we complete Exercise 1 calculating monthly portfolio returns over a 6-month period for 2 stocks.

Second, we discuss common measurement periods for linking returns used in practice at portfolio management firms.

Third, we compute a historical average return, required for a scatter plot, while thinking about different timeframes.

Fourth, we analyze rebalancing topics and calculate portfolio turnover.

And in our next episode we compute portfolio risk.

Step 1 - Calculate Monthly Portfolio Return

Let's dive in to the calculation of portfolio return by building out the table in Exercise 1.

A portfolio is an account that typically includes more than one stock. In fact a portfolio can be a group of assets of any type, meaning a group of bonds, mutual funds, real estate or cash for that matter.

Here, and for the rest of the chapter, we focus on the simpleist example of a portfolio, one with just two stocks, Microsoft and eBay.

Portfolio return is different from stock return in that you have portfolio weights to contend with meaning the percentage of the portfolio allocated to each stock at each time period.

a. Stock returns

To start out, I should mention that in an earlier tutorial called Calculate Monthly Returns we covered individual stock returns incorporating stock price changes, dividends and stock splits.

So we already have these stock returns calculated for us and they sit on the Returns tab. Let's start by pulling these forward.

The easiest way is to grab our first cell and copy that over, because like in the previous tutorial we only need a range of 21 cells that sit in the top left sections of our returns data set.

So in cell E9 use the formula =Returns!B6 which grabs our column label Date.

Copy that over two columns and down 6 rows and you have the required 21 cells for our study here. Excel automatically updates each cell reference for you.

	E	F	G
8		Stock Returns
9	Date	MSFT	EBAY
10	4/30/03	5.62%	8.91%
11	5/30/03	-3.75%	9.41%
12	6/30/03	4.19%	2.31%
13	7/31/03	3.00%	3.17%
14	8/29/03	0.42%	3.28%
15	9/30/03	4.83%	-3.19%

I formatted this data in percent in Excel because it is easier to visualize that way. You may need to format the date as well using the right click in Excel and selecting the date format you would like to see.

The Returns data tab by default is in decimal format and we will introduce the other columns, meaning the two other stocks, an index and T-bills later, as this is the only data set we need for our whole series of 30 tutorials. It will be much easier to follow along if you put the data in the range B6:H66. So if you set up the same structure, then your formulas will match mine exactly.

b. Portfolio weights

Next, portfolio weights represent the percent of the portfolio allocated to each stock. And here in columns H and I, we see 50% allocated to each, Microsoft and eBay.

I will touch more on how weights are determined in the real world in a moment, but for now to simplify we will assume that weights are 50% for each period. So input 0.50 in cell H10 and copy that over one column and down 5 rows.

	E	H	I
8		Portfolio Weights
9	Date	MSFT	EBAY
10	4/30/03	0.50	0.50
11	5/30/03	0.50	0.50
12	6/30/03	0.50	0.50
13	7/31/03	0.50	0.50
14	8/29/03	0.50	0.50
15	9/30/03	0.50	0.50

Now that you have the two inputs, let's move on to the formula.

c. Portfolio return calculation

Portfolio return is the return times the weight of each stock, then we sum or add them together. And of course if this were a 100-stock portfolio we'd have to do the calculation 100 times.

So for each period, meaning each month or row, portfolio return is the sum of the return times the weight allocated to each stock.

	E	J	K	L
8		Portfolio Return Calculation
9	Date	MSFT	EBAY	Portfolio
10	4/30/03	2.81%	4.45%	7.26%
11	5/30/03	-1.88%	4.70%	2.83%
12	6/30/03	2.09%	1.16%	3.25%
13	7/31/03	1.50%	1.59%	3.09%
14	8/29/03	0.21%	1.64%	1.85%
15	9/30/03	2.41%	-1.60%	0.82%

Think of it this way, 50% of the portfolio (column H) increased by 5.62% (column F), producing a 2.81% return (column J), and the other 50% (column I) increased by 8.91% (column G), producing a 4.45% return (column K).

Add those two products and you get 7.26% in cell L10. So the portfolio increased by 7.26% in April.

For those following along in your own spreadsheet, now is a good time to complete the rest of the table.

A quick note, we could have used the Excel functions for =SUM() and =PRODUCT() but since we're just using two stocks I didn't think it was necessary. Again, if you had hundreds of stocks you would certainly want to use Excel functions and in later tutorials I will show you how to do this using array functions, which is similar to how it is done in a programming language like Python.

Also, if you take a moment to think about the calculation, it's one you've seen thousands of times. It is a simple weighted average, just like you would use to compute grades in a course with multiple different assignments given different weights as part of a final grade.

We do these calculations often in our every day lives and upon reflection, it's really just a weighted average calculation.

Step 2 - Periodic Returns

Now that we know how to calculate portfolio returns, let's back up a bit and talk about how this works in practice.

a. Measurement periods and timeframe

So our measurement periods here are monthly, but in real life, as the modeler, you can select any measurement period you like. Returns can be for a day, week, month, quarter or year. Take your pick.

For a long-term study like ours using a monthly timeframe is common, but if you were managing real money and were equiped with portfolio accounting and portfolio management software, then you would likely be working with daily data.

In a high-frequency environment or trading desk where bets are measured intra-day then the measurement periods may be minutes.

Another consideration is the length of the time period for analysis. For this easy example we are only concerned with 6 months, but you really need samples of greater than 30 periods, 60 is even better.

The main consideration is how you are able to account for all of this. On our Returns data tab we have 60 data points for a 5-year history, which is good enough for us to learn from, but in practice you will lean heavily on portfolio software.

b. Number of holdings

A third consideration is the number of stocks in the analysis. This too increases the requirement for systems. So the selection of measurements periods and the length of time, multiplied together give you an idea of the size of your data set.

Data structured like this is called time-series data and here we have 7 columns and 61 rows, so 427 data points. Now imagine if were analyzing a portfolio with a benchmark of 2,000 small cap stocks, with daily data, or about 250 measurement periods and you can see how we could easily reach a 500,000 record data set. This is before you start collecting other data like company ratios and factor returns. This is one reason why Institutional investors need systems that scale beyond what we can do in a spreadsheet like Excel.

Step 3 - Compute Average Portfolio Return

Okay, let's now compute an average return for this portfolio over this time period.

a. Historical period

In financial modeling it helps to keep timeframes straight and here we use the terms historical, expected and forecast, as introduced in Return Calculations.

We use historical to refer to the past. So you can think of a whole slice of data from the past, like what sits on our Returns data tab. From that we can derive return measures like averages, and risk measures like standard deviations.

The expected timeframe refers to another whole series of data that is calculated based on the historical data. Here it may be to run a regression to see what is built into market expectations at the present time.

Third is a timeframe we call forecast and this is the realm of active portfolio managers and risk model providers who attempt to provide better forecasts of the future with the goal of outperforming their intended benchmarks.

So the takeaway here is that when we calculate average portfolio return we are using a historical period so we call it historical average portfolio return, so we can keep it straight.

b. Arithmetic average return

Also in the Return Calculations tutorial we covered three methods for calculating return, focusing on when to use arithmetic or geometric methods.

For our objective here we will use the arithmetic method because we are interested in average returns for a historical period, and using it for a risk study. Each month is independent and we aren't interested in a portfolio's total return with compounding, but instead interested in the stocks themselves and their characteristics, like averages.

Let's move on to compute our average arithmetic return by adding the returns and dividing by the number of observations. We have portfolio returns for six periods from Exercise 1 in the range from L10:L15. While we're at it, let's calculate the average returns for both stocks from the range F10:G15.

So, to use the arithmetic type, we ignore compounding and take the basic average. There are two ways to do this and I'll go through the long-way once so you've seen it, but the second approach will be faster in the future, so I'll show you that too, using the Excel function =AVERAGE().

	E	F	G	H
19		MSFT	EBAY	Portfolio
20	SUM()	14.29%	23.89%	19.09%
21	COUNT()	6	6	6
22	Avg. Return	2.38%	3.98%	3.18%
24	AVERAGE()	2.38%	3.98%	3.18%

For the first stock Microsoft, in cell F20 we use the =SUM() function to sum the returns from F10:F15. We can copy that formula over one column for eBay. For the portfolio, in cell H20 we sum the returns from L10:L15.

Next, let's use the =COUNT() function to count the observations using the same ranges.

Then in row 22 we divide the returns by the number of observations, so in cell F22 it is =F20/F21 and we can copy that over two columns to give us our answer, averages for stocks and the 50/50 portfolio.

The second way to go about this would be to use the Excel =AVERAGE() function. For cell F24 it would be =AVERAGE(F10:F15) for Microsoft, and for the portfoio we would grab the range from L10:L15. Again, all straightforward stuff here, and the second way is how I'd suggest doing it from here forward.

With that we are through with Exercise 2.

c. Risk and return plot

Now that we have our average return, let's revisit what we're using it for back on the Cover tab (see video). By the end of the Chapter we will generate a chart showing the risk and reward tradeoff from owning these two stocks by creating 11 portfolios, altering weights and plotting the resulting average portfolio returns on the y-axis and portfolio risk on the x-axis.

Technically this is called a risk and return plot. It is derived using the X Y Scatter Chart in Excel, and is more commonly known as a scatter plot.

Here the y-axis is the average return which we just calculated. In the next video we will work on the x-axis value portfolio risk, or more technically, portfolio standard deviation.

The end points refer to portfolios made up of 100% in each stock, so the point on the bottom refers to an all Microsoft portfolio and the one on the top refers to an all eBay portfolio. These average returns we also just calculated.

So eBay had a higher average total risk and a higher average return. So, if you had a portfolio that invested 100% in Ebay, then your return and risk would match these numbers for this historical period, right? You could say the same for a portfolio made up of Microsoft exclusively, with lower return and lower risk.

Next, and this leads us to the whole point here. We just walked through the average portfolio return for a 50/50 portfolio and the return on the y-axis was at the midpoint between the two returns, because it was a simple weighted average calculation. How then is it that the risk is much lower than the weighted average risk of the two stocks?

Well, that's the magic of portfolio diversification and is the topic of the next tutorial. There we will learn how to quantify diversification, as it is a main takeaway from this chapter.

When visualizing a risk and return plot, it should be clear that an investor would be most interested a high risk-adjusted return. So the further the portfolio lies to the top and left, the more desirable. more desirable, right? Higher return and lower risk. So the opposite position, or worst is in the bottom right, with low return and high risk. That's just simple economics. We as humans always want more for less.

So with that let's press on because we need to talk about some practical topics before heading off to portfolio risk.

Step 4 - Analyze Rebalancing

For Step 4 we need to talk about the assumption made earlier about how we adjusted the portfolio back to 50/50 weights for each period.

Well, I glossed over the topic and details, so let's address it here.

a. Portfolio drift

In the real world, portfolios are rarely rebalanced back to weights mechanically like this, snapping weights back to 50/50. Instead they are allowed to drift, and the term for this is portfolio drift. Indexes drift and active portfolios drift, meaning there is no trading often from one period to the next, especially if we were using daily periods.

So every day as a stock's price changes, its value changes and its weight in the portfolio changes. This isn't a huge issue and portfolio managers have expensive software that keeps track of this and allows them to monitor these changes and the resulting impact on the risk of a portfolio.

b. Portfolio rebalancing

Here on the other hand, in order to keep the math clean and easy, we rebalanced the portfolio back to 50/50 once a month using a process known as portfolio rebalancing, often called trading. So any time you adjust weights from current weights, you trade, selling one stock and buying another. Sometimes you introduce new stocks and other times you just readjust the weights as we have done here in our ultra-simple 2-stock example.

I want to add another technical note on rebalancing. So if you look at the table for Exercise 1, you can see we are treating each month independently, right? Meaning, we are not considering the impact on the portfolio after eBay outperformed Microsoft, in April, carrying over that to May. So we are ignoring the impact of compounding and geometric returns in our example here.

By doing this we, in effect, rebalanced the portfolio back to equal weight at the end of the month. This is one of our key assumptions that simplifies the math. Again, if we didn't rebalance the portfolio at the end of each month, then the weight of eBay would be greater than Microsoft after April and heading into May, right?

	E	F	G	H	I	J	K
28		Stock Returns		Portfolio Weights
29	Date	MSFT	EBAY	MSFT	EBAY	Portfolio	Turnover
30	3/31/03			0.50	0.50	100.00%
31	4/30/03	5.62%	8.91%	0.5281	0.5445	107.26%	0.0082
32	4/30/03			0.50	0.50	100%

Let's walk through it. Technically, at the end of March, or beginning of April, the portfolio weights were 50/50.

Then over the course of April, the 50% of the portfolio invested in Microsoft increased by 5.62% and the other 50% in eBay increased by 8.91%. So logically, at the end of the month, the portfolio had more invested in eBay, because it had increased more, right?

This table, in cells H31 and I31 measures just that. There are other ways to do this, like translate this into a currency. Here let's simplify it by imagining we had a 50 cent piece invested in each stock and allow it to grow by multiplying it by one plus the return for the month, much like we have done with geometric returns in previous tutorials.

So cell H31 looks like =H30*(1+F31) and cell I31 is =I30*(1+G31). This illustrates the point of portfolio drift.

Recall that our assumption requires that we snap the portfolio weights back to 50/50 and to do so requires portfolio rebalancing through stock trading. To go back to 50/50 we need sell some eBay and buy some Microsoft.

c. Portfolio turnover

The term portfolio turnover is a measure for this trading activity.

Bear with me for a moment. If you add up the changes to the portfolio using that 50 cent analogy, we would have 52.81 cents in cell H31 and 54.45 cents in cell I31 and a new total of 107.26 cents with the formula in cell J31 that is =SUM(H31:I31). This you can think of as the new portfolio value.

The measure portfolio turnover is a one-sided measure, meaning it takes into consideration only the sell side activity and is commonly quoted in percent. It is the ratio of the value of portfolio sells divided by the total value of the portfolio.

Next, we need to compute how much eBay we would need to sell and then buy Microsoft to get back to 50/50. To do that the formula in cell K31 looks like =I31-(J31*I32).

That's tricky so let's spell it out, start with the inside of the parentheses first. We want the ending portfolio weight of 50%, or 0.50, after the rebalance, times the ending portfolio value of 1.0726, subtracted from the weight going into the rebalance. Remember, before rebalancing eBay's weight is higher than half the portfolio.

Here's another way to think of it. On a pre-adjusted basis, half of 1.0726 is 0.5363. The difference between eBay's weight at 0.5445 and 0.5363 is 0.0082. That is the portfolio turnover for April.

(The new weight of MSFT is also 50% post rebalance because during the rebalance you would buy 0.0082 of MSFT, making it 0.5281 + 0.0082 = 0.5363, or 50% of the portfolio value of 1.0726).

Of course I'm leaving out details about how you accumulate this turnover into an annual figure, but I mainly wanted you to see how turnover is calculated.

This should help illustrate why we made the rebalancing assumption from the beginning, it prevents us from having portfolio drift to account for and from mechanically going through trading like this.

Let's come full circle and add real world examples. Imaging adding up all of the turnover for a year. This is called annual portfolio turnover and is an important measure used to evaluate portfolio managers of pooled investments like mutual funds and pension funds. This annual figure is often associated with higher transactions costs, realized gains for taxes and trading commissions. It is reported in annual shareholders reports and picked up by rating companies like Morningstar.

To put a scale to it, on an annual basis index funds typically incur less than 20% annually, while more fundamental, technical and quantitative strategies may incur up to 250% turnover on an annual basis, but around 100% is more common, at least for mutual funds.

And there you have it.

Summary

By way of summary, we have walked through calculating portfolio return while keeping in mind how large data sets can get as you alter measurement periods, timeframes and the number of holdings. Then we looked at averages for the historical timeframe. We also went through portfolio drift, rebalancing and how portfolio turnover measures are used.

Keep the two-dimensional vision of return and risk in your mind, and after we nail down portfolio risk we will have what we need to build our 11 portfolios that sit on the parabola.

Step 5 - Next: Portfolio Risk

In the next episode we stick walk through the formula for portfolio variance and standard deviation, which will help us understand and build a covariance matrix, useful for computing risk beyond two stocks and to hundreds or thousands of stocks.

We are early in our journey and the fun is just beginning. Feel free to join us any time and have a nice day.