The simplest home budget application

As you probably all know, it is Excel. I would argue that is is also the most powerful one. I want to share with you a spreadsheet I created last weekend to finally be able to predict my financial condition. I wanted to have a single chart that would plot the expected balance of my account on each and every day of the coming year by taking into account

• planned expenses and incomes
• average monthly cost of living (mostly food and entertainment)

Logically, the task is quite simple. You list all your expenses and incomes in a table with amount and date. Then, you list all days from now till the end of the year. Finally, for each day the expected balance would  be the sum of expense and income table entries with dates earlier than or equal to current.

The problem is how to implement it in Excel. I was hoping to use SUMIF formula but it turned out that it only accept very simple conditions that can’t contain variables. I nearly dropped the whole idea when I learned about a thing called ‘array formulas’. They are similar to apply operator in functional programming — they apply a formula to each cell in a range. Here’s an example:

{=SUM(IF(Sheet1!$B$8:$B$38<=B2;Sheet1!$C$8:$C$38))}

This tells Excel to apply IF(cell_value<=B2;cell_value;0) formula to all cells in Sheet1!$B$8:$B$38 range and then sum up the results. Have you noticed something unusual? The curly braces? Yes, they distinguish array formula from ordinary one. The trick is, you can’t just put the braces in the input field. To add them you need to press Crtl+Shift+Enter when focus is in the input box. Easy, right?

The results of these simple steps, at least to me, look quite amazing. Here’s my chart for this year:

And to draw yours, you can download a ‘template’ from here. And this is just the beginning. I am planning to add ‘tentative’ feature so that you can assign probability to expenses and incomes and the end result would be three lines instead of one: optimistic, pessimistic and expected.