Ultimate Financial Spreadsheet


#1

The main theme of my blog is helping people create their own financial plan. I’ve been creating a master financial spreadsheet with the idea that it could track someone’s entire financial life. I’m a CPA financial planner by day, so I know the essentials of a financial plan well, but want some feedback for what types of things you might be tracking in your finances that would be helpful to incorporate.

I found some more things that would be helpful as well when I ran across this article this week:
http://www.budgetsaresexy.com/how-spreadsheet-changed-my-life/ (great article @J.Money !)

So, for all those spreadsheet nerds out there like myself (I know they exist because that article was super popular!), I’d love some feedback.

Right now it includes:

  • Cash flow tracking, savings rate calculations
  • Financial goals
  • Calculation of emergency fund based on 3-6 months (linked to the budget)
  • Debt payoff schedules
  • Net worth (past and current)
  • Taxes (this may be the best feature, as it will directly link to budget/cash flow categories to project taxes at any point during the year and is updated for new 2018 tax laws)
  • Calculation of life/disability insurance coverage needs (also linked to the budget)
  • Summary of insurance policies
  • Projecting retirement calculations and IRS/401k investment growth projections
  • College saving calculations
  • Aggregation of all investment holding allocations and comparison to desired allocation

I’m debating whether to add FIRE-related calculations as well.

So, what are some things that you track related to your finances that you’d love to be able to automate better or that you use a spreadsheet for?


#2

I have a spreadsheet that has many of the same things. It’s evolved over 15+ years since I first created it.

Some other things I have:

Investment Tracking
Purchase History for major items (appliances, lawn mowers, etc) - track cost and purchase date
Christmas Budgeting
Allocation for Income Tax Refunds
Tracking of Blog Income
Tracking of Free Credit Report Checkups

I also have a ‘Misc’ tab that has random things that I’ve calculated or kept track of over the years that really don’t fit anywhere else. This can be kind of fun to look back on.


#3

I love the idea of incorporating credit reports, maybe as an attachment within the excel file. And major item purchases as well (maybe with the receipt embedded in the file?!). What exactly do you track with your investments? Past performance and/or future expected performance?


#4

Great list @MakingYourMoney and @moneybeagle I’m also tracking all home purchasing and remodeling costs in one tab and include taxes, insurance, and interest. I’ve got one tab for our main home, our place in Florida, the family cottage we share with my husband’s brother, and new rental house.


#5

The only thing I use a spreadsheet for these days is to re-balance my investments to keep them in line with my four fund as per my investment policy statement. Would be great if you built this into your sheet for folks out there.


#6

Yes, tracking home improvements would be a great addition even related to the tax portion. It can be a nightmare to piece together how much you put into your house if you face the situation where it ends up being a taxable gain (I’ve been there!). Thanks for the suggestion Amy!


#7

I don’t currently calculate out exactly what would be needed to rebalance everything. I think that would be extremely useful. Thanks!


#8

A few extras mine has:

  • leverage calculator - at what level of borrowing at what interest rate can an investment cash flow stream be self supporting? Then add stress test calculations should rates rise.
  • asset sales scenario analysis - compare the tax implications of selling an asset held with various types of wrappers (e.g. tax deferred pension, tax fully paid saving account, taxable account). Helps figure out what to sell in what order to find FIRE (if selling down capital is your thing… it isn’t mine)
  • salary packaging calculator - what is the impact of things like salary sacrificing into a pension, taking benefits in kind rather than cash, etc
  • inflation adjusted returns - any trend/projection/period-on-period chart or comparison is lying to you unless you’ve brought the numbers to a common inflation adjusted base.
  • investment performance analysis - just how much of the growth in an investment is due to capital growth, how much is dividend reinvestment, and how much is new money being added. Track that over time and it shows why we often don’t feel richer when our investment value increases if that increase is because we topped it up.

There is more, but those are the main ones most likely of interest to this audience.


#9

This is next level stuff - love the suggestions. I’ve incorporated dividends vs. growth vs. contributions, but thought of some good charts/presentation ideas when reading through. I like the idea of tracking potential tax gains on the investments as well by including not just the current value but basis. Thanks for the suggestions!


#10

That’s a great list and is pretty comprehensive. I have some of those on my tracking spreadsheet but also have FI calculations and other coverage metrics, like how many months of expenses do I have in my emergency fund, etc. One other thing I try to roughly track is the deferred taxes in our 401ks and taxable accounts. While I have no plans to liquidate and incur the taxes today, it useful to know what your net worth looks like with everything on an after-tax basis.

There’s some good ideas in the comments from others too. Will need to look into adding some of them.


#11

Lol! A lapsed accountant and non-practicing financial planner I may be… a spreadsheet geek sure… but not sure I qualify as a next level spreadsheet geek!


#12

Ah, those accountants and their spreadsheets. Yep, next level :wink:


#13

Sounds like you have yours set up well! What tax rate do you use to calculate your deferred taxes? Your marginal rate currently or an effective rate you expect to be at in the future years?


#14

If memory serves, I use 20% for 401ks and 15% for taxable. It’s all a guess anyway where markets will be over the next 20-30 years so no need to be precise. I just wanted an approximation to recognize that my current net worth has some level of tax burden still attached.


#15

nice! has anyone said credit score yet? that’s a fun and easy cell to update once a month :slight_smile:

I also have a section for “business $$/assets” as well, and then I used to have a spot for all kinds of ratios bloggers made up but for some reason they’ve fallen off it over the years, hmm…

I can’t remember them all now, but one of them was my own “Lifetime Wealth Ratio” - ie. Net Worth ÷ Total Income Earned. It was fun updating it here and there :slight_smile: Here’s the post on it if interested: http://www.budgetsaresexy.com/total-lifetime-earnings-wealth-ratio/


#16

That makes sense. It’s as good a guess as any!


#17

I love the idea of including the lifetime wealth ratio with the savings rates. Thanks!


#18

I have a spreadsheet to track our charitable giving so my husband and I know where we stand with our annual goal. I created a similar one in Google sheet and included in today’s post. https://downsizeyour2080.com/2018/01/14/motivations-for-charitable-giving/


#19

I love it, both as an idea for goal tracking and for tracking for tax purposes as well!