Calculating personal returns
Some investors like to calculate their personal returns. This page provides a spreadsheet for Bogleheads to calculate their personal returns in a uniform manner.
 | Past returns are not indicative of future returns. Interpretation of results is the responsibility of the reader. If there are any questions, please don't hesitate to ask in the forum for guidance. |
The spreadsheet calculates two types of personal returns: an investor return which reports on how well the invested money has been growing so far, and a portfolio return which can be used to compare the past performance of the portfolio over a specific time period to widely available index and mutual fund returns or to the performance of another portfolio.
The spreadsheet also provides a growth of $10,000 chart for the portfolio.
Download location
The latest personal returns spreadsheet (version 2.1) comes in three versions:
Google Docs (Sheets)
This version can be used online.
Read the following instructions before clicking on the link!
- Click on the link below.
- Sign into your Google account (if not already signed in).
- Make a copy of the file as follows: File -> Make a copy...
- The copy is yours to modify.
- Here is the link: BogleheadsReturns spreadsheet (Sheets, online).
Microsoft Office (Excel)
- Click on: BogleheadsReturns spreadsheet (Excel, .xlsx).
LibreOffice (Calc)
- Click on: BogleheadsReturns spreadsheet (Calc, .ods).
Screenshots
Using the spreadsheet
The spreadsheet can calculate returns across multiple accounts. Each month, the investor must to provide three pieces of data per account:
- Total contributions. (Money added by the investor and his employer to the portfolio, excludes any internal cash flow such as dividend payments).
- Total withdrawals. (Money removed by the investor from the portfolio, excludes any internal cash flow such as fees).
- Account balance at the close of the last day of the month.
The spreadsheet computes:
- The investor return (money-weighted return, internal rate of return) for the entire investment period.
- Portfolio returns (time-weighted returns, comparable returns) for 1 month, 3 months, 6 months, year-to-date (YTD), 1 year, 3 years, 5 years, and 10 years periods.
- A growth of $10,000 chart.
Two types of returns
Investor return
The investor return is also called money-weighted return or internal rate of return. It represents the annual rate of return of the invested money while taking into account the timing of various contributions to and withdrawals from the portfolio.
A simple way to understand investor return is this: it is the interest rate on a savings account, such that the same sequence (and timing) of contributions and withdrawals would end up with the same final balance.
Example
Here is an example:
- January 1st, 2012: Create portfolio with an initial deposit of $5,000.
- January 1st, 2013: Contribute $1,000.
- January 1st, 2014: Withdraw $2,000.
- Portfolio balance (after withdrawal) on January 1st, 2014: $5,150.
What should have been the interest rate on a savings account so that the final balance would be $5,150? The answer is 10%. We can verify this:
- On January 1st, 2012, the savings account balance would have been $5,000.
- On January 1st, 2013, the balance would have been $6,500 = $5,000 + 10% interest + $1,000.
- On January 1st, 2014, the balance would have been $5,150 = $6,500 + 10% interest - $2,000.
So, we say that the investor return, from January 1st, 2012 to January 1st, 2014 was 10%.
Portfolio return
The portfolio return is also called time-weighted return or comparable return. It represents the annual rate of return of a single lump sum invested in the portfolio during a selected time period.
In other words, the portfolio return tries to remove the impact of contributions and withdrawals during the time period, and to only report the return of the underlying portfolio.
Example
We will use the same example that we used to illustrate investor returns, but we need some additional information (in bold):
- January 1st, 2012: Create portfolio with an initial deposit of $5,000.
- January 1st, 2013: Contribute $1,000.
- Portfolio balance (after contribution) on January 1st, 2013: $8,937.50.
- January 1st, 2014: Withdraw $2,000.
- Portfolio balance (after withdrawal) on January 1st, 2014: $5,150.
To calculate the portfolio return, we look at the return of each sub-period (e.g. each year):
- In 2012, the portfolio grew from $5,000 to $7,937.50, before the $1,000 contribution. This represents a growth of 58.75%.
- In 2013, the portfolio shrank from $8,937.50 to $7,150, before the $2,000 withdrawal. This represents a loss of 20%.
Finally, we compute the annual return:
So, we say that the portfolio return, from January 1st, 2012 to January 1st, 2014 was 12.69%.
Why are investor and portfolio returns different?
As you saw in the example used to illustrate investor and portfolio returns, both returns were different. What caused this difference?
To understand, one must look at contributions and withdrawals during the time period. In 2012, the initial $5000 portfolio gained 58.75%. In January 2013, the investor contributed an additional $1,000. Then, in 2013, the portfolio lost 20%.
The initial $5,000 was subject to both the gain and subsequent loss. The additional $1,000 did not get the 58.75% gain, but it was subject to the 20% loss. So, overall, investor money was more subject to the 20% loss than to the 58.75% gain. As a consequence, investor return was lower (10%) than portfolio return (12.69%).
Support
On-going discussion and support for the spreadsheet is in this forum thread: A Returns Spreadsheet for Bogleheads