Synthetic Rating Estimation.xlsx

	A	B	C	D	F	G	H	I	O	P
1	Inputs for synthetic rating estimation								Yes	1
2	Please read the special cases worksheet (see below) before you use this spreadsheet.								No	2
3	Before you use this spreadsheet, make sure that the iteration box (under calculation options in excel) is checked.									3
4	Enter the type of firm =		2	(Enter 1 if large manufacturing firm, 2 if smaller or riskier firm, 3 if financial service firm)				Small: <5 billion
5	Do you have any operating lease or rental commitments?				No	Yes or No
6	Enter current Earnings before interest and taxes (EBIT) =				₹50.00	(Add back only long term interest expense for financial firms)
7	Enter current interest expenses =				₹8.00	(Use only long term interest expense for financial firms)
8	Enter current long term government bond rate =				3.00%
9	Output
10	Interest coverage ratio =			6.25
11	Estimated Bond Rating =			A2/A	Note: If you get REF! All over the place, set the operating lease commitment question in cell F5
12	Estimated Default Spread =			1.42%	to No, and then reset it to Yes. It should work.
13	Estimated Cost of Debt =			4.42%
14
15	If you want to update the spreads listed below, please visit http://www.bondsonline.com
16	For large non-financial service firms				For financial service firms (default spreads are slighty different)
17	If interest coverage ratio is				If long term interest coverage ratio is
18	>	≤ to	Rating is	Spread is	greater than	≤ to	Rating is	Spread is
19	-100000	0.199999	D2/D	20.00%	-100000	0.049999	D2/D	20.00%
20	0.2	0.649999	C2/C	17.50%	0.05	0.099999	C2/C	17.50%
21	0.65	0.799999	Ca2/CC	15.78%	0.1	0.199999	Ca2/CC	15.78%
22	0.8	1.249999	Caa/CCC	11.57%	0.2	0.299999	Caa/CCC	11.57%
23	1.25	1.499999	B3/B-	7.37%	0.3	0.399999	B3/B-	7.37%
24	1.5	1.749999	B2/B	5.26%	0.4	0.499999	B2/B	5.26%
25	1.75	1.999999	B1/B+	4.55%	0.5	0.599999	B1/B+	4.55%
26	2	2.2499999	Ba2/BB	3.13%	0.6	0.749999	Ba2/BB	3.13%
27	2.25	2.49999	Ba1/BB+	2.42%	0.75	0.899999	Ba1/BB+	2.42%
28	2.5	2.999999	Baa2/BBB	2.00%	0.9	1.199999	Baa2/BBB	2.00%
29	3	4.249999	A3/A-	1.62%	1.2	1.49999	A3/A-	1.62%
30	4.25	5.499999	A2/A	1.42%	1.5	1.99999	A2/A	1.42%
31	5.5	6.499999	A1/A+	1.23%	2	2.49999	A1/A+	1.23%
32	6.5	8.499999	Aa2/AA	0.85%	2.5	2.99999	Aa2/AA	0.85%
33	8.50	100000	Aaa/AAA	0.69%	3	100000	Aaa/AAA	0.69%
34
35	For smaller and riskier firms
36	If interest coverage ratio is
37	greater than	≤ to	Rating is	Spread is
38	-100000	0.499999	D2/D	20.00%
39	0.5	0.799999	C2/C	17.50%
40	0.8	1.249999	Ca2/CC	15.78%
41	1.25	1.499999	Caa/CCC	11.57%
42	1.5	1.999999	B3/B-	7.37%
43	2	2.499999	B2/B	5.26%
44	2.5	2.999999	B1/B+	4.55%
45	3	3.499999	Ba2/BB	3.13%
46	3.5	3.9999999	Ba1/BB+	2.42%
47	4	4.499999	Baa2/BBB	2.00%
48	4.5	5.999999	A3/A-	1.62%
49	6	7.499999	A2/A	1.42%
50	7.5	9.499999	A1/A+	1.23%
51	9.5	12.499999	Aa2/AA	0.85%
52	12.5	100000	Aaa/AAA	0.69%
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100