In this problem we have calculated the after tax IRR of drilling equipment owned by Shellout Co. using Excel. The investment of $100,000 has been depreciated using MACRS and the Pre-Tax Income amount is achieved by deducting the amount of depreciation from the lease payments. Tax rate of 34 percent is applied to the pretax income to arrive at the after tax income and the amount of depreciation is added back to this amount to arrive at the amount of net cash flows.
The book-value of the equipment is calculated by deducting the accumulated depreciation from the cost of the equipment which is then deducted from the sale proceeds to achieve the gain or loss on sale of equipment. The income of year 5 is affected by this loss and it is reflected in the net cash flow of that year. The after tax return is calculated using the IRR function on the net cash flows of the equipment.
The IRR is the expected rate of return on a project and at this rate the NPV of a project is zero (Brigham and Ehrhardt, Financial Management: Theory and Practice 11th Edition). In this problem we evaluate two options for the Profitable Company by using the NPV to find out the number of days the truck must be used to justify its purchase. The first option entails purchase of the truck at a cost of $13,000, a salvage value of $3,000 and requires maintenance of $1,100 per year and daily expenses of $35 per day. The second option requires $83 per day rental payments for the truck.
Both these options have been evaluated using excel and the tax saving factor is calculated for both options to arrive at the values of Net Cash Flow for both options These cash flows are discounted to a present values with a factor of 10% to compare the days in service. The days in service are altered using trial and error method until the NPV for both options is equalized. The NPV for both options is same at a service level of 67. 7 days which means the truck should be used for 67. 7 days. Works Cited Brigham, E. and M. Ehrhardt. Financial Management: Theory and Practice 11th Edition. Florence: South-Western Educational Publishing, 2001.