Assignment 2: Lease vs. Buy

Your employer, Barnaby Well Company, is considering the acquisition of a new drill truck and your boss has asked you to evaluate the decision that she has made to buy the truck. The truck has a purchase price of $60,000 and a useful life of 4 years and a zero salvage value. Barnaby can borrow to buy the truck for $60,000 or lease the truck for $15,000 for 4 years, paid at the beginning of each year.
If debt is used to buy the truck, Barnaby can borrow at 8% annual interest, with payments at the end of each year. The marginal tax rate for the firm is 40%. The asset is classified as a 3-year cost recovery asset for depreciation purposes. According to the current tax laws, Barnaby is allowed to use MACRS depreciation with 30% rate for year one, 45% for year two, 20% for year three and 5% for year four. There will be no salvage value at the end of the fourth year.

Questions:

What is the annual cost, before any tax considerations, of the lease option? Are there any tax considerations and if so, what is the after tax annual cost of the lease agreement? Explain your answer.

What is the total cost of leasing the truck today?

What are the annual cash flows if the truck is purchased with debt financing?

What is the cost of purchasing the truck with debt financing today?

Make a recommendation to your boss as to whether the company should buy or lease the truck. Justify your recommendations.

I need the excel sheet. I am so confused.

To answer the questions and calculate the financial implications of leasing vs. buying the truck, we'll need to perform some calculations in an Excel sheet. Here's a step-by-step guide on how to calculate the required values for each question:

1. Begin by opening a new Excel sheet and labeling the columns as follows:
- Column A: Year
- Column B: Lease payment
- Column C: Tax depreciation
- Column D: Tax shield
- Column E: After-tax lease cost

2. In cell A2, enter the value "Year 1". Then, in cells A3 to A5, enter the numbers 2, 3, and 4 representing the remaining three years of the truck's useful life.

3. In cell B2, enter the lease payment of $15,000.

4. In cell C2, calculate the tax depreciation for year 1 using the MACRS rates provided. The formula for cell C2 would be: =-$60,000 * 30%.

5. Copy the formula from cell C2 to cells C3 to C5 to calculate the tax depreciation for the remaining years.

6. In cell D2, calculate the tax shield by multiplying the tax depreciation (cell C2) by the tax rate (40%). The formula for cell D2 would be: =C2 * 40%.

7. Copy the formula from cell D2 to cells D3 to D5 to calculate the tax shield for the remaining years.

8. In cell E2, calculate the after-tax lease cost by subtracting the tax shield (cell D2) from the lease payment (cell B2). The formula for cell E2 would be: =B2 - D2.

9. Copy the formula from cell E2 to cells E3 to E5 to calculate the after-tax lease cost for the remaining years.

10. Now, let's move on to calculating the total cost of leasing the truck. In cell F2, enter the formula: =SUM(B2:E2). This will sum up the lease payments for all four years.

11. Copy the formula from cell F2 to cell F3 to get the total cost of leasing the truck.

12. Next, let's calculate the annual cash flows if the truck is purchased with debt financing. In column G, label the header as "Debt Financing".

13. In G2, enter the annual loan payment to purchase the truck using the PMT function. The formula for G2 would be: =PMT(8%, 4, -60000).

14. Copy the formula from G2 to cells G3 to G5 to calculate the loan payments for the remaining years.

15. In cell H2, calculate the tax depreciation for year 1 (similar to step 4).

16. Copy the formula from cell H2 to cells H3 to H5 to calculate the tax depreciation for the remaining years.

17. In cell I2, calculate the tax shield by multiplying the tax depreciation (cell H2) by the tax rate (40%).

18. Copy the formula from cell I2 to cells I3 to I5 to calculate the tax shield for the remaining years.

19. In cell J2, calculate the net cash flow by subtracting the tax shield (cell I2) from the loan payment (cell G2).

20. Copy the formula from cell J2 to cells J3 to J5 to calculate the net cash flow for the remaining years.

21. Now, calculate the cost of purchasing the truck with debt financing. In cell K2, enter the formula: =SUM(G2:J2). This will sum up the loan payments for all four years.

22. Copy the formula from cell K2 to cell K3 to get the cost of purchasing the truck with debt financing.

23. Finally, make a recommendation by analyzing the total costs and cash flows calculated. Compare the total cost of leasing (from cell F3) with the cost of purchasing (from cell K3). Consider other factors like cash flow flexibility, maintenance costs, and the company's overall financial situation to justify your recommendation.

Please note that these instructions provide a general framework for the calculations. Feel free to adapt them as needed based on your specific Excel version and requirements.