Annual Dividends, Earnings, P/E Ratios, Payout Ratios

Comments

• VidasMatelis

  July 2018

  Hi,
  
  TTM = Trailing Twelve Months.
  In Dates table there is an attribute "Mths-Last12". If you build any report and add this attribute to the filter area and choose value "Yes", then that report would show just results for the last 12 months.
  So copy for example holdings report, add filter for "Mths-Last12" = Yes and you will see Dividend amount and Profit % and many other values change to reprsent TTM.
  So this would be the starting point to get these values and if you need any specific metric that you cannot make it work, let me know, I'll see if I can help.
  
• Totopops

  July 2018

  Thanks Vidas,
  I have added AnnualDiv (annual dividend per share), EarnTTM (annual earnings per share) to the srcSymbol table and entered the current values for each of my portfolio holdings.
  
  From this I would really like to get a report that details my holdings information including:
  1. Estimated annual dividend income by holding and total
  2. Dividend yield (based on current holding price per share)
  3. Dividend yield cost basis (yield on current holdings cost per share)
  4. P/E ratio (based on current price and annual earnings per share in srcSymbol table)
  5. Payout Ratio (based on annual earnings per share and dividend per share in srcSymbol table)
  This information would allow me to monitor whether my holdings still meet the criteria to buy, hold or sell in my portfolio.
  
  Any help you could provide to help me with the formulas to get measures for the above would be gratly appreciated.
  
  Thank You,
  Totopops
• Totopops

  July 2018

  Vidas,
  Attached is a screenshot of what I had in mind. I have used GETPIVOTDATA() function to create some data ratios. This appears to work to automatically update ratios and yield data. (sensitive financials have been redacted)
  

  PS24xredact.jpg 332.5K
• VidasMatelis

  July 2018

  Hi,
  
  If you have it working with GetPivot, then you probably do not need my help anymore. But you could consider writing your calculations in DAX.
  I do not have much time now, but I'll give you some ideas:
  
  Annual Div = I do not have exactly that in PS. I have Dividend %, but unless you also add filter for the specific year, then number shows number over the years. If you would want Dividend % for last 12 months, then the formula would be:
  
  Dividend % TTM:=CALCULATE([Dividend %], 'Dates'[Mths-Last 12] = "Yes", All('Dates'))
  
  If you would like to calculate Dividend % for the last calendar year (current calendar year in most times will not be full), then the formula would be:
  
  =CALCULATE([Dividend %], 'Dates'[Years-Last 2] = "Yes", 'Dates'[Years-Current] = "No", All('Dates'))
  
  
  
  
• Totopops

  July 2018

  Thanks Vidas...I will try working through the DAX calculations....
• Groundhog

  July 2018

  This is a very interesting thread and I'd like to use these measures too. Totopos it would be great if you could post the DAX calculations if you get a chance, thank you.
• Totopops

  July 2018

  I haven't had a chance to get to the DAX calculations yet. Frankly, I seem to be getting what I need using the GETPIVOTDATA() function applied to a PivotTable that pulls AnnualDiv and EarnTTM from the Symbols table. (I have to maintain these two values based on any reporting changes). Using the GETPIVOTDATA() information, I am able to automatically calculate current yield, current PE ratio and percent payout along with cost basis yield. What I use it for is to assess whether each of my current holdings continue to meet my investment requirements based on Yield, PE, and Payout Ratio. I flag any of my holdings for my attention if any of the criteria do not continue to meet my minimum requirements.
• Totopops

  July 2018

  On further investigation, I found that I couldn't apply slicers on the GETPIVOTDATA() function approach. I was therefore motivated to go for the DAX solution.
  
  OK so my first pass at DAX for this report as follows:-
  1. On the srcSymbol sheet in the Symbol table, I added AnnualDiv, and EarnTTM.
  2. Added the following DAX calculations (with appropriate formats) to the Reports table measures:-
  Annual Income "PRODUCTX(Symbol,[AnnualDiv]*[Qty Held])"
  P/E Ratio "DIVIDE([Symbol Price],[Sum of EarnTTM])"
  Payout Ratio% "DIVIDE([Sum of AnnualDiv],[Sum of EarnTTM])"
  Yield % "DIVIDE([Sum of AnnualDiv],[Symbol Price])"
  Yield (Cost Basis) "DIVIDE([Sum of AnnualDiv],[Cost Basis per Unit])"
  
  Note that I am a novice with DAX and the above may be crude. However, I appear to be getting the results that I am after on a sheet that I added called Annual Performance. In addition, I added slicers for Accounts and Portfolio (RRSP, TFSA, Personal, etc.) on this sheet and they appear to properly filter the holdings and the whole sheet gives me a fantastic visual report on the health and income contribution of my holdings.
  
  Hope this helps...