help with 8 & 9 please !!! thanks
need the formula for b20 & b21, should equal 23359.13 & 23445.02
E D Initial Investment $ (42,145) $ $ B 40,000 18,000 120 1 2 3 4 40,000 $ 280 278 276 274 $ 825 3.30% 1,320 75.0% $ A 3 Current price (cost) Salvage value (calvage) 5 Salvage months (life) 6 7 Bug Scenario 8% Service contract 9 Sales tax rate 10 Tax on sale 11 Resle percent 12 13 Lease Scenario 14 Security deposit 1 Monthly payment 16 17 Buy or Lease 18 Annual discount rote 13 Monthly discount rate 20 Buy Not present value 21 Lease: Net preocht loc 22 Recommendation 5 6 7 8 9 10 272 270 268 267 265 263 $ 4,000.00 700 $ 11 12 13 14 7.00% 15 G (4,000 (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (900) (700) (700) (100) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (100) (T003 (700) (700) (700) (100) (700) (700) 700) 4.000 LEASE 3 24 25 26 27 28 23 30 31 02 261 259 257 256 254 252 250 248 247 245 243 242 240 238 237 235 233 232 230 228 227 225 224 222 221 213 18 19 20 21 22 23 24 25 26 27 28 23 30 31 32 33 34 35 36 37 40 End of Contract $ 16-41 Loan Details Buy or Lease Depreciation Profit & LOSS 8. Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1-36 and the ending value (range F4F40) as the cash flows for owning and using the shuttle bus. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell G3) as the initial investment for leasing and the monthly payments in the range G4:39. In cell 821, enter a formula that adds the security deposit amount (cell 3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1-36 and the ending value (range G4:640) as the cash flows for leasing the shuttle bus. Airport Shuttles Buy or Lease Scenarios CANTON Buy Scenario (42,145) 5 Months Initial Investment 5 Asset Value 10,000 5 280 $ 5 Shuttle Bus Current price cost) Salvage value salvage) 5 Salvage months 40.000 18.000 120 1 2 3 4 5 7 Buy Scenario 8 Service cont Selestar 10 Tax percent 12 13 Lesse Score 325 3.30% 1.320 75.0% Lease Scenario (4,000) (700) (700) (700) (700) (700) (7001 (700) (700) 1700) (700) (700) 1700) (700) 1700 (700) 1700 (700) 278 276 274 272 270 268 267 265 263 261 259 25 256 5 7 8 9 10 11 5 4,000.00 5 700 13 15 17 Buy or Lease 7. 254 252 250 17 LOL By Lee 401A 248 LEASE 21 Lease: Net present value 22 Recommendation 23 24 18 19 20 21 247 245 243 26 27 29 29 32 33 35 37 End of Contract 5 164 8. Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1-36 and the ending value (range F4:F40) as the cash flows for owning and using the shuttle bus. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell GB) as the initial investment for leasing and the monthly payments in the range G4:639. In cell B21, enter a formula that adds the security deposit amount (cell G3) to the resu of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1-36 and the ending value (range G4:G40) as the cash flows for leasing the shuttle bus. with declining balance 9. Co Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1-36 and the ending value (range F4:F40) as the cash flows for owning and using the shuttle bus. 9. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell G3) as the initial investment for leasing and the monthly payments in the range G4:G39. In cell B21, enter a formula that adds the security deposit amount (cell G3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1-36 and the ending value (range G4:G40) as the cash flows for leasing the shuttle bus. E D Initial Investment $ (42,145) $ $ B 40,000 18,000 120 1 2 3 4 40,000 $ 280 278 276 274 $ 825 3.30% 1,320 75.0% $ A 3 Current price (cost) Salvage value (calvage) 5 Salvage months (life) 6 7 Bug Scenario 8% Service contract 9 Sales tax rate 10 Tax on sale 11 Resle percent 12 13 Lease Scenario 14 Security deposit 1 Monthly payment 16 17 Buy or Lease 18 Annual discount rote 13 Monthly discount rate 20 Buy Not present value 21 Lease: Net preocht loc 22 Recommendation 5 6 7 8 9 10 272 270 268 267 265 263 $ 4,000.00 700 $ 11 12 13 14 7.00% 15 G (4,000 (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (900) (700) (700) (100) (700) (700) (700) (700) (700) (700) (700) (700) (700) (700) (100) (T003 (700) (700) (700) (100) (700) (700) 700) 4.000 LEASE 3 24 25 26 27 28 23 30 31 02 261 259 257 256 254 252 250 248 247 245 243 242 240 238 237 235 233 232 230 228 227 225 224 222 221 213 18 19 20 21 22 23 24 25 26 27 28 23 30 31 32 33 34 35 36 37 40 End of Contract $ 16-41 Loan Details Buy or Lease Depreciation Profit & LOSS 8. Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1-36 and the ending value (range F4F40) as the cash flows for owning and using the shuttle bus. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell G3) as the initial investment for leasing and the monthly payments in the range G4:39. In cell 821, enter a formula that adds the security deposit amount (cell 3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1-36 and the ending value (range G4:640) as the cash flows for leasing the shuttle bus. Airport Shuttles Buy or Lease Scenarios CANTON Buy Scenario (42,145) 5 Months Initial Investment 5 Asset Value 10,000 5 280 $ 5 Shuttle Bus Current price cost) Salvage value salvage) 5 Salvage months 40.000 18.000 120 1 2 3 4 5 7 Buy Scenario 8 Service cont Selestar 10 Tax percent 12 13 Lesse Score 325 3.30% 1.320 75.0% Lease Scenario (4,000) (700) (700) (700) (700) (700) (7001 (700) (700) 1700) (700) (700) 1700) (700) 1700 (700) 1700 (700) 278 276 274 272 270 268 267 265 263 261 259 25 256 5 7 8 9 10 11 5 4,000.00 5 700 13 15 17 Buy or Lease 7. 254 252 250 17 LOL By Lee 401A 248 LEASE 21 Lease: Net present value 22 Recommendation 23 24 18 19 20 21 247 245 243 26 27 29 29 32 33 35 37 End of Contract 5 164 8. Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1-36 and the ending value (range F4:F40) as the cash flows for owning and using the shuttle bus. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell GB) as the initial investment for leasing and the monthly payments in the range G4:639. In cell B21, enter a formula that adds the security deposit amount (cell G3) to the resu of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1-36 and the ending value (range G4:G40) as the cash flows for leasing the shuttle bus. with declining balance 9. Co Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1-36 and the ending value (range F4:F40) as the cash flows for owning and using the shuttle bus. 9. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell G3) as the initial investment for leasing and the monthly payments in the range G4:G39. In cell B21, enter a formula that adds the security deposit amount (cell G3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1-36 and the ending value (range G4:G40) as the cash flows for leasing the shuttle bus
