What aspects of your finances do you graph?

[Cecil]

Warning: Extreme Excel-geekery ahead.

I'm an Excel geek. I have a giant spreadsheet that tracks all aspects of my financial life, from net worth to income/expenses to investment returns to asset allocations to future projections. It's got VLOOKUPs and pivot tables galore.

One thing I like doing is making graphs of different aspects of my financial life. It makes it way easier for me to understand how things are going, rather than looking at a sea of numbers (as awesome as a sea of numbers is!!). I thought I'd share some of the ones that I've created for those who are into that thing.

Does anyone else do anything similar? There must be some numbers people on this forum. Do you have any cool charts?

Working Income, Expenses, and Investment Income plotted as 12-month moving averages. Also includes the monthly income I expect from a 4% SWR as the lower green line. When that line crosses the red line, we are FI!


Total balance of all my investment accounts, plotted against my total contributions. This one lets me see how much of my portfolio is due to gains versus contributions.


Savings rates. The dots are the individual months and obviously bounce all over the place. The green line is a rolling 12-month moving average, and the red line is our lifetime SR.


Years of expenses saved, based on my last 12 months of expenses. I've projected this graph out to when I expect FI (~age 40). This graph has been exciting, between lowering our expenses dramatically and the stock market going on a tear.


Red is our actual monthly expenses, and the blue line subtracts off the passive income we could derive from our investments at a 4% SWR. Cool to see  it dropping as we save more!

[the lorax]

hello, I'm also a numbers person and excel geek

Your graphs are awesome!

The only things I graph currently are actual versus budget spend, net savings per fortnight, net worth and I've been trying to figure out the best way to keep track of investment returns and asset allocation. I've never managed to find a use for pivot tables in my personal finances, use them a lot at work though.

Your post has given me some ideas of stuff to track!

[FIreDrill]

Wow, that's a lot of graphs, lol.  The only thing I track in a graph is our NW and that it through YNAB.  I track our savings rate but not in a graph form, I may have to start that.

[Ian]

Most things I prefer seeing as raw numbers, but I have an equivalent of your first graph. Over time I think it will be useful to compare expenses to various sources of passive income, though I don't have enough data yet for mine to be very interesting.

[tracipam]

I like your graphs, too--I do the same thing.  Numbers are nice to look at, but to get an idea of trends I have to do pictures.  Yay for excel! 

[Spork]

Ok, but remember:  You asked.  Danger... graph nerd.

This is a home built system.  Data input is done using our "books" in gnucash.  It just reads the back end file.  Human readable output is to cacti.  Cacti is a graphing system designed to graph networking stuff... but you can graph anything with it.  You can zoom in/out and see various levels of detail.  It graphs in a sort of "ticker tape" format by default, which is what I use. 

I also dump all of this on demand to a text based cgi web page because I found that I am visual and want graphs and the wife is a numbers person and wants numbers.

Everything is automated... data gets updated at least 2x a day with stock/fund pricing and such.

* Asset Summary - bank/stock/mutualfund/misc
* Asset Type Percentage (same as above but as a percentage)
* Assets: Financial vs non-financial
* Assets vs Liability
* Liability by itself
* A summary of "retirement" accounts vs non-retirement accounts
* Asset type percentage (expressed as retirement vs non-retirement vs other)

* Retirement accounts (breakdown of every account on one graph)
* Trading accont (breakdown of every individual stock on on graph)
* trading account cost v value (sum of basis vs worth)
* Mutual fund account (breakdown of several mutual funds on one graph)
* mutual fund cost v value (sum of basis v worth)
* 401k account (breakdown of the individual funds in 401k)
* 401k account cost v value (basis v worth)
* Other funds/stocks/bonds (used to have several things... now just some old savings bonds here)
* Bank/Cash accounts (breakdown of several cash/checking/etc accounts)
* Savings breakdown (we have some liquid savings all categorized and earmarked for different things: pets, gifts, insurance, tools, tires, etc. this is the breakdown)
* Emergency fund (how many months it will last graphed using expense data from 30days, 90 days, 365 days and 5 years)

* ROI comparison (graph with every mutual fund, every stock on one graph and "roi factor" )

For every individual stock and every mutual fund, there are then 3 dedicated graphs: cost v value,  IRR% and ROI factor.

For income/expense I have the following graphs using 4 sets of data: 30day, 90day, 365day and 5 year.  This includes:
* income: active vs passive
* all income vs expenses (nonsalary income, expenses, swr4%, swr3%)
* nonsalary income vs expenses (nonsalary income, expenses, swr4%, swr3%)
* savings percentage
* dividends (breakdown of all dividends per stock/mutual fund)
* tracked expenses (breakdown of some big ticket expense categories)
* Level 1 expenses (this is a "roll up" ...  expenses are in categories and subcategories... this is a rollup to the top level category)

* time to retirement (a combined graph of some of the stuff below... how many years to retirement computed using various methods)
* retirement required (a combined graph of some of the stuff below... how much stash is required using various methods)
* nestegg vs target (combined graph of the current financial assets vs 20x expenses and 25x expenses ... computed several times with various data sets)
* ERE current ratio (Fisker's ratio... retirement target is 25x... computed with various data sets)
* Then... for each data set (30 day, 90 day, 365 day and 5 year) there is a separate set of dedicated graphs:
**  Time to retirement
**  estimated retirement percentage
**  estimated retirement (actual assets vs required assets)
**  current withdraw rate (how long would current assets last at current expense rate and 0% return)
**  current withdraw rate 3% (how long would current assets last at current expense rate and  3% return)
**  current withdraw rate 5% (how long would current assets last at current expense rate and  5% return)
**  current withdraw rate 8% (how long would current assets last at current expense rate and  8% return)
**   ERE current ratio



* Firecalc 365 (graph of current 365day data run through firecalc for "retire now", retire 1 year ... , retire in 8 years... expressed as percent success)
* Firecalc 5 year (same as above with 5 year data)
* Firecalc first fit (years to retirement using current expenses/savings for both 1 year and 5 year data)
* Firecalc required portfolio (actual portfolio vs what would be required figured out with firecalc and a little brute force... 1 year data and 5 year data)
* firecalc current inputs (expenses, savings, portfolio value for 1 and 5 years)
* firecalc current outputs (highest/lowest/average portfolio for 1 and 5 years)o* firecalc cache age (hours since last firecalc run)

[Alfred J Quack]

Ok, but remember:  You asked.  Danger... graph nerd.

Wow, that's very extensive, I'm impressed :D (any chance of images (without numbers?)

Personally i've tried several sofware packages and eventually ended with a basic excel file with a new tab for each month and a new file each year. NW and Mortgage are seperate tabs but the modify only once a month so no graph there (maybe later). Only thing I'm not satisfied with at the moment is that the graph is too disproportionate, the house/mortgage bar takes 75% of the space where I'd like to equalize that based on expected expenses but that might skew the result/interpretation. I'll have a long think on that.

I've found that the most basic approach works best for myself but this is not so good for long-term graphing. Luckily my new bank offers a mint-like view with longer term income and expenses.

PS. I use the free Kingsoft Office which works pretty neat.

[Joel]

Ynab has a cool net worth graph that I look at periodically. That's about the extent of my graphing. Maybe annually I will check my savings rate to see what it is at. But other than that I just make sure I spend based on my ynab category balances.

[AccidentalMiser]

Cecil, Spork:  Great graphs!  How many hours per week do you spend entering transactions and other data. 

I occasionally knock together a projection spreadsheet or two and I (mostly) like the basic graphs on Mint but what you guys have is great!

[tariskat]

Wow, what a badass set of spreadsheets!  I just graph my income, expenses, debt (all student loans), total in investments (gains and contributions for my IRA, the basic status at the end of the month), and income from reachable/non-retirement investments (e.g. Prosper).

And now that I'm thinking about it, I realize I've been lumping my IRA CD interest into the wrong bucket... oops.  Oh well.

[sheepstache]

Nice!

I mostly do my thinking with the numbers, but graphs are a good motivation for some reason.  I inherited a spreadsheet with a networth graph and an income vs. spending vs. passive income graph.  I keep meaning to make one with expenses -taxes so it shows a better idea what I would actually spend in FIRE and a rolling 12-month average of passive income. 

Not having any other significant experience, the LibreOffice graphing feels a little clunky to me, so I'm mostly responding to this thread just to have a bookmark for software and graph ideas.  Thanks!

[Spork]

Cecil, Spork:  Great graphs!  How many hours per week do you spend entering transactions and other data. 

My graphs pull from the books I'm already keeping.  Both the wife and I are a little anal about tracking stuff, so ... we're entering it anyway.  We put data in gnucash, <and then a miracle happens>, graphs show up.

Wow, that's very extensive, I'm impressed :D (any chance of images (without numbers?)

Um, sure.  I've got a few hundred graphs, but I only use a couple of styles of cacti graphs, so I can show a couple of basic styles.  I've redacted account names, numbers, what equities are in them, etc...

The most basic style is a line graph.  You can put any number of lines on it.  It crawls to the right... you can zoom in/out.  The first attachment is a 2 year view of that style.  (The big jump in actual portfolio and dip in 1 year required portfolio was an error in my scripts that sat there for a long time before I noticed it.  I was trying to do some fancy stuff while we were building a house and ended up accounting for some expenses twice in the graphing.)

The second example is a 10 year view of a trading account using stacks.  In other words, these are solid graphs stacked on each other, giving a good view of the overall value of the account. 

[sol]

We have a complicated net worth graph, showing total and component values historically and in the future based on variable return expectations for each asset class.  I reproject the future values annually while keeping the old projections graphed, to get an idea of the spread of expectations bases on year to year variability.

Two separate real estate graphs.  One for the rental property tracking comparing the value of cashing it out by selling vs the value of ongoing rents.  A second one for our primary homre, showing mortgage balance and projections under various payoff schedules vs selling prices after commissions needed to hit certain benchmarks, like "our housing cost will have been equal to our tax and interest payments."

A net present value calculation for our pensions, after each year of working, which shows how much our pension value increases for each additional year of work vs how much we will have invested in it.  This is very sensitive to the inflation and discount rate you use, so I think it's only helpful in the relative sense.  It shows that federal pensions are virtually worthless unless you work to very near retirement age.  Retiring even a few years early really guts the benefit.

A college fund graph.  Our state's 529 is a credit prepurchase program instead of an investment account, so its value is scaled to future increases in the price of tuition for state schools.  We track how many quarters of college we've accrued for each kid, and their current and future expected dollar values.

A Roth IRA rollover pipeline spreadsheet (not really a graph) which shows how much we will have to convert from each account in each year after retirement in order to fund our anticipated expenses five years later.  This depends on the values in each account and the year in which we retire.  It also tracks net worth over time under the various drawdown scenarios, and accounts for expected pension and SS payments based on lifetime earnings.  Basically I started with our anticipated expenses and then broke down where each of those dollars would come from: Roth principal, TSP->Roth rollovers, taxable accounts, or eventually pensions and SS.

A crossing points graph that shows our expenses over time vs the income our assets have supported or should support over time at various withdrawal rates.  The difference between 3% and 5% is like six extra years of working.   Ouch.

[Cecil]

Cecil, Spork:  Great graphs!  How many hours per week do you spend entering transactions and other data. 

Hours per week? A handful, but then I find it fun. I could certainly set it up so that it's all automated, but I like doing it manually.