Excel Solver for Portfolio Rebalancing across Multiple Accounts

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
nvkris
Posts: 3
Joined: Mon Apr 17, 2017 7:07 pm

Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby nvkris » Thu Apr 20, 2017 5:09 pm

Hi folks!
Newbie boglehead here! So, like a lot of you, I decided to set up my own Lazy portfolio and settled on the Swensen Yale portfolio. Now I have 3 accounts

1. Vanguard Taxable
2. Fidelity solo 401k
3. Vanguard Roth

My lazy portfolio has 6 funds. I started making an excel spreadsheet for rebalancing, went down into the blackhole of making my own LP solver equation and quickly became overwhelmed. So I'm asking for help. Does anyone know of any excel spreadsheets that help with rebalancing a portfolio across multiple accounts?
The closest I got is http://www.moneysense.ca/columns/a-spreadsheet-to-manage-multiple-accounts/, but it simply groups your funds by asset class and tells you which asset class to bring down and which to bring up. That doesnt address the issue of needing to rebalance an asset class but limited in moving money only within the account.... Any suggestions greatly appreciated..!!!

If you're interested, here's the details of my portfolio:

The Swensen funds are as follows:
(Vanguard / Fidelity) : %
VTSAX / FSTVX : 30
VGSLX / FSRVX: 20
VTMGX / FSIVX : 15
VEMAX / FPMAX : 5
VUSTX / TLT : 15
VIPSX / FSIYX : 15

I ranked these by tax cost ratio (on MS) and arranged them as follows (Ill only show vanguard equalents for simplicity sake. but in fidelity i have the fidelity equalents)

Vanguard Taxable : VTSAX , VTMGX
Fidelity solo 401k : VTSAX , VEMAX, VIPSX , VUSTX
Vanguard Roth : VTMGX , VEMAX, VIPSX, VGSLX

I have more money in retirement accts that in taxable. Hence why more funds in the retirements accounts.
I replicated funds in multiple accounts so that it would make rebalancing possible... However, solving for a rebalancing is not easy.
E.g. If I want to add 5500 in Roth, how do i move the remaining funds around....

Thank you in advance for any help!

WoodSpinner
Posts: 38
Joined: Mon Feb 27, 2017 1:15 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby WoodSpinner » Thu Apr 20, 2017 8:14 pm

OP

I am not sure I understand the problem. I do something similar with Excel but I focus on the overall portfolio as a whole. This seems to be similar to what the spreadsheet you found is suggesting

FWIW here is my process

    - Establish overall Asset Allocartion ( eg. 60% Stocks 40% Bonds)
    - Establish your Emergency Fund Goal
    - Decidde which funds go where ( e.g. Emergency Funds in Taxable), see https://www.bogleheads.org/wiki/Tax-eff ... _placement for some tips
    - Rebalance to meet the 1st 3 Goals--Across entire portfolio
    -

I don't try to have each account match the AA-just the portfolio.

Occasionally I have to de-optimize due to account constraints--for instance I have tax efficient stock funds in my taxable account because there isn't room in the Roth or 401k

Not sure if I helped or confused the issue ....

Let me know

User avatar
LadyGeek
Site Admin
Posts: 36899
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby LadyGeek » Thu Apr 20, 2017 9:28 pm

This thread is now in the Investing - Theory, News & General forum (theory).

Welcome! The wiki has several spreadsheets you might be interersted in. No solver is needed. See: Rebalancing (Spreadsheets)

The link to A Spreadsheet to Manage Multiple Accounts suggests you may be Canadian. If so, I recommend asking this question on our sister Canadian forum: Financial Wisdom Forum (If not, I just wanted to mention this for our Canadian readers.)

The Canadian wiki article is here: Rebalancing - finiki, the Canadian financial wiki

Disclaimer: I'm a member of both forums.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

ryman554
Posts: 604
Joined: Sun Jan 12, 2014 9:44 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby ryman554 » Fri Apr 21, 2017 8:25 am

You're making it too complicated.

1. List your investments, including CASH.
2. List a desired AA among the investments, likley with cash at 0%.
3. Sum your investment totals across all investments and all accounts. Create subtotals for each investment and each account.
4. Divide each investment subtotal into the total to see your "true" AA. Flag (conditional format) those that are out of your rebalancing bands.
5. Figure out the excess / deficit per investment based on your AA. Some you need to add to, some you need to remove from.
6. If you want to add 5k to a roth account, add 5k in cash. See the result to your AA percentages.
6. I have a second (blank) table which I can add/subtract money from. I just use this to do rebalancing by hand and make sure each account adjustment sums to 0. Takes me five minutes, since I usually want to add to funds that I don't have in the account I just added money to and I want to keep all investments per account over 10k to qualify for the lower expense ratios.
7. Do the trade.


I suppose I could do item #6 automatically, but it's not worth the ROI.

User avatar
House Blend
Posts: 3881
Joined: Fri May 04, 2007 1:02 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby House Blend » Fri Apr 21, 2017 10:28 am

Given that you have multiple accounts with different tax treatments and/or access to different sets of funds, the solution space is going to be inherently multi-dimensional. The only way to pick a specific solution is to add constraints or make arbitrary choices. No way is someone else's spreadsheet going to have all the levers needed to account for the types of constraints that matter to you. Tax considerations alone are far too idiosyncratic.

Accept that you probably won't be able to make this purely mechanical, or that if you can, it may require making compromises. If you are worried about behavioral errors, write down in advance the principles you want to follow, and the things that don't matter to you.

Do keep track of how much you have in each asset class, and where you are relative to AA targets, so that you know how much needs to be relocated per asset class. Spreadsheets are great for that. It also helps to have error bars around your targets. For example, if your AA calls for a 70% equity allocation, it is good to have an IPS that says no action is required unless you have more than X% or less than Y%.

P.S. Using an LP solver for this is overkill IMO, but perhaps entertaining.

Ethelred
Posts: 169
Joined: Sun Oct 30, 2016 9:38 am

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby Ethelred » Fri Apr 21, 2017 11:55 am

I can see why you might think along these lines, but personally I think it's a waste of time. And that's speaking as someone who manually tracks all our investments on a complicated Google Sheets spreadsheet once a month.

I have comments on a few different aspects of this.

Firstly, rebalancing is not precise, and it's usually recommended to use wide bands, only rebalancing once you reach outside those bands. Adding $5500 to your Roth is unlikely to trigger your rebalancing bands in most cases. It's just not that important.

Secondly, either there is an obvious solution to rebalancing, or the solution is non-unique, and should likely be decided manually. I'd argue that with your investments only in three accounts, manual calculation through a very basic spreadsheet is easy. Between me and my wife, we have three employer 401ks, two IRAs and three taxable brokerage accounts, and I still think a solver calculation would be a waste of time.

User avatar
serbeer
Posts: 1017
Joined: Fri Dec 28, 2007 2:09 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby serbeer » Fri Apr 21, 2017 12:23 pm

Ethelred wrote: Adding $5500 to your Roth is unlikely to trigger your rebalancing bands in most cases. It's just not that important.

Agreed. But in most cases the real question is what type of assets to invest these $5500 into in this Roth account. Good rebalancing spreasheet shows you what assets are below the choosen AA across all accounts and thus allow to rebalance the whole portfolia with new money, which is what sufficient for majority of people, barring extreme fluctuations of the market.

Ethelred
Posts: 169
Joined: Sun Oct 30, 2016 9:38 am

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby Ethelred » Fri Apr 21, 2017 12:36 pm

serbeer wrote:
Ethelred wrote: Adding $5500 to your Roth is unlikely to trigger your rebalancing bands in most cases. It's just not that important.

Agreed. But in most cases the real question is what type of assets to invest these $5500 into in this Roth account. Good rebalancing spreasheet shows you what assets are below the choosen AA across all accounts and thus allow to rebalance the whole portfolia with new money, which is what sufficient for majority of people, barring extreme fluctuations of the market.

Of course. But why would you want to automate that? Our portfolio tracking sheet has two tables that show percentage breakdown of each asset class and the relative percentage of target. One of these is the current asset allocation, the other allows me to add any number, say $5500, to those asset classes and see the new percentages.

User avatar
serbeer
Posts: 1017
Joined: Fri Dec 28, 2007 2:09 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby serbeer » Fri Apr 21, 2017 12:40 pm

Take a look at http://www.flexibleretirementplanner.co ... onTool.xls

I used this spreasheet as starting point and pretty much quadrupled it in size through years adding a lot of custom features on top of its original main engine (tracking of securities on account-level, automating stock quotes download, ROR and ER calculations, tracking cost basis, tax-adjusted-AA option, rebalancing alerts, reporting on account and portfolio levels, monte-carlo-based projections including budget needs and tracking toward goals, etc--all undocumented of course, the stuff of such compexity that if I died no one in my family is likely to be able to comprehend or use).

But the spreasheet linked above does all that ryman554 talks about in his post, and is very usable even as is--all the heavy-lifting is done for you in it already.

User avatar
tinscale
Posts: 318
Joined: Thu Dec 17, 2009 11:16 pm
Location: North Carolina

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby tinscale » Fri Apr 21, 2017 6:58 pm

I have a spreadsheet with 9 accounts (IRAs, HSA, Checking, Savings, CD, 401k, etc.), with 2-3 different assets in each account, that feed into an allocation of US, International, Bonds, and Cash.

When the allocation is off target, human intervention is needed to figure out what adjustments to make in which accounts.

I don’t think there is a way to automate this outside of a computer simulation or whatever.

User avatar
Oicuryy
Posts: 1076
Joined: Thu Feb 22, 2007 10:29 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby Oicuryy » Sat Apr 22, 2017 12:25 pm

nvkris wrote:I started making an excel spreadsheet for rebalancing, went down into the blackhole of making my own LP solver equation and quickly became overwhelmed.

Have you tried Excel's Solver tool?

https://support.office.com/en-us/articl ... 8f96d1652c

Ron
Money is fungible | Abbreviations and Acronyms

daveydoo
Posts: 503
Joined: Sun May 15, 2016 1:53 am

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby daveydoo » Sat Apr 22, 2017 12:34 pm

House Blend wrote:Given that you have multiple accounts with different tax treatments and/or access to different sets of funds, the solution space is going to be inherently multi-dimensional. The only way to pick a specific solution is to add constraints or make arbitrary choices. No way is someone else's spreadsheet going to have all the levers needed to account for the types of constraints that matter to you. Tax considerations alone are far too idiosyncratic.

Accept that you probably won't be able to make this purely mechanical, or that if you can, it may require making compromises. If you are worried about behavioral errors, write down in advance the principles you want to follow, and the things that don't matter to you.

Do keep track of how much you have in each asset class, and where you are relative to AA targets, so that you know how much needs to be relocated per asset class. Spreadsheets are great for that. It also helps to have error bars around your targets. For example, if your AA calls for a 70% equity allocation, it is good to have an IPS that says no action is required unless you have more than X% or less than Y%.

P.S. Using an LP solver for this is overkill IMO, but perhaps entertaining.


Not the OP but this is a great post -- thank you!

retiredjg
Posts: 28538
Joined: Thu Jan 10, 2008 12:56 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby retiredjg » Sat Apr 22, 2017 1:10 pm

I know this sounds old fashioned, but this is easily done with a pencil and paper and maybe a little hand held calculator. I've done hundreds of these with no more tools than that, many with 6 to 10 or even more accounts.


I replicated funds in multiple accounts so that it would make rebalancing possible... However, solving for a rebalancing is not easy.

This could be where the problem lies. Some duplication in multiple accounts helps, but keeping it minimal makes the solution easier.

If you'll show us what you have (see format in the link at the bottom of this message) we can show you how to make this simpler. If you decide to do that, please write out the fund names. Some people do not think in ticker.

User avatar
midareff
Posts: 4527
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby midareff » Sat Apr 22, 2017 1:35 pm

It can be as simple as it sounds. Every fund has a % allocation of your total regardless of its placement in your accounts. (1) % allocation X today's total portfolio $ is how much should be in that fund. (2) # shares X today's price = how much is in that fund today. (1) minus (2) equals $ variation. $ variation/(1) equals % variation. This is all very simple programming in excel and works both by individual fund and account.


Return to “Investing - Theory, News & General”

Who is online

Users browsing this forum: #Cruncher, Dottie57, exoilman, herpfinance, jharkin, NiceUnparticularMan, retiredjg, Rob54keep, Tamarind and 62 guests