Got it. Makes sense.longinvest wrote: ↑Thu Jun 18, 2020 6:18 amI 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.sean.mcgrath wrote: ↑Thu Jun 18, 2020 6:15 am I think you missed my edit, though. I confirmed your statement.
A Returns Spreadsheet for Bogleheads
-
- Posts: 786
- Joined: Thu Dec 29, 2016 5:15 am
- Location: US in NL
Re: A Returns Spreadsheet for Bogleheads
Re: A Returns Spreadsheet for Bogleheads
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?
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?
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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.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?
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Re: A Returns Spreadsheet for Bogleheads
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
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
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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.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
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Personal returns sheet clarification
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)
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)
Re: A Returns Spreadsheet for Bogleheads
Welcome! I merged your post into the on-going support thread.
Re: A Returns Spreadsheet for Bogleheads
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 ?
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 ?
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
Welcome to the forum, RaiderG.
I just use the month end balance of each account statement.
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)
Re: A Returns Spreadsheet for Bogleheads
[ quote fixed by admin LadyGeek]longinvest wrote: ↑Mon Sep 28, 2020 5:06 pmFloating 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.
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 ?
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
RaiderG, are you trying to calculate real-time returns?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 ?
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)
Re: A Returns Spreadsheet for Bogleheads
@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.
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.
Re: A Returns Spreadsheet for Bogleheads
@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.
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.
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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.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]
The summary of the returns and the calculations sheet are bellow:
[longinvest: modified to put the image inline]
[longinvest: modified to put the image inline]
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.
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)
Re: A Returns Spreadsheet for Bogleheads
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.
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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.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.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Re: A Returns Spreadsheet for Bogleheads
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 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?
Re: A Returns Spreadsheet for Bogleheads
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 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?
Re: A Returns Spreadsheet for Bogleheads
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.Eagle33 wrote: ↑Mon Nov 30, 2020 2:04 pmI'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 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?
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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)".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?
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)
Re: A Returns Spreadsheet for Bogleheads
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)longinvest wrote: ↑Mon Nov 30, 2020 11:24 amRaiderG, 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.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.
- 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
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.
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."
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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 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)
Re: A Returns Spreadsheet for Bogleheads
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!
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!
Re: A Returns Spreadsheet for Bogleheads
@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 ?
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 ?
-
- Posts: 2481
- Joined: Wed Apr 27, 2016 9:42 pm
Re: A Returns Spreadsheet for Bogleheads
@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.
Thanks for your efforts.
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
Spammagnet, only the "Spreadsheets" sheet, which contains links to other spreadsheets, was modified.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.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
-
- Posts: 2481
- Joined: Wed Apr 27, 2016 9:42 pm
Re: A Returns Spreadsheet for Bogleheads
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?longinvest wrote: ↑Fri Dec 04, 2020 6:26 amSpammagnet, only the "Spreadsheets" sheet, which contains links to other spreadsheets, was modified.
-
- Posts: 2481
- Joined: Wed Apr 27, 2016 9:42 pm
Re: A Returns Spreadsheet for Bogleheads
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
Thanks
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
Spammagnet, with the downloaded Excel spreadsheet, you must first unprotect the sheet you wish to modify; there's no password.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
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
-
- Posts: 2481
- Joined: Wed Apr 27, 2016 9:42 pm
Re: A Returns Spreadsheet for Bogleheads
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.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.
Re: A Returns Spreadsheet for Bogleheads
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:
to a single cell on the "Returns" tab with a header of "Current Balance". No biggie but others may appreciate it.
I added:
Code: Select all
=INDEX(Calculations!E6:E989,COUNT(Calculations!E6:E989))
Re: A Returns Spreadsheet for Bogleheads
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!
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!
-
- Posts: 2481
- Joined: Wed Apr 27, 2016 9:42 pm
Re: A Returns Spreadsheet for Bogleheads
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.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%
Re: A Returns Spreadsheet for Bogleheads
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?
Re: A Returns Spreadsheet for Bogleheads
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?
Re: A Returns Spreadsheet for Bogleheads
That's what I've done... Might be a better way, but this works.
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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)
Re: A Returns Spreadsheet for Bogleheads
Great to know!longinvest wrote: ↑Sat Dec 19, 2020 5:04 pmNate7out, 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.
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...
Re: A Returns Spreadsheet for Bogleheads
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.
Thanks!
Thanks!
Re: A Returns Spreadsheet for Bogleheads
@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:
A similar change is needed for Cells Q65:Q999 (5 year return), R125:R999, S185:S999, T245:T999, U365:U999, V605:V999
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))
Last edited by depassp on Thu Dec 31, 2020 7:16 am, edited 1 time in total.
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
Depassp, welcome to the forum!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:
A similar change is needed for Cells Q65:Q999 (5 year return), R125:R999, S185:S999, T245:T999, U365:U999, V605:V999Code: Select all
=IF(A41="","",IF(J5=0,0,SIGN(J41/J5)*POWER(ABS(J41/J5),1/3)-1))
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)
Re: A Returns Spreadsheet for Bogleheads
First of all a good year to @longinvest and all the spreadsheet developers!
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?
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?
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
Siaigi, thanks for the nice comments.Siaigi wrote: ↑Thu Dec 31, 2020 11:01 am First of all a good year to @longinvest and all the spreadsheet developers!
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?
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)
Re: A Returns Spreadsheet for Bogleheads
Understood.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*
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).
Re: A Returns Spreadsheet for Bogleheads
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?
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?
Re: A Returns Spreadsheet for Bogleheads
I don't think your emergency funds should be considered as part of your investment portfolio.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?
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?
-
- Posts: 4743
- Joined: Sat Jul 08, 2017 10:09 am
- Location: New Jersey, USA
Re: A Returns Spreadsheet for Bogleheads
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.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?
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
Re: A Returns Spreadsheet for Bogleheads
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!
-
- Posts: 5682
- Joined: Sat Aug 11, 2012 8:44 am
Re: A Returns Spreadsheet for Bogleheads
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)