Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Portfolio Slicer 2.4 Explanation of New Measures
I am looking for the meaning of some of the new measures introduced in version 2.4 and have not been able to find any details. Can anyone point me to where I can find the meaning of "Compare to Value (Values)" and "Trans Ext Symb Flow Amnt (Values)" in Mnthly-Symbol tab. Also "Expd Return % (Alloc)" and "Diff Profit vs Expd" in SymbProf% tab?
Comments
Very good question, sorry I am behind with documentation.
Internal measures that should not be used, they are just for reference:
"Compare to Value" - there is some info about this internal measure on this page: http://portfolioslicer.com/docs/how-to-calculate-investment-capital-gain-percent-profit-percent.html, basically it is use to calculate Yield %, for example [Profit %]."Trans Ext Symb Flow Amnt (Values)" - again, this is internal measure, that shows how much symbol investment value changed from new investments or from investment sale.
New measures to better track your investment performance
"Expd Return % (TMTR)" - shows what is expected return (Profit) % based on TMTR (Total Market Total Return) Index in selected period. Lets say in src worksheet Config table you specified value for TMTRIndex as VT. If in 2017 VT index changed from 62.83 to 74.26, so that index return was 18.19%. If your portfolio Profit % was bigger than "Expd Return % (TMTR)", then your investment strategy is good. But if your return was smaller, then maybe you should consider investing in one very broad index. Note - known issue with this calculation that it does not account for dividends (if any) paid by index, so if possible, choose indexes that include dividends in their tracking."Expd Return % (Alloc)" - shows what is expected return (Profit) % based on your allocation settings. For each allocation you need to specify index that tracks that allocation. For example, for USA allocation you can choose index ^SP500TR that tracks S&P 500 index and includes dividends (Total Return). For Canadian allocation you can choose index HXT.TO that tracks TSX 60 and includes dividends. Expected Return % (Alloc) is calculated based on related index changes over selected period and these results are weighted for your actual allocation percent.
"Diff Profit vs Expd %" - basic formula for this measure is: Report[Profit %] - Report[Expd Return % (Alloc), that is how much (%) your actual profit bigger than expected profit (expected based on allocation settings).
Thank you very much for your explanation in response to my query. I also wish to thank you for your great work in the ongoing development of Portfolio Slicer 2.4. I find it is an invaluable tool in assisting in the analysis of my portfolio.
On another note in version 2.4, I am finding some kind of problem with the YTD % Capital Gain as displayed in the Overview tab. Unfortunately, my portfolio experienced a capital loss this YTD. Although the dollar value of the loss is reported correctly. The % Capital Gain YTD appears as a positive number???
OK...after some investigation on the % Capital Gain issue, I believe I found the problem. In the Measures table, the formula for % Capital Gain shows as
=PRODUCTX(VALUES(Dates[Month]), 1+DIVIDE(Report[Dividend], Report[Cost Basis]))-1
I changed the formula to the following and it appears to have corrected the problem...
=PRODUCTX(VALUES(Dates[Month]), 1+DIVIDE(Report[Capital Gain], Report[Cost Basis]))-1
Thank you very much for reporting this. Actually, that formula is for [Dividend % (to Cost Basis Value)".
Formula for [Cap Gain %] should be: I will fix this for next update.
Thanks again for finding this bug. This bug is just in Excel 2016 version.
I will adjust my formula based on your response..
for TSX60 I would use symbol HXT.TO. That is because this symbol change includes any dividends.
You could also use ^GSPTSE symbol.
Do not forget to add symbol to your symbol table and update psConfig.txt to get quotes for that symbol.