I need help with my homework. I need to answer the questions on under the assignment and I need to complete the excel spreadsheet like this example. Please let me know if you could help with this assignment.

Lease Buy Analysis Drake Sikich, Purdue University Northwest GENERAL INFORMATION Price of Equipment Depreciable cost Depreciation term: N Book value at end of term Annual Depreciation Use term of equipment Expected mkt value at t=N WAAC: r Tax rate: T BORROW-TO-BUY OPTION Loan rD rD(1-T) Term of Loan: N Market value at t=N Book value at t=N LEASE OPTION Lease term: N Lease PMT Down payment Other expenses Buy-out option Maintenance Residual value $ $ $ $ $ $ $ $ Yes $ $ 200,000.00 200,000.00 4 years $0.00 50,000.00 Straightline method 4 years 52,000.00 15% Applies to lessor only 20% Applies to both lessor and lessee 200,000.00 22% 17.60% 4 52,000.00 $0.00 4 51,000.00 51,000.00 $0.00 2,100.00 52,000.00 ANALYSIS PART A: DEPRECIATION SCHEDULE Year Beginning Bk Balance 1 $ 200,000.00 2 $ 150,000.00 3 $ 100,000.00 4 $ 50,000.00 PART B: LOAN AMORTIZATION SCHEDULE Depreciation Expense $ 50,000.00 $ 50,000.00 $ 50,000.00 $ 50,000.00 Ending Bk Balance $ 150,000.00 $ 100,000.00 $ 50,000.00 $ - Accum Depreciation $ 50,000.00 $ 100,000.00 $ 150,000.00 $ 200,000.00 Year Beginning Amortized Loan Balance PMT 1 $ 200,000.00 $80,204.02 2 $ 163,795.98 $80,204.02 3 $ 119,627.07 $80,204.02 4 $ 65,741.00 $80,204.02 Interest Loan PMT Re-PMT $ 44,000.00 $36,204.02 $ 36,035.12 $44,168.91 $ 26,317.96 $53,886.07 $ 14,463.02 $65,741.00 LESSEE'S ANALYSIS PART C: BUY OPTION (PERFORMA INCOME STATEMENT) - NPV ANALYISIS 0 1 2 Maintenance ($2,100.00) ($2,100.00) ($2,100.00) Interest payment ($44,000.00) ($36,035.12) Depreciation ($50,000.00) ($50,000.00) ($2,100.00) ($96,100.00) ($88,135.12) Tax effects $420.00 $19,220.00 $17,627.02 Net income ($1,680.00) ($76,880.00) ($70,508.09) Add back depreciation $50,000.00 $50,000.00 Loan repayment ($36,204.02) ($44,168.91) Cost of Asset Loan value ($200,000.00) Purchase of Machine $200,000.00 Tax effects NCF = NI + Depreciation ($1,680.00) ($63,084.02) ($64,677.00) PV of cost of BUY ($156,494.84) cash flows discounted at rd(1-T) 3 ($2,100.00) ($26,317.96) ($50,000.00) ($78,417.96) $15,683.59 ($62,734.36) $50,000.00 ($53,886.07) ($66,620.43) PART D: LEASE OPTION (PERFORMA INCOME STATEMENT) - NPV ANALYSIS Lease Payment Other lease expenses 0 ($51,000.00) $0.00 1 ($51,000.00) $0.00 2 ($51,000.00) $0.00 3 ($51,000.00) $0.00 Tax effects Net income $10,200.00 ($40,800.00) $10,200.00 ($40,800.00) $10,200.00 ($40,800.00) $10,200.00 ($40,800.00) Buyout option Total cash flow ($40,800.00) ($40,800.00) ($40,800.00) ($40,800.00) PV of cost of LEASE Net advantage to leasing (NAL): Decision: Breakeven lease payment to the lessee: ($178,601.63) cash flows dicsounted at rd(1-T) ($22,106.79) {GOAL SEEK} LESSOR'S ANALYSIS 0 Lease payment received maintenance expenses Depreciation $ 1 2,100.00 $ 2 ### $ 50,000.00 $ 3 2,100.00 $ 50,000.00 $ 2,100.00 50,000.00 $ $ 52,000.00 (400.00) Tax effects Net income NCF = NI + Depreciation Price of equipment Sale of equipment Tax effect on sale Total cash flow $ NPV of lease investment IRR of lease investment $ (200,000.00) - Cash flows discounted at WACC Err:523 Decision: Breakeven lease pmt to the lessor {GOAL SEEK} Ending Loan Bal. $ 163,795.98 $ 119,627.07 $65,741.00 $0.00 4 ($14,463.02) ($50,000.00) ($64,463.02) $12,892.60 ($51,570.42) $50,000.00 ($65,741.00) $52,000.00 ($10,400.00) ($25,711.42) 4 ($51,000.00) $0.00 $10,200.00 ($40,800.00) ($52,000.00) ($92,800.00) 4 $ $ 2,100.00 50,000.00 Leasing: Spreadsheet Modeling Assignment. FALL MGMT 61200 Please watch the Video on this subject - posted on the last page of the PP handout - and apply the SAME SPREADSHEET MODEL using the financial data presented below. Please be sure to carefully note the specific information about this case, shown in italics. Your completed work will be inspected on your LAPTOP only. Please do NOT email your work or submit a hard copy. If you do not have a laptop, we can review your work together in the COB computer lab on the 3rd floor of Anderson Hall. General Information Cost of equipment $550,000 Depreciation MACRS Term: N 4 Tax rate: T 40% WACC: r 10.00% Market value at end of term: N $125,000 3-year class Applies only to lessor's analysis Borrow-to-Buy Option Cost of debt: rd 8.00% Term of Loan: N 4 Salvage value (t=N) $125,000 Maintenance $12,500 Paid at END of each year Lease Option Lease term: N 4 Lease PMT $170,000 Other expenses None Buy-out option Yes Maintenance $12,500 Residual value at t=N $125,000 Paid at BEGINNING of each of the 4 years. Note: there are 4 payments Note: Lessee plans to keep using the equipment beyond 4 years. Cost is borne by lessor and paid at the BEGINNING of each year ADDITIONAL QUESTIONS 1. Is this lease beneficial to the lessee? Why? 2. Is this lease beneficial to the lessor? Why? 3. What is the maximum lease payment that would cause the lessee to prefer the lease over the buy option? 4. What is the minimum lease payment that would cause the lessor to lease out the equipment? 5. Now consider the lessee's breakeven lease payment. Given that amount, what is the lessor's NPV? 6. Now consider the lessor's breakeven lease payment. Given that amount, what is the lessee's NAL