Calculate the tax savings produced by these expenses

Assignment Help Financial Management

Reference no: EM132025301

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

Reference no: EM132025301

Questions Cloud

Compute the gain or loss on the sale : During her 18th month of ownership, she sold the property for $550,000 and paid transaction costs equal to eight percent of the sales price.

Complete the required federal partnership income tax return : Complete the required federal partnership income tax return forms for AAA Fast Plumbing for the 2013 tax year based upon the facts presented below.

Determine the tax on the income : Determine the tax on the income Where x represent the person taxable salary

What are the rules for business or personal trips : What are the rules for business/personal trips? Under what circumstances are the costs to/from disallowed? Should there be rules like this?

Calculate the tax savings produced by these expenses : Calculate the tax savings produced by these expenses (assuming that they offset profits from other aspects of the business).

Calculate the free cash flow : Your team is evaluating two mutually exclusive projects. The initial cost of each investment is $50,000. The probability of the cash flows is shown below.

Analyze the net present value of this projet : The tax rate is 35 percent and the required rate of return is 15 percent. What is the net present value of this project?

Determine what are some of the risks : Team A is a long term team that desires to retire at age 67, and has a current average age of 27.

What leverage ratio must a farmer achieve in order to grow : What factors should both this farmer and the lender consider when they are deciding on whether the farmer's leverage should be increased to this point?

User Account

All Pages