I am using Manager for stocks accounting (investing in shares of public companies). The inventory items represent shares of specific companies. When inventory items are sold, the proceeds minus the average cost of the inventory items sold equals capital gain/loss.
I encountered a situation where the average cost of the items sold is not calculated correctly when the following events happen in this exact order:
Event #1 - Apr 9, 2020 - bought 11,000 shares of Company X at $45.85 per share
Event #2 - Apr 9, 2020 - bought 7,000 shares of Company X at $46.55 per share
Event #3 - Apr 14, 2020 - bought 2,000 shares of Company X at $49.65 per share
Event #4 - Apr 15, 2020 - sold 20,000 shares of Company X at $46.75 per share
Event #5 - Apr 15, 2020 - bought 20,000 shares of Company X at $46.75 per share
If I look at the “Inventory Items” tab after these events, I see 20,000 items of Company X with average cost of $46.61. Clearly that’s an error, because at event #5 I paid $46.75 per share and not $46.61 and prior to event #5 I had no shares of this company as they were all sold in event #4. The computation of income and Profit Margin report also appear to be incorrect as a result.
If I change the date of event #5 to Apr 16, 2020, then everything gets calculated correctly. It appears to me that since the last 2 events are on the same day, they are processed by Manager in the wrong order, where share balance first goes to 40,000 and is averaged with events #1, #2 and #3 and then goes back down to 20,000 after even #4. That however is not the correct sequence of events and therefore the result is also incorrect.
I believe this is a small bug that needs to be fixed. Your thoughts/suggestions? I would prefer to keep the date for event #5 as Apr 15, 2020 and changing it I view as a hack.
Manager does not consider the order or time of day when you enter transactions, only the date. The fact is, you entered no information that distinguishes order of the two transactions on April 15 (which has not even arrived yet). So how do you expect the program to know?
Respectfully, I consider this a bug, because the software does not give the ability to specify time of day. Instead, it orders transactions on the same day arbitrarily rather than in the same order as they were entered into the software by the user or by time of transaction. I honestly believe time of day field should be added but not required to be specified by the user. This would improve the software accuracy for cases like this! Is this something you would be willing to implement please?
Well, the fact is you could not base calculations on the order of entry. That would mean users would be constrained to always enter transactions in the exact order they occurred, irrespective of whether they yet had necessary information, documentation, etc. That is a completely unrealistic thing to ask. So you would be required to implement a time of day field in every transaction and carry it throughout the program. That would not be a small change.
I’m not the developer.
If I can add one more thought, it would be that average cost inventory accounting is not ideal for situations where timing is crucial. When tracking securities, most brokerages would use first-in-first-out costing for individual issues. Only when purchases and sales can settle between transactions might average cost be suitable. Average cost for investments is often restricted to mutual funds, which are only valued and transacted at close of day, not throughout the day.
The advantage of the average cost method is that it does not require (or implement) a complete purchase history. You only need to know previous quantity and average cost and new transaction quantity and cost to calculate the new average cost. Its very convenient for software implementation, but not great for calculating margins on individual units of inventory.
@slavaf2000, there is another concept to consider in your circumstances. Manager considers all units of a particular inventory item to be identical and interchangeable. That is why there is no serial number or lot tracking. In your example, even though all shares were of Company X, the units were not truly identical. For your purposes, they were different lots.
The way to handle them in Manager would have been to create distinct inventory items for each of the lots, designated by both company name and purchase date (plus further identification if you ever purchase more than one lot of the same company’s shares on the same day). Then, your average cost would be the correct basis and margin calculations would correspond to capital gain when sold. Note, there would be no problem with partial sale of a lot. You would effectively be creating a specific-identification inventory system.
Thank you for your thorough response and recommendations. I live in Canada and I am a professional accountant. Here, it’s actually required by tax law to use average cost for reporting the capital gain/loss on all stock sales, and specific identification, FIFO and LIFO methods are not acceptable. They would be for other purposes but not stock transactions. So the program does calculate the cost (being average cost) correctly for my jurisdiction, except in the case I outlined above. The law requires the average cost to be calculated immediately prior to the sale transaction and does not make reference to end of day closing values etc, so it really is time dependent because transactions happening on the same day do need to be processed in the sequence they occurred. For my jurisdiction it would be nice to see a time field implemented, but I understand the application used world-wide and perhaps my requirements are a minority and therefore not worth the effort for you to implement. I think I can manage by changing the date to a later date because I am not a day trader and I don’t expect such transactions to happen frequently.
Perhaps one day you could implement more elaborate inventory functionality with LIFO, FIFO, specific identification, and average cost capabilities and letting the user decide what method applies to what inventory items. But that’s a big job!
Would using settlement dates rather than execution dates suit your needs? If permitted, that would have overcome the problem in your example.
But why not rely on broker statements for gain/loss determinations in the first place? Your brokerage is set up to properly handle exactly what your authorities require, while Manager is a general purpose accounting system, and its inventory management features were really designed more for product sales. Other aspects of security trading activities would still be adequately handled by the program.
Using settlement dates doesn’t help because the period between the transaction date and settlement date is always the same number of days (2 business days for North American stock exchanges). In fact, for stock trades, it’s actually the settlement date that’s always used!
Why not rely on the broker statements? Because they are not always correct. Consider a situation where the same legal entity (a corporation) uses two different brokers to buy stocks of the same company at different prices over the same period of time. The tax law still requires average cost to be calculated from the perspective of the legal entity, regardless of which broker was used to make the purchases. So the cost must be averaged between the two brokers, but brokers would never even attempt to calculate average costs in cooperation with other brokers! They usually don’t even know that their client might be using more than one broker! Each one will do their own calculation, and it will be correct from their perspective, but not from the perspective of the legal entity that needs to report this for tax purposes. So it becomes essential to calculate the average cost by the legal entity and not rely on broker statements. This is a more complex example, but I do see it in practice as an accountant.
It still seems like creating a separate inventory item for each tax lot would work. You might have to manually compute average cost for ensemble holdings, but the figures would be available.
I’m no expert in Canadian capital gain calculations, but I find it unusual that you would be required to average costs of share lots purchased at different times through different brokers. I assume the brokers must be furnishing the information to the tax authority directly. Since the broker only knows what has been purchased/sold through that brokerage, what choice do they have but to report on the lots they know about? If the purchaser is required to average costs over multiple lots from all sources, how is that calculation ever going to be reconciled with information supplied directly to the government?
Presuming the individual must perform these calculations (since the broker cannot), I’m driven right back to the necessity of treating each lot as a separate inventory item in Manager. What about that approach do you think would not work (other than the inconvenience of an external calculation in the absence of time-tagged transactions)? You could search the Inventory Items tab for the company name, export the results, and compute average cost in a very simple spreadsheet.
Note that as with any average cost inventory system that aggregates all shares of one company in a single inventory item, you are going to have to grab your cost basis data just before and after transactions and store them separately, because Manager does not give you the ability to recover average cost after other transactions occur. Average cost is calculated on the fly and is ephemeral. That is another reason for making each share lot a separate inventory item. At least you’ll have its average cost locked in.
To answer your question about reconciling information reported by the brokerages to tax authorities to what the legal entity reports, the minimal requirement from the brokers is to report proceeds from the sale but not the cost. It is up to the reporting entity to calculate and claim the cost of those shares, which can later be audited by the government if they choose to do so. Each country is different, but that’s how it is in Canada. In practice the reconciliation is never done (especially on the cost side), but the government does audit regularly, especially large amounts, and there are significant penalties for non-compliance.
I think your recommendation is reasonable. It’s a small workaround with a small inconvenience, but will definitely work! Many thanks for suggesting it.
I’m glad that will work for you. Don’t forget to make the inventory items (share lots) inactive after all have been sold. That will avoid confusion if the same company’s shares are bought later.
