How do you calculate your investing returns? And how does that number compare to the professionals or to the S&P 500 or your investment advisor?
The answer might seem simple: (Ending of year value) / (Beginning of year value) – 1 = Return for the year, right?
Not quite. At least not if you added any cash during the year or took some cash out. Those actions have to be corrected for. After all, just like you don’t want to be penalized for removing cash (which, after all, lowers the return number), neither should you benefit from adding cash (which boosts the final number).
So how do you correct for that? How do you drill down and get the number that actually matters, the performance of your investing decisions?
Well, it’s easier than you think.
Quick aside: You might be more familiar with (Ending – Beginning) / Beginning = Return. What I’m using is identical to that, but requires fewer keystrokes. Like so:
(Ending – Beginning) / Beginning = (Ending / Beginning) – (Beginning / Beginning) = Ending / Beginning – 1 = Return
Holding Period Return
First, start off by measuring the return between any two cash flow events. This is called a “holding period return” or HPR for short. For this, you need two things.
- The value of the portfolio just before the cash flow.
- The amount of the cash flow.
Let me illustrate. Suppose you have the following set of portfolio values and cash flows:
| Date | Ending Value | Cash Flow |
| 12/31/2015 | $20,300 | $0 |
| 2/27/2016 | $21,773 | $500 |
| 5/16/2016 | $23,937 | $500 |
| 8/12/2016 | $22,823 | ($250) |
| 10/15/2016 | $24,518 | $500 |
| 12/31/2016 | $25,992 | $0 |
Note, from the point of view of the portfolio, a positive number is a cash flow in. That is, you deposited the money into the portfolio. Conversely, a negative number is a cash flow out, aka a withdrawal.
To simplify matters just a bit, we’re also going to assume that the cash flow happens at the end of the day on which you record the value of the portfolio. (This is, in fact, how mutual funds act. All the cash flows in and out happen at the end of the day.)
What you need to do is calculate the HPRs for each of those periods. This is simply (Ending Value) / (Previous Ending Value) – 1. The first one, from 12/31/2015 through 2/27/2016 is pretty easy:
HPR = $21,773 / $20,300 – 1 = 7.26%
Correcting for Cash Flows
But, what do we do with the second one from 2/27/2016 through 5/15/2016? How do we handle that $500 deposit? Easy. We add it to the ending value on 2/27/2016 which is $21,773 and use the new amount of $22,273 as the Previous Ending Value for the period:
HPR = $23,937 / ($21,773 + $500) – 1 = 7.47%
If we didn’t do this, we would get this, instead:
HPR = $23,937 / $21,773 – 1 = 9.94%
That would have been wrong because it would have counted the $500 deposit as if it were the result of investing when, in fact, it was simply a deposit.
From there, it’s pretty easy to do the rest. Here’s the result, with a new column added showing the Value After Cash Flow, which is used as the denominator for the next period’s HPR calculation.
To reiterate, each HPR is found as (Ending Value) / (Previous Value After Cash Flow) – 1.
| Date | Ending Value | Cash Flow | Value After Cash Flow | HPR |
| 12/31/2015 | $20,300 | $0 | $20,300 | n/a |
| 2/27/2016 | $21,773 | $500 | $22,273 | 7.26% |
| 5/16/2016 | $23,937 | $500 | $24,437 | 7.47% |
| 8/12/2016 | $22,823 | ($250) | $22,573 | (6.60%) |
| 10/15/2016 | $24,518 | $500 | $25,018 | 8.62% |
| 12/31/2016 | $25,992 | $0 | $25,992 | 3.89% |
Getting to the Final Number
Now what? We’ve got a list of HPRs, but how do we convert that to a return for the whole period?
Simple. We chain those values together. To do this, add 1 to each, multiply them all together, and then subtract 1 at the end.
(1 + 7.26%) * (1 + 7.47%) * (1 + (6.60%)) * (1 + 8.62%) * (1 + 3.89%) – 1 =
1.0726 * 1.0747 * 0.9340 * 1.0862 * 1.0389 – 1 = 21.49%
That is the return the portfolio earned over the course of that year solely from the investing decisions.
That return is called the Time-Weighted Return, or TWR. This is the return reported by mutual funds, for the S&P 500 index, and by all professional investors.
Note how this differs from the simple calculation of $25,992 / $20,300 – 1 = 28.04%. That’s because TWR corrects for the three $500 deposits and the $250 withdrawal.
Three More Points
First, a couple of rules must be followed in order for this to work correctly.
- All the periods must be independent of each other. That is, no overlapping.
- The total time period cannot contain any gaps. The whole period must be represented.
Second, the only cash flows that count are actual deposits from the outside or withdrawals to the outside. Whatever happens inside the portfolio (buy or sell stock for gains or losses, receive dividends, receive interest, pay fees, and so on) stays within the portfolio. Nothing inside counts as a cash flow.
Third, the result is the total return for the portfolio across whatever time period is measured. If you want to measure the annualized rate (if the portfolio’s been running longer than a year), you convert the TWR to a Compounded Annual Growth Rate (CAGR). Here’s how:
(1 + TWR) ^ (1 / No. of years) – 1
For the above, it’s super easy.
(1 + 21.49%) ^ (1 / 1) – 1 = 21.49%
Heh, well, we should have expected that. It was over only a single year.
However, what if the final date was 12/31/2017 instead, leaving everything else the same?
For one thing, the last HPR, running from 10/15/2016 through 12/31/2017 remains 3.89%. And the overall TWR remains at 21.49%. But CAGR, the annualized return, changes:
(1 + 21.49%) ^ (1 / 2) – 1 = 10.22 %
That’s the annualized return the portfolio earned over two years.
Use a Spreadsheet
That’s it. That’s all you need to know. But if you want to set this up in a spreadsheet to make it easier, here’s how.
First let me reproduce the whole table, with column and row numbers added.
| Column / Row | A | B | C | D | E | F |
| 1 | Date | Ending Value | Cash Flow | Value After Cash Flow | HPR | |
| 2 | 12/31/2015 | $20,300 | $0 | $20,300 | ||
| 3 | 2/27/16 | $21,773 | $500 | $22,273 | 7.26% | 107.26% |
| 4 | 5/16/2016 | $23,937 | $500 | $24,437 | 7.47% | 107.47% |
| 5 | 8/12/2016 | $22,823 | $(250) | $22,573 | -6.60% | 93.40% |
| 6 | 10/15/2016 | $24,518 | $500 | $25,018 | 8.62% | 108.62% |
| 7 | 12/31/2016 | $25,992 | $0 | $25,992 | 3.89% | 103.89% |
| 8 | ||||||
| 9 | TWR: | 21.49% |
Now, here’s what’s happening.
- The numbers in Column D are the sum of the corresponding numbers in Columns B and C. For example, the formula in D3 is “=B3 + C3” and kicks out $22,273.
- HPR is calculated as the number in B divided by the number in D from the previous row. For example, the formula in E3 is “=B3 / D2 – 1”.
- Column F is just adding 1 to the values in Column E. That makes the next step real simple.
- F9 is the chaining equation from above, and uses this formula: “=PRODUCT(F3.F7) – 1”. The Product function multiplies all those 1 + HPR values together, and then we subtract one at the end.
And that’s it!
A Final Wrinkle
Oh, one last thing. What do you do if you don’t have the portfolio value just before each cash flow? Suppose all you have is the value at the end of the month, but you make deposits whenever? What then?
Don’t worry, there’s a way to deal with it. You use a weighted average during the month for the cash flow in the Cash Flow column.
For instance, say you deposited $300 on May 10 and $200 on May 23, instead of $500 on April 30, and you only know the portfolio’s values on April 30 and May 31. Here’s how to calculate the weighted average cash flow:
($300 * 20 / 30) + ($200 * 7 / 30) = $246.67
The 20 and the 7 are the number of days each deposit had been in the portfolio during May, and the 30 is the number of days in a month. (Yeah, I know that May has 31 days, and you can use that if you want, but using a simplified 30-day month makes things simpler. Your choice.)
The weighted average is then added to the end of April value, as if you had deposited that amount on April 30, instead of the full $500. So:
May HPR = (May ending value) / (April’s ending value + weighted average of May’s deposits) – 1
Don’t worry, it’s legitimate. It’s called the Modified Dietz method, if you want to look it up and geek out.
Final Thoughts
Now that you know how to calculate TWR (and can annualize it), you can accurately compare your results to the S&P 500’s or to those of Peter Lynch or Warren Buffett or your annoying, boastful next door neighbor (who’s really a good guy despite his tendency to show off; besides, he lets you borrow things).
And with that, I’ll wish you Happy Returns!
– Answer provided by Fool senior analyst, Jim Mueller, CFA.