Simple Portfolio Rebalancing Spreadsheet Template (Google Drive)

gsheetsUpdated. Automated portfolio management services like Wealthfront and Betterment will help you manage a diversified portfolio of low-cost index funds for a fee. While I understand their appeal for those that wish to outsource that task, I choose to maintain my own diversified portfolio of low-cost index funds. I enjoy having full control of all investment decisions, and I like saving the management fee (and adding that money to my snowball).

An important part of this DIY portfolio management is staying close to your target asset allocation. I use a very simple Google Spreadsheet to track my portfolio. Here is the direct link and it is also embedded below. Yellow cells are those meant to be edited.

(Download a free copy: I am sharing this spreadsheet online – free of charge – in read-only format. However, please make a copy of it using the menu option File > Make a copy or download it as an Excel file using option File > Download as). Any requests for edit access to the original public spreadsheet will be denied, because you would be changing the appearance for everyone.)


Here are some guidance on how to use the spreadsheet:

1. Decide on a target asset allocation. Don’t use the generic one I put above. There is no perfect portfolio. You can find plenty that look great based on history at this moment, but that will not be the perfect portfolio 5, 10, 25 years down the line. The best portfolio is the one that you can stick through even after your fanciest asset classes have negative returns for 5+ years.

Here are a few model portfolios to get you started. Below is what I have settled on for myself. Details here. You only have to enter this once as long as your target asset allocation stays the same.

2. Enter your total balances for each asset class. The easiest way to grab my holdings from multiple brokerage accounts is to use a aggregation service like Personal Capital (review). If you don’t have that many accounts, simply log into each individual website and add up your totals by asset class.

You could solely rely upon a service like Personal Capital to manage your portfolio, but I tend to use some specific asset classes like “US Small Value” or “Emerging Markets Value” which Personal Capital does not recognize. I do enjoy the fact that it pulls in all of my holdings and balances automatically into one screen and is always updated.

3. Check out the actual breakdown vs. your target breakdown. The spreadsheet shows the current actual percentage breakdown vs. your target breakdown, as well as the dollar amounts of any differences. A positive number means you need to buy more to reach your theoretical target (negative means sell). In the fictitious example shown, I might feel that I was close enough that I wouldn’t really bother with any rebalancing. If things were really off, I could buy/sell as needed.

3. Rebalance with new cashflow, dividends, and interest. Choose your frequency of “forced” rebalancing. By using this spreadsheet, you can see which asset classes should be invested in currently to bring you back towards your target asset allocation. This is where you should invest any new cashflow (i.e. paycheck, dividends, rental income, or interest that your portfolio generates).

In addition, you can rebalance by selling some asset classes and then buying another. I try not to sell too often as to avoid capital gains taxes. You can do this on a set calendar basis such as annually on your birthday or quarterly. Another method is to only rebalance once your percentages are off by a certain amount, like a tolerance band of +/- 5%. I personally check in quarterly to see where I should invest any new cashflows, and if things are really off then I rebalance by selling something at most once a year. If you have sizable taxable holding, you could also attempt some tax-loss harvesting during these check-ins.

Recap. If you are managing your own portfolio, it is important not to stray too far from your target asset allocation. In order to know where you should invest new funds, I track my portfolio in two ways. First, I use Personal Capital for a real-time, daily snapshot of my holdings. Second, I manually update this spreadsheet each quarter and print out a copy for my permanent, physical records. This takes about 15 minutes every 3 months. Using these two methods, I maintain complete control over my portfolio and I don’t have to pay any management fees to a robo-advisor.

FileThis App Review: Automatically Backup Your Online Statements


A few months ago on my post about Paperless vs. Paper Statements, I received this helpful comment from reader Daveraham:

I like the services It’s setup similar to MInt, where it stores account information, but instead of fetching dollar amounts and transactions, it grabs every statement available and stores them where you direct. Personally I store it an evernote account and then periodically pull it off to store on a removable HD that get’s stored in a fireproof box. Overkill?? Sure…. But its the point. You want to keep that snapshot of data for a long period of time.

I made a mental note to check the site out and… promptly forgot. I was again reminded in this Liz Weston article about apps to organize your financial life. In November 2015, FileThis announced their 2.0 version with new features. You can use the website, iOS app, or Android app (1.0 version only for now).

FileThis is now one of many “bill organizers” that ask for your account passwords in order to sift through your accounts and remind you of due dates. Personally, I don’t need or use due date reminders. I sit down at the end of every month, read through all my paper statements, track expenses, and pay my bills. I’m an old fart like that (although I do use free online billpay).

I previously shared that I maintain physical statements for critical financial accounts and have it mailed to a secure PO Box. But I also have several other financial accounts which are either dormant, temporarily opened for reviews or experiments, or have low balances which are set to paperless. Ideally, I would still log in and download those PDF statements every month and back them up. But I never do.

FileThis will log in and automatically download all your paperless statements and then save them to your cloud service of choice: Evernote, Dropbox, Google Drive, Box, Amazon Cloud, and more. You can even use their in-house storage (500 mb free). The cost options:

  • Free for up to six (6) connections. Checks weekly.
  • $2 a month ($20 a year upfront) for up to 12 connections. Checks weekly.
  • $5 a month ($50 a year upfront) for up to 30 connections. Checks daily.

Besides things like bank accounts, credit cards, and brokerage statements, FileThis will grab stuff from your mortgage provider, car loan servicer, cell phone bill, utility bills, insurance bills, and even online shopping accounts like Amazon. An added bonus is that they will even grab tax documents like 1099 forms.

I linked up a few accounts, the list is relatively extensive but it couldn’t find a local credit union. Here are some screenshots from my website and smartphone app.



Remember that the actual files are on your cloud service. Here’s a screenshot from my Dropbox app. The files are stored in the folder Dropbox > Apps > FileThis.


This is pretty cool. The initial download basically grabbed all the older documents that were available as well (up to last 3 years, supposedly). They’ll even grab PDF statements if you also get mailed paper statements (assuming they are available), giving you an additional backup copy.

By allowing backups directly to a third-party cloud service (Dropbox in my case), I will still have all of my online statements even if FileThis shuts down some day (remember Manilla?).

The trade-off here is that another FinTech startup has your account logins and passwords. Their security measures seem fair enough (encrypted SSL transmission, passwords are encrypted on server, the documents can be stored at your cloud service). I already track my paperless accounts in real-time with Mint, but I am willing to make this trade-off as I think it’s worth it to have my old statements backed up for me. (Why can’t Mint do this for me too?) The only other service I know that offers something similar is Finovera, but I think they store the statements on their own servers as opposed to your personal Dropbox.

As an existing user, if you sign up using my referral link, both you and I will receive an additional free connection (so you’d have a total of 7 free to start) and an additional 250 mb of free in-house cloud storage.

Portfolio Charts Visualization Tool: Returns vs. Time (Holding Period)

When investing in stocks and bonds, it is important to take a long-term perspective. We’ve all heard that phrase. A new tool called lets you create charts that make it easier to visualize the relationship between returns and holding periods. Created by a fellow named Tyler, found via The Reformed Broker.

With the Pixel chart, you can customize any asset allocation and see that portfolio mix’s returns over a multitude of timeframes. Here’s the chart for The Swensen Portfolio, which is the closest “lazy portfolio” to my personal portfolio – 30% US Total, 15% Foreign Developed, 5% Emerging Market, 20 US REIT, 15% 5-Year Treasuries, 15% TIPS.


You can see that depending on your starting year, the returns over the next 1-9 year period could be pretty rough. But as long as you held for 10 years or more, you always got a positive real return above inflation. You can also see that the often-promised 5% real returns aren’t always guaranteed, although historically if you held on for 20+ years your odds were pretty good.

You may recall a similar style of chart from the NYT and Crestmont Research which includes additional data going back to 1920:


My favorite style is the Funnel chart:

The Funnel chart shows the changing uncertainty of compound annual growth rates over time. This demonstrates how long you may need to hold a portfolio to experience the average long-term returns it advertises. It also provides a nice snapshot of the range of 1-year volatility.

Here’s the Funnel for the same Swensen Portfolio:


The funnel chart also supports the notion – in an even simpler way – that if you can take a long-term perspective, your risk of losing money should decrease. Here’s a similar chart from the classic investing book A Random Walk Down Wall Street that was one of my early blog posts:


Finally, the Hurricane chart allows you to simulate what would have happened to your portfolio balance if you made annual withdrawals, such as in a retirement scenario.

Warren Buffett is another famous supporter of taking the long-term view. From a recent CNBC interview:

Buffett, who looks to buy stocks or business for their long-term prospects, said recent weakness in the market does not concern him.

“Stocks are going to be higher, and perhaps a lot higher 10 years from now, 20 years from now,” he said, adding that’s why he does not try to time the market.

Hopefully for those investors with a long runway ahead of them, this new tool will help you view your portfolio in a more patient manner. I’ll try to remember it when the next market panic arrives.

Expected Returns by Asset Class Tool by Research Affiliates


In the current environment of historically-high US stock valuations and historically-low interest rates, there is a lot of discussion about what investors should expect in regards to future returns. Predictions are a dime a dozen, but some are better supported than others. Research Affiliates has interactive Expected Returns tool where they freely share both their forecasts and detailed methodology. For example, here is a PDF of their equities methodology.

1st Quarter 2015 Equities 10-Year Forecast. Currently, their forecast for equities predicts that US Large-Cap and US Small-Cap stocks will have expected real (inflation-adjusted) returns of below 1% annually. Developed European and Asian Country stocks (MSCI EAFE) have significantly higher numbers, while Emerging Markets as a whole offer the best risk/return ratio (Sharpe ratio). I’m not into single-country bets, but it appears Russia is the one to take if you have a high appetite for risk.


1st Quarter 2015 Fixed Income 10-Year Forecast. Currently, their forecast for fixed income predicts that both long and short US Treasuries will have expected real (inflation-adjusted) returns of basically zero. You might get under 1% real with a broad US bond fund like AGG or BND, or reach for a little more return with a high-yield junk bond fund but with roughly the same Sharpe ratio. The gambler’s choice appears to be Emerging Markets currencies (EM money market funds), which they predict will appreciate relative to the US dollar in the next decade. This is followed by Emerging Market bonds issued in local currency.


I like reading the theories and fundamental arguments for these forecasts, and consider the numbers as part of the big picture but not necessarily something to act on directly. I also keep track of other forecasts, including the follow which were previously mentioned on this blog – Jeremy Grantham / GMO 7-Year Forecasts (free registration required) and the Rick Ferri / Portfolio Solutions 30-Year Forecasts. The most recent GMO numbers also give the expected-returns edge to Emerging Markets in both the stocks and bond worlds (risk is not directly assessed).

Best Buy vs. Rent Calculator Ever? Interactive & Fully Customizable


There are a plethora of buy vs. rent calculators out there, but virtually all of them make at least some fixed assumptions. They might assume that you could invest the difference between renting and buying in the stock market at 8% return while you disagree, or they might assume that your property tax rate is 3% when it is only 0.5%.

The New York Times already had a pretty good one, but their new Buy vs. Rent calculator is the most interactive, user-friendly, fully customizable version that I have ever seen. Here are the factors that it lets you adjust:

  • Home details (price, length of ownership)
  • Mortgage details (rate, downpayment size, length)
  • Future growth rates (Home price appreciation rate, rent appreciation rate, overall inflation rate, investment return rate)
  • Taxes (Property tax rate, your marginal income tax rate)
  • Transaction costs (closing costs on purchase, commission paid on selling)
  • Costs of homeownership (maintenance, HOA fees, utilities covered by landlord, homeowner’s insurance)
  • Costs of renting (security deposit, broker’s fee, renter’s insurance)

If I could find a flaw with the calculator, it would be that you now have the power to tweak your assumptions to reach your desired answer of renting or buying. “Well, if I adjust investment return a bit higher, and I reduce the commission to sell with a discount real estate agent, and stay in there a couple extra years… we should buy!”

Of course, an accompanying NYT article points out that buying a home isn’t all about the numbers.

Estimate Your Portfolio Personal Rate of Return – Calculator

Updated and revised for 2014. Some of you may be wondering how well your specific portfolio performed last year (or over any specific period of time). Let’s say you started the year with $10,000 and put in another $5,000 through 10 different deposits spaced throughout the year, and ended up with $16,000. What was your rate of return? Your main goal is simply to separate the effect of new deposits (or withdrawals) and your actual return from investments.

Figuring out your exact personal rate of return requires you to know the exact dates of all your deposits and withdrawals, along with a financial calculator or spreadsheet program with an IRR function (example here). However, for a quick and simple estimate of your returns, try this calculator instead:

Initial Balance: $
Total Deposits: $
Total Withdrawals: $
Final Balance: $
Time period:   year(s)
Your estimated annualized rate of return:   %


  1. Get your initial balance. This is probably from your brokerage statements. Try January of last year.
  2. Tally up any deposits or withdrawals. For example, let’s say you know you put $3,000 in your Roth IRA and also 5% of your $40,000 salary into a 401(k). That would be $3,000 + $2,000 = $5,000. That’s it, you don’t need to worry about looking up the specific dates and amounts.
  3. Get your final balance. Your December statement is probably available already.
  4. Find the time elapsed (in years) between your initial and final balances.
  5. Hit Calculate. An estimate of your annualized return is instantly given.

How Accurate Is This Estimate?
The calculator assumes that the inflows and outflows are spread evenly around the middle of the year. I originally saw this method in the book The Four Pillars of Investing (review). However, unless the deposits and withdrawals are very large as compared to the initial balance, the estimates are actually pretty good.

For example, let’s say that you start with $100,000 on 1/1/13, and end up with $120,000 on 1/1/14. If you had net deposits of $10,000 during the year, the calculator above would estimate your return at 9.52%. If the $10,000 was actually deposited all at once on one of these specific days, you would get the following exact returns:

Deposit Date Exact Return
1/1/13 (very first day) 9.1%
6/04/13 (middle of the year) 9.5%
1/1/14 (very last day) 10%
Estimate 9.5%


Also check out the rest of my Tools and Calculators.

True Cost of Holiday Shopping Calculator

Time for my annual Black Friday Buzzkill calculator! 😉 Can you hear that sound? Sleigh bells a-jingling? Carol singers? No, it’s credit cards a-swiping as part of what is now officially BUY BUY BUY season.

Here’s a mental trick that I use to temper my “self-gifting” urges. We know that every dollar saved now will be worth much more in the future. I made this calculator to help visualize this fact and push me to forgo short-term (temporary) pleasure for long-term gain.

Step 1: Pick Your Purchase:

Name Your Own Impulse Buy Price

Step 2: Pick your estimated annual return (default is 6%):
4%     6%     8%
Step 3: Pick your time horizon (default is 30 years):
10 years    20 years    30 years    40 years
Assuming a 3% inflation rate, the inflation-adjusted TrueCost™ of your impulse buy in years is:   

$600 for a cashmere sweater? $7,000 Flatscreen TV? Ouch. This is not to say the occasional splurge is never worth it. (I do like me a steaming hot Peppermint Latte.) Perhaps it is. But I hope that this calculator can provide a little perspective while you are barraged by retailers to buy stuff you really don’t need. Who cares if you get 30% off when it’s so expensive?

Tracking Inflation: Consumer Price Index vs. MIT Billion Prices Project

AFter calculating the new savings bond rate, I noticed that from March 2012 to March 2013 the inflation rate per the Consumer Price Index was only 1.5% over the past year. Whenever you see the government announce a relatively low inflation numbers, there will always be people shouting “the government manipulates the inflation data!”. I looked into this previously with my post Does The Government Underestimate Inflation Through The CPI? Short answer: Yes they do, but maybe not in the way you think.

Usually, this is followed by the anecdotal argument “Does gas ever go down? Does your rent ever go down?”. It certainly feels like prices are rising quicker than that. My water bill just got hiked another 10%. The thing is, we always notice the increases, but tend not to notice when prices drop. When something is cheaper, we just chalk it up to being great bargain hunters. Truth is, gas prices did go down for a while.

Another way to keep an eye on inflation is with MIT’s Billion Prices Project (previous post) which tracks prices in real-time by grabbing them from websites. By checking on 50,000+ different prices daily covering everything from prescription drugs to clothing to real estate, this alternative inflation measurement has the potential to keep governments “honest” with their numbers.

Index Values: CPI vs. BPP

[Read more…]

Quicken Mac 2007 OS X Lion Compatible Version Released

I know that there was some noise when the Mac OS X 10.7 Lion was released and Quicken 2007 for Mac was reported by Intuit to be incompatible with any computer running the new operation system. Next, they release Quicken Essentials for Mac which was a neutered version of Quicken which quickly angered long-time customers even more. So they promised they would rewrite it to work on OS X Lion. Well, it has finally arrived. Cost is $15. Thanks to reader Paul for the tip.

Data migration. According to their FAQ, users can import data from Quicken 2005, 2006 or 2007 for Mac, as well as from Quicken Essentials for Mac. File conversion is not possible for Quicken 2004 for Mac and prior versions.

So now you can pay more to use their 5-year old software, hurray! I still think it’s pretty clear that Intuit isn’t going to spend too much more effort improving Quicken, instead they are spending more money on which is online and free to users (ad-supported). Has anyone tried it out yet?

The Problem With A Retirement Based On The Stock Market

As far as retirement calculators go, the new one over at the Scottrade Knowledge Center is pretty nice. It does the whole Monte Carlo thing, running theoretical scenarios based on historical data. There are fancy interactive sliders that let you input your current portfolio balances, annual contributions, and your future expenses. The result is a pretty chart:

But the same problem always occurs whenever retirements depend heavily on market returns. If future returns are on the low side of history, I could end up broke* and eating dog food by age 90. If future market returns are high, then I could die with $10 million in the bank. What the heck do I need with that much money at age 90?

One way to avoid this is to have a very conservative portfolio of safe and short-term bonds (or TIPS). This has the slight inconvenient problem of requiring a very high savings rate. (Or lottery winnings, a large inheritance, or other windfall.)

Now, it would be nice to have a way to share the risk with others out over longer periods of time. Give up some of the potential upside, in return for some downside protection. This usually involves an insurance company (annuities) or the government (Social Security). Which do you want to trust with a big chunk of your hard-earned money? It’s a tough call. 🙂

* This isn’t technically true. I’m sure in reality, if my portfolio was doing so poorly, I would adjust my spending however I could. But I would have to decrease my standard of living.

What Is Your Portfolio’s Current Asset Allocation?

If you haven’t been keeping close track of it, your portfolio’s asset allocation may have shifted significantly over the past year. Your relative mix of assets like stocks, bonds, or real estate has a great impact on the volatility and expected future return of your portfolio.

Morningstar has a bunch of helpful tools for managing your investment portfolio, but many of them require a paid membership. However, one handy trick is that anyone can use many of these premium features for free at the T. Rowe Price website by signing up for a free account with nothing but an e-mail address.

Portfolio Manager
This tool lets you enter all your portfolio holdings, which it then stores for you and allows you to track it with automatically updated prices. You can either track all your future transactions as you go, or just input your updated holdings every few months like I do.

Portfolio X-Ray
Once you enter your holdings, simply look for the Portfolio X-Ray tab and you’ll have a complete breakdown of the true asset allocation of your overall portfolio. Does your “small cap” fund really own a bunch of mid-caps and large-cap funds? X-Ray will reveal your true exposure to stock style (i.e. Small/Mid/Large, Growth/Blend/Value), geographical regions (i.e. Japan, US) , stock sectors (i.e. Telecom, Energy), average expense ratio, and more.

If you’d rather have a quick peek without needing to register at all – but also without the ability to save your portfolio – try the Morningstar Instant X-Ray tool.

If you already have a target asset allocation in mind, now might be a good time to to rebalance your assets back towards that target. Rebalancing is a way to maintain the risk/reward balance that you have chosen for your investments, and also forces you to buy temporarily under-performing assets and sell over-performing assets (buy low, sell high). If you are looking for a bit more guidance, here are my favorite posts on investing.

MIT’s Real-time Inflation Calculator

A lot of people are worrying about inflation or deflation in the future. The most widely used definition of inflation is the Consumer Price Index, which is published monthly by the Bureau of Labor Statistics and is based on a basket of consumer goods using price surveys from cities around the country. This takes a while, so the CPI for December would be published in mid-January.

Professors Roberto Rigobon and Alberto Cavallo at the MIT Sloan School of Management started the Billion Prices Project which, directly pulls data from online retailers from around the world. In the US, the software is tracking 550,000 items from 53 retailers. The best part – since it’s all automated, the numbers are updated daily! The goal is to predict the CPI before they even announce it. You can see from the charts below that the two track reasonably well together.

Daily BPP Index vs. CPI


Annual Inflation (over last 365 days)

If they start to vary widely, which one should be considered inaccurate? Via the NY Times.