Money Tracker for Excel
As teenagers and young adults start getting jobs and checking accounts and borrowing from their parents constantly, they need a way to track and manage their money. I designed this spreadsheet to work in the mobile app versions of Microsoft Excel. There is no link to actual bank accounts, so you can track your money without the risk of having your phone linked to your bank accounts. You can save this file on your phone and edit it without an internet connection.
Using the Money Tracker on Your Phone
You'll need the free Microsoft Excel app if you want to use this on your phone. Below is an example of what the spreadsheet looks like when editing it in the Excel app on a smart phone.
To make data entry as simple as possible, I added that let you select the current date and select from a list of common payees. These drop-down boxes can be customized via the Settings worksheet.
Money Tracker 2.0
for Excel
Description
Version 2.0 (published 6/25/2019) is a significant update that provides a way to allocate your cash, checking, and savings to separate spending categories or savings funds. Think of the allocation columns as representing either real envelopes of cash or virtual envelopes within your checking account. This provides a great way to transition from just tracking your money to budgeting and planning.
You can edit the labels for the allocation columns. When you spend or receive money, enter the amount in the related column or split it between columns. For example, if you spend money on food, enter the amount in the Food column. If you withdraw cash from your checking account, you can allocate the cash to different columns (make sure the sum in the Amount column represents the total withdrawn). The Balances at the top represent the current Balance for each column.
If you only want to use the spreadsheet like a checkbook or account register, you can still download the original version 1.0 below.
Money Tracker 1.0
for Excel
Downloading to an iPhone
It can be a little tricky to download the file to your phone. You could download to your PC and upload it to your Microsoft OneDrive account and access it that way. Or, you can use your phone's browser to download directly to your phone.
Using the Safari App on your iPhone, when you download the file to your phone and see it open the spreadsheet in a new window, tap on the spreadsheet until you see a "More..." link or "Open in Excel" link near the top of the screen. There may also be a "Copy to Excel" icon that will open the spreadsheet in Excel. You can then choose to save it directly to your iPhone or to your OneDrive account if you have that set up.
Learning Personal Finance Principles
When teaching personal finance principles, a good place to start with younger kids is the Money Manager for Kids worksheet. But teenagers need to start managing their own cash and checking accounts instead of just keeping track of the money their parents are holding for them.
I intentionally kept this spreadsheet as simple as possible. So, it might not use all the same terminology you would find in a commercial application like Quicken, and it definitely won't have all the advanced features. The spreadsheet is meant to help you ...
- Learn how to track your spending using a basic register (date, description, amount, balance).
- Allocate money to specific spending and savings categories.
- Learn how to keep track of what you owe different people (like borrowing $20 from parents).
1. Track Your CASH
The CASH worksheet helps you keep track of what you have in your wallet. The new version of the money tracker lets you allocate your cash for specific purposes, just like you might do using actual envelopes.
The Envelope method of budgeting involves placing cash inside actual envelopes labeled for specific purposes (like Food, Fuel or Fun), and only using the cash for that purpose. This spreadsheet lets you simulate that approach not just with cash, but also with your checking and savings accounts.
The image above shows a few sample transactions, including how to enter the beginning cash balances, how to record a food purchase, and how to move (reallocate) money from one category to another. Row 8 shows an example of withdrawing $55 from your checking account, and allocating it to 3 different envelopes or spending categories.
Occasionally, you should count your cash and verify if the totals match your spreadsheet. If the totals do not match, then you may have spent some cash without tracking it. Enter an Adjustment to Cash or Correction to correct the balance, then try to do better at tracking your spending in the future.
Tip: When possible, get and save receipts. You aren't going to remember to record every cash purchase you make, or you might not have your phone on you, or you won't have the time to update your spreadsheet on the spot. Saving your receipts will allow you to update your spreadsheet later when you have more time. Plus, you'll need receipts to make returns and you need to get in the habit of savings receipts for tax records.
2. Track Your CHECKING Account
When using a checking account, you don't want to incur the fees and embarrassment and tarnished reputation that comes from writing bad checks (writing checks for more than the amount in your account). That means you need to keep track of your account balance.
When you write a physical check or make a deposit, your bank is going to be tracking everything for you. Most banks that I know of scan the cashed checks, so you can see the scanned check when you log into your account. They may even include an image of the scanned checks on your monthly bank statement. You should keep a record of each check you write and every check you deposit using your physical checkbook register. It's also a good habit to write the purpose of the check in the memo line on the check so that when you view the online image of a cashed check, you can remember what it was for.
People often use debit cards more than writing checks, so you need to find a way to keep track of the balance in your checking account besides your physical checkbook register (because so many people don't have their checkbook on hand when making purchases via a debit card). If you have your card on you, you'll likely have your phone on you, so use this spreadsheet or some other app to keep track of your account balance. Remember to keep receipts for everything, not just cash purchases.
Cash Withdrawals and Deposits: When you record a cash withdrawal or deposit, you should update both the CHECK and CASH worksheets, because you need to keep track of the amount in/out of both cash and checking.
3. Track Your CREDIT
Any time you borrow money from anybody, you have established a CREDIT account with that person. So even if you don't have a credit card, you may still need to keep track of money that you owe. The CREDIT worksheet in this spreadsheet lets you choose WHO you are borrowing from or paying back, and the Account Balance column will tell you how much you currently owe (or how much is owed you).
Negative Balance: With a credit account, your Account Balance will usually be negative (meaning that you owe money). Showing parentheses () around a number like (25.00) is a common way to represent a negative value. If you see a negative amount in the Account Balance column, that means you OWE money.
Tithing: If you pay tithing, then you can think of tithing as a CREDIT account. When you receive income (from babysitting, gifts, work, etc.) you will need to keep track of how much you owe as tithing. Some people set up automatic payments to come out of their pay checks to pay to specific charitable organizations, but you'll need a way to keep track of other types of income as well. For example, if you get paid $25 in cash for babysitting, you will add $25 in the CASH worksheet. You can also add $2.50 (10% of $25) in the CREDIT worksheet in the Money Owed column after selecting Tithing from the Account column. Later, when you PAY your tithing, you will enter $2.50 in the Money Paid column, bringing the amount you owe back to zero.
IMPORTANT: Whenever you make a change to the CREDIT worksheet that also affects your CASH, CHECKING, or SAVINGS, make sure to update those other worksheets as well.
4. Track Your SAVINGS
Whether you use a cash envelope or jar or an actual bank account for your savings, you will want to know how much you have saved. Especially if you are trying to save for something specific, you'll need a way to track your progress. Just like the CREDIT worksheet allows you to track the balance owed to a particular creditor, the FUND column in the SAVE worksheet allows you to keep track of the balance in a particular savings FUND.
Using the Checkmark (√) Columns
You'll see that some of the worksheets contains a checkmark (√) column. You can hide this column if you don't want to use it. The purpose of this column is to provide a way for you to check off each transaction that you have verified with your bank account or verified with a receipt ... or whatever else you might want to use it for.
There is also a "c" and "R" option in this column's drop-down list in case you want to use it for marking a "cleared" or "Reconciled" transaction. You can edit what shows up in these drop-down lists in the Settings worksheet.
More Advanced Money Management
Cash Flow (money going out and coming in) is an important concept to learn, especially when you start having to pay bills and have other obligations. Predicting your cash flow by estimating future income and creating a spending plan is the next step after Tracking your money. You can call it "planning" or "budgeting" - it's pretty much the same thing. You can use a monthly cash flow or personal budget spreadsheet to help you create a plan.
When you are ready to combine your budget with your money tracking, check out the Money Management Template.
