profit % calcuation

zzricezz

On the Yearly tab, does Cap Gain% + Dividend % = Profit %? it doesn't add up.

Or how can we calculate Profit % manually to validate it?

VidasMatelis

Hi,
First, you should read how these calculations are done here:
http://www.portfolioslicer.com/docs/how-to-calculate-investment-capital-gain-percent-profit-percent.html

All calculation formulas are open and available for your review and, if you know what you are doing, you can adjust them to your liking. They all are written in programing language DAX. These calculations are "approximate" and not "precise".

DAX Formula for "Profit" is very simple:

=[Value Change] + [Dividends]

But these % calculations are much more complicated than anybody would expect. It is not just adding "Value Change %" + "Dividends %".

Here is "Profit %" formula in DAX:

=IF(CONTAINS(Symbol,Symbol[Symbol],"* Cash")
, IF(HASONEVALUE(Symbol[Symbol])
   ,var trDates = CALCULATETABLE(VALUES(Dates[Date]), CALCULATETABLE(Transactions, Transactions[TransType]="Int", Dates[Mths-Initial] = "No", ALL(Symbol[Symbol])))
    var tr = ADDCOLUMNS(trDates, "CashValue", [Cash Value Start], "Interest", CALCULATE([Trans Total Amnt], TransType[TransType] = "Int"))
    var res = PRODUCTX(tr, 1 + DIVIDE([Interest], [CashValue]))
    RETURN IF(res<>0, res-1)
  , var fDate = FirstDate(Dates[Date])
    var fPrevDate = CALCULATE(IF(PreviousDay(fDate)=Blank(), fDate, PreviousDay(fDate)), ALL(Dates)) -- First date in the selected period
    var tblDtPortf1 = DISTINCT(UNION(PreviousDay(FirstDate(Dates[Date])),   CALCULATETABLE(VALUES(Dates[Date]), CALCULATETABLE(Transactions, TransType[ExternalImpactPortfolioSignCalc] <> 0, Dates[Mths-Initial] = "No", ALL(Symbol[Symbol])))   , LASTDATE(Dates[Date])))
    var tblDtPortf  = ADDCOLUMNS(tblDtPortf1, "PrevDate", var dt = LASTDate(Dates[Date]) var tDt = MAXX(FILTER(tblDtPortf1, [Date]= fPrevDate, tDt, fPrevDate))
    var tblPortfTrans1 = CALCULATETABLE(
      ADDCOLUMNS(tblDtPortf 
        , "EndValue"  , [Total Value]
        , "ExtImpact" , [Trans Ext Cash Flow Amnt]
      ) 
     , ALL(Symbol[Symbol]) /* In holdings report we have filter [Qty Held] <>0. To go around problems with this filter we remove filter on Symbol[Symbol] */
     )
    var tblPortfTrans2 = FILTER(tblPortfTrans1, [EndValue] <> 0 || [ExtImpact] <> 0)
    var tblPortfTrans3 = ADDCOLUMNS(tblPortfTrans2, "StartValue", var pd = [PrevDate] RETURN SUMX(FILTER(tblPortfTrans2, [Date]=pd), [EndValue]))
    var tblPortfTrans  = FILTER(tblPortfTrans3, [Date] >= fDate && [StartValue] + [ExtImpact] <> 0)
    var valChgPortf = PRODUCTX(tblPortfTrans, [EndValue] / ([StartValue] + [ExtImpact]))
    /*RETURN IF(NOT(HASONEVALUE(Symbol[Symbol])), IF(valChgPortf<>0, valChgPortf-1))*/ /* If just one symbol Cash, return empty */
    RETURN IF(valChgPortf<>0, valChgPortf-1)
  )
, var fDate = FirstDate(Dates[Date])
  var fPrevDate = CALCULATE(IF(PreviousDay(fDate)=Blank(), fDate, PreviousDay(fDate)), ALL(Dates)) -- First date in the selected period
  var tblDtSymb1 = DISTINCT(UNION(PreviousDay(FirstDate(Dates[Date])),   CALCULATETABLE(VALUES(Dates[Date]), CALCULATETABLE(Transactions, FILTER(TransType, TransType[ExternalImpactSymbolSign] <> 0 || TransType[DividendFlag] = 1), Dates[Mths-Initial] = "No")), LASTDATE(Dates[Date])))
  var tblDtSymb  = ADDCOLUMNS(tblDtSymb1, "PrevDate", var dt = LASTDATE(Dates[Date]) var tDt = MAXX(FILTER(tblDtSymb1, [Date]= fPrevDate, tDt, fPrevDate))
  var tblSymbTrans1 = 
    ADDCOLUMNS(tblDtSymb 
      , "EndValue"  , [Equity Value]
      , "ExtImpact" , [Trans Ext Symb Flow Amnt] - [Dividends]
    )     
  var tblSymbTrans2 = FILTER(tblSymbTrans1, [EndValue] <> 0 || [ExtImpact] <> 0)
  var tblSymbTrans3 = ADDCOLUMNS(tblSymbTrans2, "StartValue", var pd = [PrevDate] RETURN SUMX(FILTER(tblSymbTrans2, [Date]=pd), [EndValue]))
  var tblSymbTrans  = FILTER(tblSymbTrans3, [Date] >= fDate && [StartValue] + [ExtImpact] <> 0)
  var valChgSymb = PRODUCTX(tblSymbTrans, [EndValue] / ([StartValue] + [ExtImpact]))
  RETURN IF(valChgSymb<>0, valChgSymb-1)  
)