Expense tracking, Excel and Quicken

B

BigE

Recycles dryer sheets
Joined
Jun 24, 2011
Messages
185
Looking for advice on expense tracking. I am about 4 years out from pulling the plug by rough back of the napkin calculations and based on Firecalc and a few other retirement calculators they all give very acceptable results based on a rough estimated budget. I believe it's time to really get down and anal and track our expenses, so the budget part of the equations is no longer a "rough" estimate. My wife and I are very much a team but I work a great deal and she runs the household/pays bills/etc. so she will be doing the tracking (first time). She is familiar with Excel but not any of the Quicken products. Should we investigate Q Lifetime Planner (do they still make this?). I know there are some who have mucho experience with QLP (like pb4uski) and I just went thru a recent thread on this (didn't want to hijack it with my question). My bank and checking account is linked to an Intuit product called "Financeworks"; anyone have any experience with this? Feel free to give ANY advice on this.

Thanks
 
Quicken or Mint are good at tracking what you're spending... and from that you can generate a budget. Quicken can be set up to "suck" the data down from your bank accounts, brokerage accounts, credit cards, etc... You then can "categorize" the transactions. Mint does things similarly (I've heard) - but it's all in the "cloud" - no software running on your PC. I've used Quicken for years. QLP comes with Quicken Deluxe and Quicken Premiere.

You and your wife should decide how closely you want to track things. I, personally, track fairly loosely. When I go to Costco I buy groceries, but also sundries (toilet paper, shampoo), office supplies, booze, pet supplies, etc... I toss it all under the spending category of groceries. Some folks wouldn't be comfortable with that. I toss every purchase from Home Depot, Lowes, and Dixieline under Home maintenance... Some folks would want to sub-categorize. My main goal of tracking is to get a real handle on where money is being spent. If a category looks to high - then I drill down and look at it closer.

Spreadsheets also work fine - but they require manual input. And I'm too lazy for that on an ongoing basis. That said - I take the output of quicken's spending summaries - to forecast retirement budgets in a spreadsheet.... kind of "what iff'ing different scenarios..
 
We've been using Quicken for decades, and at times have used it to track spending closely, as we have been doing since mid-February, as part of a financial planning effort we're engaging in this year to get a handle on just how much it'll cost once we're retired.

We're tracking things quite closely, and I think it often will matter: For example, lumping all Drugstore.com purchases together won't really help us understand that (for example) 60% of the money we spend there is on Nicorette. For that matter, lumping all supermarket and warehouse club purchases together won't help us understand that (for example) 35% of them are heath and beauty purchases.

We're also separating out Sales Tax, but only because that's easier math than including it, since a lot of our bills have taxes separated out as a single line, but the bill itself is for items that are in different categories: It would be much harder to divide the single Sales Tax number among three or four categories than it is to just divide each line item by 1.065 to pull the sales tax out of it, and then allocate whatever remainder there is to the Sales Tax category.
 
I use You Need a Budget which you can see at YNAB.com. I find it very good for tracking expenses. You can use it cloud based if you want but you don't have to. YNAB really excels at budgeting.
 
I like Excel (well, actually Open Office), because I like to fiddle with numbers and spreadsheets are so easy to customize.

Pick the software you like best! It is so much easier to consistently keep track of expenses when doing so is fun, than when one is battling frustrating software.
 
I use both Quicken and Excel. I track my expenses and investments with Quicken and use QLP to supplement FIRECalc and various other planning tools. But I also use excel because it can be customized to fit my exacting needs.
 
I've tried Quicken and Mint but can't seem to customize them to fit the way I track our finances, so I just use a spreadsheet.
 
Thanks for the input so far. I feel a little nervous about really finding out how much we are spending b/c I think I am underestimating, but one thing this site has taught me is the importance of knowing what you spend going into ER. I'm going to check out YNAB. And if I understand correctly, we could get QLP if we go for the Deluxe or Premiere.
 
We have never tracked our expenses to that degree...never used Quicken or any other tool. We simply break it down between what shelter costs, what we spend on groceries, personal products, gifts, entertainment, restaurants, what we spend on dental/prescriptions, and what we spend on travel. It takes me about 10 minutes with my bank statement and credit card statement max. each month.

So far it has worked. Our numbers going into ER have been borne out by the past three years of retirement.

I think that you can over analyze this.
 
Another product is Moneydance. Like Quicken it is a checkbook manager first and a lot of other things second. Budgeting on Moneydance is more labor intensive than on Quicken, but gives you the flexibility to set your planned expenses and annually, quarterly, weekly, etc. This is important in reporting as Quicken reports budgeted amounts on a Pro-rated basis, whereas you can get un-pro-rated reporting in MoneyDance.

That said, one of the weaknesses of MoneyDance is its suite of reports. It is half the cost of Quicken, upgrades don't happen as often, and it has an online community of active users and MoneyDance customer service reps.
 
I think the difference is between whether you have/will have enough saved for retirement to cover your expenses, based on your spending habits, versus not having/will not have enough saved. If you have enough, then any effort to better characterize and account for your expenses is wasted effort. You didn't need to do it, because you didn't need to do anything about it. However, if you don't have enough, then you cannot over-analyze - at least not until you get switched back into the "have/will have enough" category. You need to analyze your spending and re-analyze it, modifying your spending behaviors until your spending conforms to the income stream you can expect in retirement.
 
Silly me, I just find it interesting to see exactly where my money goes! I have tracked my spending for years.

I love the increased depth of understanding of my spending that results. When I can directly see the exact effects of my decisions on my spending, I also feel like I am more in control of my spending rather than a victim of vague unseeable forces compelling me to spend.

Different strokes for different folks, I guess. :D Maybe tracking my expenses has become a hobby for me?
 
Last edited:
The following is what I do.

rodi said:
Quicken can be set up to "suck" the data down from your bank accounts, brokerage accounts, credit cards, etc... You then can "categorize" the transactions...

Spreadsheets also work fine - but they require manual input. And I'm too lazy for that on an ongoing basis.
Click to expand...

I also like to have my portfolio value up-to-date along with my expenses, without having to do anything more than to hit "Update".

I also track fairly loosely. Exactly like the following...

... When I go to Costco I buy groceries, but also sundries (toilet paper, shampoo), office supplies, booze, pet supplies, etc... I toss it all under the spending category of groceries. Some folks wouldn't be comfortable with that. I toss every purchase from Home Depot, Lowes, and Dixieline under Home maintenance... Some folks would want to sub-categorize...
Click to expand...

I would sub-categorize when it is important to me. For example, instead of lumping in all gasoline expenses under Automobile, I would spend a few seconds to reclassify the gas for my guzzling motorhome under the category of Travel-Fuel. That's many thousands of dollars a year, and it is a large discretionary spending item, certainly not as essential as the gas I put in my car to go run errands. It's one of the items I know I can cut in bad years.

brett said:
We have never tracked our expenses to that degree...never used Quicken or any other tool. We simply break it down between what shelter costs, what we spend on groceries, personal products, gifts, entertainment, restaurants, what we spend on dental/prescriptions, and what we spend on travel. It takes me about 10 minutes with my bank statement and credit card statement max. each month.

So far it has worked. Our numbers going into ER have been borne out by the past three years of retirement.

I think that you can over analyze this.
Click to expand...

My wife used to do the above with Excel. However, I found that Quicken saves me a lot of typing, and also keeps my investment records and expenses all under my fingertips.

If I happen to wonder how much electricity cost has gone up in 2010/2011/2012, just a few clicks tell me that.

It's just as painless to know how much I have received in dividends and interest, all summed up from 22 different investment accounts.

Absolutely no data entry. I love it. And I am sure that programs other than Quicken would offer the same functionality.
 
Last edited:
I have done such tracking in Quicken for years. Most of my spending is on credit cards whose transactions are periodically imported into Quicken and categorized. Once Quicken "learns" the category for a particular vendor, it is set so all I need to do is a quick review to see if all is in order. Same thing with banking/checks written, investment transactions, etc. And I then reconcile vendor balances monthly.

For spending in cash I just have a "Spending money" category that all ATM withdrawals go to because to me it is more effort than worth to break these out but I know other people are more particular about categorizing their cash spending as well.
 
About over-analyzing, yes, one can do that, but he can do the same with Excel, not just with Quicken.

As for me, to get the big picture, what I do is to ask for the "Total expenses for the last 12 months", then to compare it with the "Total expenses for 2012", then for 2011, etc... This lets me track the trend, to see if my expenses are creeping up without me knowing. Because 2013 is not over, I use the last 12 months as the surrogate.

Right now, I spent $5K more for last 12 months than for entire 2012. Uh oh! Where did that money go? It is indeed cause for alarm, because we are only half way into 2013, and if this persists, I will be spending $10K more in 2013 than in 2012.

A few clicks showed me that the extra was for a big one-time expense that will not be repeated. So, with my curiosity satisfied, I went back to Web surfing.
 
Last edited:
BigE said:
Looking for advice on expense tracking. I am about 4 years out from pulling the plug by rough back of the napkin calculations and based on Firecalc and a few other retirement calculators they all give very acceptable results based on a rough estimated budget. I believe it's time to really get down and anal and track our expenses, so the budget part of the equations is no longer a "rough" estimate. My wife and I are very much a team but I work a great deal and she runs the household/pays bills/etc. so she will be doing the tracking (first time). She is familiar with Excel but not any of the Quicken products. Should we investigate Q Lifetime Planner (do they still make this?). I know there are some who have mucho experience with QLP (like pb4uski) and I just went thru a recent thread on this (didn't want to hijack it with my question). My bank and checking account is linked to an Intuit product called "Financeworks"; anyone have any experience with this? Feel free to give ANY advice on this.

Thanks
Click to expand...
I use a very old version of Quicken, but I'm sure I could do just as well with Excel if all I want to do is track expenses and ordinary income.

I'm assuming that "familiar" means "isn't afraid to write formulas the might, for example, select all expenses for the last quarter" or else "knows how to use pivot tables".

I do the year-end analysis in Excel. It's easy to copy/paste a register report from Quicken into Excel.
 
Last edited:
If you or DW get overwhelmed with the detail, you could do what I do to get an extremely accurate measure of spending with very little effort.

We have just two accounts that money flows out of. So each month, I just note the total WD from each, and enter that into a spreadsheet. The only thing I need to adjust for is any money that went to another account. Like transfers to ROTH - that wasn't spent it was saved, or things we bought that we were reimbursed for. Watch taxes paid/refunded in April for the previous year, you might want to move those around, depending.

That tells me how much I spend. If you need to analyze it, you need to do more work. I've never bothered.

-ERD50
 
We go with the 80/20 rule. We can capture 80 percent very easily, the remaining is subject to an educated guess.

We based our retirement on 125 percent of our after tax requirements. So far we are bang on 100 percent over a three year period with some variations between years. We are fortunate...we have government health insurance which has made early retirement and budgeting for retirement very straightforward.
 
FIREd said:
I use both Quicken and Excel. I track my expenses and investments with Quicken and use QLP to supplement FIRECalc and various other planning tools. But I also use excel because it can be customized to fit my exacting needs.
Click to expand...

+1

We also use both Quicken and Excel. Quicken tracks our expenses and investments. Credit card, stock/fund/ETF prices, etc are downloaded into Quicken. Once in a while, we run some Quicken reports, export into Excel for more analysis.
 
Another (very gross) way of capturing what you spend is as follows:

- Take gross income from ALL sources for the year. (Pay, rental, dividends, investment withdrawals, ....any income that hit your household)
- Deduct out money that was diverted TO savings and/or investments. (401k, investment contributions, etc.)
- Take the difference. That's what you spent. Sure some went to taxes, some to groceries, some to shelter.... but if it didn't go to savings/investment... (or increasing your checking account)... it's spent... it's an expense.

That's the spending budget I used (5 years worth of data) for my retirement plan. Then I adjusted for retirement spending needs (increased health care, no SS or Medicare tax, increased travel.)

If the spent money is higher than you thought - time to get more details and figure out what you can whittle - or why it's bigger. (Like one time big expenses.)
 
We've used Quicken for over 10 years and I'm relatively pleased with its user interface. Once in a while I get frustrated when I can't get it to do what I want (special reports for instance) but then I slow down, read the help guide and settle in on a solution.

I'm sure there are other software choices. Excel to me is great for some things but I would not use it to track expenses. That's just me. I don't want to bother making equations, if statements, create charts, etc. when Quicken and other tools have all this build-in for $60 or less.

I track a wide variety of categories that are useful for DW and me. For us, toilet paper is not a grocery. Ice cream is not a grocery. Beer is not a grocery. Prescriptions are not a grocery. Even though all of these are typically purchased at a grocery store.

Good luck with your tracking. I find the exercise very useful and I hope you will too. I think it's a life-long hobby at this point.
 
I use Yodlee and it imports my expenses. For the most part, Yodlee puts my expenses in the categories I want. In addition, Yodlee tracks my investment accounts ultimately showing net worth. For my purposes, it has been easy to use, since it is seamlessly aligned with all of my accounts. Some folks here are concerned with having this much information on line. I am using keepass to protect my passwords. I am sure I have added some risk by using this more convenient approach but have elected to take that risk to allow me to track my income and expense almost without any input from me.
 
We are in the "Tracker" camp. I started about 4 years before retirement because I wanted to know exactly how much it cost us to live. I also wanted an idea of how much was really necessary spending and how much was discretionary. I used Quicken for a long time, but switched to YNAB (previously mentioned) because I wanted a better budgeting tool.

Now I know, pretty much to the dollar, where the money goes. I also can see trends which helps with forecasting. I can also tell DH with confidence, "We can afford it" when contemplating such luxuries as an expensive trip. We are in our 5th year of retirement and I plan to continue. It's become a habit now.
 
I'm in the tracking camp. But I don't go pouring over the details.

Instead I use the tracking as a guide for the year to keep myself honest.
 
You must log in or register to reply here.

Similar threads

S
Anyone still using Quicken 2014?
Replies
23
Views
954
spncity
S
calmloki
Quicken and T-Bills recording
Replies
5
Views
631
JoeWras
JoeWras
C
LLC Consulting Taxes
Replies
22
Views
1K
G-Man
G
O
Tax return changed and IRS notification
Replies
4
Views
456
oldphd
O
K
tax rate estimates
2
Replies
25
Views
2K
kens450r
K

Latest posts

Back
Top Bottom