StockReturn functions give wrong results

Discussion in 'Troubleshooting' started by Nemanja, Jun 15, 2018.

StockReturn functions give wrong results

  1. Nemanja

    Nemanja New Member

    Joined:
    Apr 18, 2018
    Messages:
    6
    Likes Received:
    2
    ... because it would seem they are calculating wrongly.
    For example, I want to calculate the return for AAPL in this year (to date). The proper way to do that would be to divide the last price with the ADJUSTED closing price on the last trading day of 2017, which would be Dec 29th.
    The last close, as of now, was close on June 14th, and the price was 190.80. Adjusted close on Dec 29th 2017 was 167.90. So the return is 13.64%. However, the formula =StockReturnCustomDates("AAPL",Last2017,Today-1)
    (I have named cells) returns 14.45%. After banging my head against the wall for some time, I concluded that the way marketXLS calculates StockReturn functions is like this:
    (last close+all dividends paid since the period in formula started)/adjusted close on the start date.
    This is wrong. Dividend is already considered for in the adjusted close. So in fact, you're calculating dividend twice. I don't see where I could be mistaken, please help and correct as needed.
     
  2. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,107
    Likes Received:
    10
    Many thanks for your feedback.

    As discussed we just made a release for this.
    Thank You
    Ankur for MarketXLS
     

Share This Page