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: 5
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: 179
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: 38648
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: 652
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: 4117
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: 183
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: 1030
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: 183
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: 1030
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: 344
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: 1109
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: 626
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: 29429
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: 4721
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.

nvkris
Posts: 5
Joined: Mon Apr 17, 2017 7:07 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby nvkris » Tue Jun 13, 2017 8:59 pm

Thank you all for your inputs! I did try to do the solver (since I'm using google sheets, I couldn use the excel one). I found opensolver and tried placing constraints. but as @HouseBlend said (thanks mate!) that was resulting in a suboptimal solution. Finally, I did what you guys have been saying all along and went the manual route. It was soooooooo relaxing! Next time I'll try not to engineer a https://www.digitaltrends.com/cool-tech/best-rube-goldberg-machines/ for a simple solution. Thank you once again!!!

AntsOnTheMarch
Posts: 121
Joined: Mon May 29, 2017 5:47 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby AntsOnTheMarch » Tue Jun 13, 2017 9:43 pm

I do what ryman554 outlined. It sounds complicated but it is intuitive and easy. I don't even use excel. I use numbers on my iPad. It does everything I need including pie charts and automatic price updates.

A little more detail: to reallocate I have copied/pasted my portfolio table to another spreadsheet tab and made some extra columns to track the difference between the actual portfolio and what I'm changing. Simple math tells me how much of the fund I currently own in my portfolio vs. what I want to add/subtract (target). I use various checksums to ensure I haven't mistyped a number in the allocation table as I fiddle around with changes and watch the pie charts update (i.e., the checksums verify that the portfolio total matches the new asset allocation projection total).

ryman554 wrote:I want to keep all investments per account over 10k to qualify for the lower expense ratios.


Not to hijack but are you saying if you buy 10k in an admiral fund and the valuation drops below 10k you get charged investor share costs?

jmk
Posts: 234
Joined: Tue Nov 01, 2011 7:48 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby jmk » Tue Jun 13, 2017 11:17 pm

I rebalance using total amount like everyone else. But to avoid having to buy or sell taxable account I do sometimes use Excel "goal seek" (solver) to figure out what total it would take to zero out new equity amounts.
Last edited by jmk on Wed Jun 14, 2017 8:13 am, edited 2 times in total.

spammagnet
Posts: 575
Joined: Wed Apr 27, 2016 9:42 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby spammagnet » Wed Jun 14, 2017 7:39 am

DW and I have 10 retirement accounts between us. The choices available within them vary widely, as do the ERs. I created a spreadsheet that calculates the aggregate asset allocation and ER.

There are too many variables to calculate automatically (or, at least, for me to devise a way) so it involves a little trial and error. But the summarized information is a good guide for me to use in making decisions resulting in the desired AA range and minimized ER.

I simplified my efforts by allowing drift within bands. New contribution are invested in proportion to the AA. The result is the need to reallocate is rare.

After retirement we'll consolidate accounts. Having tIRAs, Roth IRAs and a brokerage account requires a minimum of 5, but we can eliminate 5 employer accounts, at least.

Edit: years ago I assisted a senior VP at my organization in setting up a linear programming tool to optimize staffing reassignments in a near-real-time manner. (I'll guess that the similar functionality is now a standard feature of commercial time-keeping apps.) I probably could find an LP package to do the same thing with my asset allocation but the tool would be expensive, the effort would be significant and the returns would be nominal.

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

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby ryman554 » Wed Jun 14, 2017 9:30 am

AntsOnTheMarch wrote:
ryman554 wrote:I want to keep all investments per account over 10k to qualify for the lower expense ratios.


Not to hijack but are you saying if you buy 10k in an admiral fund and the valuation drops below 10k you get charged investor share costs?


No. I'm on Fido, but I suspect VG is the same: If the valuation drops below 10k through market changes, you typically don't get kicked out of the better funds. OTOH, if you sell funds to take you below the 10k target (or, likely if you sell funds when it's below 10k to begin with), then you are at high risk in losing the better ER.

AntsOnTheMarch
Posts: 121
Joined: Mon May 29, 2017 5:47 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby AntsOnTheMarch » Wed Jun 14, 2017 9:32 am

ryman554 wrote:
AntsOnTheMarch wrote:
ryman554 wrote:I want to keep all investments per account over 10k to qualify for the lower expense ratios.


Not to hijack but are you saying if you buy 10k in an admiral fund and the valuation drops below 10k you get charged investor share costs?


No. I'm on Fido, but I suspect VG is the same: If the valuation drops below 10k through market changes, you typically don't get kicked out of the better funds. OTOH, if you sell funds to take you below the 10k target (or, likely if you sell funds when it's below 10k to begin with), then you are at high risk in losing the better ER.


Thank you for the clarification. That makes sense.

Adam13
Posts: 11
Joined: Mon Apr 29, 2013 10:42 am

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby Adam13 » Wed Jun 14, 2017 9:53 am

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.!


I did the same when I consolidated a few years back, it often turns out to be non-linear so solver is not your friend. I found an easy answer with the Optimal lazy portfolio rebalancing calculator (http://optimalrebalancing.tk/).

It does exacly what you are asking, and tells for a given cash amount what assets you should move things to. The only thing for you to do is to create a spreadsheet that helps summarize by asset class (or fund) and desired allocation and use the entry format of that site. Works great for me the couple times a year I rebalance.

Northster
Posts: 104
Joined: Wed Oct 28, 2009 3:30 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby Northster » Wed Jun 14, 2017 10:24 am

I have 7 accounts and 10 funds. I endorse the simple approach that has been described. Here are a few tips that I have found useful for my Google sheet. I show the percent allocation for each fund at the left, followed by the dollar amount that represents (based on total invested). I keep the actual share amounts in a second sheet so I can easily update my data for accounts that reinvest. The dollar amount for each fund in each account is calculated from this share number and a google function that fetches yesterday's closing price, eg, =Sheet2!$B$3*(Googlefinance("vsiax","closeYest")). At the right is a sum of the dollars held in each fund, followed by the percentage of total invested. I tend to just compare the dollar amount targeted with the dollar amount invested in making rebalance decisions. At one time I also calculated how much the percent invested deviated from the target (however you define your band). There is a Google (and Excel) function that can put this amount in red type if it exceeds some set value, thus alerting you that you are out of bounds and need to rebalance. But I never used it and have dropped it.

AnonJohn
Posts: 59
Joined: Wed Oct 07, 2015 2:45 pm

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby AnonJohn » Wed Jun 14, 2017 11:04 am

I'm a little late, but have nearly the same situation (~Swenson portfolio, same number of accounts) so figured I'd chime in. I took a middle course, following House Blend's advice (adding constraints) to the point where the necessary rebalancing is semi-automatic. To be clearer:

Each account (Taxable, Wife regular IRA, Husband regular, ditto TSP, H 401k) has ~two funds, except 401k (bigger) which has everything. The spreadsheet spits out, based on algebra, what changes are needed to the 401k to achieve the desired overall allocation. Usually that's all I need to rebalance. Periodically (like when I add contributions) I will adjust the other accounts by follow a sequence (based on the pairs of funds) like "First Wife IRA, then Husband IRA, then ... finally 401k.

As an aside, I use conditional formatting so that things turn red when they are out of band and need rebalancing. Another variant on error bars.

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

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby WoodSpinner » Fri Jun 16, 2017 11:30 pm

You might find the rebalancing tool being discussed in this thread usefull....

viewtopic.php?f=2&t=221267

User avatar
dratkinson
Posts: 3871
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Solver for Portfolio Rebalancing across Multiple Accounts

Postby dratkinson » Sat Jun 17, 2017 8:22 pm

General guideline: all else being equal, choose the cheapest funds.

Example. If Fidelity (401k) has the cheapest stocks and Vanguard (IRA) the cheapest bonds, then hold stocks at Fidelity (TSM, TISM, EM, REIT) and bonds at Vanguard (IPS, treasuries): total of 6 funds, down from 12 funds.

In your Vanguard taxable, own: TSM, TISM.

Your total AA should be appropriate, but every account does not need to replicate it.



With your larger tax-advantaged space (dwarfing your taxable), may need to tweak above example to include 1 bond fund with Fidelity 401k and 1 stock fund with Vanguard IRA. Then rebalancing could be as simple at redirecting distributions between stocks<-->bonds in your TA space.



Our AA does not need to be exact, only close. This means we don't need to put a lot of effort into rebalancing to exact amounts.

Some go a step further and choose to use an all-in-one fund as the major holding in their TA accounts (then skew toward their desired AA with unique funds: REIT, EM, UST,...), so the fund company does the majority of the rebalance for them. But as all-in-one funds are a little more expensive, they violate my initial premise of using the cheapest funds.



Simplicity becomes more important as we age. And when deciding between the small additional expense of an all-in-one fund and simplicity, many choose simplicity... to help them... and their heirs.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.


Return to “Investing - Theory, News & General”

Who is online

Users browsing this forum: ftobin and 36 guests