• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weighted Average - With Letters

lunicus

New Member
I'm trying to do a weighted average of credit quality for fixed income solutions - which are graded using letters.
AAA, AA, A, BBB, BB, B, CCC

So I want to great a weighted average of the various investments - so I can change the percentages and letter ratings - and have a weighted average calculated.
Any help out there??
 

Attachments

  • Sample.xlsx
    9.5 KB · Views: 6
Hi lunicus!

You can use a helper table, when you assign some values to every letter (i did with equal ranges between letters, but you can change the values in the column if you want), and this values can be used to calculate the equivalent letter using weighted average.

Check the file carefully. Blessings!
 

Attachments

  • Sample (1).xlsx
    12.6 KB · Views: 13
The attached contains the same approach as @John Jairo V with 2 differences, (a) I've used lambda to create a named formula and (b) I've used a ratings table derived from https://www.researchgate.net/public...gn_Credit_Rating_Model_for_European_Countries

83452

As you enter the formula, you'll gets hints about what goes where:
PortFCqs is supposed to mean your PortFolio Credit qualities
PortFWts means Portfolio Weights
CreditQual means your Credit Quality ratings table.
That formula is in cell H3

On top of that, once you have decided how you want to
change the percentages and letter ratings
you can eliminate the credit ratings table on the sheet by hard-coding it into the lambda named function, which I've done using WeightedQuality2, which means you only have to supply 2 ranges:

83453
which is in cell I3.

When changing the percentages and letter ratings, just remember to finish by sorting that table small to large by the number column.
 

Attachments

  • Chandoo51871Sample.xlsx
    11.3 KB · Views: 10
Back
Top