In the recent post "What I'm Teaching my Son about Money" (
http://www.mrmoneymustache.com/2015/05/20/what-im-teaching-my-son-about-money/), MMM describes the spreadsheet he's been using with his son instead of a piggy bank: "To make a deposit, he just hands me some cash. To withdraw, he asks me for cash or has me buy something for him online. But for every dollar that remains in the account, he accrues interest at a 10% annual rate with monthly compounding."
For those who are looking for an easy way to stamp out a Bank of MMM-like "spreadsheet bank account" for your kid - or multiple ones for multiple accounts (like spend/save/give) and/or multiple kids - I've put together a public sheet in Google Docs that can be copied and customized to suit your needs. It also has some embedded script that creates a special "Bank" menu that can be used to automatically insert new transactions for allowance, interest, etc with fields filled in based on your allowance formula, interest rate, etc.
I've tried to put enough comments in the sheet to make it self-explanatory, but let me know if you don't understand something, see problems, or have suggestions. It's just a little side-project, so I can't guarantee I'll address all requests, but I'll certainly consider each. Of course, you're welcome to copy it and tweak it yourself, but it'd be nice to share those tweaks back with the community.
Here's a quick "Getting Started" guide:
- Make your own copy of the spreadsheet by clicking here: https://docs.google.com/spreadsheets/d/1VAuagUZ4B1lc91sDHDDorguVXmRF7ri1x--SlYfL_yQ/copy?usp=sharing. 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.
- 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.
- 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”.
- 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%. 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. 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.
- 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.
- 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. Let me know what you think. (Sample screenshot attached.)