In cell F4, enter a Lookup function to display the interest rate of the loan. The function should reference the borrower?s credit rating and the table array in range A15:C18. Include the range_lookup argument to ensure an exact match.

In cell F5, insert a lookup function that uses the credit rating to determine the minimum down payment based on the table array A15:C18. Include the range_lookup argument to ensure an exact match. Multiply the function result by the negotiated cost of the vehicle.

Transcribed Image Text:

F4 1 2 3 6 7 Credit Rating Inputs 4 Negotiated Cost of Vehicle 5 Additional Down Payment A 22 23 24 25 1 8 9 Constants 10 Sales Tax Rate 11 Term of Loan in Years. 26 27 28 29 30 31 12 # of Payments Per Year 13 32 33 34 35 36 37 x ✓ fx Date of First Payment 14 Credit Down Payment 15 Excellent 5% 16 Good 10% 17 Fair 15% 18 Poor 20% 19 20 21 B Ready Payment с Auto Loan Calculator $ 35,000.00 $ 5,000.00 10/16/2017 Excellent APR 5% 5 12 3.25% 3.50% 4.25% 5.25% D E Intermediate Calculations APR Based on Credit Rating Min Down Payment Sales Tax Outputs Total Down Payment. Amount of the Loan. Monthly Payment (P&I) Monthly Sales Tax Total Monthly Payment F G H I 4 J K L M N O BU P + Q RA + 100% F4 1 2 3 6 7 Credit Rating Inputs 4 Negotiated Cost of Vehicle 5 Additional Down Payment A 22 23 24 25 1 8 9 Constants 10 Sales Tax Rate 11 Term of Loan in Years. 26 27 28 29 30 31 12 # of Payments Per Year 13 32 33 34 35 36 37 x ✓ fx Date of First Payment 14 Credit Down Payment 15 Excellent 5% 16 Good 10% 17 Fair 15% 18 Poor 20% 19 20 21 B Ready Payment с Auto Loan Calculator $ 35,000.00 $ 5,000.00 10/16/2017 Excellent APR 5% 5 12 3.25% 3.50% 4.25% 5.25% D E Intermediate Calculations APR Based on Credit Rating Min Down Payment Sales Tax Outputs Total Down Payment. Amount of the Loan. Monthly Payment (P&I) Monthly Sales Tax Total Monthly Payment F G H I 4 J K L M N O BU P + Q RA + 100%

Answer rating: 100% (QA)

All t... View the full answer