Overview
Now that I have multiple years of transactions in my Tiller sheet, I want to be able to see my annual Income and Expenses for multiple years on one page and wanted to know what my average was over the number of years I choose.
In addition, I wanted to see, by Category and Category Group, my annual Income and Expenses over a period of years without certain Categories or Category Groups. One reason why? I am getting closer to retirement and want to know what spending has been without expenses that will be gone by retirement- e.g. kids college expenses, loan payments that will be paid off, getting rid of a car, etc.
I thought this would have existed but couldn’t find it so I created my own. I actually started with another worksheet I had already modified a while ago because i liked the layout. However, It’s been so long, I don’t remember the original source of the worksheet so I apologize to the original author!
Installation
You can download the worksheet from here.
Copy the worksheet into your Tiller workbook and you’re good to go. It uses the Transaction and Category worksheets.
Setup
To setup, the only thing you have to do is choose the Start and End year…
Once you pick the starting year, it will only allow up to 10 years to pull. This is simply a limitation I put in to limit the number of columns to display. Also, the earliest year it will allow you to use is the first year you have a transaction.
However, I think the real strength is being able to exclude Categories or Category Groups from the table so you can see the totals with these items.
To do this, hit the “+” above column P to display the following columns…
You can choose up to 6 Category Groups and 6 Categories to exclude from the data. Simply pull down the item and check the box to the right.
For example, here are my Auto Expenses, including insurance for my daughter’s Kia Soul…
I then choose to exclude this Category in column X through AA…
and it no longer shows up in the data…
Permissions
I encourage others to copy, use, and modify however you want.
Notes
- It will not display any Categories or Category Groups that have $0 for the years you choose
- You can also choose to Show “Hidden Categories” if you want…
- I purposely kept this very simple- no charts, nothing to do with Budgets, no limiting by accounts, etc. However, with a few tweaks all of these items are possible. I don’t see the benefit for my use.
- For the Average, it will calculate a simple average over the years you choose, regardless if the final year you choose is a full year or not. If the final year you choose is not a full year, this will skew the average. So, I also added an average using the run-rate of the current year = YTD/# of days into year * # of days in the year.
Would love to hear your comments, feedback, or questions.
Enjoy.
