Question

Your firm is contemplating the purchase of a new $1,831,500 computer-based order entry system. The system will be depreciated straight-line to zero over its 5-year life. It will be worth $178,200 at the end of that time. You will be able to reduce working capital by $247,500 (this is a one-time reduction). The tax rate is 22 percent and your required return on the project is 18 percent and your pretax cost savings are $706,900 per year. a. What is the NPV of this project? b. What is the NPV if the pretax cost savings are $508,950 per year? c. At what level of pretax cost savings would you be indifferent between accepting the project and not accepting it?

Answer 1

a.

As the first step, we need to calculate the post tax annual savings. We adjust savings for tax, because the saved amount will end up increasing the company's profit before tax. This will lead to an increase in taxes and so the benefit of amount saved would actually be lower after taking tax into the picture. The adjustment can be made using the following formula:

$Post\;tax\;savings=pre\;tax\;savings(1-tax\;rate)$

The excel implementation is as shown:

Post tax savings calculation Tax rate 22% Year Pretax cost savings Post tax cost savings 0 2 4 $706,900.00 $706,900.00 706,900.00 $706,900.00$706,900.00 $551,382.00 $551,382.00 $551,382.00 $551,382.00 $551,382.00

Formula in excel:

Net step is to calculate the depreciation tax shield. Because of our investment, we are going to end up adding depreciation expense in our income statements. Now, this has 2 aspects to it:

• First, our net income is going to go down because expenses are increasing. This would lower the tax expense of the company.
• Second, since depreciation is a non cash expense, no real cash outflow will happen.

Now, this has a net effect of saving us taxes which have to be considered while evaluating the project. To calculate the depreciation tax shield we first calculate the depreciation amount using straight line method which is calculated using the formula:

$Depreciation=\frac{Initial\: value\: of\: asset-Final\; value\; of \;asset}{Life\; of\; asset}$

And finally the tax benefit on the above calculated depreciation amount is simply calculated as:

$Depreciation\;tax\;shield=Depreciation*tax\;rate$

Formula in excel:

Now, we prepare the cash flow statement by including the initial investment, cash flow from reduction in working capital, depreciating tax shield and post tax savings. We sum them all up to calculate net cash flow from the project. Then NPV can be calculated easily in excel using the NPV funciton.

NPV formula:

So, the net present value in first case is $ 300,079.70

b.

For the second, by simply changing the pre tax savings figure to $508,950, we can compute the NPV:

So, the NPV is this case is - $ 109,105.32.

c.

In the last part, we have to calculate a pretax savings figure at which NPV is zero. This can achieved using goal seek in excel. Goal seek can be found in the data tab->what if analysis->goal seek.

How goal seek works is that you set a cell (NPV in this case) to a value (zero) by changing another cell (pretax cash flow) as shown below:

If you implement it correctly you will find that NPV is zero at a pretax cash flow of $561,731.50.