Excel Online Structured Activity: Lease versus Buy Sadik Industries must install $1 million of new machinery in its Texas plant. It can obtain a bank loan for 100% of the required amount. Alternatively, a Texas investment banking firm that represents a group of investors believes that it can arrange for a lease financing plan. Assume that these facts apply: 1. The equipment falls in the MACRS 3-year class. 2. Estimated maintenance expenses are $54,000 per year. 3. The firm's tax rate is 38%. 4. If the money is borrowed, the bank loan will be at a rate of 12%, amortized in six equal installments at the end of each year. 5. The tentative lease terms call for payments of $280,000 at the end of each year for 3 years. The lease is a guideline lease. 6. Under the proposed lease terms, the lessee must pay for insurance, property taxes, and maintenance. 7. Sadik must use the equipment if it is to continue in business, so it will almost certainly want to acquire the property at the end of the lease. If it does, then under the lease terms it can purchase the machinery at its fair market value at Year 3. The best estimate of this market value is $190,000, but it could be much higher or lower under certain circumstances. If purchased at Year 3, the used equipment would fall into the MACRS 3-year class. Sadik would actually be able to make the purchase on the last day of the year (i.e., slightly before Year 3), so Sadik would get to take the first depreciation expense at Year 3 (the remaining depreciation expenses would be at Year 4 through Year 6). On the time line, Sadik would show the cost of the used equipment at Year 3 and its depreciation expenses starting at Year 3. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet 54 Net advantage of leasing 55 56 Should the firm lease the machinery? \begin{tabular}{|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F \\ \hline 1 & Lease versus Buy & & & & & \\ \hline \multicolumn{7}{|l|}{2} \\ \hline 3 & Cost of machinery & $1,000,000 & & & & \\ \hline \multicolumn{7}{|l|}{5} \\ \hline 6 & MACRS Depreciation Rates: & & Year 1 & Year 2 & Year 3 & Year 4 \\ \hline 7 & & & 33.33% & 44.45% & 14.81% & 7.41% \\ \hline \multicolumn{7}{|l|}{8} \\ \hline 10 & Length of lease term (in years) & 3 & & & & \\ \hline 11 & Annual end-of-year lease payments & $280,000 & & & & \\ \hline 12 & Lessee pays for insurance, property taxes, and maintenance & Yes & & & & \\ \hline 13 & Machinery fair market value at Year 3 & $190,000 & & & & \\ \hline 14 & Firm's tax rate & 38.00% & & & & \\ \hline 15 & Bank loan rate & 12.00% & & & & \\ \hline 16 & Length of loan term (in years) for annual end-of-year payments & 6 & & & & \\ \hline 17 & & & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 18 & Borrow and Buy Analysis: & & & & & & & \\ \hline 19 & Depreciation Schedule of New Machinery: & & Year 1 & Year 2 & Year 3 & Year 4 & & \\ \hline 20 & Depreciation expense & & & & & & & \\ \hline 21 & Book value of new machinery & & $1,000,000 & $1,000,000 & $1,000,000 & $1,000,000 & & \\ \hline 22 & & & & & & & & \\ \hline 23 & Amortization Schedule of Loan: & & Year 1 & Year 2 & Year 3 & Year 4 & Year 5 & Year 6 \\ \hline 24 & Beginning loan balance & & $1,000,000 & $0 & $0 & $0 & $0 & $0 \\ \hline 25 & Loan payment & & & 0 & 0 & 0 & 0 & 0 \\ \hline 26 & Interest payment & & & & & & & \\ \hline 27 & Principal payment & & & & & & & \\ \hline 28 & Ending loan balance & & & & & & & \\ \hline 29 & & & & & & & & \\ \hline 30 & Cost of Owning: & Year 0 & Year 1 & Year 2 & Year 3 & Year 4 & Year 5 & Year 6 \\ \hline 31 & Purchase price of machinery & & & & & & & \\ \hline 32 & Loan proceeds & & & & & & & \\ \hline 33 & Loan payments & & & 0 & 0 & 0 & 0 & 0 \\ \hline 34 & Interest tax savings & & & & & & & \\ \hline 35 & Depreciation tax savings & & & & & & & \\ \hline \end{tabular} a. What is the net advantage of leasing? Should Sadik take the lease? Do not round intermediate calculations. Round your answer to the nearest dollar. Net advantage of leasing \$ Since the cost of leasing the machinery is than the cost of owning it, the firm should the equipment. b. The decision almost can be considered a bet on the future residual value. Do you think the residual cash flows are equal in risk to the other cash flows? (Hint: if you discount a negative cash flow at a higher rate, you get a better NPV - the NPV of a negative cash flow stream is less negative at high discount rates.) Excel Online Structured Activity: Lease versus Buy Sadik Industries must install $1 million of new machinery in its Texas plant. It can obtain a bank loan for 100% of the required amount. Alternatively, a Texas investment banking firm that represents a group of investors believes that it can arrange for a lease financing plan. Assume that these facts apply: 1. The equipment falls in the MACRS 3-year class. 2. Estimated maintenance expenses are $54,000 per year. 3. The firm's tax rate is 38%. 4. If the money is borrowed, the bank loan will be at a rate of 12%, amortized in six equal installments at the end of each year. 5. The tentative lease terms call for payments of $280,000 at the end of each year for 3 years. The lease is a guideline lease. 6. Under the proposed lease terms, the lessee must pay for insurance, property taxes, and maintenance. 7. Sadik must use the equipment if it is to continue in business, so it will almost certainly want to acquire the property at the end of the lease. If it does, then under the lease terms it can purchase the machinery at its fair market value at Year 3. The best estimate of this market value is $190,000, but it could be much higher or lower under certain circumstances. If purchased at Year 3, the used equipment would fall into the MACRS 3-year class. Sadik would actually be able to make the purchase on the last day of the year (i.e., slightly before Year 3), so Sadik would get to take the first depreciation expense at Year 3 (the remaining depreciation expenses would be at Year 4 through Year 6). On the time line, Sadik would show the cost of the used equipment at Year 3 and its depreciation expenses starting at Year 3. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet 54 Net advantage of leasing 55 56 Should the firm lease the machinery? \begin{tabular}{|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F \\ \hline 1 & Lease versus Buy & & & & & \\ \hline \multicolumn{7}{|l|}{2} \\ \hline 3 & Cost of machinery & $1,000,000 & & & & \\ \hline \multicolumn{7}{|l|}{5} \\ \hline 6 & MACRS Depreciation Rates: & & Year 1 & Year 2 & Year 3 & Year 4 \\ \hline 7 & & & 33.33% & 44.45% & 14.81% & 7.41% \\ \hline \multicolumn{7}{|l|}{8} \\ \hline 10 & Length of lease term (in years) & 3 & & & & \\ \hline 11 & Annual end-of-year lease payments & $280,000 & & & & \\ \hline 12 & Lessee pays for insurance, property taxes, and maintenance & Yes & & & & \\ \hline 13 & Machinery fair market value at Year 3 & $190,000 & & & & \\ \hline 14 & Firm's tax rate & 38.00% & & & & \\ \hline 15 & Bank loan rate & 12.00% & & & & \\ \hline 16 & Length of loan term (in years) for annual end-of-year payments & 6 & & & & \\ \hline 17 & & & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 18 & Borrow and Buy Analysis: & & & & & & & \\ \hline 19 & Depreciation Schedule of New Machinery: & & Year 1 & Year 2 & Year 3 & Year 4 & & \\ \hline 20 & Depreciation expense & & & & & & & \\ \hline 21 & Book value of new machinery & & $1,000,000 & $1,000,000 & $1,000,000 & $1,000,000 & & \\ \hline 22 & & & & & & & & \\ \hline 23 & Amortization Schedule of Loan: & & Year 1 & Year 2 & Year 3 & Year 4 & Year 5 & Year 6 \\ \hline 24 & Beginning loan balance & & $1,000,000 & $0 & $0 & $0 & $0 & $0 \\ \hline 25 & Loan payment & & & 0 & 0 & 0 & 0 & 0 \\ \hline 26 & Interest payment & & & & & & & \\ \hline 27 & Principal payment & & & & & & & \\ \hline 28 & Ending loan balance & & & & & & & \\ \hline 29 & & & & & & & & \\ \hline 30 & Cost of Owning: & Year 0 & Year 1 & Year 2 & Year 3 & Year 4 & Year 5 & Year 6 \\ \hline 31 & Purchase price of machinery & & & & & & & \\ \hline 32 & Loan proceeds & & & & & & & \\ \hline 33 & Loan payments & & & 0 & 0 & 0 & 0 & 0 \\ \hline 34 & Interest tax savings & & & & & & & \\ \hline 35 & Depreciation tax savings & & & & & & & \\ \hline \end{tabular} a. What is the net advantage of leasing? Should Sadik take the lease? Do not round intermediate calculations. Round your answer to the nearest dollar. Net advantage of leasing \$ Since the cost of leasing the machinery is than the cost of owning it, the firm should the equipment. b. The decision almost can be considered a bet on the future residual value. Do you think the residual cash flows are equal in risk to the other cash flows? (Hint: if you discount a negative cash flow at a higher rate, you get a better NPV - the NPV of a negative cash flow stream is less negative at high discount rates.)