Please help me to solve this problem and show me the excel formula to learn it, thank you.

EXERCISE 3-0 -Example You work for a nuclear research laboratory that is contemplating leasing a diagnostic scanner (leasing is a very common practice with expensive, high-tech equipment). The scanner costs $4,800,000, and it would be depreciated straight-line to zero over four years. Because of radiation contamination, it actually will be completely valueless in four years. You can lease it for $1,430,000 per year for four years. Assume that the tax rate is 21 percent. You can borrow at 8 percent before taxes. Should you lease or buy? Suppose the entire $4,800,000 purchase price of the scanner is borrowed. The rate on the loan is 8 percent, and the loan will be repaid in equal installments. Create a LOAN TABLE that explicitly incorporates the loan payments (interests v.s. principal). Show the differences in the CFs for this method and leasing year by year. A B C D E F G H I J K L M N 0 1 2 3 4 Input Area: Cost Life of machine Lease price Borrowing rate Tax rate $ 4,800,000 4 $ 1,430,000 8% 21% Output Area 1 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Depreciation tax shield Aftertax cost of debt Aftertax lease payment Total cash flow NAL You should buy. OUTPUT AREA2 : LEASE INCREMENTAL CASH FLOWS (year by year) 1 2 0 3 4 28 29 30 31 32 33 Year Choice 1) Purchase cash flows Cost of machine Depreciation tax shield Total #N/A #N/A 34 35 Choice 2) After-tax lease payments #N/A A B C E F H J K OUTPUT AREA2: LEASE INCREMENTAL CASH FLOWS (year by year) 2 0 3 4 25 26 27 28 29 30 31 32 33 34 35 Year Choice 1) Purchase cash flows Cost of machine Depreciation tax shield Total #N/A #N/A Choice 2) After-tax lease payments #N/A 36 The lease saves #N/A 37 38 39 NPV of Choice 1 NPV of Choice 2 #N/A #N/A 40 41 42 IRR of lease savings Alternative cost (after-tax bank interest) NPV (NAL net advantage to leasing) Lease or purchase? #N/A #N/A #N/A #N/A 43 44 45 46 47 #N/A #N/A 48 Borrow $0,000 from the bank and buy the computer LOAN TABLE Year 0 1 2 3 Loan at beginning of year Payment at end of year Of this payment Interest Repayment of principal Remaining principal at end of year #N/A #N/A #N/A 49 50 51 52 53 54 55 56 57 58 59 60 61 After-tax interest Net after-tax loan cash cost #N/A #N/A Machine + loan (Cash flow of buying the loan-financed machine) Cost of machine Depreciation tax shield After-tax loan cash flow Method 1: Total: Buy machine + take loan #N/A #N/A #N/A #N/A #N/A Borrow $0,000 from the bank and buy the computer LOAN TABLE Year 0 1 2 3 Loan at beginning of year Payment at end of year Of this payment Interests Repayment of principal Remaining principal at end of year #N/A #N/A #N/A 54 After-tax interest Net after-tax loan cash cost #N/A #N/A 89 88 8 3 98% 989a 57 Machine + loan (Cash flow of buying the loan-financed machine) Cost of machine Depreciation tax shield After-tax loan cash flow Method 1: Total: Buy machine + take loan #N/A #N/A #N/A Method 2: the after-tax lease payments #N/A 65 METHOD 1 NPV METHOD 2 NPV 69 EXERCISE 3-0 -Example You work for a nuclear research laboratory that is contemplating leasing a diagnostic scanner (leasing is a very common practice with expensive, high-tech equipment). The scanner costs $4,800,000, and it would be depreciated straight-line to zero over four years. Because of radiation contamination, it actually will be completely valueless in four years. You can lease it for $1,430,000 per year for four years. Assume that the tax rate is 21 percent. You can borrow at 8 percent before taxes. Should you lease or buy? Suppose the entire $4,800,000 purchase price of the scanner is borrowed. The rate on the loan is 8 percent, and the loan will be repaid in equal installments. Create a LOAN TABLE that explicitly incorporates the loan payments (interests v.s. principal). Show the differences in the CFs for this method and leasing year by year. A B C D E F G H I J K L M N 0 1 2 3 4 Input Area: Cost Life of machine Lease price Borrowing rate Tax rate $ 4,800,000 4 $ 1,430,000 8% 21% Output Area 1 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Depreciation tax shield Aftertax cost of debt Aftertax lease payment Total cash flow NAL You should buy. OUTPUT AREA2 : LEASE INCREMENTAL CASH FLOWS (year by year) 1 2 0 3 4 28 29 30 31 32 33 Year Choice 1) Purchase cash flows Cost of machine Depreciation tax shield Total #N/A #N/A 34 35 Choice 2) After-tax lease payments #N/A A B C E F H J K OUTPUT AREA2: LEASE INCREMENTAL CASH FLOWS (year by year) 2 0 3 4 25 26 27 28 29 30 31 32 33 34 35 Year Choice 1) Purchase cash flows Cost of machine Depreciation tax shield Total #N/A #N/A Choice 2) After-tax lease payments #N/A 36 The lease saves #N/A 37 38 39 NPV of Choice 1 NPV of Choice 2 #N/A #N/A 40 41 42 IRR of lease savings Alternative cost (after-tax bank interest) NPV (NAL net advantage to leasing) Lease or purchase? #N/A #N/A #N/A #N/A 43 44 45 46 47 #N/A #N/A 48 Borrow $0,000 from the bank and buy the computer LOAN TABLE Year 0 1 2 3 Loan at beginning of year Payment at end of year Of this payment Interest Repayment of principal Remaining principal at end of year #N/A #N/A #N/A 49 50 51 52 53 54 55 56 57 58 59 60 61 After-tax interest Net after-tax loan cash cost #N/A #N/A Machine + loan (Cash flow of buying the loan-financed machine) Cost of machine Depreciation tax shield After-tax loan cash flow Method 1: Total: Buy machine + take loan #N/A #N/A #N/A #N/A #N/A Borrow $0,000 from the bank and buy the computer LOAN TABLE Year 0 1 2 3 Loan at beginning of year Payment at end of year Of this payment Interests Repayment of principal Remaining principal at end of year #N/A #N/A #N/A 54 After-tax interest Net after-tax loan cash cost #N/A #N/A 89 88 8 3 98% 989a 57 Machine + loan (Cash flow of buying the loan-financed machine) Cost of machine Depreciation tax shield After-tax loan cash flow Method 1: Total: Buy machine + take loan #N/A #N/A #N/A Method 2: the after-tax lease payments #N/A 65 METHOD 1 NPV METHOD 2 NPV 69