Fund Manager
PORTFOLIO MANAGEMENT SOFTWARE
Contact Us

Calculating 52 Week Average Yield for dividend reinvestment.

Questions on using, creating, or understanding data in Fund Manager reports.

Postby divinest » Mon Oct 09, 2017 8:12 am

Hi Mark, Is there a way to create a custom formula that calculates a running 52-week average yield for a stock? Here is an example of what the report would look like, the objective is to find out if the current price is at attractive yield for reinvesting dividends. Trying to calculate the last 3 columns shown in the attachment. (Also I do have the income dividend populated for these stocks). Thanks very much.
Attachments
DivReinvestAnalysis.jpg
DivReinvestAnalysis.jpg (159.17 KiB) Viewed 4903 times
divinest
 
Posts: 149
Joined: Tue Sep 05, 2017 7:07 pm

Postby data8504 » Mon Oct 09, 2017 1:56 pm

Hi divinest,

Hope it's OK if I interject... I'm not fully understanding your column definitions. Given the sparsity of the dates in the table, I infer that these are dividend pay dates? If so, the Yield-ttm doesn't really line up. Else, are these just dates of interest to you?

Basically, it's pretty easy to get a ttm (trailing twelve month) dividend yield from FM. A ttm yield is itself an average/annualization over a twelve month span. Are you looking for a rolling average of ttm yields? Mathematically this would equate to a trailing 24 month average, with a linear weight towards the -12m point in time.

Perhaps I've misinterpreted your intention?

Thanks, -Matt
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Postby divinest » Mon Oct 09, 2017 2:59 pm

Hi Data8504,
Thanks for your response.

Apologize, i should have explained the col defn bit more

Date - is the weekly date (can be any week day of the week, but mostly one line item per week)
Price - is the price of the stock on the given date
Div - is the last quarterly dividend
Current yield (Div x payout frequency) / Price
52 Week Average Yield - is the rolling 52 weekly average yield (same as current yield, but the average of past 52 weeks)
Offset is the different between the current yield and rolling 52 weekly average.

The Objective:
Lets say i have 20 stocks in my portfolio, i would like to reinvest all my dividends, however i don't want to DRIP, instead i would like to see which of my currently held 20 stocks has the highest current yield in relation to the past 52 average yield, that way i would reinvest to the position that is trading at the lowest price versus in relation to other.

I could do this in excel, however that is time consuming, so trying to see if i can create custom formulas in a report to show the same results. Thanks much!
divinest
 
Posts: 149
Joined: Tue Sep 05, 2017 7:07 pm

Postby data8504 » Mon Oct 09, 2017 4:09 pm

Ah, I see. So confirm if I've got this right:

You want to know the difference between:
- Instantaneous yield (most recent distributions * frequency / NAV)
- Trailing twelve month yield (sum of all 12 mo. dists / NAV) - this should be mathematically equivalent to grabbing the rolling average of instantaneous yields, assuming distributions are made on a regular, periodic basis.

The offset between these two would show, as your point out, how your positions' instantaneous yields compare to their recent-term historical distributions.

So yes. You can do this with FM's custom fields. I will take you through building the custom formula:

* Last_dist - the date of the most recent distribution, upon which the instantaneous yield will be built
* EVAL(DATE2DAYS(Inv_incptn), DATE2DAYS(Last_dist) - 1, Last_dist) - returns the *second* most recent distribution date, for use in determining distribution frequency.
* EVAL(DATE2DAYS(Last_dist), DATE2DAYS(Last_dist), Totaldist_s_btw) - the per-share value of the most recent distribution.

Here's where it gets dicey:
* 365 / (DATE2DAYS(Last_dist) - DATE2DAYS(EVAL(DATE2DAYS(Inv_incptn), DATE2DAYS(Last_dist) - 1, Last_dist))) -- The number of distributions per year.
* (365 / (DATE2DAYS(Last_dist) - DATE2DAYS(EVAL(DATE2DAYS(Inv_incptn), DATE2DAYS(Last_dist) - 1, Last_dist)))) * EVAL(DATE2DAYS(Last_dist), DATE2DAYS(Last_dist), Totaldist_s_btw) -- The instantaneous per share dividend amount.
* ((365 / (DATE2DAYS(Last_dist) - DATE2DAYS(EVAL(DATE2DAYS(Inv_incptn), DATE2DAYS(Last_dist) - 1, Last_dist)))) * EVAL(DATE2DAYS(Last_dist), DATE2DAYS(Last_dist), Totaldist_s_btw)) / End_NAV -- ***your instantaneous dividend yield***

Now let's work on your ttm yield:
* EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) -- Gives total distributions over the past year.
* EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) / End_NAV -- ***your ttm yield***

Now for the comparison:
* (((365 / (DATE2DAYS(Last_dist) - DATE2DAYS(EVAL(DATE2DAYS(Inv_incptn), DATE2DAYS(Last_dist) - 1, Last_dist)))) * EVAL(DATE2DAYS(Last_dist), DATE2DAYS(Last_dist), Totaldist_s_btw)) / End_NAV) / (EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) / End_NAV)

(I am realizing that you could mathematically simplify this to remove the End_NAV terms, but... egh...)

BTW, I'm not claiming this is entirely error free... probably needs some double-checking. I don't do stock positions myself, so I don't really have good data to run regressions on the above formulas, BUT I've confirmed they work syntactically! :) (barring copy/paste errors...)

Let me know how it goes... I'll certainly poke around this more if it's helpful.

-Matt
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Postby divinest » Mon Oct 09, 2017 4:43 pm

Thanks much, appreciate the detailed information. Let me try to incorporate to formulas in a custom report and will post how it goes, Thanks again.
divinest
 
Posts: 149
Joined: Tue Sep 05, 2017 7:07 pm

Postby divinest » Mon Oct 09, 2017 5:50 pm

I am trying to add one custom formula at a time, first step

EVAL(DATE2DAYS(Inv_incptn), DATE2DAYS(Last_dist)-1, Last_dist)

when displaying the Inv_incptn and Last_dist the dates are displaying correctly, however the above custom formula does not appears to display the results (no errors though) here attached a screen shot as well. Is there a way to debug the report log to see why the formula is not evaluating correctly ? Thanks!
Attachments
custom_field.png
custom_field.png (16.08 KiB) Viewed 4874 times
divinest
 
Posts: 149
Joined: Tue Sep 05, 2017 7:07 pm

Postby data8504 » Mon Oct 09, 2017 5:58 pm

Interesting. Are there two distribution dates to actually go off of? What date do you expect to come back for "Last_dist" and then the calculated second-to-"Last_dist?"
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Postby divinest » Mon Oct 09, 2017 6:05 pm

Yes there are more than two distribution dates to go off, when including the Last_dist field, the last distribution does come back correctly, i was trying to include the formula as shown in your earlier response

"* Last_dist - the date of the most recent distribution, upon which the instantaneous yield will be built
* EVAL(DATE2DAYS(Inv_incptn), DATE2DAYS(Last_dist) - 1, Last_dist) - returns the *second* most recent distribution date, for use in determining distribution frequency."
divinest
 
Posts: 149
Joined: Tue Sep 05, 2017 7:07 pm

Postby data8504 » Mon Oct 09, 2017 6:48 pm

Ah. My mistake. "Last_dist" already returns a "days" format, so my DATE2DAYS is not needed. There are going to be lots of these to correct... (Many assumptions I made because again, I don't have good data to test this on). Try this, and if it works, cascade down to the other formulas:

With corrections:
* Last_dist - the date of the most recent distribution, upon which the instantaneous yield will be built
* EVAL(Inv_incptn, Last_dist - 1, Last_dist) -- returns the *second* most recent distribution date, for use in determining distribution frequency.
* EVAL(Last_dist, Last_dist, Totaldist_s_btw) -- the per-share value of the most recent distribution.

Here's where it gets dicey:
* 365 / (Last_dist - EVAL(Inv_incptn, Last_dist - 1, Last_dist)) -- The number of distributions per year.
* (365 / (Last_dist - EVAL(Inv_incptn, Last_dist - 1, Last_dist))) * EVAL(Last_dist, Last_dist, Totaldist_s_btw) -- The instantaneous per share dividend amount.
* ((365 / (Last_dist - EVAL(Inv_incptn, Last_dist - 1, Last_dist))) * EVAL(Last_dist, Last_dist, Totaldist_s_btw)) / End_NAV -- ***your instantaneous dividend yield***

Now let's work on your ttm yield:
* EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) -- Gives total distributions over the past year.
* EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) / End_NAV -- ***your ttm yield***

Now for the comparison:
* (((365 / (Last_dist - EVAL(Inv_incptn, Last_dist - 1, Last_dist))) * EVAL(Last_dist, Last_dist, Totaldist_s_btw)) / End_NAV) / (EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) / End_NAV)


..This sure looks a lot simpler without all those (unneeded) conversions! :) Try this and let me know... I'll keep poking at it here too. -Matt
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Postby divinest » Mon Oct 09, 2017 6:52 pm

Thanks much. Will try the updated formulas and see how it goes.
divinest
 
Posts: 149
Joined: Tue Sep 05, 2017 7:07 pm

Postby data8504 » Mon Oct 09, 2017 6:53 pm

Uh oh. We might have a problem...

Last_dist doesn't seem to be bounded to the report or EVAL() dates, which means that my formula finding the second-most-recent distribution always returns 0. Hmm. I am looking into a way around this now...
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Postby data8504 » Mon Oct 09, 2017 6:58 pm

Here are the alternatives that come to mind:
* Dist_btw - shows number of dists, for instance EVAL(ENDDAYS() - 365, ENDDAYS(), Dist_btw), in the past 365 days. However, you can run into problems on the edges when a dividend occurs a day too soon (would show briefly as 5, even if it's quarterly), or a day too late (would show briefly as 3, even if quarterly.)
* On the other hand, you could populate a custom field with the payout frequency in days (91.25) for quarterly, etc. This has the benefit of always being "accurate," but you need to manually upkeep this field.

Thoughts?
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Postby divinest » Mon Oct 09, 2017 7:02 pm

Custom field property idea is fine. As I mentioned earlier mainly looking for finding the stock that is currently trading at lower price compared to other holdings in the portfolio. So if there are other ways to calculate that I am fine with that as well. Thanks
divinest
 
Posts: 149
Joined: Tue Sep 05, 2017 7:07 pm

Postby data8504 » Mon Oct 09, 2017 7:19 pm

OK. Then let's try revision 3. :) In my below example, I am going to hardcode 91.25, but you can clearly sub this out for a custom field.

* Last_dist - the date of the most recent distribution, upon which the instantaneous yield will be built
* EVAL(Last_dist, Last_dist, Totaldist_s_btw) -- the per-share value of the most recent distribution.

Here's where it gets dicey:
* 4 -- The number of distributions per year. Hardcoded. Use user custom field as "dividends per year."
* 4 * EVAL(Last_dist, Last_dist, Totaldist_s_btw) -- The instantaneous per share dividend amount.
* (4 * EVAL(Last_dist, Last_dist, Totaldist_s_btw)) / End_NAV -- ***your instantaneous dividend yield***

Now let's work on your ttm yield:
* EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) -- Gives total distributions over the past year.
* EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw) / End_NAV -- ***your ttm yield***

Now for the comparison:
* 4 * EVAL(Last_dist, Last_dist, Totaldist_s_btw) / EVAL(ENDDAYS() - 365, ENDDAYS(), Totaldist_s_btw)


...It's funny how this is getting simpler and simpler as I refine it. :) I've tested through instantaneous yield, and it seems to work in my contrived case... continuing to test...
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Postby data8504 » Mon Oct 09, 2017 7:21 pm

Woohoo! The last formula (comparison) is returning valid numbers for me... I think the above changes cleared the major bugs. Not sure if the data's right; I'll leave that to you.
data8504
 
Posts: 39
Joined: Sat Dec 08, 2007 11:09 pm

Next

Return to Reports

Who is online

Users browsing this forum: No registered users and 5 guests

FundManagerSoftware.com | Search | Site Map | About Us | Privacy Policy
Copyright © 1993-2024 Beiley Software, Inc. All rights reserved.