Required Reading – Chapter 1 Model (from textbook companion Excel Spreadsheet)
This spreadsheet model performs many of the calculations contained in Chapter 1. We recommend that you use the model in the following manner:
1. First, recognize that you do not have to use this model at all to understand the basic concepts of this chapter. However, using the model will increase your understanding of the relevant concepts, and it will surely help when you use spreadsheet models for other purposes, especially any problem sets or cases assigned for this course.
2. Start by reading the chapter in its entirety.
3. Now place the text alongside your computer with this model on the screen. When you come to an explanation of a calculation in the text, see if the model has a matching calculation. The Table of Contents tab allows for improved navigation of the model.
4. We assume that you know the basics of Excel, but that you have not encountered some of its features or may need a refresher or two. So we have built in explanations of how to do some of the functions in the model. As a result, you will learn more about Excel at the same time you learn about taxes and depreciation.
5. Throughout this model, page numbers of the matching text calculations are provided in pink. Input data are in red on a yellow background, and output data are in green on a beige background. You are encouraged to change the input data to learn more about the calculations in the model.
Corporate Income Taxes (Page 17)
The corporate tax structure below has marginal rates as high as 39 percent, which brings the average rate up to 35 percent.
| Tax | Marginal | Taxable | Corporate | Average | ||
| Taxable Income Between: | Base Amount | Tax Rate | Income | Taxes | Tax Rate | |
| $0 | $50,000 | $0 | 15% | $50,000 | $7,500 | 15.0% |
| $50,001 | $75,000 | $7,500 | 25% | $75,000 | $13,750 | 18.3% |
| $75,001 | $100,000 | $13,750 | 34% | $100,000 | $22,250 | 22.2% |
| $100,001 | $335,000 | $22,250 | 39% | $335,000 | $113,900 | 34.0% |
| $335,001 | $10,000,000 | $113,900 | 34% | $10,000,000 | $3,400,000 | 34.0% |
| $10,000,001 | $15,000,000 | $3,400,000 | 35% | $15,000,000 | $5,150,000 | 34.3% |
| $15,000,001 | $18,333,333 | $5,150,000 | 38% | $18,333,333 | $6,416,666 | 35.0% |
| $18,333,334 | – | $6,416,667 | 35% | $20,000,000 | $7,000,000 | 35.0% |
The average federal corporate income tax rate is progressive to $18,333,333 of income, but it is constant thereafter.
Depreciation (Page 21)
| Federal plus state tax rate | 40% |
| Hospital A | Hospital B | ||
| Revenue | $1,000,000 | $1,000,000 | |
| Costs except depreciation | $700,000 | $700,000 | |
| Depreciation | $100,000 | $200,000 | |
| Taxable income | $200,000 | $100,000 | |
| Federal plus state taxes | $80,000 | $40,000 | |
| After-tax income | $120,000 | $60,000 | |
| Add back depreciation | $100,000 | $200,000 | |
| Net cash flow | $220,000 | $260,000 |
Hospital B’s cash flow is larger by $260,000 − $220,000 = $40,000, which represents the tax savings, or tax shield, on its additional $100,000 in depreciation expense:
Tax shield = Tax rate × Depreciation expense = 0.40 × $100,000 = $40,000.
Suppose the hospitals were not-for-profit hospitals. If you enter 0 in cell C51, taxes would be zero for both and they would have $300,000 in net cash flow. However, Hospital A would report $200,000 in earnings, while Hospital B would report $100,000 in earnings.
Book Depreciation (Page 22)
The most common method of determining book depreciation is the straight-line method. To apply the straight-line method:
(1) start with the capitalized cost of the asset (generally, price plus shipping plus installation);
(2) subtract the asset’s salvage value, which, for book purposes, is the estimated value of the asset at the end of its useful life; and
(3) divide the net amount by the asset’s useful life.
| Purchase price of machine | $100,000 | |
| Shipping and installation | $10,000 | |
| Salvage value | $5,000 | |
| Useful life in years | 10 | |
| Book depreciation expense | $10,500 |
The name “straight line” comes from the fact that the annual depreciation under this method is constant. The book value of the asset, which is the cost minus the accumulated depreciation to date, declines evenly (follows a straight line) over time.
MACRS Depreciation Illustration (Page 24)
The calculation of MACRS depreciation uses three components:
(1) the depreciable basis of the asset, which is the total amount to be depreciated;
(2) a recovery period that defines the length of time over which the asset is depreciated; and
(3) allowance percentages for each recovery period, which, when multiplied by the basis, give each year’s depreciation expense.
MACRS Recovery Allowances
| Ownership | Recovery Period | |||
| Year | 3-Year | 5-Year | 7-Year | 10-Year |
| 1 | 33% | 20% | 14% | 10% |
| 2 | 45% | 32% | 25% | 18% |
| 3 | 15% | 19% | 17% | 14% |
| 4 | 7% | 12% | 13% | 12% |
| 5 | 11% | 9% | 9% | |
| 6 | 6% | 9% | 7% | |
| 7 | 9% | 7% | ||
| 8 | 4% | 7% | ||
| 9 | 7% | |||
| 10 | 6% | |||
| 11 | 3% | |||
| Total | 100% | 100% | 100% | 100% |
MACRS 5-Year Example
| Purchase price of machine | $100,000 | |
| Shipping and installation | $10,000 | |
| Depreciable basis | $110,000 |
| Recovery | Tax depreciation | ||
| Year | percentage | expense | Tax book value |
| 1 | 20% | $22,000 | $88,000 |
| 2 | 32% | $35,200 | $52,800 |
| 3 | 19% | $20,900 | $31,900 |
| 4 | 12% | $13,200 | $18,700 |
| 5 | 11% | $12,100 | $6,600 |
| 6 | 6% | $6,600 | $0 |
| Total | 100% | $110,000 |
According to the IRS, the value of a depreciable asset at any point in time is its tax book value. If a business sells an asset for more than its tax book value, the implication is that the firm took too much depreciation, and the IRS will want to recover the excess tax benefit. Conversely, if an asset is sold for less than its book value, the implication is that the firm did not take sufficient depreciation, and it can take additional depreciation on the sale of the asset.
If the machine above is sold after Year 2:
| Sale price of machine | $60,000 | |
| Tax book value | $52,800 | |
| Change in taxable income | $7,200 |