Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Yet still another rebalancing thread: spreadsheet or website?
Old 01-23-2008, 08:14 PM   #1
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
Yet still another rebalancing thread: spreadsheet or website?

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.
Nords is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

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!

Old 01-23-2008, 08:27 PM   #2
Thinks s/he gets paid by the post
 
Join Date: Jul 2004
Posts: 1,072
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.
__________________

__________________
Deserat aka Bridget
“We sleep soundly in our beds because rough men stand ready in the night to visit violence on those who would do us harm.” - George Orwell/Winston Churchill
deserat is offline   Reply With Quote
Old 01-23-2008, 08:32 PM   #3
Administrator
W2R's Avatar
 
Join Date: Jan 2007
Location: New Orleans
Posts: 38,856
Quote:
Originally Posted by Nords View Post
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?
__________________
Already we are boldly launched upon the deep; but soon we shall be lost in its unshored, harbourless immensities.

- - H. Melville, 1851
W2R is online now   Reply With Quote
Old 01-23-2008, 08:38 PM   #4
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
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
__________________
Animorph is offline   Reply With Quote
Old 01-24-2008, 03:58 AM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jul 2005
Posts: 5,408
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
__________________
mathjak107 is offline   Reply With Quote
Old 01-24-2008, 12:24 PM   #6
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
Quote:
Originally Posted by deserat View Post
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 View Post
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 View Post
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.
Nords is offline   Reply With Quote
Yup, there is a Microsoft Ad-In for that.
Old 01-24-2008, 12:42 PM   #7
Full time employment: Posting here.
 
Join Date: Apr 2005
Posts: 524
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.
__________________
JB is offline   Reply With Quote
Old 01-24-2008, 12:56 PM   #8
Thinks s/he gets paid by the post
free4now's Avatar
 
Join Date: Dec 2005
Posts: 1,225
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.
__________________
free4now is offline   Reply With Quote
Old 01-24-2008, 07:21 PM   #9
Thinks s/he gets paid by the post
walkinwood's Avatar
 
Join Date: Jul 2006
Location: Denver
Posts: 2,676
Quote:
Originally Posted by Nords View Post
... 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.
__________________
walkinwood is offline   Reply With Quote
Old 01-24-2008, 08:06 PM   #10
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
Quote:
Originally Posted by walkinwood View Post
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.

Quote:
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.
Nords is offline   Reply With Quote
Old 01-24-2008, 09:45 PM   #11
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
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...
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Old 01-25-2008, 09:34 AM   #12
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
Quote:
Originally Posted by HFWR View Post
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...
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Rebalancing Spreadsheet
Old 01-28-2008, 02:09 PM   #13
Dryer sheet wannabe
grfiv's Avatar
 
Join Date: Jan 2008
Posts: 15
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
__________________
grfiv is offline   Reply With Quote
Old 01-31-2008, 11:47 AM   #14
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
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.
Nords is offline   Reply With Quote
Old 01-31-2008, 11:45 PM   #15
Dryer sheet aficionado
 
Join Date: Aug 2006
Posts: 39
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
__________________
baldeagle is offline   Reply With Quote
Old 02-01-2008, 09:15 AM   #16
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Moemg's Avatar
 
Join Date: Jan 2007
Location: Sarasota,fl.
Posts: 10,032
Quote:
Originally Posted by grfiv View Post
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 .
__________________
Moemg is offline   Reply With Quote
Thanks
Old 02-02-2008, 07:39 AM   #17
Dryer sheet wannabe
grfiv's Avatar
 
Join Date: Jan 2008
Posts: 15
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.
__________________
grfiv is offline   Reply With Quote
Old 02-02-2008, 11:30 AM   #18
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Sep 2005
Location: Northern IL
Posts: 18,276
Quote:
Originally Posted by Want2retire View Post
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
__________________
ERD50 is online now   Reply With Quote
Timing of rebalancing
Old 02-02-2008, 12:14 PM   #19
Dryer sheet wannabe
grfiv's Avatar
 
Join Date: Jan 2008
Posts: 15
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
__________________
grfiv is offline   Reply With Quote
Old 03-04-2008, 01:15 AM   #20
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
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.
Nords is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Budget Spreadsheet Rustic23 FIRE and Money 5 08-25-2007 11:53 AM
Another spreadsheet bug? Cadence FIRECalc support 0 07-18-2007 07:48 AM
Budget Spreadsheet daverph FIRE and Money 1 11-10-2006 03:06 PM
Financial Spreadsheet Developed PatM FIRE and Money 9 11-18-2005 12:38 PM
spreadsheet nnkrealtor Other topics 4 09-18-2005 04:01 PM

 

 
All times are GMT -6. The time now is 09:07 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.