Forum Discussion
calculating net weighted yield of a string of yield investments
I use excel quite frequently to track investments, but I don't know how to calculate to weighted yield of a portfolio of yield generating stocks whose yield varies and the size of the positions in the portfolio vary. I would appreciate some help; thanks in advance
5 Replies
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.xlsx
