Pofit Calculation Error

inigo

Hi everyone,

I've encountered a problem with the profit calculation in the Holdings tab.

While the Unrealized Capital Gains are calculated correctly, the Profit value shown does not seem to correspond to Unrealized Cap Gain + Dividends, and the result doesn't make sense to me. A similar inconsistency appears in the Profit % column for some individual assets.

I've attached an image of the table for reference.

Additionally, I've noticed that for index funds without commission, the calculation seems to work properly in some cases (e.g. MIV), but not in others. I can't figure out why this discrepancy occurs.

For context, I've entered the dividends manually through the Transactions tab, and they appear correctly, so I don't think the issue is related to that.

Any insight would be appreciated.

Thanks in advance.

Iñigo

VidasMatelis

In PortfolioSlicer Profit formula is as follows:

Profit:=[Value Change] + [Dividends]

Lets looks at your BABA position

Unrlzd Cap Gain:=var res=[Equity Value] - [Cost Basis] RETURN IF(ROUND(res, 2) = 0, Blank(), res)

EquityValue=399, CostBasis=405. So Unrlzd Cap Gain=-6 (that calculations matches)

[Unrlzd Cap Gain] is similar to [Value Change] but not the same. Value change formula:

Value Change:=[Equity Value] - [Equity Value Start] - [Trans Ext Symb Flow Amnt] + [CostBasis Adj] + IF(CONTAINS(VALUES(Symbol[Symbol]), Symbol[Symbol], "* Cash"), [Capital Gain For Cash])

Can you add [Value Change] calculation to this report and see what is the number. Can you please also review all transactions for BABA - do you have any "special" dividend entries on it or Cost Basis adjustments?

inigo

Thanks, Vidas, for your reply.

The profit seems to be calculated correctly from the Value Change, but I’m a bit confused about how the Value Change itself is worked out. Specifically, I think the exchange rate is being applied where it shouldn’t, and I don’t see why there should be a difference for MSCI_WA since it’s just the Amundi MSCI World fund in EUR.

I’ve attached some screenshots with the BABA transactions, the value change table, and the symbols info so you can get a better idea of what’s happening.

VidasMatelis

It appears that the problem is related to the fact that you bought USD symbol BABA and placed in into EUR currency account. So any cost you specified for buy or dividend transaction - that is in EUR currency, so you see the conversion play.
If you want to confirm that, create USD account and enter both BABA transactions (Buy and dividends) and there you should see that Profit should make more sense.