Personal Finance Spreadsheet Re-do
Aug. 27th, 2012 06:24 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
livingdebI've been re-doing my personal finance spreadsheets after reading the Comprehensive Guide to Tracking your Finances by m741 on SkillsFIRE.
If you prefer Mint or winging it, the following will probably be a real snoozer, but if you like the power of setting up your own spreadsheets, you might find some new ideas in here.
Technical Change
The biggest change I made was to use the sumif command, which I'd never heard of before. This is good if you want to itemize things but also have a summary. Before, I made a separate column for each category so I could easily sum up each category (using a sum function for each column). Now I can have one column for the name of the category (and another weird data-combining column) and can still get summary data.
I actually checked out Excel: The Missing Manual to see if there were any other awesome spreadsheet formulas I'd missed, but I didn't notice any.
The Setup on SkillsFIRE
m741 has a nice clean setup of seven worksheets. He (just guessing on the gender) lists out all his expenditures on one and all his income received on another. (Actually, he has expenditures on two worksheets, but they could be combined.)
Then he summarizes all that on a "Finances" page that looks like a budget and he's got a graph showing his savings rate [(income - expenditures)/income]. So that's the cash flow information.
Then he's got an "Investments" page for his dividend stocks where he keeps track of current values for the stocks, quantities he currently has, dividends currently being paid, and lots of other stats to help him with future decision making about what to buy, sell, and keep. On the same worksheet he tracks his annual dividends for the year each year and graphs that.
On his "Net Worth" page, he tracks all his assets and debts by month and graphs his assets. Then he has a "Summary" page where he tracks his progress on various financial independence goals.
m741's post includes a well-written, detailed explanation of his system and a link to an actual spreadsheet (with fake data in them) which he invites you to copy and zero out so that you can fill in your own data.
Organizational Changes
I am not, however, copying that spreadsheet for myself. My setup is more complicated. I like to think that it's because I'm older and therefore have more baggage, I mean interests and concerns, and not that I'm just a slob and can't clean up my spreadsheets. So I have twice as many worksheets as he has. Even after taking this opportunity to clean things up. [Mainly I made sure to keep the cash-flow things (like budgets) more separate from the snapshot things (like net worth). I also got rid of a few things I no longer feel are worth the time of updating. So my organization is a bit more user friendly now.]
The biggest reason I have so many extra worksheets is that I do detailed tracking of more of my investments. Of all his different investments, m741 makes a separate page only for his dividend stocks. Admittedly, I have two pages for that (one for current holdings and a separate one for research). But I also have one sheet each for my mutual funds and for my savings bonds. I also have one for my pension and one for Social Security (you can estimate your future benefits by inputting all your annual salaries multiplied by their inflation factors and doing some additional math on that). And I have one with all my other, less complicated assets: savings accounts, pension balance, and property. For property, I'm keeping track of my extensive real estate holdings (my house--I average the Zillow estimate with the county tax assessor's estimate) and my fleet of vehicles (actually just my car; I don't even bother with my bicycle, roller skates, etc.).
Then I also track what my Roth IRA's value would be if it had risen at a steady 8% level or a steady 11% level and compare those to my IRA's actual value. If the current value is higher than the two estimates, I'll use one of those other numbers when deciding how much I can pull out under the assumption that the actual value is inflated. (Currently it's just a little bit higher than the 8% estimate.)
I also have a page to keep track of all my insurance policies, though I haven't updated it recently. Can't decide if it's a good idea. It's definitely a good idea to keep track of my beneficiaries of different things somewhere.
Like M741, I also have pages for income, expenditures, and net worth. And I have a single cash flow/budget page where I also track progress toward my financial independence goals. Finally I have another page showing the diversification of my investments, so I can monitor whether to rebalance.
Another complication I finally took care of is that I don't like the way some expenditures are highly irregular and make it hard to see the full picture. So I count the budgeted amount for things like car and house maintenance as a monthly expenditure, even if I don't spend anything, or even if I spend ten times as much, because that is the monthly cost of a lifestyle where I have a properly maintained car and house, etc. But then I also have to keep track of my actual expenditures to gauge whether my budgeted amount is actually realistic/true. I finally figured out a way I'm happy with in dealing with this issue.
So, I had thought that my spreadsheet was finely honed after all these years, but now that I've added some usability and gotten rid of some clutter, it's much more pleasant.
All of my explanations above are vague and useless compared to m741's if you are thinking you want to adopt some of these ideas, so feel free to ask questions.
Quote of the Day - At Hyde Park Bar and Grill: "If they didn't keep having such good service, we wouldn't have to pay so much" - me
Cake of the Day - This one's for when you want a cake, but you really like cupcakes, too.
Oh, and to follow up on the surprise awaiting my friend under his bathroom floor. Perhaps you guessed that it was not buried treasure or anything resembling the underwater wonderland you can see at the edge of the photo above. Perhaps you thought it was, at best, a rotted subfloor, and at worst, serious structural problems with the foundation. If so, you were right, and of those choices, he got the happy ending involving rotted wood (over the perfectly fine concrete foundation).
If you prefer Mint or winging it, the following will probably be a real snoozer, but if you like the power of setting up your own spreadsheets, you might find some new ideas in here.
Technical Change
The biggest change I made was to use the sumif command, which I'd never heard of before. This is good if you want to itemize things but also have a summary. Before, I made a separate column for each category so I could easily sum up each category (using a sum function for each column). Now I can have one column for the name of the category (and another weird data-combining column) and can still get summary data.
I actually checked out Excel: The Missing Manual to see if there were any other awesome spreadsheet formulas I'd missed, but I didn't notice any.
The Setup on SkillsFIRE
m741 has a nice clean setup of seven worksheets. He (just guessing on the gender) lists out all his expenditures on one and all his income received on another. (Actually, he has expenditures on two worksheets, but they could be combined.)
Then he summarizes all that on a "Finances" page that looks like a budget and he's got a graph showing his savings rate [(income - expenditures)/income]. So that's the cash flow information.
Then he's got an "Investments" page for his dividend stocks where he keeps track of current values for the stocks, quantities he currently has, dividends currently being paid, and lots of other stats to help him with future decision making about what to buy, sell, and keep. On the same worksheet he tracks his annual dividends for the year each year and graphs that.
On his "Net Worth" page, he tracks all his assets and debts by month and graphs his assets. Then he has a "Summary" page where he tracks his progress on various financial independence goals.
m741's post includes a well-written, detailed explanation of his system and a link to an actual spreadsheet (with fake data in them) which he invites you to copy and zero out so that you can fill in your own data.
Organizational Changes
I am not, however, copying that spreadsheet for myself. My setup is more complicated. I like to think that it's because I'm older and therefore have more baggage, I mean interests and concerns, and not that I'm just a slob and can't clean up my spreadsheets. So I have twice as many worksheets as he has. Even after taking this opportunity to clean things up. [Mainly I made sure to keep the cash-flow things (like budgets) more separate from the snapshot things (like net worth). I also got rid of a few things I no longer feel are worth the time of updating. So my organization is a bit more user friendly now.]
The biggest reason I have so many extra worksheets is that I do detailed tracking of more of my investments. Of all his different investments, m741 makes a separate page only for his dividend stocks. Admittedly, I have two pages for that (one for current holdings and a separate one for research). But I also have one sheet each for my mutual funds and for my savings bonds. I also have one for my pension and one for Social Security (you can estimate your future benefits by inputting all your annual salaries multiplied by their inflation factors and doing some additional math on that). And I have one with all my other, less complicated assets: savings accounts, pension balance, and property. For property, I'm keeping track of my extensive real estate holdings (my house--I average the Zillow estimate with the county tax assessor's estimate) and my fleet of vehicles (actually just my car; I don't even bother with my bicycle, roller skates, etc.).
Then I also track what my Roth IRA's value would be if it had risen at a steady 8% level or a steady 11% level and compare those to my IRA's actual value. If the current value is higher than the two estimates, I'll use one of those other numbers when deciding how much I can pull out under the assumption that the actual value is inflated. (Currently it's just a little bit higher than the 8% estimate.)
I also have a page to keep track of all my insurance policies, though I haven't updated it recently. Can't decide if it's a good idea. It's definitely a good idea to keep track of my beneficiaries of different things somewhere.
Like M741, I also have pages for income, expenditures, and net worth. And I have a single cash flow/budget page where I also track progress toward my financial independence goals. Finally I have another page showing the diversification of my investments, so I can monitor whether to rebalance.
Another complication I finally took care of is that I don't like the way some expenditures are highly irregular and make it hard to see the full picture. So I count the budgeted amount for things like car and house maintenance as a monthly expenditure, even if I don't spend anything, or even if I spend ten times as much, because that is the monthly cost of a lifestyle where I have a properly maintained car and house, etc. But then I also have to keep track of my actual expenditures to gauge whether my budgeted amount is actually realistic/true. I finally figured out a way I'm happy with in dealing with this issue.
So, I had thought that my spreadsheet was finely honed after all these years, but now that I've added some usability and gotten rid of some clutter, it's much more pleasant.
All of my explanations above are vague and useless compared to m741's if you are thinking you want to adopt some of these ideas, so feel free to ask questions.
Quote of the Day - At Hyde Park Bar and Grill: "If they didn't keep having such good service, we wouldn't have to pay so much" - me
Cake of the Day - This one's for when you want a cake, but you really like cupcakes, too.
Oh, and to follow up on the surprise awaiting my friend under his bathroom floor. Perhaps you guessed that it was not buried treasure or anything resembling the underwater wonderland you can see at the edge of the photo above. Perhaps you thought it was, at best, a rotted subfloor, and at worst, serious structural problems with the foundation. If so, you were right, and of those choices, he got the happy ending involving rotted wood (over the perfectly fine concrete foundation).