Weighted Average - With Letters

[lunicus]

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??

 

[John Jairo V]

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!

 

[p45cal]

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/publication/355304742_Determination_of_Sovereign_Credit_Rating_Model_for_European_Countries



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:


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.

 

[lunicus]

You guys are amazing!!! Thank you so much!!