Chevron Phillips has put into place new laboratory equipment for the production of chemicals; the initial cost is $1,800,000 installed. Chevron Phillips borrows 45% of all capital needed, and the borrowing rate is 12.5% over 4 years. The throughput rate for in-process test samples has increased the capacity of the lab, with a net savings of $X per year. Depreciation follows MACRS-GDS, MARR is 11%, and the planning horizon is 6 years with a salvage value of $250,000 at that time. Use Goal Seek or Solver in Excel to determine the value of X such that MARR is exactly achieved, no more and no less, if: The loan is paid back using Method 1 (interest only at the end of each year of the loan, plus principal at the end of the last year). The loan is paid back using Method 2 (equal annual principal payments plus interest on the unpaid loan balance). The loan is paid back using Method 3 (equal annual principal plus interest payments during each year of the loan). The loan is paid back using Method 4 (principal plus interest is paid at the end of the loan period). An important step in this problem is determining the principal and interest payments on the loan for the laboratory equipment. Method 1 has interest only at the end of each year of the loan plus principal at the end of the last year. Method 2 has equal principal payments plus interest on the unpaid loan balance. Method 3 has equal annual principal plus interest payments during each year of the loan. Method 4 has principal plus interest is paid at the end of the loan period. They financed 45% of the $1,800,000 at 12.5% per year over 4 years and keep it for 6 years.

For each method of loan payback, a table similar to the one below can be set-up in order to calculate the unknown value X. The table below is set-up for Method 1. The tax rate is 40%.

Notice that the salvage value for the equipment has been placed in year 6. This is where the problem becomes complex. You need to determine the ATCF values that make the PW = FW = AW=0 using a MARR of 11%. You will need to go ahead and set-up the table with all calculations in it. For example, the formula that calculates the TI. All calculations that are not filled in are based on the BT&LCF value. You can use goal seek in Excel to determine the value. GOAL SEEK is the data tab>data tools group>what-if analysis>goal seek. Set Cell box is the formula that you want to solve (such as =NPV(i%, A₀:A₈)+ATCF₀). To value is the result you want, and by changing cell box, it is a reference for the cell that contains the value you want to adjust. See Excel help: Goal Seek for further information.

Determine X for method 1,2,3,4.

EOY IPMT BT&LCF PMT DOW TI T ATCF 10 1 X $0.00 $101,250.00 $360,000.00 $576,000.00 2 $0.00 $101,250.00 3 X $0.00 $101,250.00 $345,600.00 X 1a 1b $207,360.00 4 $0.00 $0.00 $207,360.00 5 X $0.00 $0.00 $103,680.00 6 X+$250,000 EOY IPMT BT&LCF PMT DOW TI T ATCF 10 1 X $0.00 $101,250.00 $360,000.00 $576,000.00 2 $0.00 $101,250.00 3 X $0.00 $101,250.00 $345,600.00 X 1a 1b $207,360.00 4 $0.00 $0.00 $207,360.00 5 X $0.00 $0.00 $103,680.00 6 X+$250,000