Sales Capital Gain Incorrect

breadbreadbread

Hello,

First, thank you so much for this wonderful sheet. It is amazing. I have been testing it out and I think there may be an issue with the sales capital gain under the Sales tab. I am comparing the reported gain to the reported capital gain in adjustedcostbase.ca using the same transaction information and I believe the Portfolio Slicer is under-reporting the capital gains.

Looking at the equation is it possible the sales fee is being subtracted twice? Is it not already being taken into consideration during the sales amount.

=Report[Sales Amnt] - Report[Sales Cost Basis] - Report[Sales Fees]
- CALCULATE(SUM(Trans[AccruedInterest])
, TransType[BookValueSign]=-1
)

VidasMatelis

Hi,

Thanks for reporting this! I just tested what you reported and I believe that you are right - there is a bug.
Formula should be:

Sales Capital Gain:=Report[Sales Amnt] - Report[Sales Cost Basis] + Report[Sales Fees]
- CALCULATE(SUM(Trans[AccruedInterest])
	, TransType[BookValueSign]=-1
  )

There should be "+ Report[Sales Fees]", so + instead of -.

I am sorry about this bug! And thank you very much for reporting it!
Can you edit your formula?

breadbreadbread

Hi Vidas,

Thank you for taking a look. Since the sales fee is deducted in the sales amnt, I think by adding it back in (via + Report[Sales Fees]) the capital gain would be higher than it should be. Would the correct formula be:

Sales Capital Gain:=Report[Sales Amnt] - Report[Sales Cost Basis] 
- CALCULATE(SUM(Trans[AccruedInterest])
	, TransType[BookValueSign]=-1
  )

VidasMatelis

Hi,

I believe you are from Canada, as you are comparing results to adjustedcostbase.ca. When we file taxes, for each symbol sale we need to specify "Sales Capital Gain" and "Sales Fees". In that context "Sales Capital Gain" is calculated BEFORE "Sales fees" - that is why I am making an adjustment for "Sales Fees". So basically I am using that report while filing taxes.
You are right that normally you would expect that "Sales Capital Gain" would also account for "Sales Fees".
Does that make sense?

breadbreadbread

Ohh okay I understand! That makes total sense, thanks for the clarification. I appreciate your help Vidas.

VidasMatelis

Hi,
Also - you can add "Capital Gain" measure that will behave as you expected - the difference is that "Sales Capital Gain" does adjustment for "Sales Feel".
Thanks again for reporting this issue - it will be fixed in the next release!

wirecutter

Hi, I'm just noticing the same problem. The way tax reporting in Canada is done I think it would be more correct for the 'Sales Amnt' to be the Cash Impact + Sales Fees and then the Sales Capital Gain would just subtract the same sales Fees?
I also seem a bit lost...where can I see the actual formulas in the workbook? I normally consider myself fairly savvy in Excel, but I've not worked with a spreadsheet like this before!
Great work BTW.

VidasMatelis

wirecutter, to access formulas you need to enable PowerPivot for Excel as per instructions here: http://portfolioslicer.com/docs/excel2013-powerpivot.html, but it depends if your Excel has full PowerPivot support or no. In PortfolioSlicer the formulas are PowerPivot formulas, not Excel formulas.

In Portfolio Slicer Sales Amnt formula is:

Sales Amnt:=CALCULATE(SUMX(Trans, (ROUND([TotalAmnt] * Related(TransType[CashImpactSign]), 2)) * Report[TransReportExchRate])
	, TransType[SellFlag]=1
)

That is roughly equal to SaleValue-SellCommission, where SaleValue=QtySell*ShareSellPrice

Corrected Sales Capital Gain formula is:

Sales Capital Gain:=Report[Sales Amnt] - Report[Sales Cost Basis] + Report[Sales Fees]
- CALCULATE(SUM(Trans[AccruedInterest])
	, TransType[BookValueSign]=-1
  )

This is equal to SalesAmount - SalesCostBasis + SalesCommission

I am adding back SalesCommission to SalesCapitalGain as it is reported separately in your tax form and subtracted from your profit/loss there.

wirecutter

Thank you. I was able to find the PowerPivot Add on. I actually added the '+ Report[Sales Fees]' to the Sales Amount calculation. That seems to better reflect how I'd usually file. Do you see any issue with that?

wirecutter

Just to clarify, my Sales Amnt formula looks like this now:
=CALCULATE(SUMX(Trans, (ROUND([TotalAmnt] * Related(TransType[CashImpactSign]), 2)) * Report[TransReportExchRate] + Report[Sales Fees])
, TransType[SellFlag]=1
)

VidasMatelis

Sales fees should also be multiplied by exchange rate, so formula is not correct.
Current Sales Amnt formula:

Sales Amnt:=CALCULATE(SUMX(Trans, (ROUND([TotalAmnt] * Related(TransType[CashImpactSign]), 2)) * Report[TransReportExchRate])
	, TransType[SellFlag]=1
)

That formula already excludes sales fees. To include sales fees you would do following:

Sales Amnt:=CALCULATE(SUMX(Trans, (ROUND([TotalAmnt] * Related(TransType[CashImpactSign]) + Report[Sales Fees], 2)) * Report[TransReportExchRate])
	, TransType[SellFlag]=1
)