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 25%. EOY BT&LCF PPMT IPMT DOW TI T ATCF 0 1 $95,625.00 $0.00 $0.00 2 X 3 X X $95,625.00 $95,625.00 1b $340,000.00 $544,000.00 $326,400.00 $195,840.00 $195,840.00 $97,920.00 $0.00 1a $0.00 $0.00 4 5 X $0.00 6 X+$250,000 $0.00 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%, A2:A3)+ATCFO). 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. a. Method 1: X= $ b. Method 2: X = $ c. Method 3: X= $ d. Method 4: X= $ Round your answer to 2 decimal places. Do not round your intermediate computations. Tolerance is + 10. 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 25%. EOY BT&LCF PPMT IPMT DOW TI T ATCF 0 1 $95,625.00 $0.00 $0.00 2 X 3 X X $95,625.00 $95,625.00 1b $340,000.00 $544,000.00 $326,400.00 $195,840.00 $195,840.00 $97,920.00 $0.00 1a $0.00 $0.00 4 5 X $0.00 6 X+$250,000 $0.00 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%, A2:A3)+ATCFO). 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. a. Method 1: X= $ b. Method 2: X = $ c. Method 3: X= $ d. Method 4: X= $ Round your answer to 2 decimal places. Do not round your intermediate computations. Tolerance is + 10
