One of my favorite personal finance tools is the spreadsheet. Although I’m no Excel master, I’ve found that I can create a spreadsheet to find answers for many money questions that I have. If I run into problems, I ask Google…or my wife. (Kris took an Excel training course.) Here are some recent questions GRS readers have e-mailed me that could be answered in just a few minutes playing with formulas:

  • “Won’t using multiple savings accounts mean I earn less compound interest?”
  • “I found a great deal on a used car. Should I buy it, or should I fund my Roth IRA?”
  • “I have $5,000 in an HSBC Direct account. Does it make sense to switch to a bank with a higher rate?”
  • “How much does it really cost to pay the minimum on your credit card bill?”

I know that Microsoft Excel and its ilk can be intimidating, but I’ve found that an hour playing with numbers can yield excellent results. Not only do I learn a little more about spreadsheets, but I can find answers to some of my financial questions. Let’s look at some examples.

Subaccounts and compound interest
First, let’s tackle a problem that confuses a lot of people (including my wife, who is the smartest person I know). Jules recently wrote to ask:

Regarding ING and multiple accounts: Would the overall interest earned be different if you have the total amount in one account versus having the same amount split into several subaccounts? I love the subaccounts, but am curious if I’m decreasing my monthly interest.

This is a fairly simply spreadsheet to construct. For this example, let’s compare a single $10,000 investment to the same total divided among three different accounts (labeled Split One, Split Two, and Split Three). A few minutes of formatting, and we can see the results:

As long as all of your accounts are earning the same interest rate, you’ll earn the same compound return over 100 small accounts as you would over one large account. This fools a lot of people, but it’s true. 

The cost of waiting one year
What about investing? How much harder did I make it on myself by waiting to begin my retirement savings until I was nearly 40 than starting at age 20? What happens if one year I choose to use my money to buy a car instead of invest for retirement? Again, a spreadsheet can help us find the answer.

If I were 20 years old and made $5,000 investments every year until age 65, and I earned an 8% return after taxes (just assume with me), I’d have $1,932,528.09 saved at retirement. But I’m not 20 year old. I’m 39. If I still wanted that $1.9 million at retirement, how much do I need to save?

Holy cats! I’d need to contribute $24,170 a year for the next 25 years in order to catch up with my hypothetical younger self. Sure, my earning power has increased since I was 20, but it hasn’t increased that much. There’s a reason I marvel at the extraordinary power of compound interest. A spreadsheet makes it clear.

Notes: This “cost of waiting” spreadsheet is based on one from by JLP at All Financial Matters. Remember, even if you’re old like me, it pays to start investing now instead of waiting.

Rate chasing
Some people enjoy “rate chasing”, tracking down the best interest rates on savings accounts and certificates of deposit. Different banks adjust their interest rates at different times, so it can pay to hop from one to the other. But how much does it pay?

The following spreadsheet shows the annual interest earned on different deposit amounts from different sources. All are actual recent savings rates (CCFCU is my local credit union) except the last, which is a typical rewards checking rate at the moment. (ING Direct would like me to note that their current rate is not what is shown in the image below. That rate was current when I created the screenshot.)

For somebody living paycheck-to-paycheck, chasing the best interest rate doesn’t make sense. If you only keep about $100 in the bank, you only save a few bucks a year by moving from the worst rate to the best. If, however, you had a million dollars (and for some reason had it in a bank instead of a higher-yielding investment), you could earn thousands of dollars a year by chasing the best rate.

Most of us fall somewhere in the middle. For myself, I’m happy to continue using ING Direct based on my current balances. But if I had substantially more in savings, I might look for a better deal elsewhere.

Playing with numbers
Some of you are probably frustrated that I’m not showing how to actually build these spreadsheets: the formulas, etc. If there’s enough interest, I may do that on a Saturday entry sometime. For today, though, I wanted to demonstrate that you don’t need fancy web calculators or personal-finance programs to answer certain questions. All you need is Google Docs.

“Excel is awesome,” Kris said when she saw me working on this post. These are just a few examples of how you can use a spreadsheet to explore your finances and answer questions about money. You can do a lot more. In fact, many GRS readers have told me they keep their entire financial lives in a spreadsheet. (And here’s a post from the early days of GRS about handy personal finance spreadsheets.)

It can be a lot of fun to play with a spreadsheet. (Well, for me, anyhow.) What about you? Do you use a spreadsheet to track your money? To explore “what if” scenarios? Do you have any tips or tricks you can share?

It’s spreadsheet day for me today! I also wrote about my love of fitness spreadsheets at Get Fit Slowly.