Unrealized gains calculation - wrong still?
Quicken Classic Deluxe for Windows - R54.16, Build 27.1.54.16 (updated fully).
I read through several posts about unrealized gains, but did not get a sense that it was addressed fully. My own example below says it has not been addressed (or that I am missing something basic).
Example transaction set - shows a few buy/sells leading to closing out the position on 1/11/2024.
2023-05-25 | Buy | 150 | $100.35 |
|---|---|---|---|
2023-06-30 | Buy | 100 | $121.34 |
2023-07-12 | Buy | 50 | $117.81 |
2023-08-15 | Buy | 100 | $117.65 |
2023-08-18 | Sell | 150 | $114.44 |
2023-09-01 | Buy | 50 | $121.20 |
2023-09-08 | Buy | 100 | $125.24 |
2024-01-11 | Sell | 400 | $103.81 |
Running the Investment Transactions Report for YTD including Unrealized Gains shows the following entries:
Date | Action | Security | Symbol | Category | Memo | Quote | Shares | Cash | Amount Invested | Cash+Invest |
|---|---|---|---|---|---|---|---|---|---|---|
1/5/2024 | UnrlzG | Oracle Corp | ORCL | _UnrlzdGain | 102.73 | -1,080.00 | ||||
1/10/2024 | UnrlzG | Oracle Corp | ORCL | _UnrlzdGain | 103.92 | 476.00 | ||||
1/11/2024 | Sold | Oracle Corp | ORCL | 103.81 | -400.00 | 48,373.57 | -48,373.57 | -455.00 | ||
ORCL | _RlzdGain | -6,848.61 | 0.00 | -420.00 | ||||||
1/11/2024 | UnrlzG | Oracle Corp | ORCL | _UnrlzdGain | 103.81 | 6,805.57 | 180.00 | |||
As you can see, the final sale (and I have checked the box saying "Sell All Shares") shows the full sale proceeds as Unrealized Gains.
- How can there be unrealized gains for a position fully closed out?
- Why is the full sale proceeds value being shown as gains?
- To further muddy the waters, there is a row showing a similar negative amount as Realized Gains (Loss) - what is that doing there?
Comments
-
Personally, I don't know why you would include Unrealized Gains in a "transaction" report. What the report does is show daily changes in unrealized gain/loss as if it was a transaction. Then, when you sell the security that gain or loss gets reversed and results in a realized gain or loss. I have never analyzed it in detail to say whether it is calculating the correct numbers but that is why you are seeing an unrealized gain number which is close to your realized gain number when you sold the security.
Quicken Windows user since 1993.
1 -
You could try the new Income by Security report (may only be available in Premier and up) and check the Unrealized gains box to display that column.
I have not studied this report extensively so please check its numbers and let us know what you find.
QWin Premier subscription0 -
You can definitely use the report Jim_Harman suggested. The numbers for unrealized gains it produces are very close to the numbers produced by the Investment Income report (with unrealized gains box in settings checked). I use the Investment Income report all the time. I have a spreadsheet where it reconciles my investment balances by account from one month to the next using the numbers from this report. It matches to the penny. The unrealized gain in the Income by Security report is off slightly which I believe is just rounding. In the Investment Income report you also can get the details by security by double clicking on the unrealized gain number. This will bring up a report of the unrealized gains (which I believe could be the Investment transaction report for just unrealized gains) which you can have subtotaled by security.
Quicken Windows user since 1993.
0 -
Sorry - will update after rerunning my numbers.
0 -
Sorry - will update after rerunning my numbers.
0 -
This may not be specific to this case, but a quirk does develop in some presentations. I believe the unrealized gains on a day-to-day basis are computed frequently as today's shares times (today's price less yesterday's price). That becomes inaccurate when there is also a change in the number of shares 'today'.
I too would not normally check to "Include unrealized gains" in a transactions report.
To further muddy the waters, there is a row showing a similar negative amount as Realized Gains (Loss) - what is that doing there?
You had a Sale transaction. That turned some accumulation of unrealized gains/losses into a realized gain or loss (loss apparently). How does that -6,848.61 compare to what shows up on the Capital Gains report?
Why is the full sale proceeds value being shown as gains?
It does not appear to me that the full sale proceeds are being shown. 400 shares sold for $103.81 should have full sale proceeds of $41,524. The $48,373.57 appears to be the cost basis of the 400 shares. Not knowing (or chasing down) which lot of shares were sold on 08/18/23, I have not confirmed that. That suggest you realized a loss of $6,849.57 on that set of shares (close enough for me to the realized loss shown given available information.)
How can there be unrealized gains for a position fully closed out?
Note that from 01/05 to 01/10, the share price changed by $1.19/share which on 400 shares is the $476 unrealized gain shown on 01/10. The same thing in principle occurs from the 10th to the 11th up until you sell the shares - a further unrealized gain/loss. Then the sale as a separate transaction generates realized gains/losses. While that could be why there would be an unrealized gain/loss on the date of the sale, it does not explain the values shown for that line - nor can I directly explain those values.
which report is best for understanding my YTD unrealized gains
I would likely be looking at the Income by Security report Jim mentioned or at a properly customized and specified portfolio view (Options / Portfolio preferences set to calc from 01/01/2024)
0 -
Sorry - took me a while to get back on topic. Just a quick update with the summary of "not particularly pleased, but putting this issue to bed".
@Jim_Harman - thanks. The Income by Security report is available in Deluxe as well (although only by going into the Reports Center and not off the Reports menu on top). Works well for the most part. One issue is that it does not bring in the Account column and therefore is not easy to use if you have the same security in multiple accounts. The information it provided was still the same - while total of Realized and Unrealized matches up with my numbers, it is still odd that the Unrealized gains persist even well after the position is closed.
@bmciance - thanks. The Investment Income report works better in the sense that the Account column is available in my export to Excel and I am better able to reconcile in situations where the same security is traded in multiple accounts. Here again, after closing out the position, I would have expected all gains to have shown as Realized and there should not have been any Unrealized gains.
@q_lurker - yes, I had noticed my mistake just when you pointed it out as well - that the realized gains was not indeed reflecting the full proceeds of the sale. Thanks for the additional context - however, as I mentioned above, the Unrealized gains number will still remain a mystery to my non-accountant brain.
0
