Taxes and Depreciation
Excel Assignment
Newnan Problem 12-19
•The Shellout Co. Owns drilling equip. that costs $100,000 and will be depreciated by MACRS with a lifetime of 10 years. Tax=34%. Shellout will lease the equip each year for $30000.
•At the end of 5 years Shellout Co. will sell the equip for $35,000. What is after tax IRR?
Enter the column and row headings into Excel:
1) Enter the appropriate investment cash outflow in year 0 and inflow in Year 5
2) Enter the yearly income and MACRS percentages (from the appropriate table in the book).
3) Develop formulas for the yearly depreciation, pre-tax profit, and tax.
4) Develop a formula for Net Cash flow.
5) Calculate the book value of the drilling equipment in year 5. Calculate any gain or loss and modify the pre-tax profit formula for Year 5 to reflect this.
6) Use the IRR function to find the after-tax return.
Newnan Problem 12-45
•A profitable company has two options:
1) $13,000 truck, salvage value is $3000 at end of 7 years, straight line depreciation, maintenance is $1100/year, daily expenses $35/day,
• Or
2)$83/day rental
The company has a MARR of 10% and pays an income tax rate of 50%
How many days must the truck be used to justify purchase?
1) Develop and enter a formula for expenses as a function of days in service. Make days in service a master cell.
2) Develop and enter a formula for depreciation.
3) Develop and enter a formula for total deductions.
4) Note that this problem considers expenses, not the overall project. Assume that the business is profitable overall so that the net negative cash flow here can be deducted from other profits. In this manner the tax savings produced by a deduction are actually a benefit for the company. Calculate these tax savings.
5) Now find the net cash flow of the project (cash flows + any tax benefits).
6) In the final column discount the net cash flow to present value. Establish a master cell for interest rate. Develop a formula for present value of the year's cash flow using the interest rate and referencing the year. Now you can copy this down, and it will give the appropriate discount factor for each year.
7) Continue the spreadsheet throughout the life of the project and enter cash flows as appropriate.
8) Sum the present value of the net cash flows to find the NPV
Now make a similar spreadsheet for the option where you rent the equipment on a daily basis.
1) Enter an appropriate formula for expenses as a function of days used. Refer to the same master cell you used before.
2) Calculate the tax savings produced by these expenses (assuming that they offset profits from other aspects of the business).
3) Enter a formula for the net effect of the expenses, that is, the expenses less the tax savings produced by the deduction for the expenses.
4) Enter a formula for net impact of the project on cash flow
5) Discount the net cash flow to present value. Reference the master cell for interest rate and use the same formulate in which the present value of the year's cash flow is discounted using the interest rate and referencing the year.
6) Continue the spreadsheet throughout the life of the project and enter cash flows as appropriate.
7) Sum the present value of the net cash flows to find the NPV.
8) Now find the value of days used that makes the NPVs of the two options the same. You should be able to do this by trial and error but you may also use Solver or Goal Seek.
Year Investment Income MACRS Depreciation Pre-Tax Tax Net Income Cash flow