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?

__________________

__________________*
*The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. Thanks.

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

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.

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

Quote:

Originally Posted by Want2retire

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.

Quote:

Originally Posted by 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.

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

__________________*
*The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. Thanks.

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.

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.

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.

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.

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

__________________*
*The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. Thanks.

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

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

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.

__________________*
*The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. 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.

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.

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

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.

__________________

__________________*
*The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. Thanks.

Latest Threads

Social Knowledge Community

About Us

This community was started in 2002 as an alternative to a then fee only Motley Fool. The focus of the discussions is on topics related to early retirement and financial independence. The community is moderated to ensure a pleasant experience for our members.