Critique My Rebalancing Spreadsheet

[zucckerbugger]

Here is my portfolio tracking spreadsheet (mac numbers on ipad). Works pretty well for me.

Yellow Column - Reits (dont own any yet)
Blue Columns - Stocks (US first two, international next 5)
ORange Columns - Bonds
Black Column - Money Market Account (plan to get this money into ETF/funds soon)

Start with Row 3 = Ticker and info about ETF/Fund
Row 2 right above = ER for that fund (with an average weighted ER for my entire portfolio off to the right (not including money market)
Row 1 (top row) = how much each fund/ETF costs yearly in dollars..... with a total off to the right (not including money market)
Row 4 = current total $ in each fund (the spreadsheet pulls this from another sheet where I only have to change the current share cost)
Row 5 - green row where I can use touch screen to ADD money to fund/ETF (little stepper opens up and i can add 50 dollar increments)
Row 6 - Red row where i can subtract money from fund/ETF
Row 7 - gives the new total as i add and subtract money in rows 5 and 6
Row 11 - gives percentage of REITS/STOCKS/BONDS FOR ENTIRE PORTFOLIO (according the new values in row 7. this allows me to use rows 5/6 to play with percentages and see how adding/subtracting money changes AA percentages)
Row 8 - give percentages of individual fund/ETFs (relative to entire portfolio) that is listed right below it in row 9 (changes as i add/subtract $ row 5/6)
Row 13 - gives US versus INTL for stocks (also changes automatically) as well as percentage of each bond fund/ETF (relative to bond money)
Row 15 - gives dev versus EM for international (changes automatically as well based on add/subtract rows 5/6) - this calculation is based on style boxes weighted averages for all international funds (not just VWO)
Row 10 - the little percentages tucked away in row 10 above the word "STOCKS" are the percentages of dollars of VTSAX to VBR as well as INTL (VTIAX, VXUS, VEA) to VSS to VWO

The green boxes at the bottom give MY box numbers for my US dollars / international / EM international dollars as I add and subtract (calculations from different page and based on weighted style boxes of each relevant fund) (the little gray numbers below green boxes are reference numbers for non-tilted markets)

Orange box gives my bond make-up and changes as i add or subtract money to lines 5/6 as well

what do you think?

how do you guys do it?

[bottomfisher]

Very impressive! I track mine with an Excel spreadsheet with just balances and percentage each holding is in my portfolio.

[chicagobear]

I think it would be a lot easier to just buy Vanguard Total World Stock and Total Bond along with a year's worth of expenses in an online savings account and just rebalance as you add to your investments (if you are still working) or withdraw if you are retired.

[Aptenodytes]

Not sure why you want it critiqued. Such tools should support decision making, and you are the best judge of how best to support your decisions. Every one is different so you need to make it work for you.

In my case the prime decision is where to direct new contributions so I hide a lot of what you have and zero in on the actual-target comparison by fund.

[zucckerbugger]

Not sure why you want it critiqued. Such tools should support decision making, and you are the best judge of how best to support your decisions. Every one is different so you need to make it work for you.

In my case the prime decision is where to direct new contributions so I hide a lot of what you have and zero in on the actual-target comparison by fund.

Perhaps somebody is doing something interesting and helpful with their spreadsheet that I neglected to do. That's all.

[Rosencrantz]

I understand the OP is seeking comments on his spreadsheet, not his particular holdings chicagobear.

I track my portfolio with Excel, with each mutual fund occupying a separate row, not column. Interesting to see things organized this way though. I like how everything is color-coded as well. For me however, I need the name of the fund and not just the ticker.

Thanks for posting this, zucckerbugger.

[zucckerbugger]

I understand the OP is seeking comments on his spreadsheet, not his particular holdings chicagobear.

I track my portfolio with Excel, with each mutual fund occupying a separate row, not column. Interesting to see things organized this way though. I like how everything is color-coded as well. For me however, I need the name of the fund and not just the ticker.

Thanks for posting this, zucckerbugger.

Just for the record, I don't mind comments on my holdings. Appreciate the time anybody takes to comment. Thanks.

Thanks
-z

[Aptenodytes]

Not sure why you want it critiqued. Such tools should support decision making, and you are the best judge of how best to support your decisions. Every one is different so you need to make it work for you.

In my case the prime decision is where to direct new contributions so I hide a lot of what you have and zero in on the actual-target comparison by fund.

Perhaps somebody is doing something interesting and helpful with their spreadsheet that I neglected to do. That's all.

In that case it would help to know how you use the spreadsheet. If I'm reading it correctly, it looks like it doesn't necessarily tell you how to rebalance, but rather lets you plug in what-if scenarios and gives you feedback. How often do you update it? How often do you act on the information it gives you? What is a representative action?

[LadyGeek]

For starters, I'd put the text descriptions in each row instead of numbers, e.g. Row 1 would say "ETF Costs" - it's much easier to read that way.

Next, this is a much more complicated view of the cascading asset allocation spreadsheet in the wiki: Using a Spreadsheet to Maintain a Portfolio (Under "Asset allocation visualized")

- We normally suggest that you only need to track everything to the nearest 5%. Do you really need more precision?
- The costs, while part of your considerations on which funds to hold, are extra information and seem distracting for the purposes of rebalancing or tracking your funds.

Why do you have so many funds? Are they in different accounts, e.g. taxable or deferred? If so, you should identify them.

To do rebalancing, I use the spreadsheet in the wiki: Rebalancing (Under "External links")

[zucckerbugger]

For starters, I'd put the text descriptions in each row instead of numbers, e.g. Row 1 would say "ETF Costs" - it's much easier to read that way.

Next, this is a much more complicated view of the cascading asset allocation spreadsheet in the wiki: Using a Spreadsheet to Maintain a Portfolio (Under "Asset allocation visualized")

- We normally suggest that you only need to track everything to the nearest 5%. Do you really need more precision?
- The costs, while part of your considerations on which funds to hold, are extra information and seem distracting for the purposes of rebalancing or tracking your funds.

Why do you have so many funds? Are they in different accounts, e.g. taxable or deferred? If so, you should identify them.

To do rebalancing, I use the spreadsheet in the wiki: Rebalancing (Under "External links")

I only did the numbers in row one for this post. I know what everything is otherwise.

I like to see how the costs change as I add or subtract funds.

All funds are in the correct location. All stock funds and munis in taxable. All bonds (except munis) in Roth. IBONDS at treasury direct.

REITS will be in 401k if I add them

I don't think i have too many funds. Going to follow Larry swedroe type plan and tilt so I need the small cap and emerging market. I do need to convert my vtsax to VXUS if possible but need to add total international TAX MANAGED based on the post on here that showed it saved on expenses. As far as bonds, have total bond, int treasury, tips, muni, IBONDS. The schwab will be in a company plan 401k.

Thanks for the feedback...
-z

[zucckerbugger]

Not sure why you want it critiqued. Such tools should support decision making, and you are the best judge of how best to support your decisions. Every one is different so you need to make it work for you.

In my case the prime decision is where to direct new contributions so I hide a lot of what you have and zero in on the actual-target comparison by fund.

Perhaps somebody is doing something interesting and helpful with their spreadsheet that I neglected to do. That's all.

In that case it would help to know how you use the spreadsheet. If I'm reading it correctly, it looks like it doesn't necessarily tell you how to rebalance, but rather lets you plug in what-if scenarios and gives you feedback. How often do you update it? How often do you act on the information it gives you? What is a representative action?

This is correct. It lets me plug in additions and subtractions of dollars and then automatically provides the updated AA percentages. I found this to be the easiest and most enjoyable way to arrive at the info I was seeking. It provides the total being added off to the right hand side of rows 5/6.

I try add money to my portfolio just about every paycheck so I use it for that purpose once every two weeks to see where to put the money. My stock allocation is lower than my goal so that is where I need to add new funds right now. Although I do have to admit that it has been hard getting my money into the funds/ETF when everything is so high in price ALTHOUGH I completely understand the logic to just get it in. I'm working on it. I got about 5K in last week. Been hoping for a livesoft RBD. Still have some sitting on the sideline but plan to get it in soon........

Just to see what is "going on" I update the current value per share (on another page) daily and that adjusts the totals on this page automatically. I enjoy doing this daily....just like I enjoy going to the gym daily (hopefully), playing with my kids daily, eating dinner with my wife and family daily. I only act on the info when it is time to put new money in.

[red5]

Thank you for sharing this. I enjoy looking over other people's spreadsheet creations to look for ideas and differences. I built one before coming to this site and over the years it has become expansive (yet easy to maintain). I do have a tab dedicated to asset allocation and it is quite similar to yours. All my funds are organized by rows, not columns as you have done, but it really is the same either way.

I do make my own USA and International M* style boxes but I don't for EM (which I do have). Seems a bit overkill to me as what would you do if you you didn't like what you saw inside that box? Do you even have an AA within EM? At least with the USA and Intl boxes you can see if your small cap exposure (via VBR and VSS) are to low or high.

Like someone mentioned earlier I think it would be a good idea to somehow incorporate a rebalancing function somewhere in there. Afterall isn't that the purpose of such an excel sheet? For example, you could utilize "Conditional Formatting" in Excel (not sure if Mac Numbers has this?) to order Row 11 to light up a certain color when your AA is off. If the stock allocation of 55% went below 50%, then that cell would light up red, letting you know you need to increase that allocation. Same could be done within your M* boxes with small cap. If your exposure gets to high or low, then the bottom row could light up letting you know that you need to rebalance.

I also agree with some of the previous posters that you a bit of a complicated portfolio. Unless VTIAX, VXUS and VEA have to be in different accounts then I'd try to combine them. And is it possible to consolidate some of those bonds?

Thanks for sharing though! I really like your organization.

[zucckerbugger]

Thank you for sharing this. I enjoy looking over other people's spreadsheet creations to look for ideas and differences. I built one before coming to this site and over the years it has become expansive (yet easy to maintain). I do have a tab dedicated to asset allocation and it is quite similar to yours. All my funds are organized by rows, not columns as you have done, but it really is the same either way.

Its funny. All the spreadsheets I see are usually in rows. My brain wants to see it in columns. Rows just feel and look wrong to me. Not ACTUALLY right or wrong, just the way it is. Reminds me of wearing a watch on my left wrist (im right handed) but it just feels so wrong, i have always put my watch (when i wear one) on my right wrist even though i am right handed,,...just feels right.....

I do make my own USA and International M* style boxes but I don't for EM (which I do have). Seems a bit overkill to me as what would you do if you you didn't like what you saw inside that box? Do you even have an AA within EM? At least with the USA and Intl boxes you can see if your small cap exposure (via VBR and VSS) are to low or high.

Like someone mentioned earlier I think it would be a good idea to somehow incorporate a rebalancing function somewhere in there. Afterall isn't that the purpose of such an excel sheet? For example, you could utilize "Conditional Formatting" in Excel (not sure if Mac Numbers has this?) to order Row 11 to light up a certain color when your AA is off. If the stock allocation of 55% went below 50%, then that cell would light up red, letting you know you need to increase that allocation. Same could be done within your M* boxes with small cap. If your exposure gets to high or low, then the bottom row could light up letting you know that you need to rebalance.

Good Point. However I know what my goals are so I really dont need the boxes to light up when they drift. Also, I dont think numbers on mac (ipad) has that function but I did have an earlier version of my spreadsheet do that when it was on excel. Again good point, but I mess around with this thing so often I really dont forget my goal AA etc.

I also agree with some of the previous posters that you a bit of a complicated portfolio. Unless VTIAX, VXUS and VEA have to be in different accounts then I'd try to combine them. And is it possible to consolidate some of those bonds?

I am planning on converting VTIAX to VXUS but I will also need to keep the tax managed international if I believe that it will save me money in doing so. There was a post on here regarding that. Dont have the link. Ill get it later and post it. But good points nonetheless. I dont see the need to combine the bond funds, although one could argue getting rid of either total bond or Int term treasury ,,I like having both. And want to diversify with TIPS, ibonds, munis (plus will run out of bond room and need more munis as well). The Schwab fund is an option in upcoming employer plan. Thanks for feedback.

Thanks for sharing though! I really like your organization.

[zenb]

Related question: I'm using one of the spreadsheets from the wiki ("Ducks has another variation"). This one and in others that has a simple Small+Large split for domestic stocks which bucket do you account mid-cap stocks?. I have a mix of "Vanguard 500 index fund" and "Vanguard Extended Market Index Fund". Both of these have some % of mid-cap. For the purpose of AA planning do you account the mid-cap as part of "Large" or "Small" portion of your domestic stocks?

[frequentT]

Enjoyed seeing your spreadsheet design! Very creative and elegant! Hope it helps you better manage your assets.

One suggestion: consider using google docs and you will be able to have your fund prices downloaded automatically and save you the manual labor of inputting and also transcription errors. I have used the google spreadsheet for about 18 months and it works well. The only thing I manually input are dividend increases and transactional changes.

[obgraham]

I liked seeing yours, too. Mine is certainly less "finessed". I go through the exercise only once a year.

All I track is the dollars in my funds. I don't bother with share prices -- after all 500Index share price is 6X Total Market share price.
Is there a reason you track share price so closely?

[red5]

Related question: I'm using one of the spreadsheets from the wiki ("Ducks has another variation"). This one and in others that has a simple Small+Large split for domestic stocks which bucket do you account mid-cap stocks?. I have a mix of "Vanguard 500 index fund" and "Vanguard Extended Market Index Fund". Both of these have some % of mid-cap. For the purpose of AA planning do you account the mid-cap as part of "Large" or "Small" portion of your domestic stocks?

I have no idea how others account for midcap stocks or what the "normal" way to account for them is, but, what my AA calls for is for 25% my domestic stocks to be small. Thus 75% of my domestic stock allocation would be anything else that is not small, and I suppose this would include midcap stocks. So I guess for me, a more precise way of labelling my allocation would be Small / Not Small, instead of Small / Large.

[SSSS]

Here's my spreadsheet:

http://i48.tinypic.com/1zzgzn5.png

Only the blue cells are meant to be edited -- everything else is auto-calculated (with the core component being =googlefinance() for price lookups).

[zucckerbugger]

Enjoyed seeing your spreadsheet design! Very creative and elegant! Hope it helps you better manage your assets.

One suggestion: consider using google docs and you will be able to have your fund prices downloaded automatically and save you the manual labor of inputting and also transcription errors. I have used the google spreadsheet for about 18 months and it works well. The only thing I manually input are dividend increases and transactional changes.

That sounds like a good idea. Gotta check it out....

But I do this on my iPad....not sure google docs work well on iPad.....

[zucckerbugger]

Here's my spreadsheet:

http://i48.tinypic.com/1zzgzn5.png

Only the blue cells are meant to be edited -- everything else is auto-calculated (with the core component being =googlefinance() for price lookups).

Nice job...

[LadyGeek]

I hate to bring this up, but my wiki editor eye has spotted a typo in H5. (Don't ask, I do this to my coworkers all the time.)

[hoppy08520]

The green boxes at the bottom give MY box numbers for my US dollars / international / EM international dollars as I add and subtract (calculations from different page and based on weighted style boxes of each relevant fund) (the little gray numbers below green boxes are reference numbers for non-tilted markets)

About the Spreadhseet
Pretty impressive. How do you generate the figures in the Morningstar style boxes on the bottom? Did you enter the 9 values for each fund on a different tab, and then compute these out proportionally to your holdings? I guess you would need to update the "source data" for the M* style boxes every so often for each fund you have as these do change over time (annually? quarterly?).

Did you do something similar for the orange box with the bonds by sector?

I never thought about doing that in my own spreadsheet but it's a good idea.

Now all you need to do is integrate your spreadsheet with Simba's backtesting spreadsheet so you can compute your portfolio's historical returns

About Your Portfolio

• Why do you have both the ETF and the mutual fund of Vanguard TISM? Is this because you have these in different accounts?
• I'm curious why you have a value tilt in US but not international?
• It looks like you're trying to do a slight international overweight with SC (VSS) and EM (VWO). Why do you also have VEA? If you're trying to overweight EM, then why is your overall international AA at 75/25? I thought EM is 23% of international, so it doesn't look like you're really over-weighting anything.
• As for your US SCV tilt, it looks like you've got 85% TSM and 15% SCV? Why so timid If you're even going to bother tilting, shouldn't you bump it up to 25% at a minimum?

[SSSS]

I hate to bring this up, but my wiki editor eye has spotted a typo in H5. (Don't ask, I do this to my coworkers all the time.)

Fine...



VBR, you sneaky little bugger, don't think I didn't notice you turning yellow since this morning.

[zucckerbugger]

The green boxes at the bottom give MY box numbers for my US dollars / international / EM international dollars as I add and subtract (calculations from different page and based on weighted style boxes of each relevant fund) (the little gray numbers below green boxes are reference numbers for non-tilted markets)

About the Spreadhseet
Pretty impressive. How do you generate the figures in the Morningstar style boxes on the bottom? Did you enter the 9 values for each fund on a different tab, and then compute these out proportionally to your holdings? I guess you would need to update the "source data" for the M* style boxes every so often for each fund you have as these do change over time (annually? quarterly?).

".................Yes. Calculated on another tab. It's pretty simple to do.

Did you do something similar for the orange box with the bonds by sector?

. .............Same kinda thing.

I never thought about doing that in my own spreadsheet but it's a good idea.

Now all you need to do is integrate your spreadsheet with Simba's backtesting spreadsheet so you can compute your portfolio's historical returns

About Your Portfolio

• Why do you have both the ETF and the mutual fund of Vanguard TISM? Is this because you have these in different accounts? No. Started with mutual funds. But switching to ETFs so I can take advantage of livesoft RBDs.
• I'm curious why you have a value tilt in US but not international? I have both. Just building those up.
• It looks like you're trying to do a slight international overweight with SC (VSS) and EM (VWO). Why do you also have VEA? was thinking about using VEA based on the post (grabiner??) that showed that using tax managed international saved a few bucks.
  
  If you're trying to overweight EM, then why is your overall international AA at 75/25? I thought EM is 23% of international, so it doesn't look like you're really over-weighting anything. EM is 17% of international. I have EM at 26% of my international including all the EM in vtiax,VXUS,vss,vwo
• As for your US SCV tilt, it looks like you've got 85% TSM and 15% SCV? Why so timid If you're even going to bother tilting, shouldn't you bump it up to 25% at a minimum?

just adding that tilt. Needs to be built up more. I have the paralysis of being afraid to buy right now...not proud of that fact.