It’s been exactly 100  days that I last posted on the blog and this last post of mine was on shifting to Bangalore and starting with my first job! I shifted here in June, but even after 4+ months, the city is yet to impress me. Bangalore has little charm. Everyone here seems to be stuck in a town that’s away from home, the usual pursuit of happiness, you know the rest of the story…

Anyways, let’s not get overwhelmed with the sad life of Bangalore, this is not why I’m back on the blog. Like I said, I haven’t posted anything on the blog in the last 3+ months, it was mostly because I had been catching up with my new job. College did very little to equip me with what was needed at the job. So, you can say, I was busy filling that gap. Finally I have some control over things, so here I am back on the blog!

One thing that I am doing a lot these days is Excel. (I know it is a clever sentence! but, I’m just referring to the Microsoft tool). Probably this is true for all fresh graduates who are currently working as an analyst.

I might not have shared this but I have taken up a flat with 2 of my friends. We shifted to the apartment in September and it has been a really good experience so far. I had created an excel workbook to track our expenses and to solve our fundamental problem – who owes how much to whom.

You might find it useful and it is ideal for people who are living with friends and are spending money on a shared basis. The workbook captures the following metrics –

• Monthly expenditure for each person
• Monthly combined and average expenditure
• Monthly delta between the average expenditure and expenditure of each individual
• YTD delta (based on the same logic as above) telling you who is to pay whom and how much

 

Below is the screenshot of the summary tab of the workbook –

 

 

Each monthly tab is the sheet where you would enter the details –

 

The bigger table on left is meant to have all the expenses which cover all of the flatmates. You shouldn’t be entering any value doesn’t cover all of the flatmates in this table as this table is used to calculate the average expense which in turn is used in calculating who owes how much to whom.

The smaller table on right is an auxiliary table to track transactions between any 2 flatmates, and these transactions are not used for calculations in the Summary tab for obvious reasons that this amount shouldn’t contribute to calculation of average expenditure. The table has just 1 cell for each pair of flatmates, so you’d have to use a sum formula incase of multiple entries for a particular pair. There is a similar table on the summary page that shows the YTD pair-wise transaction.

Update – I’m working on expanding the side table as it’s better to have the details like the date and description for these pair-wise interaction as well. The homepage would have an sumif formula to give a 1 cell summary of who is to pay whom and how much.

I have parked the file in google drive. I hope everything is simple enough to be self-explanatory.

[google-drive-embed url=”https://drive.google.com/uc?id=1GNmVVivhwGd_Y6ylEGMoUaW2O19xZp6esSUQb1Wt2uk&export=download” title=”Home Budget Template.xlsx” icon=”https://drive-thirdparty.googleusercontent.com/16/type/application/vnd.google-apps.spreadsheet” style=”download”]

It’s not something out of the world but still solves the purpose without any frills. We’ve not had any problems with the tool so far, we’ve just hosted the file on google drive and thus all the flatmates get a notification whenever there is any update in the file, isn’t that convenient!

All you have to do to start using the file is put the correct names of the flatmates in the summary tab. The monthly tabs use Data Validation for which the list got updated when you replaced the dummy names with the actual names. However, you’d have to update the formulas and the auxiliary table if the number of flatmates is >3.

Let me know in the comments below if you found it useful and are using it or have any suggestions for improvements!