Yet still another rebalancing thread: spreadsheet or website?

[Nords]

Over the next few months we'll be rebalancing our ER portfolio. There's no hurry and I can crunch a spreadsheet from scratch, but I'd rather extend someone else's path than blaze my own trail.

Has anyone used a website or a rebalancing spreadsheet that could simplify the process of deciding which assets to hold in taxable accounts or Roth IRAs, as well as the number-crunching of how much to sell & buy?

If I decide to use a spreadsheet, is there a macro or ticker function that downloads share prices from a website? Ideally I'd click a button to update the latest prices before making a decision.

The reason for the rebalancing is Berkshire, which has swollen to over 35% of our ER portfolio. Last year we sold off our last mutual fund, so this year we'll sell some Berkshire shares and move the profits into other assets. When we're done we'll essentially have four asset classes spread among a taxable account and two IRAs:
- 23% Berkshire Hathaway (BRK/B),
- 23% spread between the Dow dividend achievers ETF (DVY) plus a few shares of Intel (INTC),
- 23% among the S&P600 small-cap value ETF (IJS) plus a few shares of Superior Industrial (SUP), KBW Retail Bank Index ETF (KRE), and spouse's TSP "S" shares, and
- 23% spread between the Powershares international dividends ETF (PID) plus a few shares of Tate & Lyle PLC (TATYY)
- 8% cash (money market & CDs)

The next decision is whether the assets should be in a taxable account or a Roth IRA. The remaining Berkshire shares will stay in the taxable account. No questions there.

The other individual stocks and the bank ETF will also stay in the taxable account. Over the next 3-4 years they'll be sold for spending cash, leaving us with Berkshire and the three bigger ETFs. Or if one of the bigger ETFs gets way out of whack (e.g., from 25% to 35%) then we'd sell a few shares of it. We're no longer picking individual stocks but we have no compelling reason to part with the ones we're holding.

I'm not sure what to do with spouse's TSP shares. They're only 2% of the total and not worth moving around or selling for other TSP funds. So I'll leave them alone until spouse's Reserve retirement (in 2009) forces a decision.

By happenstance the PID shares have ended up in the IRAs while the other assets are in the taxable account. We're not paying taxes on the dividends but we might be missing foreign tax credits, which I'll learn more about when I get our 2007 1099s. If the foreign tax credits appear to be a big deal then I could sell & buy the PID shares to move them from the IRAs to the taxable account. Of course then I'd have to sell & buy other shares to move them from a taxable account to the IRAs, which could be a fairly messy cap-gains tax bill. I guess I'd have to assess the dubious long-term value of the foreign tax credit against the tax impact.

DVY & IJS also throw off dividends which I don't mind paying taxes on. Or we could move them to the IRAs if we decided to move PID shares to the taxable account. It would greatly simplify our portfolio tracking to keep an asset classes in either the taxable account or an IRA and not splattered among them.

By not rebalancing until an asset gets way out of whack, I'm hoping that we won't be doing it more than two or three times a decade.

Any other issues or suggestions?

 

[deserat]

Nords - now I know what you do with your time all day Sheesh, that's a lot of stuff to manage.

Nope, don't have anything to offer you - I'm boring - Vanguard Stock Market, 500, Bond or Retirement Stuff here - fire and forget, for the most part.

 

[W2R]

By not rebalancing until an asset gets way out of whack, I'm hoping that we won't be doing it more than two or three times a decade.

Is it THAT bad to rebalance it? Wouldn't you do better to rebalance at least annually?

How long does it take?

 

[Animorph]

Excel does have a feature that will download web pages, or selected portions of web pages. Look up "web query" or "edit query" in the help window to track down how to do it in your version. Once it is set up you can click on an update button and download the latest data for one or all of your "queries".

Set up a portfolio (I use Yahoo) and download it into the spreadsheet. Then you can pick out the stock prices and number of shares from that data and set up an AA table.

Dan

 

[mathjak107]

nords i like PID also and use it in my etf mix. dvy has been a dog lately being almost 38% financials.

i use pid,pwb,vti,ivv and trade in and out of shy ,tlt and tips for quick capital gains in bonds

 

[Nords]

Nords - now I know what you do with your time all day Sheesh, that's a lot of stuff to manage.

Is it THAT bad to rebalance it? Wouldn't you do better to rebalance at least annually?
How long does it take?

Yeah, yeah, guilty as charged. My best defense is that I've worked through almost all of my "What if...?" grass-is-greener issues.

When I was at PG school in 1986-9 we used to make fun of crapware called Windows 2.0 and wrestle with products made by Sun & Silicon Graphics. I worked at a military training command 1994-7 where we used these little black boxes called "Cisco routers". I had similar experiences on sea duty with other manufacturers. Being a nuke I was accustomed to [-]beating the crap out of[/-] working with cool technology and it never occurred to me that my customer experience might actually represent a Peter Lynch investment opportunity. I felt a horrible sense of betrayal at my blissful ignorance and was even more humbled by watching the tech wreck. I recognized that I was playing with a different type of explosives without any handling training.

I started ER in the 2002 bear with an attitude of "I'm gonna fix this portfolio once & for all" and an obsession with finding value stocks. I even read Bernstein's "Intelligent Asset Allocator" and Dimson & Marsh's "Triumph of the Optimists" while pruning the mutual funds and going through a stock-picking phase.

I've come full circle while learning a lot about what we didn't want to do. It began to dawn on me that I'd traded one career for another with just as many duty days, midwatch wakeups, and nuclear-grade procedures. Our trading activity has dropped way off and Schedule D takes a lot less time to complete. "Simpler" is the priority but we have the pension security & lifestyle to keep the ER portfolio aggressively invested. I've watched friends & relatives for long enough to perceive their mistakes at both ends of the asset-allocation bell curve. We also spent a leisurely five years heading for our "ideal" portfolio without paying huge taxes along the way, so we never really had to rebalance anything.

All that research & effort hasn't been wasted. I've learned enough investor psychology to immunize myself from just about every investing fad for the rest of my life. We think we have the right asset allocation (& cash stash) to ignore volatility. We even think we have a handle on IRA conversions, charitable donations, and estate planning.

We've gotten a lot more comfortable with our spending and we're [-]evicting[/-] launching one from the nest in a couple years. The latest upheaval was regaining control of our rental property and boosting its cash flow. Managing dividends & cap gains isn't as important as it used to be.

So I've never paid much attention to rebalancing and I'm trying to keep it simple too. This is really the first time we've had to rebalance, and I'd rather trigger on the percentages than the calendar. My impression from the research is that mechanically rebalancing every year (or quarter or minute) tends to cut the winners short and reduces volatility more than it boosts gains.

Excel does have a feature that will download web pages, or selected portions of web pages. Look up "web query" or "edit query" in the help window to track down how to do it in your version. Once it is set up you can click on an update button and download the latest data for one or all of your "queries".
Set up a portfolio (I use Yahoo) and download it into the spreadsheet. Then you can pick out the stock prices and number of shares from that data and set up an AA table.

Excellent, that's the kind of thing I'm looking for. Thanks, Dan.

 

[JB]

Yup, there is a Microsoft Ad-In for that.

I've used the Excel Stock Quote Ad-In from MSN and it works well. The only thing I didn't like is that the stock quotes are stored once for all spreadsheets, so any backup copies will be updated with newer quotes.

Later I moved to a different machine and started downloading the spreadsheet from Yahoo like Animorph does. I have a stock portfolio configured there, then click on download spreadsheet at the bottom, then cut and paste the column of quotes into my own. I like this method better because you can easily store historic quotes and I don't have to install anything.

I tried the web fetch but couldn't figure it out in 5 minutes so I gave up.

 

[free4now]

I'm in a similar boat... feeling the need to rebalance because my Berkshire Hathaway has swollen to 33% of my portfolio. But I can count my positions on one hand, so my rebalancing will be done simply with pen, paper, and calculator.

 

[walkinwood]

... and I'd rather trigger on the percentages than the calendar.

Nords,
I read this article today and found it quite good. It talks about "looking" at your asset allocation every 2 weeks, but balancing only when an asset exceeds its allocation by a certain amount. I found the conclusions quite interesting.

FPA Journal - Opportunistic Rebalancing: A New Paradigm for Wealth Managers

He also refers to an earlier article on asset location. Since you're trying to decide between taxable and a Roth, I don't know if you'll find much there, but others may
FPA Journal - Asset Location: A Generic Framework for Maximizing After-Tax Wealth

Since you do this so infrequently, you should throw a rebalancing party. See who can balance best after a few cold ones.

 

[Nords]

Nords,
I read this article today and found it quite good. It talks about "looking" at your asset allocation every 2 weeks, but balancing only when an asset exceeds its allocation by a certain amount. I found the conclusions quite interesting.

FPA Journal - Opportunistic Rebalancing: A New Paradigm for Wealth Managers

He also refers to an earlier article on asset location. Since you're trying to decide between taxable and a Roth, I don't know if you'll find much there, but others may
FPA Journal - Asset Location: A Generic Framework for Maximizing After-Tax Wealth

Thanks, so help me, those are the kinds of articles I enjoy reading.

High-return, high-tax-efficiency classes do much better in a taxable account, while high-return, low-efficiency classes do better in an IRA.

Sounds like PID will do just fine in the Roth IRA, although I'll check the foreign tax credit impact.

I also like the idea of rebalancing bands of 20%-- in other words keeping an allocation at roughly 25% +/- 5%.

Now just as soon as Berkshire stops going up...

 

[HFWR]

This is an Excel function that will compare two cells, in this case your target upper and lower allocation limits, with a reference cell, in this case indicating your current allocation in percentage. The function will return the indicated text value, in this case either buy or sell, in the cell where the function is placed.

=IF(E3>I3,"BUY",IF(E3<H3, "SELL", ""))

This is the stock quote function:

=MSNStockQuote.Functions.MSNStockQuote($A$1,"Last Price","US")

Obviously, you enter the ticker symbol in a cell, then reference it in the function...

Not sure how to make the taxable v. Roth decision using Excel...

 

[HFWR]

This is an Excel function that will compare two cells, in this case your target upper and lower allocation limits, with a reference cell, in this case indicating your current allocation in percentage. The function will return the indicated text value, in this case either buy or sell, in the cell where the function is placed.

=IF(E3>I3,"BUY",IF(E3<H3, "SELL", ""))

This is the stock quote function:

=MSNStockQuote.Functions.MSNStockQuote($A$1,"Last Price","US")

Obviously, you enter the ticker symbol in a cell, then reference it in the function...

Not sure how to make the taxable v. Roth decision using Excel...

Oops, my bad...

The equation should be: =IF(E3>I3,"SELL",IF(E3<H3, "BUY", ""))

Chp...

 

[grfiv]

Rebalancing Spreadsheet

The following blog contains links to two rebalancing spreadsheets I made in Excel and posted to the web.

They were made for specific securities but are logically extensible.

Philadelphia Reflections: Asset Allocation and Portfolio Rebalancing

George Fisher

 

[Nords]

It turns out that some of this can be done via Fidelity's sample trade calculator as well.

Thanks for the download functions and the websites, everyone. The spreadsheet worked out fine and checked with my back-of-the-envelope calculations.

 

[baldeagle]

Hello George,

Thanks for the link to the Philadelphia physician's site. Good stuff and a lot of it.

Been a long time since I've seen you on any forums. I still refer to your paper "Financial Planning for a Long Retirement." Apparently your web site on Verizon back in Mar-06 is no longer operational.

Have you published recently?

Ron

 

[Moemg]

The following blog contains links to two rebalancing spreadsheets I made in Excel and posted to the web.

They were made for specific securities but are logically extensible.

Philadelphia Reflections: Asset Allocation and Portfolio Rebalancing

George Fisher

Thank you for the excellent article .

 

[grfiv]

Thanks

Thank you for your comments. The "Long Retirement" article is the ancestor to the article I've posted here. I took down the original because I received some feedback that it was too strongly worded. I'm hopeful that this new piece is more fair and balanced.

 

[ERD50]

Is it THAT bad to rebalance it? Wouldn't you do better to rebalance at least annually?

How long does it take?

Why would rebalancing be on a time-table or a calendar? I don't see any relationship at all? Am I missing something?

I think it makes sense to rebalance when your AA is, ummm, unbalanced. If I decide that 50% equities is all I want in terms of risk, shouldn't I adjust when it gets out of whack from that, say <45% or >55% ? What difference does it make if it happened over period of two months, or two years, or in January?

Unless some study says that there is an historic advantage to rebalancing in months that begin with 'A' or something, I can't see any reason that you wouldn't just rebalance when needed, no matter the timing.

-ERD50

 

[grfiv]

Timing of rebalancing

Swensen, manager of Yale's endowment, has claimed that by near-real-time rebalancing he has increased the return of his portfolio by some measurable amount.

But ...

- For one thing, he is really as much a trader as he is an investor; trading is a difficult skill to master but he does seem to be one of its masters.

- For another thing, he pays no taxes and faces minuscule transaction expenses; those of us who pay taxes and commissions (and who don't spend all day watching real-time market movements) view things very differently than do pension and endowment managers.

So ... you are right. Portfolio rebalancing for the individual investor is something that should be done only when the portfolio is significantly out of balance. (The definition of "significantly" being a matter both of preference and individual situation.)

George Fisher

 

[Nords]

OK, it's all done. Thanks to everyone for your suggestions & links. Everyone can feel free to whip out their wallets and restore the economy to robustness.

We spent two weeks unloading a third of our Berkshire Hathaway shares and finished a couple days before the annual report was posted. (Some days the limit orders went through, some days not so much.) Average sale share price was $4700 and BRK closed today at $4509. It feels good to do it right once in a while. I suspect BRK will be back up above $5000 by 2009.

Of course 29 Feb's fire sale was similarly much appreciated for 3 March's purchases of DVY, PID, & IJS. I'd been holding off for the late spring or even summer but this was too good to resist.

The kid's college fund has also been liquidated and we're looking for a good two-year CD. (By then I'll know if we're laddering another four years or administering the military oath.) Best candidate so far is NFCU's 3.85%, although I think by the end of this year we'll all feel that 3% is great stuff.

I tend to be early to the bottom-calling parties. If my typical investor psychology is any indication then the market will promptly drop another 10% for at least another year.

But this time I don't have to worry about a recovery-- we're reinvesting the dividends.

 

[DallasFH]

Hi Nord,

Where exactly is this on Fidelity. I am looking for it and can not seem to find it.

It turns out that some of this can be done via Fidelity's sample trade calculator as well.

Thanks for the download functions and the websites, everyone. The spreadsheet worked out fine and checked with my back-of-the-envelope calculations.

 

[CCdaCE]

<snip>

We spent two weeks unloading a third of our Berkshire Hathaway shares and finished a couple days before the annual report was posted. (Some days the limit orders went through, some days not so much.) Average sale share price was $4700 and BRK closed today at $4509. It feels good to do it right once in a while. I suspect BRK will be back up above $5000 by 2009.

Of course 29 Feb's fire sale was similarly much appreciated for 3 March's purchases of DVY, PID, & IJS. I'd been holding off for the late spring or even summer but this was too good to resist.

<snip>

Dirty market timer!


-CC

 

[Nords]

Hi Nord,
Where exactly is this on Fidelity. I am looking for it and can not seem to find it.

I think you have to have an account with Fidelity and be logged in. For me it's on the "Accounts & Trade" tab, "Trade", scroll down to the Analyze section on the left, and choose "Hypothetical Trade". Fidelity uses either your actual portfolio data or you can enter your own numbers.

Dirty market timer!

Well, when the rest of the market sucks and your portfolio's tanking, it does tend to raise the remaining Berkshire %%. We held some of the shares for as little as 29 months and others for as long as six years...

If I'd really been successfully timing the market then I would've nailed that 300-point drop. As it was I just felt stupid. But then I didn't call the 400-point jump either!

I'll check back with more dirty market-timing reports when our Berkshire shares drop below 18% or rise above 28%.

 

[trixs]

Actually this would be a useful tool to have. Something that allows you to put in the tickers, your current number of shares, and your desired allocation. After that have it automatically tell you how much of each share you should sell and buy to meet the desired allocation. Such a tool would only take a few hours of programming to create in a web based form. Hmmm

 

[CCdaCE]

Nords: I appreciate the reply.

Did you sell the old (assuming cheaper) shares or more expensive shares (of Berkshire)?

How'd you pick that particular Small Cap Value Index? Seems like there's a dozen to choose from.

Actually, I'm just envious of having something to rebalance. Right now I just have VG Total Stock Mkt Index.

-CC