Forum Discussion
calculating net weighted yield of a string of yield investments
daviddlhiggins .... Better answers come quicker when you provide a concrete example, ideally in an Excel file that you attach or upload to a file-sharing website.
Perhaps the following example will be useful. See the attached file.
Key formulas (there are others):
D8: =B8/C8 - 1
D9: =SUMPRODUCT(D2:D6, C2:C6) / SUM(C2:C6)
D10: =SUMPRODUCT(D2:D6, B2:B6/(1+D2:D6)) / SUMPRODUCT(B2:B6/(1+D2:D6))
The key point is: in D9, the percentage returns are weighted by cost, not by market value.
In D10, the expression B2:B6/(1+D2:D6) calculates the cost basis, based on market value and rate of return, if that is all the data that you have.
If you have both market values and costs (you should, if only for tax purposes), it is better to forgo the weighting factors and simply use total market value and total cost (D8).
-----
FYI, the infinitesimal difference between D8 and D9 is due to arithmetic anomalies. Essentially, "the sum of the rounded parts does not always equal the whole".
Hello; what I am trying to calculate is not the investment return on the portfolio, but rather the weighted dividend yield on the entire portfolio given that greater capital is invested in certain positions. Hope this makes sense. A screen shot of a small piece of the portfolio is below; if needed I can forward the entire spread sheet. Thank you
- See the full spreadsheet below
/Users/davidhiggins/Desktop/SHELLEY:BEN/Shelley.Ben Worksheet.3.7.22.xlsxdaviddlhiggins ....
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.
