This problem has caused me so much stress. I have tied myself in knots and now I

Question

This problem has caused me so much stress. I have tied myself in knots and now I am desperate for help. Could someone please assist me with it and show the formulas in Excel so I can see where I'm going wrong?

Here is a link to the spreadsheet.
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0CB0QFjAAahUKEwjV3ZKx9JDJAhVDVyYKHYU2C4w&url=http%3A%2F%2Fwww.swlearning.com%2Ffinance%2Fbrigham%2Fifm8e%2Fbuildamodel%2Fbuildamodel18-06.xls&usg=AFQjCNFSDn73wsN43lqgaCeEC0uEAX2Uwg&sig2=7z-tzC4DZxyASalVZheE1Q&bvm=bv.107467506,d.eWE

The Problem:
As part of its overall plant modernization and cost reduction program, Western
Fabrics’s management has decided to install a new automated weaving loom. In the
capital budgeting analysis of this equipment, the IRR of the project was found to be
20% versus the project’s required return of 12%.
The loom has an invoice price of $250,000, including delivery and installation
charges. The funds needed could be borrowed from the bank through a 4-year
amortized loan at a 10% interest rate, with payments to be made at the end of each
year. In the event the loom is purchased, the manufacturer will contract to maintain
and service it for a fee of $20,000 per year paid at the end of each year. The loom
falls in the MACRS 5-year class, and Western’s marginal federal-plus-state tax rate
is 40%.
Aubey Automation Inc., maker of the loom, has offered to lease the loom to Western
for $70,000 upon delivery and installation (at t = 0) plus four additional annual lease
payments of $70,000 to be made at the end of Years 1 to 4. (Note that there are five lease
payments in total.) The lease agreement includes maintenance and servicing. The loom
has an expected life of 8 years, at which time its expected salvage value is zero; however,
after 4 years its market value is expected to equal its book value of $42,500. Western plans
to build an entirely new plant in 4 years, so it has no interest in either leasing or owning
the proposed loom for more than that period.
a. Should the loom be leased or purchased?
b. The salvage value is clearly the most uncertain cash flow in the analysis. What effect
would a salvage value risk adjustment have on the analysis? (Assume that the
appropriate salvage value pre-tax discount rate is 15%.)
c. Assuming that the after-tax cost of debt should be used to discount all anticipated
cash flows, at what lease payment would the firm be indifferent to either leasing or
buying?

Explanation / Answer

Answer:

a.   Should the loom be leased or purchased? First, we want to lay out all of the input data in the problem. INPUT DATA Invoice Price $250,000 Length of loan 4 Loan Interest rate 10% Maintenance fee $20,000 Tax Rate 40% Lease fee $70,000 Equipment expected life 8 Expected salvage value $0 Market value after 4 years $42,500 Book value after 4 years $42,500 First, we can determine the annual loan payment that must be made on the new equipment. We will do so using the function wizard for PMT. Annual loan payment = $78,868 Year 1 2 3 4 Beginning loan balance $250,000 $196,132 $136,877 $71,698 Interest payment $25,000 $19,613 $13,688 $7,170 Principal payment $53,868 $59,255 $65,180 $71,698 Ending loan balance $196,132 $136,877 $71,697 ($0) Now, we see that the decision being made is whether to purchase the equipment at a net cost of $250,000 (with annual payments of $78,868) or lease the equipment and make annual payments of $70,000. To make this decision, we must analyze the incremental cash flows. Before proceeding with our NPV analysis we must determine the schedule of depreciation charges for this new equipment. MACRS 5-year Depreciation Schedule Year 1 2 3 4 5 6 Depr. Rate 20% 32% 19.20% 11.52% 11.52% 5.76% Depr. Exp. $50,000 $80,000 $48,000 $28,800 $28,800 $14,400 We can now construct our table of incremental cash flows from these two alternatives. Remember, that the appropriate discount rate in this scenario is the after tax cost of borrowing, or: 10%*(1-40%) = 6%. NPV LEASE ANALYSIS OF INCREMENTAL CASH FLOWS Year = 0 1 2 3 4    Cost of ownership Purchase cost ($250,000) Loan proceeds $250,000 After-tax interest payment ($15,000) ($11,768) ($8,213) ($4,302) Principal payment ($53,868) ($59,255) ($65,180) ($71,698) Maintenance cost ($20,000) ($20,000) ($20,000) ($20,000) Tax savings from maintenance cost $8,000 $8,000 $8,000 $8,000 Tax savings from depreciation $20,000 $32,000 $19,200 $11,520 Salvage value $42,500 Net cash flow from ownership $0 ($60,868) ($51,023) ($66,193) ($33,980) PV cost of ownership -185323.87    Cost of leasing Lease payment ($70,000) ($70,000) ($70,000) ($70,000) ($70,000) Tax savings from lease payment $28,000 $28,000 $28,000 $28,000 $28,000 Net cash flow from leasing ($42,000) ($42,000) ($42,000) ($42,000) ($42,000) PV cost of leasing -187534.44    Cost Comparison PV ownership cost @ 6% ($185,324) PV of leasing @ 6% ($187,534) Net Advantage to Leasing ($2,211) Our NPV Analysis has told us that there is a negative advantage to leasing. We interpret that as an indication that the firm should forego the opportunity to lease and buy the new equipment. b.   The salvage value is clearly the most uncertain cash flow in the analysis. Assume that the appropriate salvage value       pre-tax discount rate is 15 percent. What would be the effect of a salvage value risk adjustment on the decision? All cash flows would remain unchanged except that of the salvage value. Our new array of cash flows would resemble the following: Standard discount rate 10% 0.06 Salvage value rate 15% 0.09 Year    = 0 1 2 3 4 4 Net cash flow $0 ($60,868) ($51,023) ($66,193) ($76,480) $42,500 PV of net cash flows $0 ($57,423) ($45,410) ($55,577) ($60,579) $30,108 NPV of ownership ($188,880)    New Cost Comparison PV ownership cost @ 6% ($188,880) PV of leasing @ 6% ($187,534) Net Advantage to Leasing $1,345 Under this new assumption of using a greater discount factor for the salvage value, we find that the firm should lease, and not buy, the equipment. c.   Assuming that the after-tax cost of debt should be used to discount all anticipated cash flows, at what lease payment       would the firm be indifferent to either leasing or buying? We will use the Goal Seek function to determine the lease payment that makes the Net Advantage to Leasing zero. Crossover = 70,502 used what if analysis

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.