Personal Finances in Excel
What's This All About?
This spreadsheet is designed to be a good starting place for tracking personal finances. It is populated with sample data for you to see what's possible. Features in the spreadsheet include:
- Cashflow forecast and tracking of actual funds for years into the future
- Cashflow graphs that make identifying trends easier
- Separated monthly forecast assumptions that allow you to run scenarios like: "What if I get a 5.7% raise this year?" or "Can we survive if my wife stops working?"
- Easy to get started - just plug in some vital information (salary, etc.) to see what your cashflow forecast looks like for the next 4 years
- Monthly budget planning that's easy to keep up to date - just enter an expense or income on the "2006" sheet and the rest is calculated for you
- Color-coded sections that make things easier to find
Download
Screenshots
See the summary, cashflow forecast, actual cashflow, and budget on one page:
View the cashflow forecast in a graph to look for trends and problem areas:
Updating your budget is as easy as filling in one line item. The spreadsheet does the rest:
Getting Started
There are four tabs in this worksheet to start you out: Cashflow, 2006, Mortgage, and Total Funds.
Cashflow Tab
The Cashflow tab shows a summary (yellow), the 3-year cashflow forecast (blue), your actual cashflow to date (green), and your monthly budget (purple). It also includes the forecast and actual cashflow graph.
The first thing you should do is plug data into the blue Forecast Monthly Assumptions section on the left. You will see the spreadsheet adjust its forecast depending on your input. Definitely customize this to meet your needs: if you don't have a husband, delete that section; if you need to take care of your grandmother, add that in!
Additionally, you should update the Summary tab with information that you care about. I use it to keep track of summary information from my other tabs. For example, I reference the last row in the Total Funds tab to see my net worth on the summary section.
2006 Tab
The 2006 tab is where the purple monthly budget section gets its data. This is where you enter in "$5.60 for Taco Bell on 9/15/2006." The spreadsheet will update the monthly budget accordingly. Note that you can use the top row to filter for different data. For example, when I'm reconciling purchases with my MasterCard, I filter for "MC" in the "Payment" row to make life easier.
Mortgage Tab
The Mortgage tab is where you can keep track of the equity you have in your house. If you're renting, you can delete this tab and add some other useful data!
Total Funds Tab
The Total Funds tab keeps track of your net worth, summing funds from different accounts you may have.
Adding Tabs
You should most definitely add your own tabs to track data pertinent to your financial situation. I personally have eight: Cashflow, 2006, Bills, Mortgage, Tithes, ESPP, Total Funds, and Uncashed Checks. Do what works for you!
Learning Excel
Keeping track of your budget using Excel is a great way to learn the program. I learned a wide range of stuff creating this spreadsheet, from color and border formatting, to named formulas, and even to the powerful sumproduct.