on | 23 COMMENTS

Boost Your Kid's Money IQ with a Simple Spreadsheet

Sample Virtual Family Bank Spreadsheet

Let’s face it. Your kid’s piggy bank isn’t cutting it any more.

Sure, it was a fine first introduction to the most rudimentary concepts of money, but your kid is a smart cookie. Basic lessons learned.

So when are you going to take your kid’s money IQ to the next level? Your youngster is perfectly capable of grasping the following and more:

  • How bank accounts work.
  • How to track income and spending as numbers instead of coins.
  • How to avoid costly debt.
  • How to put money to work with compound interest.
  • How to allocate money to specific financial goals.

That sounds like a daunting parental task, but it doesn’t have to be.

You can do it all with a simple spreadsheet. That’s how I started out with my kids, and I’m not alone. The wildly popular financial blogger Mr. Money Mustache is doing the same. He’s using his “Bank of Mr. Money Mustache” spreadsheet to teach his young son about money too.

If you’re already sold on the family bank spreadsheet concept, and you’re just looking for a handy Google Docs template, go ahead and click here to grab a copy. You can skip down to the last section of this post for a quick primer on how to use it. I’d love to hear from you and keep improving it. Leave your feedback and suggestions in the comments below.

For those still warming up to the idea, I’ll walk you through how to introduce your child to a “Bank of YOUR-FAMILY” spreadsheet. You’ll learn how you can use it to level-up your kid’s financial literacy.

How Bank Accounts Work

The first step in weaning your kid from the piggy bank is to explain the basics of bank accounts. Here’s how that conversation might go between Mom and little Suzy in the Smith family:

Mom: Suzy, instead of keeping your money in your piggy bank, hand it over to me. I’ll keep it for you in what we’ll call “The Bank of Smith”.

Suzy: Why are you taking my money? I catch Joey doing that sometimes.

Mom: I’m not taking your money away. I’m just keeping it in a safe place so your brother Joey can’t steal it, and you can’t lose it. That’s why us grownups keep our money in banks instead of our sock drawers. It’s safer.

Suzy: How do I know how much money I have if I can’t see it any more? With my piggy bank, I can dump it out and count it whenever I want.

Mom: Good question. At The Bank of Smith, your money will be hidden from bad guys (and brothers) in something we call a bank account. See how that word includes the word count? A bank account counts your money for you automatically. The amount you have right now is called your current balance. Want to know how much you have? Just ask me “What’s my current balance?” anytime, and I’ll show you.

Suzy: OK. What’s my current balance?

Mom strolls over to her computer and opens up the Bank of Smith spreadsheet.

Mom: Let’s open up your new account in The Bank of Smith. Look here. This tells you what your current balance is. How much money do you have?

Suzy: Zero dollars?

Mom: Yep.

Suzy: Thanks Mom. I think I’ll just stick with my piggy bank.

Uh oh, time to move quickly to topic #2.

Tracking Income as Numbers

Mom: Hold on Suzy. Your bank account has a zero balance because you haven’t given me any money yet. Let’s count up all of the money in your piggy bank. How much do you have?

Suzy: 6 dollars and 35 cents.

Mom: OK, now hand it to me. When you hand your money over to the bank, it’s called a deposit. To make sure we remember how much you handed me and to make sure I don’t accidentally mix it up with Joey’s money, I’m entering a line in your bank account that says you handed me $6.35 today. Now, look what it says here for your current balance.

Suzy: 6 dollars and 35 cents.

Mom: That’s right. Your balance went from $0 to $6.35 because you deposited that amount from your piggy bank. Every time you deposit more money, we’ll enter a new line here with a positive number, and your balance will go up.

Suzy: OK, that sounds better. So whenever I find a quarter in the sofa or I get a five dollar bill from grandma for my birthday, I can just bring it to you. You’ll deposit it in my account by entering a new line, and I’ll have that much more money in my account.

Mom: Exactly. Oh, and by the way, I’m also going to stop handing you bills and coins for things like allowance, chores, and odd jobs.

Suzy: Now I really want my piggy bank back!

Mom: Oh, don’t worry, you’ll still get paid. What I mean is I won’t have to fumble around looking for spare bills and change all the time just so I can hand them to you and have you hand them right back to me to make a deposit. Instead, I’ll just enter a line in your account that makes the balance go up by the right amount. That’s called a direct deposit. No fussing with coins or dollar bills. It’s how us grownups get paid at work. We never even touch the actual money.

Tracking Spending as Numbers

Suzy: Wow, that sounds like magic. So, what happens when I need money to buy something?

Mom: Let’s say you want a 5 dollar bill to spend at the candy store. You come to me and say you want to take $5 from your bank account. As long as your balance is greater than $5, I’ll hand you the cash. That’s called a withdrawal.

Suzy: Oh, I see. It’s just like that machine that gives you free money.

Mom: Well, sort of. That machine is called an ATM, and I’m like your ATM for the Bank of Smith. But your ATM doesn’t give you free money. Guess what happens to your bank account balance when you withdraw the $5?

Suzy: Oh, right. It probably gets smaller.

Mom: Yep. Exactly. I enter a line in your account with a negative number showing that you took out $5, so your balance will go down by that amount.

Suzy: Got it.

Mom: There is another way to buy that candy without withdrawing the $5 from your ATM. I can make the purchase for you using my card.

Suzy: Cool! So I can get candy for free just like you get stuff free with those cards?

Mom: Nope. Guess what I’m going to enter in your account after that purchase?

Suzy: One of those lines that makes my balance go down, right?

Mom: Bingo! And that’s exactly what happens when us grownups use our cards to make a purchase. Our bank balance goes down. So, even though you may never see any actual coins or bills handed over at the store, real money is still coming out of a bank account. Never forget that! There’s no magic free money. Ever.

Suzy: Except from the Tooth Fairy, right?

Mom: Yes, of course — there is that one exception.

How to avoid costly debt.

Mom: Oh, and one more thing when it comes to spending: always check your bank balance first! Make sure you have enough before you make a purchase. If you spend more than you have, the bank will charge you a penalty. You might even end up with less than zero dollars! That means you owe the bank money.

Suzy: That sounds bad. What happens if I owe the Bank of Smith money?

Mom: That’s what we call a loan. You have to pay the bank back for the loan using your allowance, chore, or odd job money. That’s not all. Loans aren’t free. Every week that you still owe money, The Bank of Smith will charge you more money. That’s called interest on your loan. You’ll want to work extra hard to pay that money back as soon as possible so you don’t have to pay back even more money because of interest.

Suzy: Yuck. I hate interest!

How to put money to work with compound interest.

Mom: Well, interest is bad if you’re the one who has to pay it, but if you’re the one being paid interest, it’s pretty awesome.

Suzy: Oh. How can I become one those people being paid interest?

Mom: Simple. All you have to do is save your money in the Bank of Smith. At the end of every week, if you have money in your account, I will pay you some interest.

Suzy: Really? That is awesome! How much?

Mom: Every week, I’ll multiply your current balance by this small number called the interest rate to calculate the amount of interest to be added to your account. The cool thing is, the more you save, the more interest you’ll make. The interest that gets added to your account makes your balance bigger. That means as long as you don’t take any money out during the week, your next interest payment gets bigger too. That’s called compound interest because you’re getting paid interest on the interest you already earned in the past. It’s like your money is working for you without you having to even lift a finger.

Suzy: Hey, didn’t you say only the Tooth Fairy hands out free money?

Mom: Oh, yeah — I forgot about the magic of compound interest when I said that. There are indeed two exceptions to the No-Free-Money rule, as long as you are willing to save.

How to allocate money to specific financial goals.

Suzy: So what should I do with all this money I’ll be saving?

Mom: Ah yes. That brings us to the last thing I wanted to talk to you about: financial goals. A financial goal is just something you plan to do with your money in the future. It could be something soon like going to a movie this weekend with your friends, or something a little ways off like donating some money to the animal shelter over the holidays, or even something very far off like helping to pay for college or a car when you’re older.

Suzy: I like all those goals. Can I save for all of them at once?

Mom: Yep. And there’s a neat trick for keeping track of how you’re doing on each one: split your money into separate bank accounts. For example, you can have one account for smaller, everyday type spending — like the movies. Call that your “Spending” account. You can have another account for your occasional donations. Call it “Giving”. You could have a third account to help save for college called “College Savings” and so on. Then, when you get an allowance, chore, or job payment, you can split that payment between your accounts — maybe half to spending, a quarter to giving, and the remaining quarter to college saving. That way, every time you get paid, you’ll fill each account up just a bit more and you’ll move closer to all of your goals at once. Here you can see all your accounts on separate pages in the Bank of Smith, each with its own goal and balance. Make sense?

Suzy: Yep. I think I’m going to like the Bank of Smith. But, Mom?

Mom: Yes?

Suzy: Can I still keep my piggy bank? It’s cute.

Mom: Of course. In fact, you can still use it to collect spare change just like I do with that jar on my desk. Every time your piggy bank fills up, you can dump it out, count the change, and make a deposit in the Bank of Smith. That way, you can put your coins to work and start earning some sweet Bank of Smith interest!

Suzy: Thanks Mom!

Suzy scampers off to play nicely with her little brother. Mom relaxes peacefully. Birds chirp. And the kids grow up to lead debt-free, financially independent lives filled with joy.

The end.

OK. Ready to make that lovely story a reality in your home? Well, I can help you with the spreadsheet part at least.

How to Build Your Family Bank Spreadsheet

I’ve built a simple Google Docs template for you. All you have to do is copy some things, fill in the blanks, and click on some custom menu items to insert automatically calculated entries.

Just follow these steps:

  1. Make your own copy of the spreadsheet by clicking here. You’ll land on a Google page asking if you’d like to make a copy of the “Bank of YOURFAMILY’ spreadsheet in your Google Drive. Click the button to do so.
  2. Rename the spreadsheet from “Copy of Bank of YOURFAMILY” to something suitable (like “Bank of Smith”) by clicking on the spreadsheet name in the upper left.
  3. Make a copy of the account template sheet for each account in your family bank by right clicking on the “Account Template” tab at the bottom of the document and selecting Duplicate from the menu. Rename the tab by double clicking on its label. For example, if you have one child named Billy, you might stamp out three copies of the account template tab and name them “Billy - Spending”, “Billy - Saving”, and “Billy - Giving”.
  4. Fill in the green cells in the upper section of each of your account sheets.
    • There are obvious fields for the account owner (your kid’s name) and the account name (like “Spending”).
    • The birthday field is handy for those who calculate allowance based on age, and it drives the value of the named cell “AgeInYears” in the field beneath it.
    • The total amount field in the Allowance section defines your child’s allowance. By default, the formula is set to “=1*AgeInYears” which happens to be the most popular allowance formula in the US as far as I can tell.
    • If you’re splitting allowance between multiple accounts, use the next field to fill in the percentage of the total amount that should flow into this account; otherwise, set it to 100%. If you’re curious what others are doing, last year we measured the average spend, save, give splits to be 61%, 27%, and 12%.
    • At this point, the calculated cell labeled Next Allowance will always show the calculated amount of allowance that should be delivered to this account (assuming the current day is the delivery date).
    • If you want to reward your kid with parent-paid interest in this account, set the savings rate to the desired percentage. Looking for a benchmark? We recently calculated that FamZoo families were paying 0.74% weekly, 2.12% monthly, or 6.51% annually on average.
    • If you want to charge your kid interest on a negative account balance in this account, fill in the loan rate. Accounts go negative when there’s an unplanned “overdraft” from an expense transaction or if you’re using this account to model a loan.
  5. Fill in Transactions in the bottom section of the sheet for this account as they occur.
    • Start by filling in the date and the amount cells for the existing Initial Deposit transaction.
    • You can enter a new transaction with a manual copy/insert/paste/edit of an old transaction. Edit the green cells in the new row to fill in the appropriate transaction date, description, memo, type, and amount.
    • Alternatively, you can insert new transactions of various types with one click by using the special Bank menu (rightmost entry in the document’s menu bar). Note: the first time you select a menu item, a dialog will appear alerting you that authorization is required. Continue and select Accept on the next screen use the custom Bank menu functions in your spreadsheet.
  6. Fill in a goal amount just to the right of the Current Balance cell if this account is tracking a specific financial goal like saving for a special purchase or a donation. The next cell over will show current progress toward the goal.

Your family bank spreadsheet is now open for business.

There’s an App for That!

Some parents are perfectly comfortable with spreadsheets and willing to do some manual work to keep the family bank rolling. Others, not so much. If you’re looking for:

  • a more intuitive, mobile-friendly, and functional user experience,
  • full automation of allowance and interest delivery,
  • support for automated chore charts with rewards or penalties tied to accounts,
  • the option to link prepaid cards to your accounts to let your kids make purchases and free you from having to manually enter transactions, and
  • a whole bunch of other family finance automation tools that encourage good money habits,

there’s an app for that. It’s called FamZoo, and the answers to all your questions are right here. If your question is missing, leave it in the comments below, and I’ll do my best to answer it.

The Bottom Line

Whether you use a spreadsheet, FamZoo, or some other family banking app, kudos to you for moving beyond the piggy bank. You’re taking your kids’ money smarts to the next level. Thanks to you, they’ll probably draw a blank when asked this question as an adult.


Unknown aka The Blog Post Author

One of the best things a parent can do is to develop their children's PF IQ as early as possible. The family bank spreadsheet concept is good stuff!

Bill Dwight aka The Blog Post Author

Amen to the start-early-with-your-kids mantra, James. Thanks for stopping by and leaving the nice comment. (Folks, be sure to check out James' posts on effectively planning for your retirement over at http://retirementsavvy.net/ - also good stuff!)

Ginger aka The Blog Post Author

Awesome! Getting familiar with personal finance early on is a great way to increase their confidence in handling money. Seems like a great opportunity to bond as well, and share some fun memories with your kid.

Bill Dwight aka The Blog Post Author

Thank you for the nice comment Ginger. The transaction record does end up being a wonderful walk down memory lane when the kids grow up. BTW, love the name of your blog http://www.girlsjustwannahavefunds.com/ and your mission.

Unknown aka The Blog Post Author
This comment has been removed by the author.
Unknown aka The Blog Post Author

Thanks for the template. We've been looking for something like this for a while. One question I had was how to modify the "Bank" menu. I'd be interested in rearranging the menu options to put our most used ones on top. Also, I'd like to modify the default notes for some of the menu items.
Thanks again.
On Edit: Oh wait, just found it in the Tools / Script Editor menu. Perfect, now we'll customize it for our specifics.
This has been a great teaching tool, with comparing what our son is making through our "family bank" and what we make in our regular bank savings account.

Bill Dwight aka The Blog Post Author

Tim - awesome! So glad you found the template useful and that you're able to customize it for your family. I appreciate you taking the time to leave a nice comment to let me know. -Bill

Anonymous aka The Blog Post Author

Thanks for sharing this and being abundance minded. I tried out your app a while back but it was too much for young pre-elementary school kids at the time (but I loved the concept). They are still a bit young for the completely computer-centric website/app route (their screen time is kept to a minimum for now as K and 2nd graders), but this spreadsheet approach may be the perfect transition - it will help give me and my husband structure (that will then help with consistency - I can't tell you how many times we've set up a system only to let it go a few weeks/months later because it was too laborious to maintain). It helps us with consistency, but we can also use real money (I feel at their age, they still need to touch/count/feel real money to help with the math skills that are easily abstracted with a credit card and virtual accounts) to actually pay them. Thanks again, Deb

Bill Dwight aka The Blog Post Author

Thank you for the thoughtful comments Deb. Yes, it's key to have a system, but there's definitely no single "right system" for all. As you allude to, the right system is one that works for your family given your family's unique situation. And, as your kids mature, that system will likely need to evolve too. Kudos to you for continuing to seek a system that fits and for being intentional in teaching your kids critical life skills like personal finance. That's good parenting! :-)

Unknown aka The Blog Post Author

Found this through MMM and are using it and loving it. Thank you! But, hit a snafu today. We had our first withdrawal, and when I entered it and chose "purchase" from the "type" menu, the amount was still added, rather than subtracted, from the running balance. I poked around a bit but couldn't figure out how to fix it. Advice please! (I'm at threeobriens@hotmail.com.)

Bill Dwight aka The Blog Post Author

When you have a purchase transaction (or anything else that is a debit rather than a credit), you can just enter the amount as a negative number in the Amount column's cell - so, a $5 purchase would be entered as -5 instead of just a 5.

Carlos Martínez Herrera aka The Blog Post Author

This is totally awesome. However, any idea why the menu bar to add a transaction in one click isn't working for me?

Bill Dwight aka The Blog Post Author

Hi Carlos, thank you for the kind words. I just tested it out with a fresh copy of the spreadsheet. I inserted a variety of transactions via the Bank menu items without incident. The first attempt should ask you for authorization to allow the spreadsheet to run its embedded code. Were you prompted for authorization and did you successfully grant access? If not, the code behind each menu item won't run. That's the only thing I can think of that might be causing a problem there.

amp-architecture aka The Blog Post Author

This is fantastic, thank you for sharing. Just wondering if there an automated way to add interest payments on the first of each month? thank you

Bill Dwight aka The Blog Post Author

I haven't gotten around to automating interest or allowance delivery, but it's certainly possible using a time-driven trigger - see https://developers.google.com/apps-script/guides/triggers/installable
If you're comfortable with Google Apps Script, you could add that functionality to your sheet.

jturner2 aka The Blog Post Author

Hi Bill,

I love, love, LOVE your spreadsheet and have used it for years now! Unfortunately, I am a total beginner when it comes to spreadsheets and so I have a very basic question. I only can see the bottom 3 rows of data because the top part (rows 3-17) take up the majority of my viewing window. I'm always prompted to resize the frozen rows; however, I don't want to potentially lose all of that info by accidentally doing that wrong. Is there an easy way to resize so that it can toggle - sorta like open (viewable) and closed (not viewable) - so that I can have more of the allowance data visible at a time?

Bill Dwight aka The Blog Post Author

Hi J, you can use Google Sheet's row grouping feature to collapse the top rows. Select the rows you want to group together (select the row number cell on the lefthand side of the first row and drag the mouse down through to the last desired row). Right click on the selected range and choose the "Group Rows N - M" entry of the pop-up menu. Then, you'll be able to click on the [-] in the margin to collapse the rows and the [+] to expand them when desired.

danbalzer aka The Blog Post Author

I just found this site today and it looks like just the tool I'm looking for! Now that I've copied a version to my Google account and am setting it up, I have a few questions that I don't see answered above (I may discover the answers later, but it might be useful for other newbies like me):
1. I don't see anywhere that the periodicity of the allowance is explicit -- is the default set to weekly? If not, is there a way to adjust it?
2. Can I assume that the regular allowance is automatically added to the account, or will I/my spouse have to go in to manually enter it every week? (this is a feature I am really looking for -- in our experience with spreadsheet-based budgeting, it is the requirement for constant maintenance (or remedial reconstruction after a few missed weeks) that always seems to defeat us)
3. Same with parental-paid/collected interest rate? And how do I set the period for the rate (monthly, quarterly, yearly)?

Bill Dwight aka The Blog Post Author

Hi Dan, sorry, but I didn't do the programming work to automate the allowance/interest delivery (like I did in the FamZoo service), so you have to do that manually in Step 5 (see Bank menu).

danbalzer aka The Blog Post Author

Thanks for your reply, Bill. I think I found a partial solution -- putting a SUMIF formula in cell I18 (the Current Balance cell):

=SUMIF(D22:D31, "<"&TODAY(), H22:H31)

[Note: the second row number (31) is just a reflection of how many transactions there are in the register. As the number of transactions increases, that row number needs to accommodate. There may be a way for it to do so automatically, I'm not sure.]

This tells the sheet to add only those transactions that occurred prior to the current date. I still have to manually enter each allowance, but I can enter multiple allowance payments any time I want, including future payments, and only those dated in the past will be tallied in the current balance. This way I don't have to be quite so slavish about inputting each allowance payment exactly on time, but my child can still check her balance whenever she wants and get a true number for the money she has available to spend.

Unknown aka The Blog Post Author

Anyone else have issues being able to scroll down on the spreadsheet? My browser is all the way open and it won't let me scroll down. Thanks!

David Wain aka The Blog Post Author

I have my child's allowance split into "spend" "save" "give" - it looks like I have to (using the banking menu) add to each pot individually every time I give allowance. Am I missing something?

Bill Dwight aka The Blog Post Author

Hi David, you aren't missing anything. You do indeed have to add to each pot individually. The spreadsheet is quite basic and for folks who are OK with doing things manually like I used to do way back in the day before building the FamZoo app(lication).

Post a Comment