I’ve encountered an apparent inconsistency with the total return metric. I reloaded my broker data with new cash tracking feature being released (really nice!). I would expect that in the Capitally project: Net account transfers (in minus out) + Total return = Market Value
For some reason, this equation does not seem to hold for my project. For the max period, the difference is small but for YTD the total return is 1-2% higher than I would expect from the resulting market value of the portfolio. Is this a calculation error or am I making a mistake?
The net account transfers are part of the Interactive Brokers export. When I filter the transfer transactions in Capitally they add up to 75 and my broker is also reporting 75. The total return in Capitally (incl. realized returns, tax and costs) amount to 25 and yet the current value of the portfolio is reporting 98 in Capitally.
The most relevant bit to verify is current market value - it should be roughly the same as reported by IBKR, and balances of all assets should be the same - stocks should be exactly the same, cash balances may be slightly off (we’re speaking cents here due to possible rounding errors).
Now, if you look at transfers, then I assume you mean money going in and out of IBKR - this is different than invested principal and realized returns. It would only be true, if you’d only invest using the money you transferred to IBKR and everything you sold or any income you had - you’d transfer out - so that you never ever reinvest or repurpose account’s revenue.
Also, even you don’t reinvest, if you look at shorter period than Max, your returns will only be from that period in time, while your transfers will span another time period.
The balance on stock quantities is exactly the same, price differ slightly due to difference in end-of-day reporting or after hours prices that IBKR shows - but this is beside the point. Most currencies are showing the same balance except for USD (due to the effect of options not imported, so this deviation is explained) and EUR (which has a 35 euro difference, probably a dividend not administered correctly somewhere). Anyhow, these effects are not at the root of the puzzle I encountered.
I fully agree that principal invested and realized returns are different from transfer going in and out of a broker and I’m not looking at the principal invested specifically. What I would expect now given the double bookkeeping entry update is that when you add up all profits, money flows and subtract all costs it results in the NAV / market value incl. cash, for any given period (be it max or a sub-period). As in:
Beginning balance (NAV in EUR) + Gross return (period) - Costs (period) + Net money flow = Ending balance (NAV in EUR)
I’m currently not able to reconcile this equation with the figures I’m getting out of Capitally, unfortunately. Hope this clarifies the issue, would love to have this reconciliation. If it would help to finding an answer, I could also provide some actual data.
Beginning balance = 0 for Max or (Market) Value on start date
Gross return - costs: = Total returns (using settings below). This should include all costs that have a cash impact on the account
Net money flow = transactions tab, filter on ‘Transfer’ and export / add-up quantity or value in Excel for the given period
End balance = Market value as depicted in the top-left corner of the portfolio page
Thanks. I’m yet before my morning coffee, so I might not be thinking straight yet, but I see a few things here:
Gross return includes realized returns. If you assume you pay out your returns, these will be double-counted in the net money flows. You would need to use Unrealized return - cost - tax paid instead
This still does not account for cash that was reinvested within the account
If you have foreign currencies, looking on transfers does not account for currency fluctuations (as transfer is converted at different rate than when return was generated)
With the cash tracking, you could include internal cash flows. It’s a little bit tricky, but here’s how you can extract it:
Navigate to your broker and choose one cash position
Export the transactions
Now the tricky part:
– Some rows will be direct transactions on this position - you need quantity (or value for income), tax from them. And fees (but only if they’re in the same currency).
– Some will be related - meaning a cashflow caused by transaction on another position. For these, you only want quantity (fee and tax should be always empty).
– If you have multiple currencies, you rather want to use Value instead of quantity
?
