Link to my sanitized, cleaned up worksheets that show how this works. The chart workbook can probably be copied as is, but your base tiller workbook needs to be modified to add in the 4 columns that specify the portion of the expense that each person is responsible for. The chart workbook uses the “importrange” function to bring in the transactions and categories from your base tiller workbook.
Below are some screenshots of the inputs/outputs I’ve set up.
Here are a few brief explanatory notes:
Disclaimers:
-
There are some imperfections in this workflow/spreadsheet.
-
This “sanitized” version of the spreadsheet has AJ/KJ inputs that aren’t matched up properly with the transaction on the row. That had to be done manually be me when sanitizing and I couldn’t get it to match up row by row for some reason.
Naming conventions:
- J = Joint, A = Ashleigh, K = Kyle, AJ = Ashleigh portion of joint expenses, KJ = Kyle portion of joint expenses
Transaction inputs:
-
If purchase is made on joint card/account, no input is required in the AJ/KJ/A/K columns (right side of screenshot). This is set up like this because we each pay 50% of the joint card purchases. If that was not 50%, additional formulations could be added to correctly attribute the right percentage to “AJ” and “KJ”. It may be able to be done by inputting the
-
If purchase is made on a personal credit card and is a joint purchase, but one person is paying for it all or part of it, then you would input the amount that each person is responsible for. We use splitwise to input this information as well, so that we know how much we owe each other for situations like this. My goal is to be able to take this information from Tiller, but I’m not there yet.
-
Any money moving between my personal accounts, my wife’s personal accounts, and our joint accounts are all considered transfers (“T in” and “T out” categories).
-
As you can see, there is an input for percentages (A%, K%). I used to have formulas in each of these 4 cells for every row. They were formulas that referenced each other as a circular reference, so that if the user inputs a number in any of the 4 cells, the $ cells are populated automatically. For example, if I want to input 40% into K%, and it’s a $100 expense, $40 will show up in the KJ$ cell and $60 will show up in the AJ$ cell. These $ cells are the cells used in the output charts.
Outputs:
Below are example outputs of the data (table, bar chart, and pie chart).
Use examples:
-
I will look at “K” actuals to see what my personal (non-joint) expenses are.
-
Ii will look at “J” (joint) actuals to see what our overall joint expenses are.
-
I will manually add K and ½*J to understand what my theoretical financial spending projections would be if I paid for ½ of all joint expenses, plus my own.
-
I will look at K+KJ to understand what I actually paid for, personal and my portion of joint expenses.
