Finance Friday: Data – Part 1

Finance Friday: Data – Part 1

Happy Friday everyone! It’s that time for another installment of Finance Friday. Buckle yourselves in cause this one is a monstrosity for sure and will take up multiple parts since it’s so massive. Today we’ll be taking a look at my Data sheet. Essentially just a single large sheet that acts as a sort of database for my other sheets! It’s not the prettiest, but it gets the job done, storing a lot of monthly information. In Part 1 we’ll be covering from Rows 1-111.

You can download your own copy here. Like previously, this is all using fake data, so I would suggest plugging in your own numbers here and there to get a feel how things would work for you.

This sheet really ties together everything in my whole Excel workbook. Like I mentioned in the initial Finance Friday post, these will be slowly building on one another and this one is definitely a big testament to that. So if you’re looking to mimic my sheet in anyway this is definitely an important step NOT to skip! I’ll be going over each of the colored sections more or less individually unless they have similar traits to other colored sections. The screenshots given will only span a few columns. This is because the entire sheet is more or less just a collection of columns which represent a single month. No need to see any more than a few.

Cashflow Data

I’m skipping the headers (Year/Month/Age) since they are relatively self explanatory. What I consider the first section of the sheet consists of a bit of a repeat from the first post in Cashflow – Monthly. This covers the ASSETS, DEBTS, CASH FLOW, EXPENSES, and MISC TOTALS. You’ll see the exact same row headers here that match up with one of the headers on that sheet. Here I’m just simply storing that data once more, but adding one extra step. You’ll note that aside from the first header in each section (For the Assets section I’m referring to Cash) I’m adding an additional row below each main row which adds up the total of the two categories above. This won’t make much sense now, and isn’t completely necessary to do, but I use it to make one of my charts in a different sheet.

As a side note, you can obviously feel free to change the headers as you wish to suit your needs. For Example: You may not feel the need to keep track of a Cryptocurrency Contribution or Vision Insurance. Just make sure that you have a one-to-one match with the headers you use in Cashflow – Monthly!

In the MISC TOTALS section we are referring to similarly named rows from the Cashflow – Monthly sheet. This data is actually pulled directly from that sheet. These are things like True Expenses and Net Cashflow.

Tax and Savings Rates

The TAX/SAVINGS RATES section is a collection of rates which are also captured from the Cashflow – Monthly sheet. Additionally, we are calculating the 4 month average of each of the rates. This way we can get a good idea of how we’re performing over a longer period. Feel free to add 12 month averages as well if you have the data to support it.

FI Metrics

The FI METRICS section is pretty eye opening and a great way to envision your current progress on the FI (Financial Independence) Track. This section is the first to take advantage of the VARIABLES section which we’ll discuss in next week’s post. Just know that this section stores a few constant points of data that we’ll be reusing.

Starting from the top we have the Yearly Nominal Salary. This row changes every time your salary changes. Fill in the months appropriately for what your salary is for those months. If you only get a salary change once a year this is simple since you just update the whole year with the new salary. Estimated salary is just as you’d expect. It uses an estimate for your annual raises which you’ve provided in the VARIABLES section and calculates an estimate of your salary each month. If your salary changes you should update the estimate by adding the new salary as the estimate for the month it starts.

For example: If I got a raise in March, I would set the Est. Salary in March to my new salary. Future estimates will continue to pick up from there.

I pull down the Est. Pre-Tax Savings Rate into this section for easy access, but it wasn’t totally necessary. This is used in calculating the Networth Prediction below. The calculation is already in the formula on the sheet, but at a high level we use the following: Previous Networth, estimated Market Movement/Raises, current Pre-tax Savings Rate, and Estimated Salary. This is one of my favorite data points to look at. It really puts into perspective how you’re progressing towards FI at your current pace.

Pull Down

Below the estimate for Networth, we see some metrics on what your actual yearly/monthly/daily spendable cash might look like if you retired right now. The Current SWR (Safe Withdrawal Rate) is pulled from the VARIABLES section. This is the rate at which you’ll pull cash from your portfolio each year when retired. Using our projected Networth and our chosen SWR we can see how much we’ll have available to safely spend at a certain month in the future. If you have on hand your average annual expenses, you can cross-reference that number with your projected Yearly Pull down. The point that the pull down becomes higher than your expenses you would be fully Financially Independent at that month in the future!

Future Networth Estimates

This section is very similar to the Projected Networth estimate above, but adds a couple of additional projections as well. I graph these projections separately from the above section which is why I wanted to keep them together. The three different outlooks do the same exact calculation, but use different constants to show differing market conditions. In Optimistic, as you would expect, we use a higher annual market performance. Additionally, we use a higher expected annual raise. In Pessimistic, we do the exact opposite and plan to expect some poor performance from the market and your career. Best Guess is the exact same as Projected Networth, BUT this time around we’re adding in Inflation to the equation. This will allow us to see our Networth with respect to today’s dollars. All of these constants mentioned are stored in the VARIABLES section.

Investment Stats

The INVESTMENT STATS is our final section of today’s installment. I added a few more columns here to really show what’s going on. Here we take note of our Total Investments value each month. This comes from the sum of our assets not including cash. Additionally, the Total Investment Gains is the difference between the total of the current month and the previous month. Running totals are an accumulation of this month’s total added to the previous month’s total. These gains are separated by two types: Contribution and Market gains. Gains based on what I contributed that month and gains based on the change in the market value at the end of that month.

For Example: In March my investments might total to $10,000 and in April they might total $13,000. Between the end of March and April, I’ve contributed $2,000. The total Investment gains are $3,000 and from this we can deduce that the market gains were $1,000 since I added $2,000 in gains myself with contributions!

Using these figures we can calculate the market movement for that month and make estimates based on the previous months data. This is what all the remaining rows in this section are about and you can check out the calculations there yourself, though I don’t think they are perfect. The 12 month estimate sections contain 0’s because there wasn’t enough columns shown to get 12 months of data.

Closing Thoughts

This sheet can really take a lot of playing around with to get exactly right, so don’t expect to get the full value immediately. I would take the time to reverse engineer it a bit on your own. In the next installment we’ll likely complete the rest of the sheet so stay tuned for next Friday!

Leave a Reply

Close Menu