Playing with numbers: Using spreadsheets to learn about money

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.

More about...Planning

Become A Money Boss And Join 15,000 Others

Subscribe to the GRS Insider (FREE) and we’ll give you a copy of the Money Boss Manifesto (also FREE)

Yes! Sign up and get your free gift
Become A Money Boss And Join 15,000 Others
guest
112 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Trevor - 14 Year Old Blogger
Trevor - 14 Year Old Blogger
11 years ago

Its fun for me to play with them too! I love trying out the different combination to see what I can get in the end.

Cory
Cory
11 years ago

I love spreadsheets! I’m an engineer by trade, and so is my girlfriend, so we use spreadsheets for a lot of personal stuff because of our familiarity to them, and their usefulness. We have a snowball spreadsheet on which all of our student/car/personal loans and mortgage are on. This helps us remember how much we’ve decided to overpay, and on which loan. It also gives us payoff dates for each of our loans so we can feel good that we’re approaching goals. We also use a workbook to track costs of finishing our basement and I manually enter ALL basement… Read more »

Luci
Luci
11 years ago

I think the first community college course I take after I graduate with my bachelor’s in December is going to be along the lines of Excel 101. I’ve always been interested in learning how to use spreadsheets, but I just don’t seem to be able to get the hang of it on my own. Are there any books or websites you could recommend?

Chett
Chett
11 years ago

JD,

Not trying to plug my site and you can remove the link if you want, and just try and upload the file (I can’t do that on my end.)On this post there is a link to download some spread sheets I’ve created for budgeting, income comparisons, and other purposes. The spread sheets are all excel, so users must have the program to manipulate the data. Here’s the link.

http://www.my5k5k.com/profiles/blogs/time-to-crunch-some-numbers

Beth
Beth
11 years ago

Thanks for this post! It has some great tips 🙂

I use a spreadsheet for my monthly budget and to keep track of my spending… and to see how much my net worth actually increases each month. It’s a great motivator to save.

Happiness Is Better
Happiness Is Better
11 years ago

I also love playing with Excel. I’m not the best at creating eye pleasing spreadsheets, but I’m usually able to get the job done.

J.D. says that his wife is the smartest person he knows, but it sounds like he is pretty smart himself for making sure he compliments her :).

Good post!

DDFD at DivorcedDadFrugalDad
DDFD at DivorcedDadFrugalDad
11 years ago

Great post.

I love Excel– I use it as my scratch pad for budgets and decisions all the time.

Angie
Angie
11 years ago

I really don’t know how people make decisions without Excel. I have a sheet for just about everything. The most important is my “Snowball” sheet I’d guess you’d call it. It has all my loans (8-9) amortization schedules lined up from highest to lowest interest. It has input what I expect to pay extra each month along with all the minimum payments. It does a few main things: 1. Double checks the amount of my payment which should go to principal/interest. (The bank is NOT always right on this one) 2. Carrys over extra payments as loans are paid off.… Read more »

Momma @ 3princessesmomma.com
Momma @ 3princessesmomma.com
11 years ago

I do all of my finance planning in Excel. It’s the only tool I use for budgeting. If you learn just a little about creating an Assumptions page, you can play around with different variables (like percentage rates of return) by changing one field on the Assumptions tab instead of changing your whole spreadsheet. Here’s a link to the Microsoft tutorial on Assumptions 🙂 http://msdn.microsoft.com/en-us/library/bb839334.aspx

Davidv
Davidv
11 years ago

I have no excel skills. I’ve looked at certain sites to learn excel skills but they don’t relate to what I’d want (personal finance) so I don’t stick with them. If you could do a saturday post to help us non-users out, that would be great. Specifically, I’d love one that you could input your monthly expenses in, and similar to the retirement calendar show me how much cutting cable would mean to my retirement. Now I’m saving $50 a month and assuming I get 8%… That would make small choices today easier to make since I would be able… Read more »

Angie
Angie
11 years ago

I don’t really think its a matter of learning how to use Excel. But rather understanding the fundamental principals and main equations of personal finance. Primarily the different ways of interest calculation. Along with fully understanding all the terms (rates, fees, etc.) of your accounts.

Miss M
Miss M
11 years ago

I’m a spreadsheet geek too, I love to play around with scenarios. Adjust the various savings amounts, expected rate of return, time period etc to see where I might be in 10 or 20 years. I don’t use budgeting software, just my trusty spreadsheet that I’ve been using for years. I prefer them because they can be as simple or complex as you need and tailored however you want.

Lakita
Lakita
11 years ago

I use Excel (or Acess) for almost everything! I actually prefer the Access DB because of the reports and user interfaces I can generate, but I found when sharing them with other people, they preferred Excel. So I started using that to be more compatable. I use Excel currently to track my expenses from Month to month. I also created a “Leave Projection Tracker” for my job. I accumulate x hours of leave per pay period. I travel to teach at a lot of conferences and workshops. I try to project my calendar a year in advance to see if… Read more »

The Personal Finance Playbook
The Personal Finance Playbook
11 years ago

Excel is amazing. I use it to keep track of my rental property accounts – no real accounting necessary. It’s also vital to budgeting/keeping track of your expenses.

frank
frank
11 years ago

Do I like to use excel? Hmm, I would have to say yes 🙂

Kristen
Kristen
11 years ago

I am an accountant by trade and spend most of my days in Excel. That being said I love playing with spreadsheets! I have one to track all of our spending, summarize it on one page, create a budget (using past summary spreadsheets to see where we spend). My hubby and I use this spreadsheet to pinpoint where we are doing well and where we need to cut back. I also have a grocery list spreadsheet that we use every week. I pull prices from our grocery stores online shopping program. We come up with a meal plan for the… Read more »

angie
angie
11 years ago

JD – Great post, and your expletive of “Holy cats!” made me choke on my morning Red Bull!

Grant Baldwin
Grant Baldwin
11 years ago

Great post and thanks for showing us specific examples of how you can use spreadsheets. I’m also addicted to using Excel to crunch some numbers here and there, but these were some great ways to make that happen.

Thanks!

Sam
Sam
11 years ago

Excel is awesome, but since I have no idea how to make it work I’m happy Mr. Sam can make up those charts for me/us. He also can create pie and bar charts which are helpful when I’m using an excel chart to track a specific goal.

Megan Martin
Megan Martin
11 years ago

Comments #2 & #8 both mention a “Snowball” spreadsheet. I would like to learn more about what this is and how you set it up. Any handy links for this concept?

bakednudel
bakednudel
11 years ago

#4 Chet, any other place with the spreadsheets? I don’t want to sign up for a site about running a 5k.

Finally Frugal
Finally Frugal
11 years ago

I love, love, LOVE Excel, and not just because it’s free. I use it for my zero-based budget, to track my monthly expenses, to chart my progress (vis a vis my spending versus my income) and to estimate the time it will take to pay off my $56K in student loans (too long, btw). I publish my zero-based budget on my blog, if anyone is interested in seeing what this is. I also have a ‘vanilla’ version (i.e. none of my personal information) that I’ll email to folks who want to try it out. This type of budget (and the… Read more »

grimsaburger
grimsaburger
11 years ago

I was all into spreadsheets when we were ramping up our snowball strategy, then when we knocked out three or four accounts, I let it slide. Result: that auto loan is still hanging out there. This morning, though, I built a new spreadsheet to track how we’re going to pay it off in nine months.

There are templates out there, but I find it easier to make my own. All I used was a statement showing my last three payments, balance, and the daily periodic rate, to double-check my calculations before projecting it out.

tinyhands
tinyhands
11 years ago

Question #1 is a case of “all things being equal…” except that in the real world all things are not equal. Every institution I’ve ever dealt with has a variable interest rate dependent upon the total funds on deposit. In such a case, it is best to have as few accounts as possible.

J.D.
J.D.
11 years ago

I wrote about a debt snowball spreadsheet in August, for those of you who are looking. I haven’t used this spreadsheet myself. Have any of you?

Jo
Jo
11 years ago

LOVE spreadsheets – I use them all the time at work and home! They make tracking things so much easier.

Chett
Chett
11 years ago

@bakednudel

Sorry, I forgot I had privacy settings on my site. I have opened the site up to the public for today, so you may want to try it again.

Kathryn
Kathryn
11 years ago

I use Quicken to keep me on track for actual movement of money here and there, but I use spreadsheets to play with different scenarios. I too adore spreadsheets. My formulas are crude, but they’ve sure helped me keep track of certain investments.

Steve
Steve
11 years ago

I use Excel all the time. It’s #1 on my windows XP “most opened programs” list. Often I use it instead of a calculator (any time I have more than 3 numbers to combine) because it lets me go back and adjust some of the numbers.

I read somewhere that the most popular use of Excel is to simply make lists.

Joel
Joel
11 years ago

For those of you looking for an excellent free alternative, the OpenOffice.org suite is quite nice:

http://www.openoffice.org/

I didn’t have a copy of Excel, so I’ve been using the OOo Calc program. It can open and edit Excel spreadsheets (and other formats, I think).

No affiliation; I just like what they do. 🙂

DIYJoe
DIYJoe
11 years ago

I have an Excel spreadsheet that I use to keep track of my short-term financial life (not investments/401k etc): Sheet 1: An overall balance sheet. I can look here for the snapshot Sheet 2: My checking account Sheet 3: My budget: Here I have my income broken down into budgeting categories (Housing, transport, Debt, food, household etc). I can adjust the percentage for each category and can figure how much each account gets. Sheet 4: A paycheck split calculator: Since I have two jobs, and earning from the second job fluctuates some, this sheet lets me figure how much money… Read more »

Bob
Bob
11 years ago

I, too, use Excel a lot and recommend that everyone get acquainted with it. However, if you don’t have a copy of Excel try Google Documents. Open a new spreadsheet there and get familiar with the basics. I have learned more about Excel (and MS-Word) from fooling around with the menus and the help function than any classes I’ve taken.

ABCs of Investing
ABCs of Investing
11 years ago

You can count me in as one of those people who put a good portion of their lives on a spreadsheet.

Financial stuff, blog stuff, ‘to do’ list for things around the house…it’s all there!

Rhiannon
Rhiannon
11 years ago

My husband and I keep track of our finances using Google-document spreadsheets. That way, we can access the information from anywhere, on any computer, and even at the same time from different locations. It works almost the same as Excel, and you can transfer the documents to Excel easily. I love it!

Stephanie PTY
Stephanie PTY
11 years ago

I use spreadsheets for just about everything. For example, right now my boyfriend is evaluating job offers from different firms. So I whipped up a spreadsheet, where he can just plug in the salary each firm is offering, how much they take out for health insurance, how much they match for retirement, and the amount he would have to spend on an apartment in that area and VOILA! It breaks everything down for him: taxes, disposable income, everything he wants to know. We keep adding things to it and adjusting it, so it’s really helpful for him.

Moneyblogga
Moneyblogga
11 years ago

I use a basic spreadsheet to track my budget and spending. I have found that the spreadsheet has been a huge contributing factor in helping me to become a more effective money manager. Being able to see at a glance how much I’ve spent over the month is a big motivator.

Jonathan
Jonathan
11 years ago

Wonderful Post, JD. This is why I keep coming back to your site.

thomas
thomas
11 years ago

I remember having to do a retirement calculator in my accounting grad school class. I still cringe at the amount of money that needs to be saved and at the impact inflation has. I noticed that inflation isn’t included in this example.

Neal Frankle
Neal Frankle
11 years ago

Speadsheets can be great. But they can also be dangerous. I work with some folks who get so into the details that they forget about the big picture. Also, they try to project out 10 or more years. Often, you can’t do that. I sometimes fear that people get addicted to the small details and it costs them an accurate view of reality.

Cheap Like Me
Cheap Like Me
11 years ago

I have used spreadsheets to compare things like job offers or health insurance plans. The click-and-drag feature to pull an assumption out to a future date is fabulous!

Currently I have our household budget in a spreadsheet and a debt payment/savings spreadsheet going as well. That one was especially effective when I saw how a drop in income that involved reprioritizing our payments affected how quickly we paid a mortgage vs. what we had hoped to pay. Very motivating for this year’s budget.

Bill
Bill
11 years ago

Won’t using multiple savings accounts mean I earn less compound interest?

As they say, there are no stupid questions…

Jenni
Jenni
11 years ago

I use Excel spreadsheets for all of my finances, as well as decision making, like deciding which flight to purchase. I have spreadsheets for my budget, my loans, and just started one for my ‘price book.’

I think this is what keeps me from trying software like Quicken, Mint, or YNAB. I’m already doing it myself and like my system, and can add or delete categories the way I like.

Mike
Mike
11 years ago

I use Excel spreadsheets all the time, and am almost obsessed with them. I suppose my favourite one is my one of Bills. Each sheet is a running ledger of Bills-Payments-Debts-Balances of each utility Gas, Power, Water, etc. My favourite sheet in that workbook would have to be my mortgage. I track my payments, balance, total interest paid (Since owning my house, in almost 7 years we’ve paid $22,000 in interest on a $65,000. FYI, In your first few years make more than the minimum payments,ouch) and love watching the balance owing to the bank fall. @JD, I love your… Read more »

AY
AY
11 years ago

I use Excel for basic budgeting/tracking where my money is (I use Excel to track “subaccounts”) instead of creating actual separate ones. The one thing I’ve been struggling to figure out is how to set up a spreadsheet that shows actual return on investments over time. (So say if I input numbers every six months, I’d get a snapshot of how I’m doing overall.) I’d like to see if I’m getting the return on my retirement accounts that I’m aiming for or not. [Also… I’d like to someday understand how my own contributions factor into my ROI.] I think Mint… Read more »

Glenn Carver
Glenn Carver
11 years ago

The Small Business Administration says that less than 7% of business owners have or understand financial statements. Financial literacy is key to running a business, but it barely taught in school outside of a specific major. Hmmmmm……..

Jeremiah Bell (Digital Trainer)
Jeremiah Bell (Digital Trainer)
11 years ago

I wrote an article about how compound interest works for weight loss and muscle gain.
http://www.digitaltrainer.ca/blog/2009/01/pumping-iron-is-money-in-the-bank/

Cool to see you are applying the same spreadsheet principles to fitness.

@Joel: I agree with openoffice. What a great program, I recommend it to my students all the time.

Natasha
Natasha
11 years ago

Hello All –

I went to the Microsoft site, and downloaded a free budget spreadsheet. I use the “Monthly Family Planning Budget”, but there are other very good ones.

http://office.microsoft.com/en-us/templates/results.aspx?qu=budget&av=TPL000

Also, there is a site named vertex42 that has a great budget spreadsheet as well.

I hope this helps!

Leon
Leon
11 years ago

Check out openoffice.org spreadsheet if you want a non microsoft alternative. Has many of the same features. Completely free and open source.

DMurry
DMurry
11 years ago

Excel is wonderful. I’ve had hours of informal and formal training with it. One of the latest spreadsheets I made was for my recent hunt to purchase a new/used car so I could tell the true cost of the car.

It can be found at the bottom of the post here: http://www.dmurry.com/2008/08/top-10-new-car-buying-tips

The document can be opened with Excel, Open Office or Google Docs. It’s not formatted the best, but works well to get the job done. I prefer it over using online calculators because I can easily compare multiple cars, interest rates, and terms side by side.

Brian
Brian
11 years ago

I have been using spreadsheets to manage basic tracking of expenses. I use it to track income, gas, receipts and tuition. I have also been working to develop my own programs to do it for me, storing the data in a secure database. However, i love the idea of being able to use google docs to access my data from anywhere. Your post has got me thinking a lot about how I want to use spreadsheets more.

shares