Janet Wigandt, an electrical engineer for Instrument Control, Inc. (ICI), has be
ID: 2782175 • Letter: J
Question
Janet Wigandt, an electrical engineer for Instrument Control, Inc. (ICI), has been asked to perform a lease-buy analysis of a new pin-inserting machine for ICI's PC-board manufacturing that has a project life of four years with annual revenues of $200,000:
- Buy Option: The equipment costs $140,000. To purchase it, ICI could obtain a term loan for the full amount at 14% interest, which is payable in four equal end-of-year annual installments. The machine falls into a five-year MACRS property classification. Annual operating costs of $39,000 are anticipated. The machine requires annual maintenance at a cost of $9,750. Because technology is changing rapidly in pin-inserting machinery, the salvage value of the machine is expected to be only $22,000.
- Lease Option: Business Leasing, Inc. (BLI) is willing to write a four-year operating lease on the equipment for payments of $44,000 at the beginning of each year. Under this arrangement, BLI will maintain the asset so that the annual maintenance cost of $9,750 will be saved. The operating costs will be the same $39,000. ICI's marginal tax rate is 40%, and its MARR is 17% during the analysis period.
How do you solve this in excel?
Thank you.
Explanation / Answer
Initial Cost 140000 Annual equal installment to repay the loan in 4 installments PV of annuity for making pthly payment P = PMT x (((1-(1 + r) ^- n)) / i) Where: P = the present value of an annuity stream PMT = the dollar amount of each annuity payment r = the effective interest rate (also known as the discount rate) i=nominal Interest rate n = the number of periods in which payments will be made 140000 =Annual equal payment*(((1-(1 + 14%) ^-4)) / 14%) 140000 =Annual equal payment*2.913 Annual equal payments =140000/2.913 Annual equal payments 48060.419 Amortization schedule AmortiZation Schedule Year Principal Interest Repayment Outstanding Tax saving on int 1 140,000 19,600 (48,060) 111,540 7,840 2 111,540 15,616 (48,060) 79,095 6,246 3 79,095 11,073 (48,060) 42,108 4,429 4 42,108 5,953 (48,060) 0 2,381 Annual maintenance 9,750 After tax maintenance 5,850 Year Cost of Assets Depreciation Depreciation Tax saving @ 40% 1 140,000 20.00% 28,000 11,200 2 140,000 32.00% 44,800 17,920 3 140,000 19.20% 26,880 10,752 4 140,000 11.52% 16,128 6,451 115,808 46,323 WDV 24,192 Sale value 22,000 Loss 2,192 Tax saving 876.80 After tax salvage value 22,876.80 After tax annual cost of buying Year Maintenance Tax saving on interest Tax saving on Depreciation Total cost 0 - - - - 1 (5,850) 7,840 11,200 13,190 2 (5,850) 6,246 17,920 18,316 3 (5,850) 4,429 10,752 9,331 4 (5,850) 2,381 6,451 2,982 After tax annual cost of leasing Year Maintenance Tax saving on lease payments Tax saving on Depreciation Total cost 0 (44,000) - - (44,000) 1 (44,000) 17,600 - (26,400) 2 (44,000) 17,600 - (26,400) 3 (44,000) 17,600 - (26,400) 4 17,600 - 17,600 Cost comparison Year Buying Leasing Differential 0 - (44,000) 44,000 1 13,190 (26,400) 39,590 2 18,316 (26,400) 44,716 3 9,331 (26,400) 35,731 4 2,982 17,600 (14,618) Year Cost Saving Net cash flow PV factor @ 17% PV 0 44,000 44,000 1.000 44,000 1 (48,060) 39,590 (8,470) 0.855 (7,240) 2 (48,060) 44,716 (3,344) 0.731 (2,443) 3 (48,060) 35,731 (12,329) 0.624 (7,698) 4 (48,060) (14,618) (62,678) 0.534 (33,448) 4 22,877 22,877 0.534 12,208 Net present value 5,379 Since there is positive NPV, we can say that there is benefit in buying the machine
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.