Along with rebalancing my portfolio, the other thing that I do with my finances before the year ends is updating my household’s end-of-year spreadsheet. It doesn’t have to be overly complex. Mine is very simple and can be done by anyone who is Excel savvy.

I’ve been updating the Excel document since 2013 to help us keep track of our expenses relative to our income on a yearly basis.

What does mine contain?

I’m loosely describing below what my year-end report looks like. Yours can be completely different depending on your situation.

Itemized Expenses

The first column is reserved for the name of the institution receiving the payment. For example, I’d write “American Express” for my credit card payments (which, BTW, I always pay in full). For expenses that are difficult to track, I end up using the generic description of the expense (e.g. ATM withdrawals).

The second column contains the total payment that I’ve sent to that institution for the year. In the case of a generic expense, I just use the column to total the expense for that category.

I use the third column for the monthly average, i.e. I divide the corresponding value under the 2nd column by 12 to obtain the average. This is useful for budgeting– you’ll know how much per month to set aside for that item next year.

Lastly, the bottom row contains both the yearly and monthly average totals (sums of second and third columns, respectively).

Net Income per household earner

This section is used to list our household net income as individual wage earners. It’s very similar in format to the above except that the ‘expenses’ are recorded from the perspective of our employers. I write my wife’s name and mine under the first column since we are the only ones working in the household.

The second column contains the net income received by the individual for the year. Like what has been done above, the third column is used for the monthly average and the totals are calculated on the bottom row.

Total Savings

I reserve a section to record the total savings that we made that year. The total is calculated by subtracting the total expenses from the combined total net income that my wife and I earned that year.

In addition, I also record our total 401K contributions (including employer contributions) for the year as separate line items. It doesn’t make sense to add them to the total savings because they were made before taxes.

Net Worth Statement

This spreadsheet also contains our net-worth statement (i.e. assets minus liabilities). This is sort of like the year-end balance sheet of a Fortune 500 company except that the items are expressed in hundreds instead of millions (not to mention that the C.E.O. of the household is also the Chief Financial Officer).

Institutions

This tab contains a list of institutions that I conduct business with. It includes the account numbers, website info, and other essential information. I try to refrain adding password information in this tab for security reasons.

Why is this important

Maintaining this spreadsheet has the following benefits, among others:

Decision-making tool

You can use it to make informed budget decisions for the year ahead. For example, you can adjust your emergency fund goal for next year based on the average monthly expenses that you have incurred in the year.

Historical Projection tool

It helps you perform intelligent ‘guesstimates’ like when you need to project your future net worth. The numbers can help you set long-term goals such as setting a retirement savings amount goal. You see, retirement is not a matter of reaching a certain age– who wants to retire broke?

Recordkeeping tool

I’ve uploaded this spreadsheet on the ‘cloud’ (One Drive, to be specific) so I can easily share the document with my wife (who happens to be my household’s chairman of the board). In this way, it is easily accessible from any device and not forever lost in the unlikely event that I get run over by a truck.

That, my friend, is why you need to create a household year-end report…

I wish you all a happy, healthy and prosperous New Year!!!

via GIPHY