A Returns Spreadsheet for Bogleheads

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
sean.mcgrath
Posts: 786
Joined: Thu Dec 29, 2016 5:15 am
Location: US in NL

Re: A Returns Spreadsheet for Bogleheads

Post by sean.mcgrath »

longinvest wrote: Thu Jun 18, 2020 6:18 am
sean.mcgrath wrote: Thu Jun 18, 2020 6:15 am I think you missed my edit, though. I confirmed your statement.
I saw your edit, but it didn't retract your statement about how I was calculating returns. I thought that it was important to clarify that the choice of reinvesting dividends or withdrawing them has no impact on time-weighted returns which are used in most forum discussions and are reported by mutual funds.
Got it. Makes sense.
jalm1
Posts: 35
Joined: Sat Jun 09, 2012 1:55 pm

Re: A Returns Spreadsheet for Bogleheads

Post by jalm1 »

First, thanks @Longinvest for making this sheet! I've been looking using the workbook to replace my current sheet.

One of the strengths is showing both IRR and TWR. I currently only track inception to date IRR and my issue has been that I can't see returns by other periods of time. This workbook solves that.

My question, which is more theoretical, why not also look at IRR across various time frames (e.g., 1 yr, 3 yr, 5 yr, 10 yr, total)? TWR is great for comparing portfolios, but it isn't the return I can touch. Is there anything that I am missing on why I wouldn't want to also calculate various IRRs?
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

jalm1 wrote: Mon Jun 29, 2020 9:51 pm My question, which is more theoretical, why not also look at IRR across various time frames (e.g., 1 yr, 3 yr, 5 yr, 10 yr, total)? TWR is great for comparing portfolios, but it isn't the return I can touch. Is there anything that I am missing on why I wouldn't want to also calculate various IRRs?
Jalm1, I don't see a good use for period-specific money-weighted returns and plenty of bad uses, like comparing them to time-weighted fund or benchmark returns over identical periods, which is invalid and could easily lead to behavioral mistakes.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
haranoth
Posts: 151
Joined: Wed Apr 06, 2016 12:07 pm

Re: A Returns Spreadsheet for Bogleheads

Post by haranoth »

Hi,
First off, thank you all for working on the google sheet for Calculating personal returns.
It has been a great help to me.

Lately, I have been trying out couple of accounts with slightly different non-boglehead approach e.g. hedgefundie adventure approach and trying out highly conservative HSA plan approach. And would like to see individual account performance and also an overall portfolio performance.
The current Google sheets does a great job on tracking the overall portfolio returns/performance etc.
I am wondering whether the same can be done at an individual account level.

The information is already there. It's a matter of presenting the result in a useful manner catering to a different user focus.
Wondering whether other's in the thread feel this is something they might find useful for their purpose.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

haranoth wrote: Mon Aug 31, 2020 10:46 am Hi,
First off, thank you all for working on the google sheet for Calculating personal returns.
It has been a great help to me.

Lately, I have been trying out couple of accounts with slightly different non-boglehead approach e.g. hedgefundie adventure approach and trying out highly conservative HSA plan approach. And would like to see individual account performance and also an overall portfolio performance.
The current Google sheets does a great job on tracking the overall portfolio returns/performance etc.
I am wondering whether the same can be done at an individual account level.

The information is already there. It's a matter of presenting the result in a useful manner catering to a different user focus.
Wondering whether other's in the thread feel this is something they might find useful for their purpose.

Thanks
Haranoth, 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.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Personal returns sheet clarification

Post by raiderG »

Hello all,

New to the forum, just want to ask some questions related to https://www.bogleheads.org/wiki/Calcula ... al_returns sheet (I use google sheet).

1. Is the 1 month (3 month etc.) actual return for that period or are the sub year returns annualized ?
2. Are the cash flows considered to be made at the start of the month ? (balance is at the end as it is dated)
User avatar
LadyGeek
Site Admin
Posts: 95695
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek »

Welcome! I merged your post into the on-going support thread.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Re: A Returns Spreadsheet for Bogleheads

Post by raiderG »

I clarified that sub year are actual returns (not annualized). I was misled by the fact that contributions are distributed at start and end of the month, hence showing potential higher earns at the beginning (double)

1. On month that pay dividends we should just consider them as part of the final balance for the month ? - considering them reinvested or even sitting there as money seems to indicate same thing to the formula, i.e. total return.

2. If dividends get reinvested much later they won't be considered as added capital (cash flow) - the effect will only be seen later as a final balance ?
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Welcome to the forum, RaiderG.
raiderG wrote: Mon Sep 28, 2020 3:41 pm 1. On month that pay dividends we should just consider them as part of the final balance for the month ? - considering them reinvested or even sitting there as money seems to indicate same thing to the formula, i.e. total return.
I just use the month end balance of each account statement.
raiderG wrote: Mon Sep 28, 2020 3:41 pm 2. If dividends get reinvested much later they won't be considered as added capital (cash flow) - the effect will only be seen later as a final balance ?
Floating dividends which haven't been paid or reinvested by end of month will simply be reflected in the next month's final balance. This is good enough as it will cause calculated returns to self correct over the two months involved. It would be quite tedious to try keeping track of floating dividends. I prefer to stick to the KISS principle.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Re: A Returns Spreadsheet for Bogleheads

Post by raiderG »

longinvest wrote: Mon Sep 28, 2020 5:06 pm
raiderG wrote: Mon Sep 28, 2020 3:41 pm 2. If dividends get reinvested much later they won't be considered as added capital (cash flow) - the effect will only be seen later as a final balance ?
Floating dividends which haven't been paid or reinvested by end of month will simply be reflected in the next month's final balance. This is good enough as it will cause calculated returns to self correct over the two months involved. It would be quite tedious to try keeping track of floating dividends. I prefer to stick to the KISS principle.
[ quote fixed by admin LadyGeek]

I agree that keeping dividends as part of the normal return (end month balance) makes sense - it's same kind of return/i.e. stocks return without dividends as part of it don't make sense. I think I'll add a column next to the account monthly balance so that I can record the dividends where is the case though.

A question related to currency. Do you think for accounts with different currency that we can record the balance entries as conversion to USD (common denominator of all accounts) in real time ? (e.g. compute them from source ETF currency to our common accounts currency)
or a static conversion at the point of the month entry makes more sense ?
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

raiderG wrote: Tue Sep 29, 2020 8:00 am A question related to currency. Do you think for accounts with different currency that we can record the balance entries as conversion to USD (common denominator of all accounts) in real time ? (e.g. compute them from source ETF currency to our common accounts currency)
or a static conversion at the point of the month entry makes more sense ?
RaiderG, are you trying to calculate real-time returns? :!:

I've initially built early versions of this spreadsheet for myself. I intentionally chose a monthly frequency to help me lose the addiction I had at that time with looking at my portfolio balance daily. It's simply pointless to frequently look at an index portfolio's balance. Markets fluctuate. Looking at the portfolio balance achieves nothing and steals time away from other more useful things we can do with our lives.

(No, I won't get into an off-topic discussion about the futility of trying to earn a so-called tiny "rebalancing bonus" by dynamically checking rebalancing bands in real-time; almost all studies about it are guilty of comparing portfolios with differing effective allocation over the comparison period. If you want to slightly increase potential returns (and risks), just slightly increase the stock allocation by 1% or 2% and accept the additional risk or, better, just spend just a little less and put the extra savings into your portfolio which is more effective and less risky),

When holding a three or four-fund portfolio, one only needs to look at asset values when making a contribution (to put the money into the asset below its target allocation) or when making a withdrawal (to take the money from the asset above its target allocation) and once a year on one's birthday to fully rebalance the portfolio if an asset has drifted too far off its target allocation.

Now that I've transformed my personal portfolio into an automatically rebalanced One-Fund Portfolio, I don't even need to look at the portfolio balance when making a contribution. I only look at my portfolio's balance once a month, after month end, to fill my return spreadsheet. Some months, I even get lazy enough to wait until I receive the actual account statement PDF (near mid-month).

Interestingly, the month end portfolio value on my account statement often differs from the balance I could infer multiplying the last transaction price of my all-in-one ETF (published on many websites) by the number of units I hold in my portfolio. This is because my brokerage uses the closing bid price of the ETF to calculate the month end portfolio value.

Back to your question: I enter all amounts in my domestic currency.
Last edited by longinvest on Tue Sep 29, 2020 11:06 am, edited 1 time in total.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Re: A Returns Spreadsheet for Bogleheads

Post by raiderG »

@longinvest it does sound crazy indeed. I will probably remove the currency conversion at the time of sheet open just because of a simple fact - if I let that fluctuate I will keep seeing new values for the past performance as I open the return report in different days.

Will make sure to keep in the account the ETF currency for reference though, in case I compare at some point the monthly reports from broker/advisor with my introduced entries.

A nitpick, if I don't enter account name but have account sheet completed it reports errors. I know you don't want to be able to see individual account reports but that would be nice to have.
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Re: A Returns Spreadsheet for Bogleheads

Post by raiderG »

@longinvest Have some feedback after 3 month of usage of the return sheet. I have 2 accounts that I track here.
The main one starts in august 2020. The second one as I show in the snapshot bellow starts in september (this has a return of about 10.3 % - this is not calculated directly/separately by the sheet though).

https://ibb.co/L1SsD72


The summary of the returns and the calculations sheet are bellow:
https://ibb.co/tHNTHX0
https://ibb.co/sQvX5rH

The internal rate of return (total for both accounts) seems to be the return of the second(smaller) account (that represents lets say around 10% of the total). Something is not right.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

raiderG wrote: Mon Nov 30, 2020 4:15 am @longinvest Have some feedback after 3 month of usage of the return sheet. I have 2 accounts that I track here.
The main one starts in august 2020. The second one as I show in the snapshot bellow starts in september (this has a return of about 10.3 % - this is not calculated directly/separately by the sheet though).

[longinvest: modified to put the image inline]
Image

The summary of the returns and the calculations sheet are bellow:
[longinvest: modified to put the image inline]
Image

[longinvest: modified to put the image inline]
Image

The internal rate of return (total for both accounts) seems to be the return of the second(smaller) account (that represents lets say around 10% of the total). Something is not right.
RaiderG, I don't see any problem. Given the numbers (column W) of the Calculations sheet, the investor return (internal rate of return) is effectively 10.3% for the 4-month period.

You can check the validity using a calculator. You've contributed a total of 1,500.00, 3,210.86, 3,000.00, and 497.00 during these four months. The 10.3% return corresponds to a ((1.103^0.25) - 1) = 2.5% monthly return (rounded). If you contribute half of the amount at the beginning of the month (750.00 in the first month), apply the rounded 2.5% monthly growth, then add the other half (750.00) at the end of the month, and repeat this for the four months, you'll end up near your 8,664.31 final balance at the end of the last month.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Re: A Returns Spreadsheet for Bogleheads

Post by raiderG »

Right @longinvest. Internal rate of return must be yearly, I agree. Coincidently it was very close to the rate of return for 3 months of one of my assets, and didn't realize it must be an a annual "extrapolation" from months return at this point. Thanks for pointing my error.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

raiderG wrote: Mon Nov 30, 2020 8:24 am Right @longinvest. Internal rate of return must be yearly, I agree. Coincidently it was very close to the rate of return for 3 months of one of my assets, and didn't realize it must be an a annual "extrapolation" from months return at this point. Thanks for pointing my error.
RaiderG, the spreadsheet only reports annual returns for periods of 12 months or more. For shorter periods, it reports cumulative returns. The 10.3% return is cumulative over 4 months.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
nwnerd
Posts: 7
Joined: Mon Mar 09, 2020 11:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by nwnerd »

Hey longinvest,
When moving money from an old account (say an IRA) to a new account (say a 401k), what is the best way to handle that in the spreadsheet? In a couple of accounts I now have a 0 balance, & the money showed up in the new account in cash, which I then had to reinvest. What do you recommend?
User avatar
Eagle33
Posts: 2391
Joined: Wed Aug 30, 2017 3:20 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Eagle33 »

nwnerd wrote: Mon Nov 30, 2020 11:53 am Hey longinvest,
When moving money from an old account (say an IRA) to a new account (say a 401k), what is the best way to handle that in the spreadsheet? In a couple of accounts I now have a 0 balance, & the money showed up in the new account in cash, which I then had to reinvest. What do you recommend?
I'll be interested in longinvest response to see if different than how we did it for our rollovers. Entered a withdrawal in 401k account tab and enter same amount in contribution column of IRA account. We now have 3 old accounts with zero balance. We also do similar for Roth conversions.
nwnerd
Posts: 7
Joined: Mon Mar 09, 2020 11:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by nwnerd »

Eagle33 wrote: Mon Nov 30, 2020 2:04 pm
nwnerd wrote: Mon Nov 30, 2020 11:53 am Hey longinvest,
When moving money from an old account (say an IRA) to a new account (say a 401k), what is the best way to handle that in the spreadsheet? In a couple of accounts I now have a 0 balance, & the money showed up in the new account in cash, which I then had to reinvest. What do you recommend?
I'll be interested in longinvest response to see if different than how we did it for our rollovers. Entered a withdrawal in 401k account tab and enter same amount in contribution column of IRA account. We now have 3 old accounts with zero balance. We also do similar for Roth conversions.
Yes, that was exactly my first inclination, but then I talked myself out of it since they aren't technically contributions or withdrawals. Or are they? Maybe I'm overthinking it.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

nwnerd wrote: Mon Nov 30, 2020 11:53 am Hey longinvest,
When moving money from an old account (say an IRA) to a new account (say a 401k), what is the best way to handle that in the spreadsheet? In a couple of accounts I now have a 0 balance, & the money showed up in the new account in cash, which I then had to reinvest. What do you recommend?
When I moved an account from brokerage A to brokerage B, in the past, I didn't do anything in my personal spreadsheet. As the money was withdrawn from brokerage A and the account was closed, and the withdrawn amount was contributed to a new account at brokerage B, I kept using the same spreadsheet account. I renamed it something like "X at B (previously at A)".

For partial transfers between accounts (for example, money transferred from a taxable account into a tax-advantaged account to fill new contribution space), I make a withdrawal in the source account (taxable account in my example) and a contribution in the destination account (tax advantaged in my example).
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Re: A Returns Spreadsheet for Bogleheads

Post by raiderG »

longinvest wrote: Mon Nov 30, 2020 11:24 am
raiderG wrote: Mon Nov 30, 2020 8:24 am Right @longinvest. Internal rate of return must be yearly, I agree. Coincidently it was very close to the rate of return for 3 months of one of my assets, and didn't realize it must be an a annual "extrapolation" from months return at this point. Thanks for pointing my error.
RaiderG, the spreadsheet only reports annual returns for periods of 12 months or more. For shorter periods, it reports cumulative returns. The 10.3% return is cumulative over 4 months.
As it seems the equation that describes the "present value" of money, as that seems to be the "cash flows equals = latest accumulation - last contribution" - internal rate of return is deduced from this as the "equivalent discounted rate" for the duration of the cash flow (hope I'm not mistaken). Because of rounding in this case (4 month only) the IRR is 10.3% while only the latest month had a 7.3% return (others had way less or negative)
User avatar
abuss368
Posts: 27850
Joined: Mon Aug 03, 2009 2:33 pm
Location: Where the water is warm, the drinks are cold, and I don't know the names of the players!
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by abuss368 »

I started simplifying many years ago. One of the more rewarding changes for us was the elimination of various spreadsheets.

I have that Vanguard statements and the website provide enough, and in some respects, more information than I need.

Keep investing and life simple.
John C. Bogle: “Simplicity is the master key to financial success."
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

raiderG wrote: Tue Dec 01, 2020 10:30 am Because of rounding in this case (4 month only) the IRR is 10.3% while only the latest month had a 7.3% return (others had way less or negative)
RaiderG, a money-weighted returns (like 10.3%) shouldn't be compared to a time-weighted returns (like 7.3%). I suggest to carefully read our wiki's explanations about these two distinct types of returns.

Let's check the validity of the calculated 10.3% cumulative investor return over 4 months (this isn't an annual return because the period is shorter than 12 months, as I wrote in a previous post). To eliminate rounding errors, for our validation, I got the spreadsheet to show more digits: the investor return was more precisely 10.30223478% over 4 months, which corresponds to a (1.1030223478^(1 / 4)) = 2.48164263% monthly return.

For interested readers, here are the inputs to the calculation (including the second account data that wasn't provided, but which could be recovered from column W of the Calculations sheet, see this post):

Code: Select all

Account 1
						Date		Balance
Month		Contribution	Withdrawal	7/31/2020	$0.00
August 2020	$0.00		$0.00		8/31/2020	$0.00
September 2020	$317.92		$0.00		9/30/2020	$329.77
October 2020	$0.00		$0.00		10/31/2020	$340,00
November 2020	$342.00		$0.00		11/30/2020	$734.23

Account 2
						Date		Balance
Month		Contribution	Withdrawal	7/31/2020	$0.00
August 2020	$1,500.00	$0.00		8/31/2020	$1,503.00
September 2020	$2,892.94	$0.00		9/30/2020	$4,372.81
October 2020	$3,000.00	$0.00		10/31/2020	$7,256.80
November 2020	$155.00		$0.00		11/30/2020	$7,930.08
The 10.3% investor return (money-weighted return) can be validated by assuming that cash flows are invested into a savings account that returns 2.48% every month.

The savings account is started with $0.00 on 7/31/2020.

In August 2020, $0.00 is contributed in Account 1 and $1,500.00 is contributed in Account 2, for a total of $1,500.00. The spreadsheet assumes that half of the amount is contributed at the beginning of the month, and the other half at the end. So, $750.00 is initially contributed, bringing the savings account balance to $750.00. During August, the account grows by 2.48% to ($750 + 2.48164263%) = $768.61. At the end of the month, the second $750.00 is contributed, bringing the savings account balance to $1,518.61.

In September 2020, $317.92 is contributed in Account 1 and $2,892.94 is contributed in Account 2, for a total of $3,210.86. The spreadsheet assumes that half of the amount is contributed at the beginning of the month, and the other half at the end. So, $1,605.43 is initially contributed, bringing the savings account balance to $3,124.04. During September, the account grows by 2.48% to ($3,124.04 + 2.48164263%) = $3,201.57. At the end of the month, the second $1,605.43 is contributed, bringing the savings account balance to $4,807.00.

In October 2020, $0.00 is contributed in Account 1 and $3,000.00 is contributed in Account 2, for a total of $3,000.00. The spreadsheet assumes that half of the amount is contributed at the beginning of the month, and the other half at the end. So, $1,500.00 is initially contributed, bringing the savings account balance to $6,307.00. During October, the account grows by 2.48% to ($6,307.00 + 2.48164263%) = $6,463.52. At the end of the month, the second $1,500.00 is contributed, bringing the savings account balance to $7,963.52.

In November 2020, $342.00 is contributed in Account 1 and $155.00 is contributed in Account 2, for a total of $497.00. The spreadsheet assumes that half of the amount is contributed at the beginning of the month, and the other half at the end. So, $248.50 is initially contributed, bringing the savings account balance to $8,212.02. During November, the account grows by 2.48% to ($8,212.02 + 2.48164263%) = $8,415.81. At the end of the month, the second $248.50 is contributed, bringing the savings account balance to $8,664.31.

As expected, this is equal to the sum of the final balances of Account 1 and Account 2 at the end of November: $734.23 + $7,930.08 = $8,664.31.

We've just verified that the cumulative 10.3% investor return, reported by the spreadsheet for the 4 months (August to November), is correct. We didn't need to understand the complex formulas of the spreadsheet; we only needed a calculator.

Note that if you had done as I had suggested in this post and used a rounded 2.5% monthly return to make a similar calculation, the final savings account balance would have been $8,667.76. The $3.45 difference would have been small enough to give you confidence in the reported cumulative 10.3% investor return for the four months.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
SnowBog
Posts: 4699
Joined: Fri Dec 21, 2018 10:21 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SnowBog »

longinvest - just wanted to say "thank you!"

Your spreadsheet has been a welcome addition, helping me visualize my personal rate of returns. This is especially handy for those of us that have accounts at multiple firms.

And I appreciate the time you invest in this thread helping others understand and use this asset!

:sharebeer
raiderG
Posts: 42
Joined: Mon Sep 28, 2020 1:11 pm

Re: A Returns Spreadsheet for Bogleheads

Post by raiderG »

@longinvest. Thanks for pointing(with details) how you get from "internal rate of return" to using that number and applying it for the duration of the account flows. It resumes basically to computing the cumulated geometric average (for all the period of the cash flows, IRR) that further decomposed to geometric month average (a unique value) explains the resulting cumulated sum at the end of the cash flows. Nice - so that is the "internal rate of return". I guess that can be expanded to specific year IRR, each year IRR "decomposed" in month geometric average return explaining cash flows from each year during an investment, and IRRs for years combined explaining evolution from year to year.

Withdrawal I see that is taken into account in the sheet in the month Flow computation (B-C) - don't find any other place where withdrawal is used directly. As so it affects "comparable returns" by decreasing the initial contributions with same amount possibly months/years later -> hence resulting in a lower month return, or negative return and impacting future possible absolute growth. Is this all that can be done (and is done in computing these) to "discount" investor interference for "comparable returns" when using same portfolios ?
spammagnet
Posts: 2481
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet »

@longinvest, are the changes in v2.1 material, compared to v2.0? The changes tab says "Update links to other spreadsheets" but I'm unclear on what that refers to. I searched this thread for an explanation. I found mention of v2.0 but not v2.1.

Thanks for your efforts.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

spammagnet wrote: Thu Dec 03, 2020 9:28 pm @longinvest, are the changes in v2.1 material, compared to v2.0? The changes tab says "Update links to other spreadsheets" but I'm unclear on what that refers to. I searched this thread for an explanation. I found mention of v2.0 but not v2.1.

Thanks for your efforts.
Spammagnet, only the "Spreadsheets" sheet, which contains links to other spreadsheets, was modified.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
spammagnet
Posts: 2481
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet »

longinvest wrote: Fri Dec 04, 2020 6:26 amSpammagnet, only the "Spreadsheets" sheet, which contains links to other spreadsheets, was modified.
Thanks. I see that tab now. As it has no bearing on calculations, I won't move my data. Only the line referencing Canadian public pensions seems to have changed?
spammagnet
Posts: 2481
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet »

BTW, the date column in the account tabs may be too narrow. Some months displayed as #### on my computer and I can't adjust the column width. I was able to resolve it by changing the zoom to 75% then changing it back to 100%, but I have to do that on each tab. Can you adjust the column wider or consider changing the format to MMM YYYY? Alternatively, give users the option to change column widths when you protect the sheets. That's harmless.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

spammagnet wrote: Fri Dec 04, 2020 6:48 am BTW, the date column in the account tabs may be too narrow. Some months displayed as #### on my computer and I can't adjust the column width. I was able to resolve it by changing the zoom to 75% then changing it back to 100%, but I have to do that on each tab. Can you adjust the column wider or consider changing the format to MMM YYYY? Alternatively, give users the option to change column widths when you protect the sheets. That's harmless.

Thanks
Spammagnet, with the downloaded Excel spreadsheet, you must first unprotect the sheet you wish to modify; there's no password.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
spammagnet
Posts: 2481
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet »

longinvest wrote: Fri Dec 04, 2020 7:12 amSpammagnet, with the downloaded Excel spreadsheet, you must first unprotect the sheet you wish to modify; there's no password.
Thanks. I hadn't even checked. I revised the protection to permit reformatting and unprotected all columns to the left of the data entry sections.
csmath
Posts: 826
Joined: Sat Oct 13, 2018 11:32 am

Re: A Returns Spreadsheet for Bogleheads

Post by csmath »

Thank you for the work on the spreadsheet. I just posted this on another thread but one of the modifications I made might be worth adding to the official version and is very simple to implement.

I added:

Code: Select all

=INDEX(Calculations!E6:E989,COUNT(Calculations!E6:E989))
to a single cell on the "Returns" tab with a header of "Current Balance". No biggie but others may appreciate it.
J0721
Posts: 31
Joined: Fri Mar 09, 2018 7:37 pm

Re: A Returns Spreadsheet for Bogleheads

Post by J0721 »

Thank you for putting this workbook together. I am pretty new to calculating returns and am not sure I have the desire to go back and get every month end totals for past years but I have at least populate all of 2020 and may try to add 2019. I wish there was a way to drop the screenshot in here so you could see what I am seeing but basically the graph for the growth of 10,000 shows that is starts at 10,000, drops to about 8,000 around 5/01 and then finishes a little above 8,000. That does not seem correct since my account went up in value and I had a positive return. I copied and pasted in as much as I could from the workbook. Let me know if you think I am doing something wrong. Otherwise I think the return seems about right?

Investor return as of 11/30/2020
Since 12/31/2019 11.7%


Trailing portfolio return (time-weighted return, comparable return)

Portfolio return as of 11/30/2020
1 month 6.3%
3 months 6.8%
6 months 21.9%
YTD 9.9%


Data

NOTE: Additional entries will automatically be added on 12/31/2020


Month Contribution Withdrawal 12/31/2019 $1,307,044.00
January 2020 $0.00 $0.00 1/31/2020 $1,303,554.00
February 2020 $57,000.00 $0.00 2/29/2020 $1,304,944.00
March 2020 $132,521.00 $0.00 3/31/2020 $1,261,552.00
April 2020 $700.00 $0.00 4/30/2020 $1,346,776.00
May 2020 $0.00 $0.00 5/31/2020 $1,366,878.00
June 2020 $12,521.00 $0.00 6/30/2020 $1,465,724.00
July 2020 $1,300.00 $0.00 7/31/2020 $1,512,244.00
August 2020 $0.00 $0.00 8/31/2020 $1,574,998.00
September 2020 $3,021.00 $0.00 9/30/2020 $1,598,732.00
October 2020 $0.00 $0.00 10/31/2020 $1,585,241.00
November 2020 $0.00 $0.00 11/30/2020 $1,685,238.00

Thank you for taking a look at this!
spammagnet
Posts: 2481
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet »

J0721 wrote: Fri Dec 18, 2020 4:14 pm Thank you for putting this workbook together. I am pretty new to calculating returns and am not sure I have the desire to go back and get every month end totals for past years but I have at least populate all of 2020 and may try to add 2019. I wish there was a way to drop the screenshot in here so you could see what I am seeing but basically the graph for the growth of 10,000 shows that is starts at 10,000, drops to about 8,000 around 5/01 and then finishes a little above 8,000. That does not seem correct since my account went up in value and I had a positive return. I copied and pasted in as much as I could from the workbook. Let me know if you think I am doing something wrong. Otherwise I think the return seems about right?

Investor return as of 11/30/2020
Since 12/31/2019 11.7%

Trailing portfolio return (time-weighted return, comparable return)

Portfolio return as of 11/30/2020
1 month 6.3%
3 months 6.8%
6 months 21.9%
YTD 9.9%
I pasted your data into a fresh copy of the v2.1 spreadsheet available in the wiki. The result is the same as listed above but the graph does not resemble what you describe. It does dip around March, because the balance did. But the terminal value is $10,994.20, not around $8K. You can see the value of that point by hovering your mouse over it.

Image
train12
Posts: 35
Joined: Thu Apr 13, 2017 12:18 pm

Re: A Returns Spreadsheet for Bogleheads

Post by train12 »

Thank you for this spreadsheet! I'm currently recording my returns on a per-quarter basis. Do you have a ballpark estimate of how much difference there is in TWR calculated quarterly vs. monthly?
Nate7out
Posts: 410
Joined: Wed Jan 16, 2008 1:06 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Nate7out »

I have 4 accounts going back 15 years -12/31/05 balances. I also have an account with a later start date (2012 ish) I'd like to add - how would I add it? Just include all 0's until the start date for that account?
SnowBog
Posts: 4699
Joined: Fri Dec 21, 2018 10:21 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SnowBog »

Nate7out wrote: Sat Dec 19, 2020 2:10 pm I have 4 accounts going back 15 years -12/31/05 balances. I also have an account with a later start date (2012 ish) I'd like to add - how would I add it? Just include all 0's until the start date for that account?
That's what I've done... Might be a better way, but this works.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

SnowBog wrote: Sat Dec 19, 2020 3:54 pm
Nate7out wrote: Sat Dec 19, 2020 2:10 pm I have 4 accounts going back 15 years -12/31/05 balances. I also have an account with a later start date (2012 ish) I'd like to add - how would I add it? Just include all 0's until the start date for that account?
That's what I've done... Might be a better way, but this works.
Nate7out, SnowBog, there's a simpler way. It's sufficient to put zero as initial balance (on 12/31/2005). The spreadsheet won't request any additional data as long as no contribution is made. Actually, the spreadsheet's rule it to request no additional data when the last reported balance is $0. This can also be useful when an account is closed.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
SnowBog
Posts: 4699
Joined: Fri Dec 21, 2018 10:21 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SnowBog »

longinvest wrote: Sat Dec 19, 2020 5:04 pm
SnowBog wrote: Sat Dec 19, 2020 3:54 pm
Nate7out wrote: Sat Dec 19, 2020 2:10 pm I have 4 accounts going back 15 years -12/31/05 balances. I also have an account with a later start date (2012 ish) I'd like to add - how would I add it? Just include all 0's until the start date for that account?
That's what I've done... Might be a better way, but this works.
Nate7out, SnowBog, there's a simpler way. It's sufficient to put zero as initial balance (on 12/31/2005). The spreadsheet won't request any additional data as long as no contribution is made. Actually, the spreadsheet's rule it to request no additional data when the last reported balance is $0. This can also be useful when an account is closed.
Great to know!

Personally, I'll still probably add $0's as it takes me seconds (copy/paste), and thats my "sanity check" for "there is no other data". When I see blanks, I want to investigate why... :oops:
J0721
Posts: 31
Joined: Fri Mar 09, 2018 7:37 pm

Re: A Returns Spreadsheet for Bogleheads

Post by J0721 »

Thank you for taking a look at this. I used open office calc because that is what I had at home but after I sent it to my work PC and opened in .XLS it looks perfect. I don't really want to track a bunch of separate accounts every month and really an not sure why I need to be concerned about individual account returns. I basically took my dozenish accounts summed the contributions and summed the month end values and run them on one account tab. I would much rather know what my overall performance is. I have tax accounts that are heavy in growth indexes to keep dividend taxes low and 401k in slow growing and bond type stuff to keep the future tax hit low, etc so all I really want to know is how my overall portfolio is doing. Any thoughts on if that seems reasonable. I am willing to be talked into a better approach it I understand the rationale. I also don't need to go crazy calculating returns to the nth degree to feel comfortable. :sharebeer

Thanks!
depassp
Posts: 3
Joined: Sat Oct 24, 2020 7:22 am

Re: A Returns Spreadsheet for Bogleheads

Post by depassp »

@longinvest and everyone involved in producing this spreadheet: Thank you so much! It's been helpful for me.

There's a slight problem with the Google sheets spreadsheet when dealing with negative returns.

Specifically, the POWER() function doesn't work properly with a negative base. To deal with it, I had to make a change on the "Calculations" sheet.

Cell P41 (and copied through P999 with locations changed) should be:

Code: Select all

=IF(A41="","",IF(J5=0,0,SIGN(J41/J5)*POWER(ABS(J41/J5),1/3)-1))
A similar change is needed for Cells Q65:Q999 (5 year return), R125:R999, S185:S999, T245:T999, U365:U999, V605:V999
Last edited by depassp on Thu Dec 31, 2020 7:16 am, edited 1 time in total.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

depassp wrote: Thu Dec 31, 2020 7:07 am There's a slight problem with the Google sheets spreadsheet when dealing with negative returns.

Specifically, the POWER() function doesn't work properly with a negative base. To deal with it, I had to make a change on the "Calculations" sheet.

Cell P41 (and copied through P999 with locations changed) should be:

Code: Select all

=IF(A41="","",IF(J5=0,0,SIGN(J41/J5)*POWER(ABS(J41/J5),1/3)-1))
A similar change is needed for Cells Q65:Q999 (5 year return), R125:R999, S185:S999, T245:T999, U365:U999, V605:V999
Depassp, welcome to the forum!

The spreadsheet isn't broken. For J41/J5 to get negative, a $10,000 lump sum investment would have to lose more than -$10,000. Losing more than the invested capital can only happen when using leverage*. Such a loss can't mathematically happen with a globally-diversified Bogleheads portfolio composed of broad total-market index funds. The spreadsheet wasn't designed to handle leveraged investing. The intent of this spreadsheet is to help Bogleheads while avoiding to encourage behavioral pitfalls (and non-Bogleheads investing).

* Note the warning at the top of the Bogleheads wiki entry about leverage: "Leverage should never be used when saving towards retirement, as the level of risk taken with leverage is excessive and can jeopardize retirement savings."
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Siaigi
Posts: 130
Joined: Sun May 17, 2020 4:24 am
Location: Italy

Re: A Returns Spreadsheet for Bogleheads

Post by Siaigi »

First of all a good year to @longinvest and all the spreadsheet developers! :sharebeer
Now I monitor my situation more scientifically and I am infinitely grateful to you for this tool.

I have however a doubt. Into my portfolio I count as well my emergency cash.

Sometimes it happens that I withdraw a few hundred euros from the emergency cash to make it to the end of the month. They then set aside the monthly savings in the fund's total.

If I don't log these withdrawals and deposits in the month's activities, will I negatively affect the return rates by noting only the total emergency cash contribution?
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Siaigi wrote: Thu Dec 31, 2020 11:01 am First of all a good year to @longinvest and all the spreadsheet developers! :sharebeer
Now I monitor my situation more scientifically and I am infinitely grateful to you for this tool.

I have however a doubt. Into my portfolio I count as well my emergency cash.

Sometimes it happens that I withdraw a few hundred euros from the emergency cash to make it to the end of the month. They then set aside the monthly savings in the fund's total.

If I don't log these withdrawals and deposits in the month's activities, will I negatively affect the return rates by noting only the total emergency cash contribution?
Siaigi, thanks for the nice comments.

It's important for the reported portfolio balance and cash flows (contributions and withdrawals) to be in sync to calculate accurate returns.

In my personal life, a few years ago I made the mistake of holding my cash savings into the same taxable account as part of my portfolio for a few months. This created artificial cash flows and also negatively affected my portfolio returns (it introduced a small cash drag). I've learned from this experience to keep my portfolio accounts and savings account separate. It's much cleaner for calculating portfolio returns.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
depassp
Posts: 3
Joined: Sat Oct 24, 2020 7:22 am

Re: A Returns Spreadsheet for Bogleheads

Post by depassp »

longinvest wrote: Thu Dec 31, 2020 8:43 am For J41/J5 to get negative, a $10,000 lump sum investment would have to lose more than -$10,000. Losing more than the invested capital can only happen when using leverage*
Understood.

Please don't take my comments as criticism on the spreadsheet. It's more a limitation of google sheets. It doesn't make sense to me that POWER() can't handle a negative base and it's unfortunate that such an awkward workaround is necessary to deal with it.

I understand if you don't want to allow the tracker to deal with negative returns. One of my small "play" accounts is using leverage (to my current detriment).
BHawks87
Posts: 399
Joined: Mon May 07, 2012 2:03 am

Re: A Returns Spreadsheet for Bogleheads

Post by BHawks87 »

Love the spreadsheet!

What is everyone's opinion on including their Emergency Fund on here? Part of my Emergency Fund is I-Bonds. Should those be included? Would it be "cheating" a little bit if you excluded cash or anything considered your Emergency Fund? Would someone using a portfolio such as the Permanent Portfolio want to exclude their cash portion?

What are your thoughts?
depassp
Posts: 3
Joined: Sat Oct 24, 2020 7:22 am

Re: A Returns Spreadsheet for Bogleheads

Post by depassp »

BHawks87 wrote: Sat Jan 02, 2021 4:47 pm Love the spreadsheet!

What is everyone's opinion on including their Emergency Fund on here? Part of my Emergency Fund is I-Bonds. Should those be included? Would it be "cheating" a little bit if you excluded cash or anything considered your Emergency Fund? Would someone using a portfolio such as the Permanent Portfolio want to exclude their cash portion?

What are your thoughts?
I don't think your emergency funds should be considered as part of your investment portfolio.

Permanent Portfolio's Cash allocation is part of the investment plan so it should be included.

As an aside, I don't think your emergency funds should be in your investment account at all. It should be extremely liquid and accessible quickly. If today you were in a car accident and unconscious in the ICU, could your family get at the funds tomorrow?
retiringwhen
Posts: 4743
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: A Returns Spreadsheet for Bogleheads

Post by retiringwhen »

BHawks87 wrote: Sat Jan 02, 2021 4:47 pm What is everyone's opinion on including their Emergency Fund on here? Part of my Emergency Fund is I-Bonds. Should those be included? Would it be "cheating" a little bit if you excluded cash or anything considered your Emergency Fund? Would someone using a portfolio such as the Permanent Portfolio want to exclude their cash portion?

What are your thoughts?
I keep my cash flow accounts (regular checking and any other cash expected to be spent in the next 3-6 mos.) out of the sheet, but include the cash or other fixed income funds that represent a part of the overall investment (AA) Asset Allocation.

The AA includes CDs, savings accounts, money markets and bond funds that are considered "fixed income" for investing. I adopted the livesoft approach and ditched the EF concept a couple years ago, but then again we are essentially (F)inancialy (I)ndependent, just waiting for the right time to (R)etire (E)arly(?)

I instead model my future cash flow requirements out to ten years and calculate a duration for those funds and maintain a collection of savings accounts, money markets, CD and bond funds that average out to that duration. I personally do not own I-Bonds. This approach makes it pretty clear what belongs in the spreadsheet for me.

Another test would be: if you chose to invest in a Target Date Fund or LifeStrategy fund only for retirement/long-term, would those I-Bonds be housed there or kept separate? That would help answer the question of whether they belong in sheet.

I personally would not put the EF in the sheet if you truly keep it separate from current every day cash flow and also don't count it as part of your fixed income portfolio for long-term investing. If it does double duty with your fixed income portion of you AA, then it belongs in the sheet.

Clear as mud :D :beer
User avatar
zarci
Posts: 197
Joined: Sat Jun 10, 2017 11:02 am

Re: A Returns Spreadsheet for Bogleheads

Post by zarci »

A silly question but I could not find an answer on the wiki. How do you deal with transaction cost and taxes in your spreadsheet? Do you include them and get a gross return figure? Or do you deduct transaction cost and tax to obtain a net figure? Thanks!
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

zarci wrote: Sat Jan 02, 2021 6:11 pm A silly question but I could not find an answer on the wiki. How do you deal with transaction cost and taxes in your spreadsheet? Do you include them and get a gross return figure? Or do you deduct transaction cost and tax to obtain a net figure? Thanks!
The spreadsheet calculates gross returns after fees. Fees are internal cash flows and will simply reduce returns. But, to pay taxes, one has to either withdraw money (that's an external cash flow) or pay them using income from other sources outside of portfolio accounts (salary, pension, or Social Security). This doesn't apply to taxes paid internally within funds (like withholding taxes) which will simply reduce returns.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Post Reply