Turbitt Farm Ian Turbitt owns a
small farm located near Jackson, Ohio. He is considering purchasing or leasing
a harvester for the farm. If he purchases the harvester, he will have to take
out a loan and pay interest until he completely repays the loan or resells the
harvester. However, taking out a farm loan has advantages, including reducing
the amount of farm income subject to taxes. You will explore the Ânancial and
tax beneÂts of owning versus leasing for Ian. Complete the following:
1. Open the Turbitt workbook
located in the Excel9 > Case2 folder included with your Data Files, and then
save the workbook as Turbitt Farm in the location speciÂed by your instructor.
2. In the Documentation
worksheet, enter your name and the date.
3. Ian wants to get a $410,000
harvester for the farm. He can afford a down payment of $75,000 on the
equipment. In the Buy vs. Lease worksheet, in the range B7:B8, enter this
information, entering the down payment as a negative cash ‑ow.
4. Ian believes that he will be
able to resell the harvester for $275,000 after Âve years. The harvester itself
has a salvage value of $175,000 after 10 years. In the range B9:B11, enter this
data.
5. The cost of insurance and
housing for the harvester is $2,500 per year. The maintenance, labor, and fuel
and oil costs are $3,000, $5,500, and $9,000 annually. In the range B14:B17,
enter this data as a negative cash ‑ow. In cell B18, calculate the sum of
the annual cost of ownership, also as a negative cash ‑ow.
6. Ian can secure a loan for the
harvester at a 5.3 percent annual interest rate with annual payments made once
at the beginning of each year over a Âve-year period. Calculate the following
values:
a. In cell B21, enter the annual
interest rate of the loan.
b. In cell B22, enter the length
of the loan in years.
c. In cell B23, enter the amount
of loan by calculating the sum of the values in B7 and B8.
d. In cell B24, use the PMT
function to calculate the annual loan payment. (Hint: The value of the type
attribute should re‑ect the fact that the loan is paid at the beginning
of each year.)
7. Ian can negotiate a lease
agreement in which he pays $45,000 a year for Âve years. In the range B27:B28,
enter these parameters, entering the annual lease payment as a negative cash ‑ow.
8. In cell B31, enter the
discount rate for the time-value of money, which Ian estimates at 4.4 percent.
9. In the range B32:B33, enter
the marginal tax rate that Ian owes as 45 percent and the self-employment tax
rate of 15.9 percent.
10. Lease payments are due at
the end of each year and apply forward to the next year. In the range E8:I8,
enter the annual lease payments for Year 0 through Year 4 using the value in
cell B28.
11. In the range F9:J9, enter
the Year 1 through Year 5 cost of ownership of the harvester using the value in
cell B18.
12. In the range F10:J10,
multiply the sum of the lease payments and ownership costs by the marginal tax
rate in cell B32, and then multiply this value by –1 to calculate the reduction
in taxes owed as a positive cash ‑ow.
13. In the range E12:J12, add
the lease payment, the ownership cost, and the reduction in taxes owed for each
year to calculate the annual cash ‑ow under the lease agreement for Year
0 through Year 5.
14. In cell E14, add the value
of cell E12 to the net present value of the cash ‑ows in the F12:J12
range using the discount rate in cell B31 to calculate the present value of the
lease agreement.
15. In cell E18, enter the cost
of the down payment in Year 0 using the value in cellB8. In cell J19, enter the
income from reselling the harvester using the value in cell B9. The down
payment should appear as a negative cash ‑ow and the resell value should
appear as a positive cash ‑ow. In the range E20:J20, calculate the sum of
the down payment and resell values for Year 0 through Year 5.
16. In the range F22:J22, enter
the yearly loan payment cost using the value from cell B24.
17. In the range F25:J25, enter
the annual cost of ownership for Year 1 through Year 5 using the value in cell
B18.
18. In the range F26:J26,
calculate the interest paid for Year 1 through Year 5 on the loan. Use the IPMT
function with the loan terms in cells B21, B22, and B23 and the year values in
cells F17 through J17 for the period of the loan.
19. In range F27:J27 calculate
the depreciation of the harvester for Year 1 through Year 5. Use the DB
function with the cost, salvage, and life values in cells B7, B10, and B11, and
the year values in cells F17 through J17 to specify the period of the
depreciation. Add a negative sign before the depreciation values so that they
are treated as negative cash ‑ows.
20. In the range F28:J28,
calculate the total deductions Ian can take each year by adding the cost of
ownership, interest on loan, and annual depreciation for Year 1 through Year 5.
Change the sign of the sum by multiplying it by –1 so that the values appear as
a positive cash ‑ow.
21. In the range F29:J29,
multiply the total deductions for each year by the marginal tax rate in cell
B32 to calculate the reduction in taxes owed.
22. In cell E31, enter the value
of the harvester using the value from cell B7. In the range F31:J31, calculate
the yearly value of the harvester by adding that year’s depreciation (in row
27) to the previous year’s harvester value.
23. When Ian sells the harvester
at the end of Year 5, he will owe taxes if he sells it for more than its
depreciated value. In cell J32, insert an IF function that tests whether the
resale price (cell J19) is greater than the depreciated value (cell J31). If
the condition is true, return the difference of the resale price and the
depreciated value multiplied by the difference between the marginal tax rate
(cell B32) and the self-employment tax rate (cell B33). If the condition is
false, return a value of 0.
24. In the range F34:J34,
calculate Ian’s overall tax reduction for Year 1 through Year 5 by subtracting
the taxes owed on the harvester resale (row 32) from the reduction in taxes
owed (row 29). Note that only in Year 5 will Ian owe any taxes on the harvester
resale. These values represent how much less Ian will owe in taxes each year due
to owning and operating the harvester.
25. In the range E36:J36,
calculate Ian’s net cash ‑ow for Year 0 through Year 5 by adding the proÂt/loss
on the harvester (row 20), the annual loan payments (row 22), the cost of
ownership (row 25), and the overall tax reduction (row 34).
26. In cell E38, add the value
of cell E36 to the net present value of the cash ‑ows in the range
F36:J36 using the discount rate in cell B31 to calculate the present value of
buying the harvester.
27. The values in cells E14 and
E38 represent what the leasing plan and the buying plan will cost Ian in
current dollars. In cell B3, enter an IF function that displays the text BUY if
cell E38 is greater than cell E14; otherwise, display the text LEASE.
28. Save the workbook, and then
close it.