I wanted to calculate transition matrix in excel.
Initial Rating at year-end
Rating
AAA AA A BBB BB B CCC Default
AAA 90.81% 8.33% 0.68% 0.06% 0.12% 0.00% 0.00% 0.00%
AA 0.70% 90.65% 7.79% 0.64% 0.06% 0.14% 0.02% 0.00%
A 0.09% 2.27% 91.05% 5.52% 0.74% 0.26% 0.01% 0.06%
BBB 0.02% 0.33% 5.95% 86.93% 5.30% 1.17% 0.12% 0.18%
BB 0.03% 0.14% 0.67% 7.73% 80.53% 8.84% 1.00% 1.06%
B 0.00% 0.11% 0.24% 0.43% 6.48% 83.46% 4.07% 5.20%
CCC 0.22% 0.00% 0.22% 1.30% 2.38% 11.24% 64.86% 19.79%
Above is desired output. Inputs would be outstanding ratings at different years
like.
Entity 2012 2013 2014 2015
1 AAA AA AAA AA
2 BBB B D A
---------------------
I have done it through pivot. But wanted to know wheather same is possible through other means.
Thanks
Bookmarks