How do I generate cashflow reports rather than expense reports?

[Oon-Ee Ng]

I'm looking to estimate an annual/monthly budget, and the simplest way
I thought of to do that was to look at my annual expense reports (I
have data for almost a decade) to see trends (spending on books and
education related stuff is way up due to having young children, as are
spendings on food and insurance, while spending on eating out is down
slightly).

There's some pretty big holes in the data I'm looking at though, and
it's primarily because of debt. I'm paying off a housing loan monthly
(to the tune of a quarter of take-home pay, to get it done quickly),
and that does not show up in expense reports (the only thing that
shows there is interest accrued, which isn't much compared to the
amount I'm actually paying).

Obviously, the reason is because paying off a housing loan is (as I
model it) a transaction between my cash-in-hand/banking assets and the
large liability account called "HousingLoan". I *think* this is
standard accounting.

However that means that my annual/monthly budget, if only based on
expenses data, will miss this amount, and really I'm primarily
interested in balancing my cashflow rather than expenses. That is to
say, if I take home X amount, my cash outflow should be less than X.
If my monthly housing loan payments are Y, that means I need to budget
against X-Y rather than X itself.

Is there a way to generate reports on cashflow? Probably just
'everything that flows out of assets' would suffice at this point?

[Oon-Ee Ng]

Darn, hit 'send' too fast! Added another paragraph below...

Although now that I think about it, that would not model my
self-enforced savings (which are modeled as a transfer from
Assets:Banking:MyBank:Account to
Assets:Investments:MyBroker:AccountName or even
Assets:Banking:MyBank:FixedDeposit). Not just the local equivalent of
401k, but things like fixed deposits and scholarship funds. Ideally
I'd like to be able to see a number (monthly/annually) showing how
much money flowed from my cash-in-hand and equivalent accounts (credit
cards, savings/current accounts) to expenses+loans+long-term-savings.
I think that'd give me the base of a budget.

[Martin Blais]

No. At least I never implemented one.

 

Probably just
'everything that flows out of assets' would suffice at this point?

I'm not sure.

[Stefano Zacchiroli]

On Wed, Jan 25, 2017 at 04:05:33AM +0800, Oon-Ee Ng wrote:
> Is there a way to generate reports on cashflow? Probably just
> 'everything that flows out of assets' would suffice at this point?

I'm facing the same problem.

My solution (or, better: "workaround") is to chart not only income vs
expenses over time, but also (in a separate chart) income vs
"expenses+mortgage", where "mortgage" is the amount of debt I pay off
every month for the house mortgage. Of course you can replace "chart"
with "balance" or whatever other query you're doing to obtain similar
results in a different context.

IIRC the proper way to go about that in terms of tool support would be
to tag the accounts that are "cash-ish" for you and that you want to see
in these kind of reports. For me those would be Income:* Expenses:* and
Liabilities:Mortgage:*. On top of that you can add some easy-to-activate
knob that will pre-select those accounts.

Cheers.
--
Stefano Zacchiroli . za...@upsilon.cc . upsilon.cc/zack . . o . . . o . o
Computer Science Professor . CTO Software Heritage . . . . . o . . . o o
Former Debian Project Leader . OSI Board Director . . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »

[Alex Johnstone]

I suggest creating one or a series of queries to get what you need  - then probably some spreadsheeting if you need to do anything advanced.

I like keeping a summary table of monthly income and expenses, but which also includes my mortgage payments, and for that I use this query (saved in fava):

SELECT year, month, root(account, 2) as Account, sum(number) as Total, currency
WHERE
account ~ 'Expenses' OR
account ~ 'Liabilities:Mortgage' OR
account ~ 'Income'
GROUP BY year, month, Account, currency
ORDER BY year, month, currency, Account
FLATTEN

The root(account, 2) keeps it at a high level, like Expenses:Car. You can simplify it further with just root 1 and no month. Then you get your year's summary.  I just press the download as, and then paste it into my spreadsheet.

As I'm writing this, I realise that my query is old (from when I first started playing with it) and you could simplify it with:

account ~ 'Expenses|Liabilities:Mortgage|Income'

So, just add or subtract 

AND NOT account ~ 'Assets:Not:This:One'

 to your report and get the data that way.

[ni...@kirsch.org]

Is there a way to turn this into a quarterly query? 

Nick

[Martin Blais]

https://bitbucket.org/blais/beancount/commits/cef34085473e46256bad28c966a51ae3a488b7ae

hg fetch, I've added a QUARTER() function.

SELECT QUARTER(date), ...

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+unsubscribe@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/a830f428-b408-4b46-881a-c00895938633%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.