calculating net weighted yield of a string of yield investments

daviddlhiggins  ....

Obviously, the path to a file on your computer is not very useful.  As I wrote before, you need to ``attach or upload to a file-sharing website``.

By "attach", I mean:  click the "browse" link at the bottom of the reply window.  However, I have read that some (new?) participants are not permitted to attach a file to a reply.

In that case, you need to upload the file to a file-sharing website.  There are many.  I like box.net/files; others like dropbox.com.  But before you upload an Excel file, be sure that it does not have any personal ID information.

-----

Also, a screenshot of just numbers is not very useful.  At the very least, it makes it difficult for us to help you; at worst, we might misinterpret the data and misdirect you.  Also, without cell references (Excel row and column headers), it makes it difficult for us to refer to values.

In your table, I presume that column 2 is price per share, and column 3 is number of shares held.  Column 4 seems to be total market value, which matches column 2 times column 3.  Column 5 seems to be total dividends distributed.  Column 1 appears to be the dividend rate (rounded), which matches column 5 divided by column 4.

Technically, the dividend rate is based on the price per share on the ex-dividend date.  That corresponds to the announced dividend rate.  But I wouldn't fault you for calculating your own "effective" dividend rate based on date when you receive payment.

In either case, it is probably not based on the current price per share or market value of the shares held.  That said, of course, you have to work with what you have.

Given all that, the average dividend rate is calculated in a manner similar to the portfolio rate of return, which I described previously.

If you have all the data that you posted in the screenshot, the best and simplest calculation is:  total dividends (column 5) divided by total market value (column 4).  Of course, only the market value of positions for which you received dividends.

That is the same as the weighted average.   So, if you only have the individual dividend rates (column 1) and individual market values (column 4), calculate the same average dividend rate with the following expression (stylistically):

SUMPRODUCT(column1, column4) / SUM(column4)

-----

Hope that helps.

If that does not solve your problem, follow the instructions above to upload a file and/or improve the screenshot.