Finances Template

Tommy

Just a Millennial living in the real world...

Updated 1/15/22. Howdy folks! I wanted to refresh my Cash Flow and Budgeting post because in 2022 I decided to consolidate to make things less complicated and more efficient. So I refreshed my finances template that I use to track my finances weekly.

Fair warning. You need to know how to do basic excel functions like how to create simple formulas like the sum formula and basic mathematics to add and subtract. You can download the finances template here. I have some preset categories and feel free to tailor it to how you please with any specific expenses you may have. I have entered an inputs section where you just have to input your values and they will flow to the template. It is preset to three months.

What is Cash Flow?

Cash flow is the amount of cash that flows in and out of your checking account. Every time you buy a coffee with your debit card, cash flows out of your account. Every time you get paid, cash flows into your account. Cash flow is important because all financial institutions charge non-sufficient funds fees (NSF) if your checking account doesn’t have enough money in it to cover an expense. The big banks like JP Morgan Chase charges a $34 NSF fee, where I used to bank at in college and post grad. I switched to Navy Federal Credit Union (NFCU) post college and it charges a $29 NSF fee. I switched to NFCU because of the cheaper fees, they reimburse my ATM fees at non-NFCU ATMs, and I have been a member there for 25+ years. Checking account fees are a huge pet peeve of mine because they are expensive and usually it’s your own fault that you get charged one. Since they are expensive, you should avoid them as much as possible.

What is a budget?

A budget is a set amount of money that is used to make specific purchases. I have created budgets for savings, how much rent I can afford, groceries, utilities, travel, eating out or fun, etc. Budgets are important because they help prevent overspending and spending beyond your means.

A word of advice when it comes to budgets. Don’t beat yourself up if you overspend your budget by accident. We are not perfect people and it happens quite often. It’s okay if you overspend, but just make sure you don’t make it a habit and you should aim to be close to your budget. Spending above your budget every month is how you rack up unnecessary fees and potentially debt.

Where to begin?

I know it’s a real pain in the butt to switch banks. But for this method to be efficient, you will need a bank with decent online and mobile banking. Every time I go to update this spreadsheet, I use mobile banking on one screen and I plug in the data on my computer. It takes about 15-30 minutes every week to update.

Cash Flow Tab

So my cash flow template is probably the most important template because it helps me prevent myself from being charged NSF fees on my checking account. I have only had two NSF fees in over a decade and I got them waived by Chase and USAA when I called them. If an NSF fee rarely happens and it was a mistake, always call the bank and ask them to waive it. All they can do is say no.

At the beginning of the year, I project the entire year of the cash flow template. All of my bills are due on the same date and I get paid every 2 weeks so it is easy for me to project cash flow. When the year goes on, I delete the cells up until I reach the end of the year. You can project cash flow for every month, quarter, 6 months, or year. The longer out you project cash flow, the easier it will be to find where you will be short on cash that you can highlight in red. I typically mark in red anything under $150 as an indicator for me, but you can go with what makes you comfortable.

Here are my assumptions for this Cash Flow Template:

Assumes you have health, vision, and dental insurance through your employer so they are not listed.
Assumes you have 401(k) savings option and pre-tax transportation expenses through your employer.
Assumes you get paid on the 15th and 30th of every month.
Assumes you use only one credit card.

How to use the Cash Flow Tab?

How much money is currently in your checking account? This will always be your starting place when using this spreadsheet. You will always start with logging onto online banking to figure out what the current balance is of your checking account and plugging it into the spreadsheet. You can start using this template at anytime during year and just adjust the dates.
When does income come into your checking account? I get paid every two weeks so that is 26 times a year. Some people get paid weekly, monthly, yearly, or two times a month (24 times a year). So put your take-home pay into the take-home pay input. The template assumes that you get paid on every 15th and 30th of each month.
What expenses do you pay with a debit/credit card? If you don’t use a debit/credit card then you can skip this step. I have included some typical expenses that you can pay by debit/credit card. All of these expenses are summed into “Credit Card 1” and that is because that is the amount that you will have to pay back before the due date.
What expenses do you pay out of your checking account? Some expenses will charge you a fee if you use a credit card to pay. That fee is typically 3% and those expenses are typically rent/mortgage, utilities bills, or life insurance premiums. These fees may seem small, but they can add up quickly. For example, a 3% fee on a $1,000 in rent is $30… Using an electronic transfer is usually free. Any expenses that you pay cash out of your checking account will be a line item in the template because this is cash out of your account.
Analyze your cash flow shortfalls: If you have a negative balance, then that means you need to spend less money or shift money from your savings to cover any shortfalls. So as a warning, I highlight any potential cash shortfalls in red. My rule of thumb is I highlight anything below $150. I typically total everything up and bold it around every paycheck or a big expense like a rent/mortgage or credit card bill. You have a higher chance of overdrawing your account around a bigger expense as a math/formula error can occur. Always check the formulas and make sure they are correct.
Rinse and repeat every month: You can delete the old information when time passes. Also, I set up the inputs and formulas so when you copy & paste a month, it will be the exact same cells and formulas.

Cash Flow Tips

Set up notifications of every dollar spent. I receive text messages every single time my debit card and credit cards are used. These are real time notifications and I use this for security purposes so I know exactly where my money is going.
You will notice that I break down expenses by expenses paid on a credit card and expenses paid out of my checking account. The reason I do this is because using a credit card is not a cash outflow until you pay off the bill (in full) as you are not using you cash when you swipe a credit card. It is the bank’s money and then you pay them back, which becomes a cash outflow. Why would you pay for an expense with cash over a credit card? The reason is because there are certain expenses (typically rent & utilities) where you are charged a fee for using a credit card. There are very few times that a fee is actually worth it. The first is when a credit card has an annual fee, but the benefits outweigh the annual fee. The other example is a balance transfer with 0% APR for 12 months. Balance transfers usually have a 3% fee. So if you can pay off the debt within 12 months and the fee is less money than what you would be charged in interest, then the fee would be worth it.
Use yellow (or any color you prefer) highlights to organize variable expenses and red (or any color you prefer) highlights when your checking account is close to $0. My rule of thumb is to highlight the cells red whenever the total is under $150. This is a warning for me to watch out to make sure I don’t overdraw my account.
Because I use credit cards, I tend to keep my checking account with a lower balance. I probably have an average balance of less than $1,000. I do this because of security reasons. The less money I have in my checking account, then the less money criminals can steal from me. My monthly checking fee is waived because I make 2 direct deposits above $500 every month. The other way to waive the monthly fee is usually keeping a minimum balance of ~$1,000 every month. I rarely use cash because I want to accumulate miles or points for travel.
Anticipate future plans and expenses. At the beginning of every month, I plan my calendar out for events. This means weddings, bachelor parties, social events, vacations, etc. So if I have something big coming up then that means I should save for it or spend less money when that event occurs. Some examples of future expenses include birthday/christmas gifts, eye contacts & medical expenses, intramural sports fees, etc.
Be open to changing due dates for your bills: Rent and my credit card bills are my biggest bills. So I try to spread those bills out within the month so I don’t get into a cash flow crunch. Rent is due on the 1st of every month and my credit card bills are due around the 20th of the month. You want your biggest bills to be paid after your paycheck comes in. I get paid every two weeks, which usually occurs around the 10th and the 25th of every month. So I pay my credit card bill on the 20th and rent around the 1st of every month. This ensures me that I will have enough money in my account.

Actual Spending Tab:

The Actual Spending Tab helps me figure out how I spend my money. The template will sum up everything monthly and eventually annually. This allows me to see exactly where my money is going and I can also tailor my credit card portfolio to it to maximize earning points. Please note that you can customize the line items however you please.

How to use the Actual Spending Template?

Log into your accounts.
Go to your recent transactions or most recent statement.
Plug expenses into actual spending tab. I wait for all expenses to settle so for example if you ate out at a restaurant and left a tip. Wait for the expense to show up with the tip included when it gets out of a pending transaction. This is so you don’t have to go back and edit it.

Actual Spending Tips

Tailor the paycheck expenses and expense items to your situation. I spend a lot of money at Walgreens and Amazon so I separate line items for these categories. So go ahead and add/delete/change any line items that you see fit.
Keep all receipts until your transactions settle. A settled transaction means it is not a pending transaction and it will show up on your statement. This is very common with dining out at restaurants when you leave a tip. Those transactions typically take a day or two to settle. I keep receipts until the transaction settles to prevent fraud. I’ve read stories where tips have been changed from what was written on the receipt and I’ve had it happen a few times before. So keeping a receipt until the transaction settles helps prevent this and protects you as you can file a fraudulent claim.
At the bottom is a cash flow line. If it’s positive then that means you spent less than you earn. And if it’s negative then that means you spent more than you earn.

Credit Cards Tab:

The Credit Cards Tab is essentially “balancing my checkbook”. This is where I make sure all charges on my credit cards match my transaction history or credit card statement on mobile/online banking to prevent any fraudulent charges. I update this tab weekly and this ties into the Cash Flow Tab.

Savings Growth Tab:

The Savings Growth Tab is mainly for motivation. It helps me show savings growth over time for whatever savings goal I have. You can manually enter in data from whatever savings accounts you have and I typically update this monthly.

Balance Sheet Tab:

The Balance Sheet Tab shows a snapshot of your net worth. Data is tied from the Savings Growth Tab and you can manually enter in other data. I typically update this quarterly.

Conclusion

This is what I do to keep track of my finances. I think Excel is a great product and it is an efficient way to keep track of your finances. I don’t mind spending the time to enter things manually because I think it holds me accountable instead of automating everything. I feel that when you start to automate things that is when you start to miss things and mistakes are can be made. With that being said, hope this helps!