Net Worth Calcs off (Historical views, not current)

mark miller
mark miller Member ✭✭✭

I realize this is a broad question, but anyone ever dug into why, when you look back retroactively (over the years) net worth is off?

Specifically: I've been a user 20 years, each year i have a spreadsheet in which i update (for retirement planning/calculations) from quicken account amounts at the EOY. At that point, i 'snapshot' the amounts from quicken, putting into spreadsheet.

If i look back into Quicken net worth now for example, years back (3,5,7, etc) those amounts are different (all much less).

I've read other articles about categorization of 'other accounts' but thats dealing with the current net worth view (i'm not changing those so should be consistent..in my case excluded from NW calc).

Seeing the 'new NW report' rolled out in recent update, made me wonder if there is a underlying issue.

Answers

  • Quicken Kristina
    Quicken Kristina Quicken Windows Subscription Moderator mod

    Hello @mark miller,

    The main things that should impact net worth (and net worth calculations) would be if you delete an account (since that account and it's history would be gone) and if you mark an account separate (since accounts that are marked separate do not get counted in your net worth).

    Are either or both of those possible culprits for your historical net worth calculations being incorrect in Quicken?

    I look forward to your reply!

    Quicken Kristina

    Make sure to sign up for the email digest to see a round up of your top posts.

  • mark miller
    mark miller Member ✭✭✭

    No, unfortunately not.

    delete an account- I do not. Accounts that i move/zero out, i just hide them (for historical reasons but they are zero anyway).

    separate accounts- No, those are not included (never have been ) in m net worth totals.

  • Quicken Kristina
    Quicken Kristina Quicken Windows Subscription Moderator mod

    Thank you for your reply,

    When did you first notice this happening? If you use more than one Quicken file, is the issue consistent across all your files? Is there any pattern to the behavior or any accounts/account types that are being left out of the net worth totals?

    Thank you!

    Quicken Kristina

    Make sure to sign up for the email digest to see a round up of your top posts.

  • mark miller
    mark miller Member ✭✭✭

    This has happened for years (5-10)…As mentioned, just seeing the 'new' NW report come out prompted me to ask.

    Appreciate your responses, but doesn't seem like your familiar with the issue.

  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭

    I have a similar spreadsheet of net worth report data from an older version, but I can't say what Quicken version the prior data came from or when I initially or most recently produced it. In comparing that data versus my current Quicken net worth report (year end data only) I find:

    • For non-investment accounts, there were two rather small discrepancies. One I clearly redated a credit card charge from 1/4 back to the real purchase date of 12/31. The other is no doubt a similar minor reclassification from an account to a category. I am sure those are not the type of differences you are talking about.
    • For investment accounts,, I found 57 year end account balances that differed between the two reports by $0.01-$0.02. Again, not what you are concerned about, I'm sure.
    • For Investment account, I also found 12 cases where the difference for a year end account balance was between $37 and $23,000. Five accounts were so affected. In some cases, I can directly associate the difference to changes I likely made at some time to get taxable income reported in the correct year - re-dating a transaction from Jan back to Dec 31. In other cases, there is no obvious explanation for the difference. Could be a later share price adjustment, or a correction to a reinvested dividend (share count change).

    I have in recent years altered (corrected) some security spinoff transactions on old situations that might have impacted the net worth data, but I can't (won't) backtrack through those right now.

    Overall, I am more suspicious of things I have done than I am of Quicken misbehaving in this context.

    I will caution that deleting and rebuilding a price history can throw off historical valuations - particularly if a security has later had stock splits or spinoffs. The causative problem is that if you have the correct transactions in Quicken for those types of events, then the price history before the event needs to be the true trading prices. But virtually all of the data bureaus report an adjusted price that better represents the historical value of the current shares. Say you had 100 shares of a stock trading at $50/share that undergoes a 2-for-1 split. After the split, your now 200 shares are trading in in the $25/share range. Your raw prices in Quicken should be $50 before and $25 after the split. But if you delete or lose those prices and have to rebuild them, the data bureaus are likely to report a consistent $25 value before and after that event. Quicken would then show you had 100 @ $25 before and 200 at $25 after.

    FWIW, those 12 differences are among 186 year end account values over the last 23 years of data

  • mark miller
    mark miller Member ✭✭✭
    edited December 2023

    Thanks for your thoughtful response. As many of your points/observations are valid, not applicable to me.

    Because of many of the things you mention, I don't do auto updates, esp. with retirement accounts (401k, Non Qual, etc). I will periodically update manually. Similarly my 'EOY" updates are done in Jan also to look at the 'snapshot' of the true prior years earnings. When i said EOY, its Jan. when i update/close out prior year and do that snapshot (putting in excel as mentioned.

    on a rolling basis, ~12-18 mow prior NW track fairly closely, its when i compare my snapshot in excel (say for 2016 as an example) , Looking BACK to whats in quicken graph/report that its WAY off (hundreds of thousands).

    This does bring up a clarification of the report, to your points , made me think: NW should be 'snapshotted' (month/quarter/annually). In other words, if some ones NW was 2M in 2020 and to make it simple, all of that was in one MF (mutual fund) and it lost 1 M in 2021, if i looked at that report, Jan 1, 2022, i would expect to see NW of 2M in 2020 and 1M in 2021.

    I suspect it would show 1M NW in 2020 as well.

    Not a big deal, i have excel! Just thought when i saw the 'new' NW report come out, perhaps problems with the current that i've observed (similarly in the details of the retirement planner, which is more complex).

  • Boatnmaniac
    Boatnmaniac Quicken Windows Subscription SuperUser ✭✭✭✭✭

    For me, the historical Net Worth graph view does not always stay in sync with the Net Worth shown in the Accounts Bar on the side. I've found that for me the mismatch occurs when new securities are added to Quicken because the Net Worth view does not automatically include the new securities. Because the securities are not included it understates the Net Worth shown in the graph and report.

    The issue is resolved by clicking on Customize this graph and on the Securities tab click on Select All (followed by OK).

    After upgrading to R53.26 I noticed that the historical Net Worth and the new Lifetime Overview report were both out of sync with the Net Worth shown in the Accounts Bar on the side….in fact, all 3 were out of sync with each other. I also remembered that there were some new securities added to Quicken over the last month. I then did Select All for Securities in the customizations and all 3 were then in agreement with each other.

    If you have not done that, then you might want to try it.

    If there still are some differences, check to make sure that all Accounts, Categories and Payees are selected.

    If none of this resolves the issue for you, you might need to review each account in the Lifetime Overview report with what is shown in the Account Bar on the side. That should identify which Account(s) might be causing the issue so you can then do a deep dive in them to find what is causing the mismatch.

    Quicken Classic Premier (US) Subscription: R59.35 on Windows 11 Home

  • mark miller
    mark miller Member ✭✭✭
    edited December 2023

    I understand and have always selected what i want included/excludes., but i don't think you understand my scenario.

    Point in CURRENT time and past months (upto a year or so), they match. Its looking back at EACH years (which i had snapshotted) in history (15 years, i started snapshotting NW in Quicken into an excel ss each JAN.) is where it goes WAY off track. As i also mentioned in my prior post, thinking about assets (securities, etc) 'coming and going' (which i manually handle to reconcile each year), i highly suspect the NW report does this fluidly and doesn't keep a annual (monthly, quarterly, etc) NW 'point in time' capture.

    If i add/remove any account, i select to have in NW report or not. Ones that I have closed, have zero balances (but for history, i still keep them, just hide).

    As i mentioned as an example for that (Still not my problem but perhaps a different issue) :  if some ones NW was 2M in 2020 and to make it simple, all of that was in one MF (mutual fund) and it lost 1 M in 2021, if i looked at that report, Jan 1, 2022, i would expect to see NW of 2M in 2020 and 1M in 2021.

    I suspect it would show 1M NW in 2020 as well.

  • mark miller
    mark miller Member ✭✭✭

    Hello,

    Thanks for your detailed response. I understand your comments/observations. Matter of fact for many of the reasons you list:

    1. I don't do many investment updates, esp. retirement (Nonqual, 401k, etc) automatically due to the reasons you list, including EOY distro's..
    2. As mentioned before, my EOY is actually Jan for distribution EOY reasons. I periodically update manually, both in Quicken and my Excel.

    Also as mentioned before but to be specific:

    —If i look back, using a 2016 NW example, in my excel when i entered in Jan 2017 (Dec 2016 annual NW), it will be several hundred thousand off (similarly 2017, 2018, 2019, 2020, 2021 but then 2022 to date is fairly close) . As i have done in previous years, same "n-years" behaviour occurs if that makes sense.

    —If i were to look back, during ~18 mos at NW graph compared to my prior 'snapshot' to ongoing quicken NW updates, it tracks fairly closely.

    I've noticed this for years and not a big deal as its more of just an interesting trend view. As i saw this 'new' NW graph/report be rolled out, just made me think about it and if some underlying issues perhaps that was logged. Similarly, if you've ever used the retirement planner, i've compared to a commerical product and it does weird stuff (no idea assumptions behind many of the calcs) as well. One would think this is more straightforward in the calcs.

    Thanks for your thoughtful response.

This discussion has been closed.