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 benets 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 specied by your instructor. 2. In the Documentation worksheet, enter 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 reect 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. Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 Module 9 Exploring Financial Tools and Functions | Excel EX 591 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 cellB32 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 prot/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

X AutoSave @ Off Turbitt (1) - Search (Alt+Q) prabh kaur X File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share Insert Lucida Calligraphy ~ 22 ~ A" A =1 ab General Ap O Paste B EE $ ~ % " Conditional Format as Cell Delete Sort & Find & Analyze Formatting Table Styles v Format Filter * Select v Data Undo Clipboard Font Alignment Number Styles Cells Editing Analysis Slicer Al vix fx Turbitt Family Farm A B C D E F G H K M N 6 Purchase Data Lease Option Details Year 7 Harvester Price 0 1 2 3 4 5 8 Down Payment Lease Payment 9 Resale Value (after 5 Years) Cost of Ownership 10 Salvage Value Reduction in Taxes Owed 11 Salvage Time (Years) 12 Net Annual Cash Flow 13 Annual Cost of Ownership 14 Insurance and Housing Net Present Value of Lease Option 15 Maintenance 16 Labor Buy Option Details Year 17 Fuel and Oil 0 1 2 3 4 18 Total Down Payment on Harvester 19 Resale of Harvester 20 Loan Terms Profit/Loss on Harvester 21 Annual Interest Rate 22 Length of Loan (Years) Annual Loan Payments 23 Loan Amount Documentation Buy vs. Lease + Ready Accessibility: Investigate + 94% O 12:51 AM X ENG US 2022-09-18 1X AutoSave Off Turbitt (1) - Search (Alt+Q) prabh kaur X File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share 11 ~A A ab Insert Calibri =1 General Ap O Paste EE Conditional Format as Cell Delete $ ~ % " Sort & Find & Analyze Formatting Table Styles v Format Filter * Select v Data Undo Clipboard Font Alignment Number Styles Cells Editing Analysis Slicer M23 v I X fx A B C D E F G H K M N 15 Maintenance 16 Labor Buy Option Details Year 17 Fuel and Oil 0 1 2 3 4 5 18 Total Down Payment on Harvester 19 Resale of Harvester 20 Loan Terms Profit/Loss on Harvester 21 Annual Interest Rate 22 Length of Loan (Years) Annual Loan Payments 23 Loan Amount 24 Annual Payment Tax Calculations 25 Cost of Ownership 26 Lease Terms Interest on Loan 27 Lease Length (Years) Annual Depreciation 28 Annual Payment Total Deductions 29 Reduction in Taxes Owe 30 Tax and Discount Rates 31 Discount Rate Harvester Book Value 32 Marginal Tax Rate Taxes Owed on Harvester Resale 33 Self-Employment Tax Rate 34 Overall Tax Reduction Documentation Buy vs. Lease Ready Accessibility: Investigate + 94% ENG 12:51 AM O P X US 2022-09-18 1X AutoSave Off Turbitt (1) - Search (Alt+Q) prabh kaur X File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share Calibri 11 ~A A ab General Insert =1 Ap O Paste EE $ ~ % " Conditional Format as Cell Delete Sort & Find & Analyze Formatting Table Styles v Format Filter * Select v Data Undo Clipboard Font Alignment Number Styles Cells Editing Analysis Slicer M23 v I X fx A B C D E F G H K M N 26 Lease Terms Interest on Loan 27 Lease Length (Years) Annual Depreciation 28 Annual Payment Total Deductions 29 Reduction in Taxes Owed 30 Tax and Discount Rates 31 Discount Rate Harvester Book Value 32 Marginal Tax Rate Taxes Owed on Harvester Resale 33 Self-Employment Tax Rate 34 Overall Tax Reduction 35 36 Net Annual Cash Flow 37 38 Net Present Value of Buy Option 39 40 41 42 43 44 45 Documentation Buy vs. Lease + Ready X Accessibility: Investigate + 94% P X Co ENG 12:51 AM US 2022-09-18 1