A Returns Spreadsheet for Bogleheads

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Topic Author
longinvest
Posts: 4131
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Aug 04, 2019 10:32 am

rst113 wrote:
Sun Aug 04, 2019 9:54 am
How would I add more than 9 accounts? A couple are no longer active/rolled over but I presume deleting those will mess up the tracked returns. I can add a tab but I know I would have to do something to the display and/or calculations tabs. Thanks so much for this I diligently track all my accounts monthly using this and love the overall picture it gives.
Rst113, the current version (1.9) supports up to 20 accounts. Note that the sheet for an old closed account can be shared with a new account that was opened after the old account closing. On the "Portfolio" sheet, I would name the account "New XXX & Old YYY".

longinvest
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

rst113
Posts: 24
Joined: Fri Apr 30, 2010 10:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by rst113 » Sun Aug 04, 2019 10:46 am

Thanks!

herpfinance
Posts: 227
Joined: Tue Nov 18, 2014 4:52 pm
Location: Denmark

Re: A Returns Spreadsheet for Bogleheads

Post by herpfinance » Mon Aug 05, 2019 12:36 pm

@longinvest,

I've been meaning to get started using a proper returns tracking spreadsheet for a while, and yours fit the bill quite nicely.

Thanks for all of the work that you put into it!
"The intelligent investor is a realist who sells to optimists and buys from pessimists" - Benjamin Graham

TheEleven
Posts: 79
Joined: Thu Jul 04, 2019 11:04 pm

Re: A Returns Spreadsheet for Bogleheads

Post by TheEleven » Mon Nov 25, 2019 1:15 am

Just came across this spreadsheet - thanks longinvest! I know very little about spreadsheets, but I was able to zero out all the numbers in the original and put my latest balances in three different accounts on the last date available. So I can track from here on out - don't want to go in and backfill. I'm wondering if there is a way to delete all the rows going back to June 2013? Just right-clicking and selecting "delete row" definitely not the way to do it - had to do a quick Undo. Sorry if this is addressed somewhere in this thread already - too many posts to review.

Topic Author
longinvest
Posts: 4131
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Nov 25, 2019 7:50 am

TheEleven wrote:
Mon Nov 25, 2019 1:15 am
Just came across this spreadsheet - thanks longinvest! I know very little about spreadsheets, but I was able to zero out all the numbers in the original and put my latest balances in three different accounts on the last date available. So I can track from here on out - don't want to go in and backfill. I'm wondering if there is a way to delete all the rows going back to June 2013? Just right-clicking and selecting "delete row" definitely not the way to do it - had to do a quick Undo. Sorry if this is addressed somewhere in this thread already - too many posts to review.
TheEleven, I suggest to:
  1. Download a new fresh copy of the spreadsheet.
  2. Click on the "Portfolio" tab to select a starting year and month and give a name to each account that will be tracked.
  3. Delete example data in the Contribution, Withdrawal, and Balance columns of the "Account 1" sheet.
  4. Enter historical Contribution, Withdrawal, and Balance data into the "Account 1", "Account 2", ... sheets.
  5. Each month, fill the new row that automatically appears in the "Account 1", "Account 2", ... sheets.
Note that if an account was opened later than the selected start year and month, it's sufficient to set its initial Balance to zero to stop the spreadsheet from complaining about missing data for months preceding the opening of the account. The same trick works when an account is closed; as soon as its Balance drops to zero, the spreadsheet stops requiring new data every month.

Enjoy!
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
nydad
Posts: 450
Joined: Wed Dec 01, 2010 11:10 am

Re: A Returns Spreadsheet for Bogleheads

Post by nydad » Wed Dec 25, 2019 6:38 pm

Hi -

Thanks for putting this spreadsheet together. It is really wonderful.

I'm still trying to figure out how it all works, and I also wanted to see if I could submit a feature request -

Have you thought about adding performance calculations for 15/20/30/40 years? I lament the fact that Vanguard hasn't seen fit to provide this sort of performance data for holdings they manage, especially given that the Bogleheads/Vanguard philosophy is supposed to provide long-term returns (provided the market is growing during those periods) on the timescale of decades.

As of now, a look back 10 years (which is what Vanguard and this spreadsheet provides) misses out on the biggest market dip in recent history. I'd love to be able to see how did my portfolio do over the last 20 years!

So, I'm wondering if this is something you might consider adding to the next version of the spreadsheet? Thanks again, I've found it quite useful already!

cheers!

Topic Author
longinvest
Posts: 4131
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Dec 26, 2019 9:20 am

nydad wrote:
Wed Dec 25, 2019 6:38 pm
Have you thought about adding performance calculations for 15/20/30/40 years? I lament the fact that Vanguard hasn't seen fit to provide this sort of performance data for holdings they manage, especially given that the Bogleheads/Vanguard philosophy is supposed to provide long-term returns (provided the market is growing during those periods) on the timescale of decades.
Nydad, thanks for the suggestion. I'll add the four 15, 20, 30, and 50 years periods to the current eight periods of trailing returns.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Laurizas
Posts: 67
Joined: Mon Dec 31, 2018 4:44 am
Location: Lithuania

Re: A Returns Spreadsheet for Bogleheads

Post by Laurizas » Sun Dec 29, 2019 4:22 pm

longinvest wrote:
Thu Dec 26, 2019 9:20 am
Nydad, thanks for the suggestion. I'll add the four 15, 20, 30, and 50 years periods to the current eight periods of trailing returns.
50 years is way too much. I think 15,20,25,30 will suffice

User avatar
Eagle33
Posts: 394
Joined: Wed Aug 30, 2017 3:20 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Eagle33 » Sun Dec 29, 2019 8:42 pm

Laurizas wrote:
Sun Dec 29, 2019 4:22 pm
longinvest wrote:
Thu Dec 26, 2019 9:20 am
Nydad, thanks for the suggestion. I'll add the four 15, 20, 30, and 50 years periods to the current eight periods of trailing returns.
50 years is way too much. I think 15,20,25,30 will suffice
FIRE folks may disagree.

And why no 40?
Rocket science is not “rocket science” to a rocket scientist, just as personal finance is not “rocket science” to a Boglehead.

Topic Author
longinvest
Posts: 4131
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Jan 01, 2020 5:33 pm

I have uploaded version 2.0 of the returns spreadsheet.

As usual, you'll find the links to online and downloadable versions on the wiki:Calculating personal returns page.

If you wish to use the online version, please carefully follow the instructions to make a personal copy of the shared spreadsheet. You can then use your personal copy to calculate your returns.

Changes:
  • Add longer return intervals to better match the investment horizon of Bogleheads.
As usual, comments are welcome.

Enjoy!
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

michaelsieg
Posts: 583
Joined: Mon Jan 07, 2013 11:02 am

Re: A Returns Spreadsheet for Bogleheads

Post by michaelsieg » Sat Jan 11, 2020 11:33 am

Dear longinvest,
Thanks again for your spreadsheet, I update it monthly and it has really helped looking at the returns across all our accounts and thanks for refining it over time!
I am trying to include our direct real estate returns with your spreadsheet and I am not sure how to do it.
3 years ago, we started to participate in a few rental units, which are now about 20% of our portfolio. Since then, I have not used the cashflows for these rentals in my return calculations, and just assumed a constant value of the real estate (being conservative) – but in the long-term, I have to start tracking the real estate returns as well, just assuming the value is stable is probably not a good solution (It adds a 20% “deadweight” to my portfolio and artificially lowers the return).
So I am trying to use your spreadsheet to calculate our real estate return, but I am not sure how I should best do it.
I can download all the cashflows from the Rentec Direct webpage as an excel spreadsheet from our property manager, and it gives me the following montly cashflows:
rental income/utility payment income and all the expenses. The only other variables are the mortgage payments and the mortgage principal. I wish we could keep our accounts totally separate, but we have not been doing that, which means, if there is a positive cashflow in a month, I usually withdraw the money from the property account and either invest it (which I track with my investment return sheet) or use it to pay monthly bills/loan down payments. So my question is how do I best use your spreadsheet to calculate a monthly return?
I was thinking of taking the months with a negative cashflow (for instance last January, where we were -$1700 due to a renovation) and place this negative cashflow in the contribution column (since I had to inject the money) and if I have a month with a positive cashflow (for instance this December, $1450), I would add this to the withdrawal column, as I don’t leave this money in the property account.
I am also not sure how to enter the monthly mortgage payments, I probably should add them every month entirely in the monthly contribution column and correct for the principal increase in the balance column.
I was just curious if someone else has used this spreadsheet to track real estate returns – I don’t want to make it complicated but I think I should start to formally track our real estate returns.
Sorry that the post got too long and thanks again for creating this spreadsheet!
Michael

Topic Author
longinvest
Posts: 4131
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Jan 11, 2020 2:15 pm

michaelsieg wrote:
Sat Jan 11, 2020 11:33 am
I am trying to include our direct real estate returns with your spreadsheet and I am not sure how to do it.
...
I was just curious if someone else has used this spreadsheet to track real estate returns – I don’t want to make it complicated but I think I should start to formally track our real estate returns.
Michael, I don't have real estate investments other than whatever is included in my global balanced index One-Fund Portfolio.

If I did have a rental investment, I would try to keep things simple by separating the real estate investment and its expenses (except those related to the mortgage) in one sheet, and put the mortgage and its payments on another sheet.

In the real estate investment sheet, every month I would count all income (e.g. rent) as withdrawals and all expenses (maintenance costs, repair costs, advertising, administrative costs, taxes, utilities, etc.) as contributions. I would include the initial entire price of the rental, including any transaction-related expenses, as a contribution and the final after-expense proceeds of the sale as a withdrawal. Every month, I would try to estimate a current market value of the rental minus the costs of selling, and I would use this amount as portfolio balance.

In the mortgage sheet, I would count the initial mortgage amount as a withdrawal. Every month, I would count the mortgage payment as a contribution and use the remaining principal amount of the mortgage as a negative portfolio balance. I would count any additional borrowed money added to the mortgage as a withdrawal, and any additional mortgage payment as a contribution.

As I said, I don't have real estate investments. This is only how I think I would do it if I did.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

michaelsieg
Posts: 583
Joined: Mon Jan 07, 2013 11:02 am

Re: A Returns Spreadsheet for Bogleheads

Post by michaelsieg » Sun Jan 12, 2020 12:43 am

Thank you longinvest - I really appreciate it ! I will enter it this way - curious to see what the return will be.

JiggyWillis
Posts: 35
Joined: Sun Jan 14, 2018 1:17 am

Re: A Returns Spreadsheet for Bogleheads

Post by JiggyWillis » Sun Jan 12, 2020 7:34 pm

Thank you for this fantastic tool and the many years of hard work! I have been keeping monthly hand-written summaries of portfolio activity, balances and returns; takes me about 1/2 hour and I find it enjoyable. Your spreadsheet is a natural fit to receive the monthly data generated by this review.

I calculated our 2019 portfolio return at 17.73%. The spreadsheet calculated 17.7% so Spot ON!

It will no doubt prove to be valuable evidence the next time SWMBO says "we need to talk to a money guy." I can print the return page and be like PA-POW, look, same numbers as market returns for our asset allocation! You can just pay me the AUM fees, honey.

User avatar
lock.that.stock
Posts: 78
Joined: Sun Apr 28, 2019 2:01 pm

Re: A Returns Spreadsheet for Bogleheads

Post by lock.that.stock » Fri Jan 24, 2020 2:40 pm

longinvest - thank you for the fantastic spreadsheet. I can sure appreciate all the hard work you've put into it to build it in a way that's intuitive and works for most folks.

A suggestion - is there a way you can build the returns calculations for each account/tab separately (perhaps on the far right margin of each account/tab). It would be nice to look at the returns of the individual accounts (in addition to what you already have for total returns calculations for the entire account bundle) for folks that are employing different investment strategies in each account. Essentially - calculations of returns for each tab with a concise summary of performance for each account presented in the same way as what's on your returns tab (with the growth chart if possible).

Again, what you have put together is fantastic work. Thank you for all your contributions.

Topic Author
longinvest
Posts: 4131
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Fri Jan 24, 2020 6:17 pm

lock.that.stock wrote:
Fri Jan 24, 2020 2:40 pm
A suggestion - is there a way you can build the returns calculations for each account/tab separately (perhaps on the far right margin of each account/tab).
Thanks for the comments. Looking at the returns of individual accounts could lead to behavioral pitfalls. As this is a Bogleheads spreadsheet, it only provides returns for the overall portfolio across all accounts. The returns for separate portfolios can be calculated using separate spreadsheets.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
lock.that.stock
Posts: 78
Joined: Sun Apr 28, 2019 2:01 pm

Re: A Returns Spreadsheet for Bogleheads

Post by lock.that.stock » Fri Jan 24, 2020 6:53 pm

longinvest wrote:
Fri Jan 24, 2020 6:17 pm
lock.that.stock wrote:
Fri Jan 24, 2020 2:40 pm
A suggestion - is there a way you can build the returns calculations for each account/tab separately (perhaps on the far right margin of each account/tab).
Thanks for the comments. Looking at the returns of individual accounts could lead to behavioral pitfalls. As this is a Bogleheads spreadsheet, it only provides returns for the overall portfolio across all accounts. The returns for separate portfolios can be calculated using separate spreadsheets.
Thanks for the comment. However one can argue that not looking at the returns of individual portfolios/accounts can lead to underperforming accounts being concealed by performers, as there is no ability to identify or isolate poor performers. For instance, I didn’t realize that my employers pension plan was dragging down my overall returns for a long time because i was calculating all account returns as a lump sum. After I isolated this account to be the culprit I made some readjustments to improve.

Perhaps I’m seeking more detail than others using this sheet so I’ll build the calcs into each account tab.

curiousinvestor
Posts: 81
Joined: Fri Dec 21, 2007 11:36 pm

Re: A Returns Spreadsheet for Bogleheads

Post by curiousinvestor » Sat Jan 25, 2020 12:58 pm

Hi - Great spreadsheet!

I have been using Vanguard for about 17 years now Roth IRA. I have kept track of all my contributions on a spreadsheet. I have not taken withdrawals. How do I get the closing account balance for the last day of each month going back many years? Vanguard website info doesn't go back to 2003, I think.

In other words, I can do 1, 2, 4 in the Excel Account instructions tab below, but not 3. Vanguard account valuation report balance info is limited to the past 7 years, I think.

1. Add the contribution amount for each month (exclude internal cash flows such as dividend payments).
2. Add the withdrawal amount for each month (exclude internal cash flows such as account fees).
3. Add the closing account balance for the last day of each month.
4. Do not forget to include employer contributions, if any.

Any ideas how to get Vanguard's ending account balance data going back to 2003?

rossington
Posts: 468
Joined: Fri Jun 07, 2019 2:00 am
Location: Florida

Re: A Returns Spreadsheet for Bogleheads

Post by rossington » Sat Jan 25, 2020 2:27 pm

curiousinvestor wrote:
Sat Jan 25, 2020 12:58 pm
Hi - Great spreadsheet!

I have been using Vanguard for about 17 years now Roth IRA. I have kept track of all my contributions on a spreadsheet. I have not taken withdrawals. How do I get the closing account balance for the last day of each month going back many years? Vanguard website info doesn't go back to 2003, I think.

In other words, I can do 1, 2, 4 in the Excel Account instructions tab below, but not 3. Vanguard account valuation report balance info is limited to the past 7 years, I think.

1. Add the contribution amount for each month (exclude internal cash flows such as dividend payments).
2. Add the withdrawal amount for each month (exclude internal cash flows such as account fees).
3. Add the closing account balance for the last day of each month.
4. Do not forget to include employer contributions, if any.

Any ideas how to get Vanguard's ending account balance data going back to 2003?
I think if you go to Balances and Holdings>Balances by Date and select your date(s) that might work. But prior to 2011 it's only for Vanguard mutual funds and ETF's though.
"Success is going from failure to failure without loss of enthusiasm." Winston Churchill.

curiousinvestor
Posts: 81
Joined: Fri Dec 21, 2007 11:36 pm

Re: A Returns Spreadsheet for Bogleheads

Post by curiousinvestor » Sat Jan 25, 2020 2:35 pm

rossington wrote:
Sat Jan 25, 2020 2:27 pm
curiousinvestor wrote:
Sat Jan 25, 2020 12:58 pm
Hi - Great spreadsheet!

I have been using Vanguard for about 17 years now Roth IRA. I have kept track of all my contributions on a spreadsheet. I have not taken withdrawals. How do I get the closing account balance for the last day of each month going back many years? Vanguard website info doesn't go back to 2003, I think.

In other words, I can do 1, 2, 4 in the Excel Account instructions tab below, but not 3. Vanguard account valuation report balance info is limited to the past 7 years, I think.

1. Add the contribution amount for each month (exclude internal cash flows such as dividend payments).
2. Add the withdrawal amount for each month (exclude internal cash flows such as account fees).
3. Add the closing account balance for the last day of each month.
4. Do not forget to include employer contributions, if any.

Any ideas how to get Vanguard's ending account balance data going back to 2003?
I think if you go to Balances and Holdings>Balances by Date and select your date(s) that might work. But prior to 2011 it's only for Vanguard mutual funds and ETF's though.
Hi - Yes but I think a problem is that I think I remember I had a Vanguard IRA and Vanguard Brokerage IRA, so some of the history is lost, but now it's combined to only a Vanguard Brokerage IRA. So it doesn't have everything anymore.

In any case, I went ahead with only the past 7 years of data, so it goes back to 2013 instead of 2003 when I first opened it, which is still very useful.

rossington
Posts: 468
Joined: Fri Jun 07, 2019 2:00 am
Location: Florida

Re: A Returns Spreadsheet for Bogleheads

Post by rossington » Sat Jan 25, 2020 2:39 pm

curiousinvestor wrote:
Sat Jan 25, 2020 2:35 pm
rossington wrote:
Sat Jan 25, 2020 2:27 pm
curiousinvestor wrote:
Sat Jan 25, 2020 12:58 pm
Hi - Great spreadsheet!

I have been using Vanguard for about 17 years now Roth IRA. I have kept track of all my contributions on a spreadsheet. I have not taken withdrawals. How do I get the closing account balance for the last day of each month going back many years? Vanguard website info doesn't go back to 2003, I think.

In other words, I can do 1, 2, 4 in the Excel Account instructions tab below, but not 3. Vanguard account valuation report balance info is limited to the past 7 years, I think.

1. Add the contribution amount for each month (exclude internal cash flows such as dividend payments).
2. Add the withdrawal amount for each month (exclude internal cash flows such as account fees).
3. Add the closing account balance for the last day of each month.
4. Do not forget to include employer contributions, if any.

Any ideas how to get Vanguard's ending account balance data going back to 2003?
I think if you go to Balances and Holdings>Balances by Date and select your date(s) that might work. But prior to 2011 it's only for Vanguard mutual funds and ETF's though.
Hi - Yes but I think a problem is that I think I remember I had a Vanguard IRA and Vanguard Brokerage IRA, so some of the history is lost, but now it's combined to only a Vanguard Brokerage IRA. So it doesn't have everything anymore.

In any case, I went ahead with only the past 7 years of data, so it goes back to 2013 instead of 2003 when I first opened it, which is still very useful.
You could try calling and request a report they could mail to you. Worth a shot. I think they keep the data.
"Success is going from failure to failure without loss of enthusiasm." Winston Churchill.

User avatar
Eagle33
Posts: 394
Joined: Wed Aug 30, 2017 3:20 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Eagle33 » Sun Jan 26, 2020 1:51 am

If you have your quarterly statements back to 2003, then you can enter those values leaving 2 months between each quarterly entry. Then you can spread 1/3 the gain or loss between quarters equally in the missing months. Close enough for historical values older than 1 year.
Rocket science is not “rocket science” to a rocket scientist, just as personal finance is not “rocket science” to a Boglehead.

taxable_ted
Posts: 2
Joined: Tue Jun 05, 2012 12:54 pm

Re: A Returns Spreadsheet for Bogleheads

Post by taxable_ted » Wed Feb 12, 2020 11:49 pm

Hi longinvest, thanks so much for putting together the spreadsheet. Very helpful!

I have a question regarding best practices for dealing with capital gain taxes. If I sell investment at a profit, and then transfer money out of that account to pay taxes, should I consider that a withdrawal? In this case, wouldn't the returns show a mixed picture of before and after tax returns (vs. a clean pre-tax view if no sale transactions and tax payments are ever recorded, for example). What's the best way to deal with this issue?

Thanks!

Topic Author
longinvest
Posts: 4131
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Feb 13, 2020 7:41 pm

Congratulations for your first forum post!
taxable_ted wrote:
Wed Feb 12, 2020 11:49 pm
Hi longinvest, thanks so much for putting together the spreadsheet. Very helpful!
I'm glad it helps.
taxable_ted wrote:
Wed Feb 12, 2020 11:49 pm
I have a question regarding best practices for dealing with capital gain taxes. If I sell investment at a profit, and then transfer money out of that account to pay taxes, should I consider that a withdrawal?
Any money transferred out of the account must be counted as a withdrawal.
taxable_ted wrote:
Wed Feb 12, 2020 11:49 pm
In this case, wouldn't the returns show a mixed picture of before and after tax returns [...]?
It will just show normal returns (before taxes). It's easy to see this when you consider that the spreadsheet has no way of knowing what will be done with withdrawn money; whether it's used for taxes or it's used for food, travel, gifts, and other expenses won't affect its calculations.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

taxable_ted
Posts: 2
Joined: Tue Jun 05, 2012 12:54 pm

Re: A Returns Spreadsheet for Bogleheads

Post by taxable_ted » Thu Feb 13, 2020 9:08 pm

longinvest wrote:
Thu Feb 13, 2020 7:41 pm
It will just show normal returns (before taxes). It's easy to see this when you consider that the spreadsheet has no way of knowing what will be done with withdrawn money; whether it's used for taxes or it's used for food, travel, gifts, and other expenses won't affect its calculations.
I see. Any balance remaining in the account after the withdrawal to pay for taxes will then start accruing (or falling!) in value again on a pre-tax basis and so the returns shown in the account would always be before taxes. I confused myself there for a while, but thanks for clarifying!

Post Reply