ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
PRELIMINARY STUFF AND INPUTS
2
Objective
This spreadsheet allows you to compute the optimal capital structure for a non-financial
3
service firm. If you have a financial service firm use capstrfin.xls
4
Before you start
Open preferences in excel, go into calculation options and put a check in the iteration box.
5
If it is already checked, leave it as is.
6
Inputs
The inputs are primarily in the input sheet. If your company has operating leases,
7
use the operating lease worksheet to enter your lease or rental commitments.
8
UnitsEnter all numbers in the same units (000s, millions or even billions)
9
Income inputs
The key income inputs are EBITDA, depreciation and amortization and interest expenses.
10
Enter the most updated numbers you have for each (even if they are 12-month trailing
11
numbers). If the most recent period for which you have data has an operating income that
12
is abnormal, either because of extraordinary losses/gains or some other occurrence, use
13
an average operating income over the last few years.
14
From the statement of cash flows, also enter the capital spending from the recent period.
15
P.S: If you have negative operating income and you expect to continue having negative
16
operating income, your optimal debt ratio will be zero.
17
Balance Sheet
Enter the book value of all interest-bearing debt. If you have a market value enter that
18
number. Alternatively, input the average maturity of the debt and I will estimate the
19
market value of debt.
20
Market Data
Enter the current stock price, the current long-term government bond rate, the risk
21
premium you would like to use to estimate your cost of equity and the current rating for
22
your firm. If you do not have a rating, there is an option for you at the very bottom of
23
the spreadsheet to compute a synthetic rating.
24
Tax Rate
Enter a marginal tax rate, if you can estimate it. Otherwise, use the effective tax rate.
25
Default Spreads
This spreadsheet has interest coverage ratios, ratings and default spreads built into it in
26
the worksheet. This spreadsheet treats the imputed interest expense on operating leases as part of the
27
interest expense when computing the interest coverage ratio. You can choose between ratings for large firms
28
(firms with market capitalizations that exceed ₹ 5 billion is a simple cut off but you can deviate from it)
29
a more conservatve for small or risky firms. If you want, you can change the interest
30
coverage ratios and ratings in these tables.
31
READING THE OUTPUT
32
Summary
The summary provides a picture of your firm's current cost of capital and debt ratio, and
33
compares it to your firm's value at every debt raito, incorporating the tax benefits from debt & the expected
34
bankruptcy costs at each level of debt.
35
DetailsThe details of the calculation at each debt ratio are below the summary.
36
37
References
38
Corporate Finance: Theory and Practice, Chapter 18
39
Applied Corporate Finance: Chapter 8
40
41
42
43
44
45
46
47
48
49
50
51
52
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