Reconciling cashflow with changes in net worth

jgeisman

Changes in net worth on the Net Worth Report should reconcile with the net on the Income and Expense Report (which is the same as a Cashflow Report) but it doesn't. Stated another way, the net worth of last month plus the net cashflow for this month (income minus expenses) should equal net worth at the end of this month but it doesn't.

Has anyone else faced (and solved) this problem?

NotACPA

Have you taken into consideration Investment Gain or Loss?

J_Mike

DOes your net worth include investment accounts?

If so, changes in investment values due to market price changes are not cash flow.

jgeisman

Very helpful. I found you also need to include transfers in the cash flow reports to capture any investments that were sold. You need to include transferred amounts to cash from the investment accounts.

Tom Young

First of all, I wouldn't use the Cash Flow report to try and reconcile changes in Net Worth as that report does not automatically include ALL your Quicken Accounts and the Net Worth report does. If your Net Worth report is "looking" at all of the Accounts in the file then the Income and Expense report needs to look at all of those Accounts too.

"Very helpful. I found you also need to include transfers in the cash flow reports to capture any investments that were sold. You need to
include transferred amounts to cash from the investment accounts."

Yet another reason to NOT use the Cash Flow report. Using an Income and Expense report obviates the need to deal with any transfers.

jgeisman

Thanks to all who responded. Overall very helpful.

Here's what I found. I couldn't get Cashflow and Net Worth to tie exactly but within 2%. I was able to do this by using the Net Worth report from Net Worth & Balances and the Income/Expense report from Spending. I used the same date ranges, selected all accounts, categories and payees and included all transfers but excluded unrealized gains.

I was tearing my hair out but think I've cracked the code. Hope this is useful to others.

Tom Young

Every month I use the Net Worth report from the prior month and the Net Worth report for the month ended, along with an Income and Expense Report for the month, and make sure they balance out. I do it without reference to Unrealized Gain/Loss in any of the reports, except at year end where I include Unrealized Gain/Loss in all the reports.

The only time it doesn't work perfectly - to the penny - typically is due to only a handful o things, and I've always been able to correct the error, or at least reconcile the numbers if it's a "prior period" entry, like going back to the prior December 31 date to make adjustments, based on the 1099-Div between, "dividends" and "return of capital." Likewise, if you create a new Account and populate it with an "Opening Balance" entry, (a direct adjustment of Net Worth ), that too would be an example where you wouldn't see the Net Worth effect through an Income and Expense report, but you should be able to include it in a reconciliation of the changes in Net Worth.

You should be able to reconcile beginning and ending Net Worth through an Income and Expense report. with reconciling items, occasionally, down to the penny.

jgeisman

Thanks Tom. Very helpful. Am now all set.

NotACPA

In "comparing to the penny" you should be aware that Q uses standard 5/4 rounding in calculating the value of investments and Fidelity (and perhaps others) truncate.

SO, if my various brokerage accounts are off by a few cents at month-end, I don't worry.

jgeisman

Thanks to all of you. Here's what I found: Cashflow is the same for Income and Expense Report (I/E) and Cashflow Report (CF) providing you eliminate internal transfers. It doesn't matter which you use.

In the Net Worth Report (NW), I include unrealized gains since they are the change in investments.

Cashflow during a month is NOT the difference between beginning and ending OVERALL net worth for a month because it includes changes in investments which have nothing to do with cash (thanks @J_Mike). Cashflow ties to the change in the Cash and Bank Accounts line of the Net Worth Report not the overall amount.

This makes sense to me. Cashflow is all about cash which is contained in the Cash and Bank Account part of the Net Worth report. Cashflow for a period is just the difference between the Cash and Bank Account numbers over that period.

The result? Over a 40 month period, there was a $2K total difference between cashflow calculated using either net worth or CF or IE. Any difference, @Tom Young or @NotACPA, is due to my slovenliness and rounding or truncated numbers.

I've set things up so I can model our future net worth as we age and monitor progress using the budget reports.

Tom Young

"Thanks to all of you. Here's what I found: Cashflow is the same for Income and Expense Report (I/E) and Cashflow Report (CF) providing you eliminate internal transfers. It doesn't matter which you use. "

Correct, because they then are the exact same reports.

"Over a 40 month period, there was a $2K total difference between cashflow calculated using either net worth or CF or IE."

Not sure exactly what you're saying here. Since you've made the the two reports into exactly the same report I don't think you're referring to the calculations differing between the reports themselves. After all they're working with the exact same entries, slovenly entered, rounded or truncated, so they've got to be coming up with the same cashflow. I assume the difference you are referring to is between the reports' numbers and the net change in "cash" calculated by taking the difference between the beginning and ending balances of the "Banking" Accounts?

"I've set things up so I can model our future net worth as we age and monitor progress using the budget reports."

Of course "net worth" incorporates all the Accounts in the file, and is explained by an Income & Expense report (or a Cash Flow report modified to look exactly like an I & E report) that also uses all the Accounts in the file.

jgeisman

"I assume the difference you are referring to is between the reports' numbers and the net change in "cash" calculated by taking the difference between the beginning and ending balances of the "Banking" Accounts?"

Yes. By definition there is no difference between a CF and an IE report...