A Returns Spreadsheet for Bogleheads

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
SnowBog
Posts: 1158
Joined: Fri Dec 21, 2018 11:21 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SnowBog »

longvest - again, thank you for this wonderful tool!

I had a couple of suggested enhancements that I've added to my copy, Excel formulas below. Wanted to offer these to validate the math is correct, and to "give back" to yourself and this wonderful community.

For context, your spreadsheet has turned into double duty to provide a historical view - not only of "returns" as you have it, but also to show portfolio progression, including to view "contributions" vs. "gains" - since all the data needed was already here anyway.

I added a small chart in the empty space under "Trailing portfolio returns". While the chart isn't really applicable to that heading - it seemed a good empty place to use... It just shows date and balance, which produces a chart - very similar [or at least mine is] to your "Growth of $10,000" below, but showing actual portfolio growth [which includes contributions]. The source used:

Code: Select all

=Calculations!$D$4:$E$4,Calculations!$D$6:$E$150
On the Returns sheet, added additional columns for the Annual portfolio return, with the end result looking something like (without formatting obviously):

Code: Select all

Annual portfolio return (time-weighted return, comparable return)					
Year    Annual Return    w/ Contributions     In/Out        Gains       Year End
2011         1.6%              22.0%          $29,267       $2,165      $174,496
2012        15.9%	       36.6%          $34,295      $29,535      $238,327
2013        25.0%              47.0%          $48,074      $64,036      $350,437
This might also help for the various "how much are you up this year" threads - as it will show both "market returns" as well as their total change (including contributions) for the year. I suspect many people, including myself, didn't have a good way of getting this data prior... For example, 2020 was a much better year for me personally than I ever would have suspected:

Code: Select all

2020        20.6%              30.4%
  • w/ Contributions: Shows the "total" change in portfolio (including the "annual return" from the market as well as changes from contributions/withdrawals)

    Code: Select all

    =IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",IF(NOT(ISNUMBER($F54)),($F55-OFFSET(Calculations!F$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0))/OFFSET(Calculations!F$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0),($F55-$F54)/$F54)))
    F55 refers to "Year End" below, for the first year it will attempt to look up the start of year balance.
  • In/Out: Basically all "contributions" - "withdrawals", which seems like it works out to a close approximation [best I have] of "new contributions" (minus fees/withdrawals). Presumably this would work for decumulation showing "withdrawals" (before any gains) as well - but I've not reached that point yet...

    Code: Select all

    =IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",SUM(OFFSET(Calculations!G$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18),0):OFFSET(Calculations!G$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0))))
    Note: the above I'd be particularly interested to validate the formula/logic... It seems "good enough" for me, but I realize its not exact as "fees" would be removed which understate "contributions". But using contributions alone overstated things as if I did transfers between accounts, or included "cash" accounts [which I do], then it wasn't even remotely accurate.
  • Gains: Year End [below @ F55] - Year Start [prior year end] - In/Out [above @ D55] = the amount the portfolio "grew" by in the year

    Code: Select all

    =IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",F55-OFFSET(Calculations!F$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0)-D55))
  • Year End: End of year portfolio balance, I adjusted mine to show partial year values - reflected in the formula

    Code: Select all

    =IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",IF(TODAY()<A55,OFFSET(Calculations!E$17,(ROW($A55)-55)*12-(12-MONTH(TODAY())+1)+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18),0),OFFSET(Calculations!E$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18),0))))
Personally, I like to see the year as it progresses - as again this has become a great way to track "contributions" and "gains" since the data is already in the file. To get "partial year" values to work, I also tweaked your columns in the Annual Portfolio Return, as you suppressed the year from showing up until complete (I suppress the fields in January - as I assume nothing has been entered yet for current year).
  • B56 = Year (2nd year - note, first year has a different formula - I didn't change it, but one could surely make it match if the wanted)

    Code: Select all

    =IF(A55="","", IF(DATE(YEAR(A55)+1,12,31)>DATE(YEAR(TODAY()),12,31),"",DATE(YEAR(A55)+1,12,31)))
  • A55 = Annual Return % - for "full" years, it should use your original formula, which I attempted to adapt for a "partial year" outlook.

    Code: Select all

    =IF(A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",IF(TODAY()<A55,OFFSET(Calculations!N$17,(ROW(A55)-55)*12-(12-MONTH(TODAY())+1)+IF(Portfolio!B$18=1,0,13-Portfolio!B$18),0),OFFSET(Calculations!O$17,(ROW(A55)-55)*12+IF(Portfolio!B$18=1,0,13-Portfolio!B$18),0))))
  • I also added a "Conditional Formatting" [Excel] rule to format the "current year" - to call it out and remind me its a "partial value" with a yellow background for format

    Code: Select all

    =$A55=DATE(YEAR(TODAY()),12,31)
  • I also added a Year heading @ A54 and Annual Return heading at B54 to go with the headings I added above
Additionally, on each of the Account sheets I added the following, primarily as a "validation" of my inputs. Most of my statements list the "additions" and "subtractions", as well as the "change in value" and some sort of "% change". Adding these last two gives me a quick way to make sure I didn't mistype the first two - when they all match, I can move on to the next account/statement. The result (text version without formatting) will be something like this:

Code: Select all

Data							
							
NOTE: Additional entries will automatically be added on				1/31/2021			
							
                                                   Date         Balance			
Month           Contribution    Withdrawal       1/31/2010          $0.00      Change       IRR
February 2010          $0.00         $0.00       2/28/2010          $0.00         $0.00     0.0%
...
October 2020       $6,200.00       $980.65      10/31/2020    $150,069.60    -$3,418.50    -2.3%
November 2020          $0.00         $0.00      11/30/2020    $167,607.80    $17,538.20    11.7%
December 2020          $0.00       $702.84      12/31/2020    $173,730.90     $6,825.94     4.1%
  • Change: The "change in value" from last month

    Code: Select all

    =IF(E17<>"",E17-E16+C17-B17,"")
  • IRR: The "change" reflected as a percent (not sure if IRR is the "correct" definition - but think I got that from one of my statements)

    Code: Select all

    =IF(E17<>"",IF(E16=0,0,G17/E16),"")
  • Purely cosmetic, but I also added a "conditional formatting" rule to each of the above making text red when "Cell Value < 0".
  • Again cosmetic, but I also add a "data bar" (conditional formatting) showing green when positive and red when negative. This gives a quick visual comparison to the relative size of % changes [within each account sheet].
Note - I hadn't thought of it until now, but I just added a similar "data bar" for my "in/out", "gain", and "Year End" columns I added to your "Annual portfolio return" section, as well as a "below average" (red text) and "above average" (green text) formatting of your "annual return" % and my "w/ contributions" fields [in my case, I opted to use above/below 1 standard deviation - the "average" range remained default text color].

Thanks again for providing this great tool!
User avatar
zarci
Posts: 186
Joined: Sat Jun 10, 2017 11:02 am

Re: A Returns Spreadsheet for Bogleheads

Post by zarci »

longinvest wrote: Sat Jan 02, 2021 7:32 pm
zarci wrote: Sat Jan 02, 2021 7: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.
Thank you for the reply. Perhaps i should have clarified that I'm a European investor, using Vanguard ETFs.

So fees and taxes internal to the fund would be:

Fund operating and transaction fees
Dividend withholding taxes incurred by the fund.

Then there's fees and taxes incurred by me individuall. They're internal to my brokerage account, sort of. Those are:

Brokerage transaction fees
Transaction taxes.


In practice my broker subtracts both the transaction fee and tax from my purchase.

So, for example:

10.000 contribution in december
100 instantly withheld by broker
9.900 balance for December


So my question would be; do i include that 100 fee/tax in my contribution and withdrawal dollar amounts?

Maybe the answer is in your previous reply ("gross") but just checking to be sure i understand. Another difference for me would be that my broker would actually treat capital gains and dividend withholding taxes in the same way. Withdrawals made by me are always "net" of all fees and taxes.

How does that affect the sheet?
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

SnowBog wrote: Sun Jan 03, 2021 4:10 am This might also help for the various "how much are you up this year" threads - ...
SnowBog, the first sentence of the first post of this thread mentions the famous "What are you up YTD?" thread where many Bogleheads report incoherent returns (most often Beardstown Ladies returns). I shared this spreadsheet, which I had built for myself, on this forum to help Bogleheads calculate proper comparable (time-weighted) returns. Your proposed modifications would be a huge step back, reporting Beardstown Ladies returns and related metrics (dollar increase/decrease of the portfolio).
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

zarci wrote: Sun Jan 03, 2021 6:49 am Thank you for the reply. Perhaps i should have clarified that I'm a European investor, using Vanguard ETFs.

So fees and taxes internal to the fund would be:

Fund operating and transaction fees
Dividend withholding taxes incurred by the fund.

Then there's fees and taxes incurred by me individuall. They're internal to my brokerage account, sort of. Those are:

Brokerage transaction fees
Transaction taxes.


In practice my broker subtracts both the transaction fee and tax from my purchase.

So, for example:

10.000 contribution in december
100 instantly withheld by broker
9.900 balance for December


So my question would be; do i include that 100 fee/tax in my contribution and withdrawal dollar amounts?
I would enter $10,000 as contribution. Fees and taxes subtracted by the broker are internal cash flows; they're not part of contributions or withdrawals. The calculated returns will be net of them.
zarci wrote: Sun Jan 03, 2021 6:49 am Another difference for me would be that my broker would actually treat capital gains and dividend withholding taxes in the same way. Withdrawals made by me are always "net" of all fees and taxes.

How does that affect the sheet?
It doesn't affect the sheet; it only determines what the sheet is actually calculating. The sheet will be calculating your gross returns net of fees and "automatic taxes" (the taxes subtracted by the broker). It won't calculate returns net of all taxes, including those paid for your investments (if any), on your annual tax return outside of your brokerage account.

Note that I would make an exception for automatic tax withholding on retirement account withdrawals. For example, when a U.S. retiree takes a RMD* withdrawal, the broker can immediately withhold some state and federal taxes. I would put the gross withdrawal amount (before withholding) in the spreadsheet, as the proper tax amount will be calculated on the annual tax return of the retiree. Any amount paid in excess of effectively due taxes will be reimbursed back to the retiree. Any deficit will be due to the Internal Revenue Service (IRS).

* Required minimum distribution.
Last edited by longinvest on Sun Jan 03, 2021 8:44 am, edited 3 times in total.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
User avatar
zarci
Posts: 186
Joined: Sat Jun 10, 2017 11:02 am

Re: A Returns Spreadsheet for Bogleheads

Post by zarci »

Thanks for the clarification, it's really helpful. :sharebeer
SnowBog
Posts: 1158
Joined: Fri Dec 21, 2018 11:21 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SnowBog »

longinvest wrote: Sun Jan 03, 2021 8:07 am
SnowBog wrote: Sun Jan 03, 2021 4:10 am This might also help for the various "how much are you up this year" threads - ...
SnowBog, the first sentence of the first post of this thread mentions the famous "What are you up YTD?" thread where many Bogleheads report incoherent returns (most often Beardstown Ladies returns). I shared this spreadsheet, which I had built for myself, on this forum to help Bogleheads calculate proper comparable (time-weighted) returns. Your proposed modifications would be a huge step back, reporting Beardstown Ladies returns and related metrics (dollar increase/decrease of the portfolio).
For what it's worth (nothing :D), while I completely understand your historical perspective, I personally see the value in both results living together.

I completely accept that your original value is the one relevant for comparison, as it's the only relevant value for "how did my investments do this year".

As I mentioned, your spreadsheet is now serving a dual purpose for me. The other piece I wanted to know was "how is my portfolio doing this year", which in my mind is inclusive of contributions. Or maybe another way of asking the question, "how much did I actually save this year"? Seeing some of the FIRE calculations based on annual savings %, I had no easy way to check my own savings rate. Now I do (this gives me the amount saved, I can use tax forms for gross income, compare the two and done). In mine, I actually added another input for gross income and the resultant savings rate, but thought those were two far adrift from what you created (wanted to avoid any extra data entry).

Prior to finding your spreadsheet, I had no good source for that information. I have a historical file, but it was only gross change at an annual level (no idea how much was new investment and how much was market returns). Since the data needed is already captured by your existing inputs, I'm merely exposing this - and avoided creating yet another spreadsheet.

Regardless, the extensions help make this even better for myself.

Thanks again!
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

SnowBog wrote: Sun Jan 03, 2021 10:08 am I personally see the value in both results living together.
SnowBog, instead of calculating and looking at Beardstown Ladies returns and similarly flawed metrics, I suggest an actionable alternative: the Accumulation sheet of the VPW Accumulation And Retirement Worksheet. The past can't be changed. I use the Accumulation worksheet to determine how much to save for retirement given my current situation (age, salary, portfolio balance and allocation, target financial independence age, and future old age benefits like Social Security or pension), regardless of how I got there. For any question about it, I suggest asking on this thread or this thread.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
NewNewbie12
Posts: 1
Joined: Sun Jan 03, 2021 12:15 pm

Re: A Returns Spreadsheet for Bogleheads

Post by NewNewbie12 »

Hi guys

can someone tell me how to make a few changes on this spreadsheet?
In particular, I need to change all the currencies and every date formatting. On some cells I can use format-->cells, but on (many) others it seems like they are locked or anyway connected to other cells in other pages and I can't see the end of it.

One more thing is the "Growth of $10,000" that just shows a down fall from $10,000 to $0, after I have changed the dates an numbers (contribution) according to my situation. How can I change that so it reflect a different starting point, rather than $10,000? And I tried on the "Calculation" page, but it doesn't allow me to change the number there.
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

NewNewbie12 wrote: Sun Jan 03, 2021 12:22 pm can someone tell me how to make a few changes on this spreadsheet?
NewNewbie12, the protection should first be disabled to modify a sheet (right click on the sheet's tab, in Excel or LibreOffice, for the protection menu). There's no password.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
SnowBog
Posts: 1158
Joined: Fri Dec 21, 2018 11:21 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SnowBog »

longinvest wrote: Sun Jan 03, 2021 11:17 am
SnowBog wrote: Sun Jan 03, 2021 10:08 am I personally see the value in both results living together.
SnowBog, instead of calculating and looking at Beardstown Ladies returns and similarly flawed metrics, I suggest an actionable alternative: the Accumulation sheet of the VPW Accumulation And Retirement Worksheet. The past can't be changed. I use the Accumulation worksheet to determine how much to save for retirement given my current situation (age, salary, portfolio balance and allocation, target financial independence age, and future old age benefits like Social Security or pension), regardless of how I got there. For any question about it, I suggest asking on this thread or this thread.
If you ignore my "help with YTD thread" comment, maybe you'll see this with fresh perspective... Sadly, I added that as an after thought after seeing some recent posts in other threads were it was clear people weren't using your approach (but were using total growth). But that wasn't really my point/goal, and had I known the contentious past that bread this spreadsheet, I'd not have even mentioned it...

Personally, I value the historical context of the past returns and contributions. With my modifications, I can see not only the market returns (using your original model) but also my portfolio growth and where it came from. In my example for 2020, I can see the market gained roughly 20% on my investments and we were able to contribute nearly 10% more, bringing our portfolio up > 30% from where we ended 2019. The 20% is meaningful and comparable with others (if someone is so inclined), but the 30% is very meaningful to me personally.

Maybe this is more relevant to me as I have a highly variable income, so my "savings" can vary drastically from one year to the next. (For example, 2019 was a spectacular year highest earnings I've ever had resulting in nearly 2x saved compared to 2020, and also some of the highest market returns in my investing time frame. I've been blessed with good years and good fortune...) I also enjoy seeing the "power of compounding" when you can see the market returns passing and eventually dwarfing your annual contributions. (As another example, while 2020 had 9% less market gains than 2019 for me, it was still my largest $ gain by the market simply because the portfolio was bigger so there was more to compound.) I can only see that when all the data is together.

Ultimately, I use this as part of our family's annual "financial review". We walk through how we did against budget (using something like Mint), how much we saved and how the markets preformed and how that compared with prior years (using my modified version of this spreadsheet), and then we look ahead at how close we are to FI/RE and the plan for the coming year(s).

I have a copy of VPW, but personally I don't use it - other than as an occasional sanity check. With my highly variable earnings, and the high likelihood that I won't be in my job for the long term, I'm of the view "save as much as we can as soon as we can [while still living an enjoyable life]". This - and that we have a healthly "jump start" on savings - results in saving significantly more than VPW suggests. (For example, VPW suggests we save about 45% of what we saved last year and expect to save this year [if not more].) And I won't change that approach until we've met our goal of FI.

All that said, I value your feedback. My intent was not to "corrupt" what you made. But clearly you have a strong view that's what this did. I may ultimately just add my changes as a new worksheet called "historical" (or some such) to better delineate it from being viewed as "returns", and better reflect its purpose. Minimally this would also simplify my management, as I can simply copy the new worksheet into any future version of the underlying spreadsheet that gets created.

Thanks again!
Last edited by SnowBog on Sun Jan 03, 2021 3:58 pm, edited 2 times in total.
Laurizas
Posts: 230
Joined: Mon Dec 31, 2018 4:44 am
Location: Lithuania

Re: A Returns Spreadsheet for Bogleheads

Post by Laurizas »

zarci wrote: Sat Jan 02, 2021 7: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!
If by selling a position you have to pay capital gain taxes and you decide to pay them using income from other sources outside of portfolio accounts, then the amount of taxes paid you should "put" as contribution.
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Laurizas wrote: Sun Jan 03, 2021 3:19 pm
zarci wrote: Sat Jan 02, 2021 7: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!
If by selling a position you have to pay capital gain taxes and you decide to pay them using income from other sources outside of portfolio accounts, then the amount of taxes paid you should "put" as contribution.
Dear Laurizas,

I strongly disagree. Here are two reasons: net returns aren't comparable to the time-weighted returns of benchmarks and other portfolios (as taxes vary for each individual) which would invalidate one of the main uses of this spreadsheet, and it's complicated (mostly non-actionable)* which is contrary to the philosophy of this spreadsheet.

This spreadsheet is meant to be easy to fill. One looks at the monthly account statement and notes the total of portfolio contributions (possibly zero), the total of portfolio withdrawals (possibly zero), and the final account balance, putting these three numbers into the spreadsheet. Nothing more. The only subtlety is to ignore internal cash flows.

* There are at least two different, yet reasonable ways to estimate taxes paid specifically on investment income (which result in different amounts): using the average tax rate of the taxpayer, or doing two personal tax calculations, one with and one without investment income, then attributing the difference to investment income. More importantly, the actual amount of taxes (overall, on salary and investments) won't be known before the next tax season.

Best regards,

longinvest
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
trigger08
Posts: 116
Joined: Wed Aug 15, 2007 1:58 pm
Location: HK

Re: A Returns Spreadsheet for Bogleheads

Post by trigger08 »

@longinvest, Thank you for all of the work putting together this spreadsheet!

I tried Version 2.1 in the Apple spreadsheets program Numbers, all the calculations work but there is a slight issue with the "Growth of $10,000" chart. I know you aren't officially supporting Numbers alongside the other programs, just sharing this info in case anyone else knows an easy way to fix:

My data ranges back to 2008, but the "Growth of $10,000" chart has the ~12 years of data compressed into about a fifth of the space, and then repeats the value for 12/31/2020 as a flat line across the rest of the chart. I think it must have something to do with the fact that the "Display" sheet in the workbook has the final 12/31/2020 row repeated about 800 times. But I checked the same file in Excel and the growth of $10k chart is correct there, so it must have something to do with the Numbers app generating the graph slightly differently from Excel. I poked around in the chart formatting options in both Excel and Numbers and don't see how Excel is ignoring the hundreds of repeated rows, but I think if there is a way to get Numbers to also ignore them that should fix the chart?

Image
retiringwhen
Posts: 2112
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: A Returns Spreadsheet for Bogleheads

Post by retiringwhen »

trigger08 wrote: Sun Jan 10, 2021 8:49 pm I poked around in the chart formatting options in both Excel and Numbers and don't see how Excel is ignoring the hundreds of repeated rows, but I think if there is a way to get Numbers to also ignore them that should fix the chart?
I am not a Numbers guru, but play with a lot of spread sheets.

The easy hack is to simply change the range of dates in the chart definition to only use the period through then last month to graph not all the way down to 980 or whatever row it has. That should work, but it will require to you remember to update each month.

Another fix may be to update the formula in the date and Grows to columns to not put that same month' info there very time, but an empty cell instead.

That can be done by changing the cells in Display as follows for row 6

J6 formula from =IF(Calculations!D6="",J5,Calculations!D6) to =IF(Calculations!D6="","",Calculations!D6)
K6 formula from =IF(Calculations!J6="",K5,Calculations!J6) tp =IF(Calculations!J6="","",Calculations!J6)

This works in Excel (and most like Google Sheets), so probably a good fix for Numbers.
trigger08
Posts: 116
Joined: Wed Aug 15, 2007 1:58 pm
Location: HK

Re: A Returns Spreadsheet for Bogleheads

Post by trigger08 »

retiringwhen wrote: Sun Jan 10, 2021 9:43 pm Another fix may be to update the formula in the date and Grows to columns to not put that same month' info there very time, but an empty cell instead.

That can be done by changing the cells in Display as follows for row 6

J6 formula from =IF(Calculations!D6="",J5,Calculations!D6) to =IF(Calculations!D6="","",Calculations!D6)
K6 formula from =IF(Calculations!J6="",K5,Calculations!J6) tp =IF(Calculations!J6="","",Calculations!J6)

This works in Excel (and most like Google Sheets), so probably a good fix for Numbers.
Thanks, I tried updating the formula as you suggested and although it did empty the "future" cells, Numbers still uses the entire range of data to display the chart, now just with a blank where the horizontal line was. Instead I've updated the data range in the chart options itself and will just remember to extend it from time to time. Unlike the Excel version, in Numbers when you hover over the line it doesn't show you the date/values anyway, so the chart isn't all that significant really, just something to look at.
retiringwhen
Posts: 2112
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: A Returns Spreadsheet for Bogleheads

Post by retiringwhen »

trigger08 wrote: Tue Jan 12, 2021 2:11 am
retiringwhen wrote: Sun Jan 10, 2021 9:43 pm Another fix may be to update the formula in the date and Grows to columns to not put that same month' info there very time, but an empty cell instead.

That can be done by changing the cells in Display as follows for row 6

J6 formula from =IF(Calculations!D6="",J5,Calculations!D6) to =IF(Calculations!D6="","",Calculations!D6)
K6 formula from =IF(Calculations!J6="",K5,Calculations!J6) tp =IF(Calculations!J6="","",Calculations!J6)

This works in Excel (and most like Google Sheets), so probably a good fix for Numbers.
Thanks, I tried updating the formula as you suggested and although it did empty the "future" cells, Numbers still uses the entire range of data to display the chart, now just with a blank where the horizontal line was. Instead I've updated the data range in the chart options itself and will just remember to extend it from time to time. Unlike the Excel version, in Numbers when you hover over the line it doesn't show you the date/values anyway, so the chart isn't all that significant really, just something to look at.
I forgot to tell you add the same type of formula to column A, it may be using that column instead of J (I didn't think it used it originally).

A6 formula from =IF(Calculations!A6="","",Calculations!D6) to =IF(Calculations!D6="","",Calculations!D6)

If that does not work, then there is some other cause and I don't have Numbers to debug. You can always use the hack I mentioned originally. The next place to debug would be to look carefully at the range entered in the chart setup and verify that none of the cells in the ranges after last month have any data displayed in them. There could possibly be a range end point issue or possibly simply a bad value fat fingered (not an empty cell) that Numbers is trying to display.
InvestVS
Posts: 70
Joined: Fri Jan 05, 2018 3:19 pm

Re: A Returns Spreadsheet for Bogleheads

Post by InvestVS »

These may be silly questions, but I am confused over how this sheet will be used to track the return of each investment and then the total return of all investments.

For example, if I have VTI, VB, VXUS, and VTEB in my portfolio and I have been contributing to them here and there every year; can you use this sheet to check how much each of them returned for a specific year and how much they all returned together for that year. It seems this sheet and XIRR is more targetted to the total return for a full portfolio, not individual investments.
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

InvestVS wrote: Wed Jan 27, 2021 1:20 am It seems this sheet [...] is more targetted to the total return for a full portfolio, not individual investments.
Exactly. This helps users sidestep some behavioral pitfalls caused by looking at individual portfolio parts. What matters is overall portfolio returns and risk.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
g2morrow
Posts: 109
Joined: Thu Jun 07, 2018 7:23 am

Re: A Returns Spreadsheet for Bogleheads

Post by g2morrow »

So is there a way to see the same performance information for each account - along with the total returns that it currently displays?
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

g2morrow wrote: Thu Feb 04, 2021 3:16 pm So is there a way to see the same performance information for each account - along with the total returns that it currently displays?
G2morrow, 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.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
Pu239
Posts: 408
Joined: Mon Dec 17, 2018 6:24 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Pu239 »

longinvest - a portion of our investments is managed by a fiduciary advisor for a couple years now. It should be straightforward to calculate returns for that portfolio using the spreadsheet and I intend to do so, but I'm wondering if I could create a similar passive index fund portfolio for use as a benchmark for assessing advisor performance. The advisor portfolio is about 60:40 stock/bonds overall with some international, tax-free bonds, etc. To do this, I would need access to historical fund price records and capital gains distributions, etc. If a passive index fund approach beats the advisor assuming the same risk level, then maybe we can dispense with the hand-holding. Thanks.
Between the idea And the reality...Between the motion And the act...Falls the Shadow - T. S. Eliot
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Pu239 wrote: Thu Feb 04, 2021 6:59 pm longinvest - a portion of our investments is managed by a fiduciary advisor for a couple years now. It should be straightforward to calculate returns for that portfolio using the spreadsheet and I intend to do so, but I'm wondering if I could create a similar passive index fund portfolio for use as a benchmark for assessing advisor performance. The advisor portfolio is about 60:40 stock/bonds overall with some international, tax-free bonds, etc. To do this, I would need access to historical fund price records and capital gains distributions, etc. If a passive index fund approach beats the advisor assuming the same risk level, then maybe we can dispense with the hand-holding. Thanks.
Pu239, there are sites like portfoliovisualizer.com to calculate the total time-weighted returns of a (benchmark) portfolio with a choice of rebalancing method. Another possibility is to simply lookup the returns of an all-in-one globally-diversified index fund or ETF with an appropriate asset allocation. In your case, this could be Vanguard's LifeStrategy Moderate Growth Fund (VSMGX) which has a 60/40 stocks/bonds allocation (link).

I'm a fan of all-in-one globally-diversified index funds. I hold my entire portfolio in an all-in-one index ETF similar to VSMGX but with a different home bias. Here's a thread about the One-Fund Portfolio and a post about why I think that VSMGX, in particular, is good enough for a U.S. investor.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
Pu239
Posts: 408
Joined: Mon Dec 17, 2018 6:24 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Pu239 »

longinvest wrote: Thu Feb 04, 2021 7:05 pm
Pu239 wrote: Thu Feb 04, 2021 6:59 pm longinvest - a portion of our investments is managed by a fiduciary advisor for a couple years now. It should be straightforward to calculate returns for that portfolio using the spreadsheet and I intend to do so, but I'm wondering if I could create a similar passive index fund portfolio for use as a benchmark for assessing advisor performance. The advisor portfolio is about 60:40 stock/bonds overall with some international, tax-free bonds, etc. To do this, I would need access to historical fund price records and capital gains distributions, etc. If a passive index fund approach beats the advisor assuming the same risk level, then maybe we can dispense with the hand-holding. Thanks.
Pu239, there are sites like portfoliovisualizer.com to calculate the total time-weighted returns of a (benchmark) portfolio with a choice of rebalancing method. Another possibility is to simply lookup the returns of an all-in-one globally-diversified index fund or ETF with an appropriate asset allocation. In your case, this could be Vanguard's LifeStrategy Moderate Growth Fund (VSMGX) which has a 60/40 stocks/bonds allocation (link).

I'm a fan of all-in-one globally-diversified index funds. I hold my entire portfolio in an all-in-one index ETF similar to VSMGX but with a different home bias. Here's a thread about the One-Fund Portfolio and a post about why I think that VSMGX, in particular, is good enough for a U.S. investor.
There have been some significant withdrawals so I was interested in comparing investor returns rather than time-weighted returns. I'll figure it out. Thanks anyway.
Between the idea And the reality...Between the motion And the act...Falls the Shadow - T. S. Eliot
Topic Author
longinvest
Posts: 4630
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Pu239 wrote: Thu Feb 04, 2021 10:30 pm There have been some significant withdrawals so I was interested in comparing investor returns rather than time-weighted returns. I'll figure it out. Thanks anyway.
Pu239, I don't see the point. The spreadsheet calculates comparable time-weighted returns so that it's easy to compare portfolio returns to a benchmark.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
trigger08
Posts: 116
Joined: Wed Aug 15, 2007 1:58 pm
Location: HK

Re: A Returns Spreadsheet for Bogleheads

Post by trigger08 »

For anyone who uses Apple Numbers rather than Excel, it seems the Numbers DATE() function is different from the Excel DATE() function and the original spreadsheet throws errors when opened in Numbers (the "day" argument has to be between 1-31, the "month" argument is not defined as modulo 12 so rolling over from December with a +1 fails, perhaps some other issues I'm forgetting). When opening the file, Numbers uses the last calculated value instead of running the calculation itself.

This means the spreadsheet will not automatically update with a new row at the end of each month. One workaround is to export your file to Excel, open it there, then re-import to Numbers. But if you had both applications, you would just use Excel (presumably).

To fix the file in Numbers, I did a little tinkering and found a way to use the Numbers function EOMONTH() to increment the tables instead:

On *each* Account Page, replace the existing code as follows:
For cell E13 next to "Additional entries will automatically be added on"

Code: Select all

IF($A$2≠"","",EOMONTH(TODAY(),0))
For cell D16, to seed the table with the month before the "start" month (as defined on the Portfolio sheet)

Code: Select all

IF($A$2≠"","",IF(DATE(Portfolio::Table 1::B17,Portfolio::Table 1::B18,1)≤TODAY(),EOMONTH(DATE(Portfolio::Table 1::B17,Portfolio::Table 1::B18,1),−1),""))
For cell A17, the first month with actual inflows/outflows

Code: Select all

IF(D16="","",IF(EOMONTH(D16,1)>TODAY(),"",EOMONTH(D16,1)))
Set cell D17, the "date" column, equal to A17.
Then autofill the formulae down in columns A and D.

You also have to make a similar replacement for the Month and Date columns on the "Calculations" sheet.

Hopefully this is helpful to someone else!
spammagnet
Posts: 1243
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet »

trigger08 wrote: Thu Mar 04, 2021 8:08 am... To fix the file in Numbers, I did a little tinkering and found a way to use the Numbers function EOMONTH() to increment the tables instead: ...
I don't know if they all behave the same but the EOMONTH function is common to Excel, Sheets and Numbers. Perhaps the spreadsheet can be standardized to use it instead of the date() function, where problematic?
bfhu
Posts: 1
Joined: Fri May 03, 2019 12:23 am

Re: A Returns Spreadsheet for Bogleheads

Post by bfhu »

I see the rate of return is for entire portfolio. I have multiple accounts added in the spreadsheet. Is it possible to show the rate of return for the entire portfolio and individual accounts? I have different strategies in different accounts. I want to see their individual performance.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

bfhu wrote: Wed Apr 07, 2021 1:50 pm I see the rate of return is for entire portfolio. I have multiple accounts added in the spreadsheet. Is it possible to show the rate of return for the entire portfolio and individual accounts? I have different strategies in different accounts. I want to see their individual performance.
Bfhu, 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.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
trigger08
Posts: 116
Joined: Wed Aug 15, 2007 1:58 pm
Location: HK

Re: A Returns Spreadsheet for Bogleheads

Post by trigger08 »

spammagnet wrote: Sun Mar 07, 2021 1:30 pm
trigger08 wrote: Thu Mar 04, 2021 8:08 am... To fix the file in Numbers, I did a little tinkering and found a way to use the Numbers function EOMONTH() to increment the tables instead: ...
I don't know if they all behave the same but the EOMONTH function is common to Excel, Sheets and Numbers. Perhaps the spreadsheet can be standardized to use it instead of the date() function, where problematic?
Circling back to using this spreadsheet in Apple Numbers, I noticed that mine still did not increment automatically at the beginning of April even after I modified the formula to use EOMONTH. I believe Numbers should recalculate every formula when you open a file, but maybe something got borked when I imported the file from Excel. I remember an error about using the last calculated value for formulas with errors, but I would have thought now that I have valid formulas it should "just work." Anyway, copying and then pasting a formula back into the same cell does recalculate, so I have added a checkbox on the main page to refresh the entries in the sheet where you list all the account names and earliest date, that forces the rest of the spreadsheet to recalculate everything.

I realize probably nobody else is using this sheet in Numbers, but maybe this info is helpful to someone someday! :sharebeer
retiringwhen
Posts: 2112
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: A Returns Spreadsheet for Bogleheads

Post by retiringwhen »

trigger08 wrote: Wed Apr 07, 2021 8:31 pm I remember an error about using the last calculated value for formulas with errors, but I would have thought now that I have valid formulas it should "just work."
Excel has a setting called Calculation Options with choices of Automatic, Automatic except for Data Tables and Manual.

I am sure Numbers has a similar option, make sure it is set to Automatic or whatever is like that.
trigger08
Posts: 116
Joined: Wed Aug 15, 2007 1:58 pm
Location: HK

Re: A Returns Spreadsheet for Bogleheads

Post by trigger08 »

retiringwhen wrote: Wed Apr 07, 2021 8:42 pm
trigger08 wrote: Wed Apr 07, 2021 8:31 pm I remember an error about using the last calculated value for formulas with errors, but I would have thought now that I have valid formulas it should "just work."
Excel has a setting called Calculation Options with choices of Automatic, Automatic except for Data Tables and Manual.

I am sure Numbers has a similar option, make sure it is set to Automatic or whatever is like that.
After poking around some more, Numbers does recalculate whenever you open a file. I must have screwed something up when I was tinkering around with the spreadsheet. My file was updating stock quotes and exchange rates, but was not checking the value of TODAY even if I entered that function into a new/blank cell. :oops:

I went back to a fresh copy and fixed the formulas to work in Numbers (as above, the DATE function is not identical to the Excel version but you can work around it using EOMONTH) and now it seems to be working perfectly. Sorry to have cluttered up this thread!
Post Reply