How to Calculate Cash Flow for an Account in a Tiller Google Sheet

I was recently asked if Tiller offered a spreadsheet or template that could calculate the net cash flow of a single financial account.

Read: All About Cash Flow and How It Helps You Achieve Financial Goals

In short, the answer was no, but I figured out a way to build this quickly using a mix of simple formulas and a sheet featured in our Reports Master spreadsheet.

Calculating cash flow of a single account

First, add the “Type” column to your Transactions sheet by following the steps in this help doc. I added mine to the right of the Account column so it ends up in column G.

Next, add the “Account Transactions” sheet from the Reports Master to your spreadsheet by following the steps in this help doc.

Then, assuming that the Type column is in column G in Transactions and your Amount is in column E in Transactions enter these formulas into cells at the top of the Account Transactions sheet.

For Income enter (I entered this into cell F2)

=sumif(F5:F,"Income",D5:D)

For Expenses enter (I entered this into cell G2)

=sumif(F5:F,"Expense",D5:D)

For the net cash flow, assuming you entered the two above into the same cells I did enter (I entered this into cell H2)

=F2+G2 

Give it some flare

I added headers, and then added a little custom formatting to the cells by adding a fill to the headers Income, Expense, and Net Cash Flow.

I also added a fill to the amounts and highlighted my cash flow in green. The cash flow amount is a great example of when conditional formatting might come in handy.

You could add a conditional formatting rule to the cell (under the Format menu > conditional formatting) to have it turn red if the amount is a negative number.

Stay on top of categorizing

Keep in mind that this will only show you cash flow based on transactions that have been categorized. If you have uncategorized transactions you’ll see the Type column is blank in the Account Transactions sheet and your cash flow won’t be accurate until you finish categorizing your transactions.

It’s also ignoring transactions that are categorized using Transfer categories since those shouldn’t be factored into your cash flow.

Bonus

If you want to see the cash flow for your budget in the Tiller Budget you can enter =x10 into any cell on the Budget Dashboard to pull the cash flow (income – expenses) value from the hidden area of this dashboard.