| Date updated: | 5-Jan-14 |
| To update this spreadsheet, enter the following | | | | | | Cost of Debt Lookup Table (based on std dev in stock prices) |
| Long Term Treasury bond rate = | | | 3.04% | | | Standard Deviation | | Basis Spread |
| Risk Premium to Use for Equity = | | | 5.00% | US implied premium | | 0 | 0.25 | 0.50% |
| Global Defautl Spread to add to cost of debt = | | | 0.00% | None for the US | | 0.25 | 0.5 | 1.00% |
| Do you want to use the marginal tax rate for cost of debt? | | | | | No | 0.5 | 0.65 | 1.50% |
| If yes, enter the marginal tax rate to use | | | | | 40% | 0.65 | 0.8 | 2.00% |
| | | | | | | 0.8 | 0.9 | 2.50% |
| | | | | | | 0.9 | 1 | 3.00% |
| | | | | | | 1 | 10 | 4.00% |
| Industry Name | Number of Firms | Beta | Cost of Equity | E/(D+E) | Std Dev in Stock | Cost of Debt | Tax Rate | After-tax Cost of Debt | D/(D+E) | Cost of Capital |
| Advertising | 65 | 1.03 | 8.19% | 65.55% | 113.36% | 7.04% | 6.04% | 6.62% | 34.45% | 7.64% |
| Aerospace/Defense | 95 | 1.01 | 8.08% | 84.04% | 88.97% | 5.54% | 15.03% | 4.71% | 15.96% | 7.54% |
| Air Transport | 25 | 0.94 | 7.73% | 47.74% | 92.87% | 6.04% | 13.79% | 5.21% | 52.26% | 6.41% |
| Apparel | 70 | 1.15 | 8.77% | 82.42% | 94.86% | 6.04% | 10.29% | 5.42% | 17.58% | 8.18% |
| Auto & Truck | 26 | 1.28 | 9.42% | 50.62% | 103.75% | 7.04% | 4.71% | 6.71% | 49.38% | 8.08% |
| Auto Parts | 75 | 1.46 | 10.35% | 75.76% | 102.64% | 7.04% | 9.43% | 6.38% | 24.24% | 9.38% |
| Bank | 7 | 0.72 | 6.62% | 54.29% | 41.40% | 4.04% | 22.17% | 3.14% | 45.71% | 5.03% |
| Banks (Regional) | 721 | 0.58 | 5.96% | 58.14% | 70.20% | 5.04% | 18.00% | 4.13% | 41.86% | 5.19% |
| Beverage | 47 | 1.42 | 10.16% | 81.86% | 109.89% | 7.04% | 3.95% | 6.76% | 18.14% | 9.54% |
| Beverage (Alcoholic) | 19 | 1.14 | 8.74% | 78.40% | 97.07% | 6.04% | 10.72% | 5.39% | 21.60% | 8.01% |
| Biotechnology | 349 | 1.12 | 8.62% | 91.61% | 133.64% | 7.04% | 1.13% | 6.96% | 8.39% | 8.48% |
| Broadcasting | 30 | 1.53 | 10.70% | 67.35% | 110.90% | 7.04% | 13.21% | 6.11% | 32.65% | 9.20% |
| Brokerage & Investment Banking | 49 | 1.11 | 8.58% | 19.98% | 87.95% | 5.54% | 13.44% | 4.80% | 80.02% | 5.55% |
| Building Materials | 37 | 1.27 | 9.40% | 76.89% | 85.47% | 5.54% | 16.33% | 4.64% | 23.11% | 8.30% |
| Business & Consumer Services | 179 | 0.90 | 7.53% | 77.38% | 89.91% | 5.54% | 13.41% | 4.80% | 22.62% | 6.91% |
| Cable TV | 16 | 0.97 | 7.91% | 67.01% | 86.81% | 5.54% | 15.00% | 4.71% | 32.99% | 6.86% |
| Chemical (Basic) | 47 | 1.01 | 8.10% | 77.12% | 108.14% | 7.04% | 6.27% | 6.60% | 22.88% | 7.76% |
| Chemical (Diversified) | 10 | 1.42 | 10.12% | 74.97% | 64.97% | 4.54% | 25.03% | 3.40% | 25.03% | 8.44% |
| Chemical (Specialty) | 100 | 1.01 | 8.11% | 85.99% | 85.18% | 5.54% | 12.74% | 4.83% | 14.01% | 7.65% |
| Coal & Related Energy | 45 | 1.28 | 9.42% | 53.58% | 120.20% | 7.04% | 2.44% | 6.87% | 46.42% | 8.24% |
| Computer Services | 129 | 0.92 | 7.65% | 83.00% | 96.81% | 6.04% | 9.94% | 5.44% | 17.00% | 7.27% |
| Computer Software | 273 | 1.07 | 8.38% | 92.01% | 100.31% | 7.04% | 6.16% | 6.61% | 7.99% | 8.24% |
| Computers/Peripherals | 66 | 1.15 | 8.79% | 91.89% | 111.60% | 7.04% | 5.66% | 6.64% | 8.11% | 8.61% |
| Construction | 18 | 0.96 | 7.84% | 76.65% | 94.83% | 6.04% | 9.82% | 5.45% | 23.35% | 7.28% |
| Diversified | 20 | 0.77 | 6.89% | 51.42% | 58.91% | 4.54% | 12.60% | 3.97% | 48.58% | 5.47% |
| Educational Services | 40 | 1.24 | 9.22% | 71.93% | 120.83% | 7.04% | 11.84% | 6.21% | 28.07% | 8.37% |
| Electrical Equipment | 135 | 1.14 | 8.75% | 87.92% | 109.94% | 7.04% | 7.49% | 6.51% | 12.08% | 8.48% |
| Electronics | 191 | 1.02 | 8.16% | 86.20% | 111.11% | 7.04% | 7.52% | 6.51% | 13.80% | 7.94% |
| Electronics (Consumer & Office) | 26 | 1.37 | 9.88% | 71.27% | 101.29% | 7.04% | 8.97% | 6.41% | 28.73% | 8.88% |
| Engineering | 56 | 1.20 | 9.04% | 81.49% | 89.28% | 5.54% | 14.86% | 4.72% | 18.51% | 8.24% |
| Entertainment | 85 | 1.19 | 8.98% | 79.38% | 102.02% | 7.04% | 4.85% | 6.70% | 20.62% | 8.51% |
| Environmental & Waste Services | 108 | 1.13 | 8.71% | 69.64% | 118.46% | 7.04% | 5.02% | 6.69% | 30.36% | 8.10% |
| Farming/Agriculture | 29 | 0.79 | 7.01% | 70.86% | 84.88% | 5.54% | 9.01% | 5.04% | 29.14% | 6.44% |
| Financial Svcs. | 76 | 0.99 | 7.97% | 49.48% | 79.79% | 5.04% | 18.37% | 4.11% | 50.52% | 6.02% |
| Financial Svcs. (Non-bank & Insurance) | 17 | 1.05 | 8.29% | 23.18% | 66.66% | 5.04% | 9.77% | 4.55% | 76.82% | 5.41% |
| Food Processing | 97 | 0.85 | 7.30% | 78.17% | 86.41% | 5.54% | 14.00% | 4.76% | 21.83% | 6.74% |
| Food Wholesalers | 18 | 1.18 | 8.92% | 85.63% | 91.05% | 6.04% | 10.69% | 5.39% | 14.37% | 8.41% |
| Furn/Home Furnishings | 36 | 1.24 | 9.25% | 78.33% | 100.03% | 7.04% | 10.03% | 6.33% | 21.67% | 8.62% |
| Healthcare Equipment | 193 | 0.85 | 7.31% | 85.28% | 105.14% | 7.04% | 5.80% | 6.63% | 14.72% | 7.21% |
| Healthcare Facilities | 47 | 1.13 | 8.68% | 45.27% | 90.96% | 6.04% | 13.49% | 5.23% | 54.73% | 6.79% |
| Healthcare Products | 58 | 1.00 | 8.06% | 83.47% | 100.72% | 7.04% | 8.76% | 6.42% | 16.53% | 7.79% |
| Healthcare Services | 126 | 0.83 | 7.20% | 78.17% | 91.19% | 6.04% | 13.77% | 5.21% | 21.83% | 6.77% |
| Heathcare Information and Technology | 125 | 0.99 | 8.00% | 87.32% | 112.18% | 7.04% | 6.09% | 6.61% | 12.68% | 7.83% |
| Heavy Construction | 46 | 1.67 | 11.39% | 63.98% | 89.60% | 5.54% | 19.40% | 4.47% | 36.02% | 8.89% |
| Homebuilding | 32 | 1.71 | 11.61% | 63.85% | 105.53% | 7.04% | 6.68% | 6.57% | 36.15% | 9.78% |
| Hotel/Gaming | 89 | 1.27 | 9.41% | 65.65% | 99.66% | 6.04% | 10.48% | 5.41% | 34.35% | 8.03% |
| Household Products | 139 | 1.00 | 8.06% | 83.80% | 106.72% | 7.04% | 9.51% | 6.37% | 16.20% | 7.79% |
| Information Services | 71 | 0.84 | 7.26% | 90.39% | 66.77% | 5.04% | 17.05% | 4.18% | 9.61% | 6.96% |
| Insurance (General) | 26 | 0.92 | 7.64% | 68.43% | 55.60% | 4.54% | 19.19% | 3.67% | 31.57% | 6.38% |
| Insurance (Life) | 27 | 1.21 | 9.09% | 60.00% | 57.69% | 4.54% | 17.82% | 3.73% | 40.00% | 6.95% |
| Insurance (Prop/Cas.) | 53 | 0.76 | 6.86% | 74.09% | 65.78% | 5.04% | 19.42% | 4.06% | 25.91% | 6.13% |
| Internet software and services | 330 | 1.05 | 8.27% | 96.00% | 120.28% | 7.04% | 4.59% | 6.72% | 4.00% | 8.20% |
| Investment Co. | 65 | 0.82 | 7.12% | 48.92% | 76.57% | 5.04% | 6.62% | 4.71% | 51.08% | 5.89% |
| Machinery | 141 | 1.04 | 8.25% | 85.11% | 78.89% | 5.04% | 13.02% | 4.38% | 14.89% | 7.68% |
| Metals & Mining | 134 | 1.26 | 9.34% | 67.47% | 127.20% | 7.04% | 1.90% | 6.91% | 32.53% | 8.55% |
| Office Equipment & Services | 30 | 1.14 | 8.76% | 64.48% | 81.92% | 5.54% | 12.81% | 4.83% | 35.52% | 7.37% |
| Oil/Gas (Integrated) | 8 | 1.00 | 8.06% | 92.13% | 52.52% | 4.54% | 20.55% | 3.61% | 7.87% | 7.71% |
| Oil/Gas (Production and Exploration) | 411 | 1.24 | 9.24% | 39.04% | 112.16% | 7.04% | 6.29% | 6.60% | 60.96% | 7.63% |
| Oil/Gas Distribution | 80 | 0.82 | 7.12% | 65.82% | 63.33% | 4.54% | 4.18% | 4.35% | 34.18% | 6.18% |
| Oilfield Svcs/Equip. | 163 | 1.30 | 9.56% | 83.14% | 101.55% | 7.04% | 10.73% | 6.28% | 16.86% | 9.01% |
| Packaging & Container | 24 | 0.99 | 7.97% | 67.03% | 56.60% | 4.54% | 21.28% | 3.57% | 32.97% | 6.52% |
| Paper/Forest Products | 21 | 1.34 | 9.72% | 65.79% | 75.10% | 5.04% | 8.20% | 4.63% | 34.21% | 7.98% |
| Pharma & Drugs | 138 | 1.10 | 8.56% | 87.82% | 117.64% | 7.04% | 4.30% | 6.74% | 12.18% | 8.33% |
| Power | 106 | 0.68 | 6.43% | 54.02% | 55.54% | 4.54% | 16.03% | 3.81% | 45.98% | 5.23% |
| Precious Metals | 166 | 1.33 | 9.71% | 72.98% | 140.33% | 7.04% | 0.82% | 6.98% | 27.02% | 8.97% |
| Publshing & Newspapers | 52 | 1.08 | 8.45% | 73.09% | 88.39% | 5.54% | 13.92% | 4.77% | 26.91% | 7.46% |
| R.E.I.T. | 46 | 0.78 | 6.95% | 14.93% | 56.02% | 4.54% | 2.48% | 4.43% | 85.07% | 4.80% |
| Railroad | 10 | 1.08 | 8.43% | 80.85% | 67.40% | 5.04% | 20.51% | 4.01% | 19.15% | 7.59% |
| Real Estate (Development) | 22 | 0.99 | 7.99% | 80.06% | 94.11% | 6.04% | 3.00% | 5.86% | 19.94% | 7.57% |
| Real Estate (General/Diversified) | 11 | 0.72 | 6.63% | 85.25% | 74.92% | 5.04% | 8.72% | 4.60% | 14.75% | 6.33% |
| Real Estate (Operations & Services) | 47 | 1.40 | 10.05% | 62.67% | 101.66% | 7.04% | 8.56% | 6.44% | 37.33% | 8.70% |
| Recreation | 70 | 1.30 | 9.56% | 79.92% | 97.36% | 6.04% | 8.81% | 5.51% | 20.08% | 8.75% |
| Reinsurance | 3 | 0.56 | 5.85% | 72.20% | 37.61% | 4.04% | 13.34% | 3.50% | 27.80% | 5.20% |
| Restaurant | 84 | 0.86 | 7.32% | 78.39% | 78.32% | 5.04% | 15.17% | 4.28% | 21.61% | 6.66% |
| Retail (Automotive) | 30 | 1.15 | 8.80% | 63.49% | 84.47% | 5.54% | 19.23% | 4.47% | 36.51% | 7.22% |
| Retail (Building Supply) | 7 | 1.07 | 8.38% | 81.85% | 97.40% | 6.04% | 21.97% | 4.71% | 18.15% | 7.72% |
| Retail (Distributors) | 87 | 1.00 | 8.04% | 69.53% | 85.26% | 5.54% | 16.18% | 4.64% | 30.47% | 7.00% |
| Retail (General) | 21 | 0.98 | 7.94% | 73.74% | 77.55% | 5.04% | 25.03% | 3.78% | 26.26% | 6.84% |
| Retail (Grocery and Food) | 21 | 0.81 | 7.11% | 63.75% | 79.40% | 5.04% | 22.83% | 3.89% | 36.25% | 5.94% |
| Retail (Internet) | 47 | 1.03 | 8.19% | 95.18% | 114.31% | 7.04% | 9.80% | 6.35% | 4.82% | 8.10% |
| Retail (Special Lines) | 137 | 0.99 | 7.97% | 72.57% | 91.79% | 6.04% | 18.90% | 4.90% | 27.43% | 7.13% |
| Rubber& Tires | 4 | 1.27 | 9.38% | 47.67% | 106.10% | 7.04% | 15.21% | 5.97% | 52.33% | 7.59% |
| Semiconductor | 104 | 1.19 | 9.01% | 88.48% | 95.41% | 6.04% | 7.30% | 5.60% | 11.52% | 8.61% |
| Semiconductor Equip | 51 | 1.25 | 9.31% | 85.49% | 101.14% | 7.04% | 5.13% | 6.68% | 14.51% | 8.93% |
| Shipbuilding & Marine | 14 | 1.50 | 10.52% | 60.48% | 117.34% | 7.04% | 4.99% | 6.69% | 39.52% | 9.01% |
| Shoe | 14 | 0.83 | 7.19% | 92.69% | 78.29% | 5.04% | 19.82% | 4.04% | 7.31% | 6.96% |
| Steel | 37 | 1.19 | 8.99% | 68.54% | 83.60% | 5.54% | 14.13% | 4.76% | 31.46% | 7.66% |
| Telecom (Wireless) | 28 | 0.68 | 6.44% | 48.75% | 96.71% | 6.04% | 11.54% | 5.34% | 51.25% | 5.88% |
| Telecom. Equipment | 131 | 1.14 | 8.76% | 90.28% | 106.61% | 7.04% | 6.90% | 6.55% | 9.72% | 8.55% |
| Telecom. Services | 82 | 0.94 | 7.74% | 58.40% | 98.32% | 6.04% | 8.40% | 5.53% | 41.60% | 6.82% |
| Thrift | 223 | 0.53 | 5.70% | 1.84% | 69.10% | 5.04% | 18.93% | 4.09% | 98.16% | 4.12% |
| Tobacco | 12 | 0.94 | 7.73% | 82.76% | 58.61% | 4.54% | 14.23% | 3.89% | 17.24% | 7.07% |
| Transportation | 22 | 1.01 | 8.07% | 82.81% | 75.94% | 5.04% | 21.21% | 3.97% | 17.19% | 7.37% |
| Trucking | 28 | 1.17 | 8.87% | 56.03% | 82.51% | 5.54% | 27.93% | 3.99% | 43.97% | 6.73% |
| Utility (General) | 20 | 0.56 | 5.84% | 59.05% | 33.80% | 4.04% | 29.93% | 2.83% | 40.95% | 4.61% |
| Utility (Water) | 20 | 0.75 | 6.78% | 63.33% | 77.13% | 5.04% | 14.52% | 4.31% | 36.67% | 5.87% |
| Total Market | 7766 | 1.01 | 8.07% | 57.38% | 94.94% | 6.04% | 10.32% | 5.42% | 42.62% | 6.94% |
Monis3322
example2.xls
