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 following column and row headings into Excel: Year Investment Income MACRS Depreciation Pre-Tax Income Tax Net Cash flow 0 1 A B 2 C 3 4 5 D 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? Input the following column and row labels into Excel: Year Investment Expense Depreciation Gain Loss Total Deduction Tax Savings Net Cash flow PV 0 E 1 F G H I 2 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 years 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.