2015 money tracking
I created a really pretty spreadsheet to start out 2015. I attempted to address some deficiencies previous versions had… but I’m already frustrated by it. I really don’t want to spend a ton of time on this, but I’m not getting in information I want.
Here are the questions I want to be able to answer at a glance:
- How much will we take home this year (net)?
- How much are we spending? What big expenses remain? What categories are trending over budget, and why?
- How much are we going to save for retirement, towards mortgage pre-payment, or in cash (for mid-term spending)?
- Are our tax withholdings approximately correct to net us a small refund?
- What is our net worth?
- Are there any major investment / strategic changes we need to make? [I know the answer is yes for this one.]
- How have these answers changed since the last time I looked? Are we still on track?
I was trying to do a zero based budget and track each dollar coming in. That is too rigid if I also want to use a slush funds to smooth out cash flow. For example, I want to ignore the fact that I spent about $4k on reimbursable work travel. I want to ignore the fact that we paid for a big house project in February even though we didn’t allocate income towards it until later in the year. I want to ignore this stuff in the big picture, while understanding that it is happening. Our cash flow is actually net negative thus far – but this is OK.
I’m really jealous of the PoP’s monthly income statements and balance sheets. This would be my ideal, but I’m not quite jealous enough to do them myself. The problem is, I’m not really a details person. They bore me to death. Quarterly reports might be something I could and should handle.
There is a lot of value in summarizing the information for myself, and I’d like something T can digest. Since he doesn’t often look at the whole picture, he has a constant low-level anxiety that we are about to go broke. It would be funny if it weren’t also mildly stressful for both parties.
Stacking Pennies 
But if you don’t enter the data, how will you get the graphs, summaries, etc? 🙂
I definitely have the data and will continue to have it! Graphs will be possible!
I think my issue is the timescale. For one, we had a $10k home project this month, so it looks like we are WAY over spending this month. But who cares? On a yearly basis, this was planned for and we are NOT going to overspend. Also applies to travel, property taxes, and other significant bills that we actually CANNOT cash flow in non-summer months with the way we have our investments set up. We have significantly more income during the summer months due to the way one of our salaries works.
I could skip knowledge of categories too – but that makes it hard to optimize. I just don’t think it is important on a monthly basis, at least not for all categories. Groceries, yes. Insurance, no.
Also, if I mark a transaction as “ignore” (because it will be reimbursed by work later), it won’t go into any spending graphs (which is great). However, then my accounts won’t line up to my spending data, since I’m missing money. Tying the income statement to the balance sheet is not super important to me. I look at all the spending, but I’m most interested in projected yearly spending vs projected yearly earnings.
Also (replying to myself), I don’t enter any data, I just download my transactions and review them. Just FYI. Entering data is too much work.
If only MINT were just a little more useful.
right? It just isn’t quite what I need. It is very close though.
I have a spreadsheet that covers these questions:
1) How much will we take home this year (net)?
2) Are our tax withholdings approximately correct to net us a small refund?
I think I’m going to change it up a bit for the new job, but it has a worksheet for each year that looks like this:
Row 2 – Title for Now-Old Employer W-2
Rows 3 through 16 each pay day of some sort (regular, cash bonus, or stock bonus has each own row)
Column A – Date/Bonus name
Column B – Gross. This is the sum of Columns H and T
Column C – Fed Taxable =B3+L3+M3++N3+O3+P3
Column D – Tax Paid =-1*I3
Column G is a short form of Column A
Column H – Salary
Column I – Federal income tax. This is actually a really complicated formula based on the allowances and withholding taxes
Column J – Social Security tax. This looks something like =MAX(-$J$24-SUM(J$4:J4),ROUND(U5*-K$24,2)) where $J$24 ix the maximum Social Security tax for the year and K$24 is the Social Security tax rate
Column K – Medicare tax. This is U5 multiplied by 1.45%.
Column L – Dental cost
Column M – Vision cost
Column N – Medical cost
Column O – 401(k)
Column P – HSA deduction
Column Q – a local tax
Column R – Total =SUM(H3:Q3)
Column S – Net =SUM(H3:K3,Q3)
Column T – life insurance taxable benefit
Column U – Taxable for Social Security and Medicare =SUM(H3,L3:N3,P3,T3)
I put in expected values for each of these rows and then update them from my paystub each month (or less frequently sometimes) to see how on track I am.
Row 18 – Title for Interest and Dividend Income (1099s)
Rows 19-22 list each of the banks that I expect interest income from. I have another worksheet that I use to estimate how much I will get each month, which I update occasionally (usually once every few months) and this just sums up those rows. The last row is for the Vanguard taxable dividends, which I estimate as well based on last year’s dividends and the number of shares I have.
Summary section!
Row 25, Column B sums up the actual gross income from the W-2 section and the 1099 section.
Row 26, Column C is the adjusted gross income, which sums up the fed taxable values for the W-2 and 1099 sections.
Row 27, Column C lists my estimated itemized deductions (which are in another worksheet) or the standard deduction before I bought my condo
Row 28, Column C lists my personal exemption (just the one)
Row 29, Column C lists my expected other tax credits, entered as a negative quantity (just the foreign tax credit from my Vanguard Total International Stock Market Index fund shares)
Row 30, Column C is the total taxable income which sums Rows 26, 27, and 28, less 72% of the Vanguard international stock dividends (since they were already summed in).
Row 30, Column D references the tax paid from the W-2 income
Row 31, Column D is a tax estimation which for single me uses the tax brackets on the value of Row 30, Column C, plus Row 29, Column C, 72% of the Vanguard international stock dividends x 15% tax.
Row 32, Column D is the difference of the last two: =D30-D31. If it’s positive, I forecast overpaying taxes and if it’s negative, I forecast underpaying.
This sounds really complicated, but I’ve been using the same spreadsheet since 2010 and I just copy it every year and manually change it a bit.
I think Bridget was saying that Money for the Mac lets you do yearly budgets? That’s what I do with my budget spreadsheet, setting aside the budget/12 each month and then rolling over the leftover each month.
For the question of What is our net worth? You might be able to use an aggregator like Personal Capital or Mint. It would probably do that fine. If that’s your goal, I would use Personal Capital – I think it does a better job at that. It also shows you what your asset allocation is, which is cool.
I don’t like zero based budgets. They force you to only think about what is going to happen in the next month, rather than throughout the year, which is how I prefer to budget since it smooths costs out more. For example, I know the electricity bill is due on even months, so why would I budget for it only then when I could budget for half of it on the odd months? I also let categories get negative for months sometimes (e.g. travel or work costs until they’re reimbursed) and this is totally fine since I have a buffer in my checking account.
I do have most of the questions covered. The point of the list was that these are all things I think are important to know. The main part that isn’t working is the spending tracking. This stems from the lack of intelligent yearly budgeting tying to monthly budgets. I used to do zero based budgets, so it has been adjustment to find something more sensible. I have something now that I think will work, but I might look at Money for Mac too. (Software is just typically not customizable enough).
I still need to look at asset allocation. I have a tax estimate similar to yours (as of late last year).