Cost of debt (Default risk).xls
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

Comment only
 
 
ABCDEFGHIJKLM
1
Inputs for synthetic rating estimation
2
Please read the special cases worksheet (see below) before you use this spreadsheet.
3
Before you use this spreadsheet, make sure that the iteration box (under calculation options in excel) is checked.
4
Enter the type of firm =
3
(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
6
Enter current Earnings before interest and taxes (EBIT) =
4351
(Add back only long term interest expense for financial firms)
7
Enter current interest expenses =
375
(Use only long term interest expense for financial firms)
8
Enter current long term government bond rate =
0,87%
9
Output
10
Interest coverage ratio =
11,60
11
Estimated Bond Rating =
Aaa/AAA
Note: If you get REF! All over the place, set the operating lease commitment question in cell F5
12
Estimated Default Spread =
0,54%
to No, and then reset it to Yes. It should work.
13
Estimated Cost of Debt =
1,41%
14
15
If you want to update the spreads listed below, please visit http://www.bondsonline.com
16
For large manufacturing firms
For financial service firms (default spreads are slighty different)
17
If interest coverage ratio is
If long term interest coverage ratio is
18
>≤ toRating isSpread isgreater than≤ toRating isSpread is
19
−1000000,199999D2/D18,60%−1000000,049999D2/D18,60%
20
0,20,649999C2/C13,95%0,050,099999C2/C13,95%
21
0,650,799999Ca2/CC10,63%0,10,199999Ca2/CC10,63%
22
0,81,249999Caa/CCC8,64%0,20,299999Caa/CCC8,64%
23
1,251,499999B3/B-4,37%0,30,399999B3/B-4,37%
24
1,51,749999B2/B3,57%0,40,499999B2/B3,57%
25
1,751,999999B1/B+2,98%0,50,599999B1/B+2,98%
26
22,2499999Ba2/BB2,38%0,60,749999Ba2/BB2,38%
27
2,252,49999Ba1/BB+1,98%0,750,899999Ba1/BB+1,98%
28
2,52,999999Baa2/BBB1,27%0,91,199999Baa2/BBB1,27%
29
34,249999A3/A-1,13%1,21,49999A3/A-1,13%
30
4,255,499999A2/A0,99%1,51,99999A2/A0,99%
31
5,56,499999A1/A+0,90%22,49999A1/A+0,90%
32
6,58,499999Aa2/AA0,72%2,52,99999Aa2/AA0,72%
33
8,50100000Aaa/AAA0,54%3100000Aaa/AAA0,54%
34
35
For smaller and riskier firms
36
If interest coverage ratio is
37
greater than≤ toRating isSpread is
38
−1000000,499999D2/D18,60%
39
0,50,799999C2/C13,95%
40
0,81,249999Ca2/CC10,63%
41
1,251,499999Caa/CCC8,64%
42
1,51,999999B3/B-4,37%
43
22,499999B2/B3,57%
44
2,52,999999B1/B+2,98%
45
33,499999Ba2/BB2,38%
46
3,53,9999999Ba1/BB+1,98%
47
44,499999Baa2/BBB1,27%
48
4,55,999999A3/A-1,13%
49
67,499999A2/A0,99%
50
7,59,499999A1/A+0,90%
51
9,512,499999Aa2/AA0,72%
52
12,5100000Aaa/AAA0,54%
53
54
55
56
57
58
59
Loading...