Problem with Expected Return% in Yearly Tab

oakapple

I am tracking four investment accounts. For three of the four, the "Total Expected Return" field is showing ridiculous negative numbers that cannot be right. One of the four appears to be correct. I cannot see anything in the way I set up the one that's working, to distinguish it from the three that are not. Any ideas?

VidasMatelis

Make sure that in src tab, Allocation table all allocations (except cash) has Index symbol assigned to them. Make sure that this index symbol is listed in Symbol table and check PSData\Quotes.csv file and make sure you have quotes for these index symbols@
Then make sure that in the Symbol table you have correct Allocation assignment for each symbol.
If above all good, then go to SymbolProf% tab, select ReportCurrency as "*Original*", then select Year as current year (2018) and symbol by symbol check "Profit %" and "Expd Profit %(Alloc)" measures - here you will see how each symbol contributes to that total Expd Return % (Alloc) calculation. If you see any issues - investigate symbol and related index symbol and make sure you have quotes for them.
If after above steps you still have issues, please post here.

oakapple

Thanks for the help. The problem seems to exist in the original download files. I checked the Excel 2016 download on this site (v. 2.4.05). Without changing anything, as downloaded: in the yearly tab, the Yearly Movements table has large negative numbers in the right-hand column, between -289% and -400%. Those large negative numbers also appear in the graph, "Profit% vs Index Return% for last 5 Years."

The table Yearly Profit % by Allocation seems to have valid values for Total Expected Return %, but those values don't make it into the graph. Instead, the graph shows the large negative numbers in the far right-hand column of the Yearly Movements table.

The SymbProf% tab seems to have correct data for VT, the only holding in the sample file downloaded. I assume it is intentional that under the "US Broad" allocation, there is an entry for * Cash with -100% expected return. Again, this is in the downloaded sample, before I added any of my own stuff.

VidasMatelis

Hi,

Thanks for reporting this. I now fixed this issue and updated workbook is available from Download page - version 2.4.06.
This issue mostly affects Excel 2016 and PowerBI files, I did not see it on Excel 2010/2013 files, but to be consistent I updated suing same logic.
Issue - when allocation has defined allocation index, but there are no holdings for this allocation, this makes calculation fo "Expd Return % (Alloc)" incorrect. So another way to fix this issue - remove allocation index value from allocation records if you do not have any holdings for these allocations.

Just one formula was updated, with new code below (Excel 2016 version):

=IF(COUNTROWS(VALUES(Symbol[Allocation]))=1
  , var AllocIndex = IF(COUNTROWS(VALUES(Symbol[Allocation]))=1, CALCULATE(VALUES(Allocation[Index]), FILTER(Allocation, Allocation[Allocation] = VALUES(Symbol[Allocation]))), Blank())
    var res= IF(AllocIndex <> ""
        , DIVIDE(CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, LASTDATE(Dates[Date]), All(Dates), All(Symbol))
               , CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, PreviousDay(FIRSTDATE(Dates[Date])), All(Dates), All(Symbol))
          ) - 1
      )
     RETURN IF(res=0 || res = -1, BLANK(), res)
  , SUMX(FILTER(Allocation, Allocation[Index]<>"" && Report[Total Value]<>0)
        , var AllocIndex = Allocation[Index]
          RETURN
          CALCULATE(
            PRODUCTX(FILTER(VALUES(Dates[Month]), Report[Total Value] <> 0
                            && CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, LASTDATE(Dates[Date]), All(Dates), All(Symbol)) <> 0
                            && CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, PreviousDay(Dates[Date]), All(Dates), All(Symbol)) <> 0
                           )
            , 1 + CALCULATE( (DIVIDE(CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, LASTDATE(Dates[Date]), All(Dates), All(Symbol))
                                    , CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, PreviousDay(FIRSTDATE(Dates[Date])), All(Dates), All(Symbol))
                            ) - 1)
                    * ROUND(DIVIDE(CALCULATE(Report[Total Value], All(Symbol), VALUES(Allocation[Allocation]), VALUES(Symbol[Allocation]))
                                 , CALCULATE(Report[Total Value], All(Allocation), All(Symbol))), 2)
                    )
            ) - 1
         )         
    )
 )

Again, thank you for reporting this.

qosmioamit

Hi

I transferred my data over to the new version of Excel v2.4.07. I am using Excel 2016. I re-ran the scripts and refreshed the data. I did not get any errors. However, when I re-ran a refresh on the PowerBI file v2.4, some of the data does not match with Excel. I did not get any errors on PowerBI refresh.

VidasMatelis

Please make sure that reported currency is the same in Excel and PowerBI.

qosmioamit

Oh, you were spot on! Excel was set to Original and PowerBI was set to CAD. What's original?

VidasMatelis

Original - that means there is no currency conversion involved, so for example CAD and USD symbols are treated as the same currency.
WHen you select reporting currency as (for example) USD,then any CAD symbols are first converted to USD and then calculations are performed.