Scenario Manager Down Payment $9,000 $2,000 Payment Calculation Payment: Ring Cost: Down Payment: Loan Amount: APR: Years: Pmts per Year: Periodic Rate: #of Payments: 5.50% 5 12 D III. Loan Information You will calculate the payment for any ring by entering in the Cost, APR, and Years of the loan. You will also help the sales associate by creating a two-variable table that will allow them to discuss with a customer how changing the down payment or ring cost can impact the payment. 12. On the Loan worksheet, insert a function in cell B5 that will calculate the total loan amount. 13. Enter 12 in cell B8 as the payments will always be required to be made monthly.
14. Insert a function in cell B10 that will calculate the total number of payments for the loan. 15. Insert a function in cell B9 that will calculate the periodic rate for the loan. 16. Insert a function in cell B2 that will calculate the payment for the loan. 17. Insert a data validation rule in cell B7 with the following settings: • Input Message = Enter Length of Loan in Years • Validation Rule = Whole number between 1 and 6 • Alert Type = Stop • Alert Title = Invalid • Alert Message = Maximum Years is 5 18. Enter the following information into the Payment Calculation Table: • Ring Cost = $9000 Down Payment = $2000 • APR = 5.5% • Years = 5 19. In cells E3:H3, complete a series of substitutions starting at 5000 in increments of 1000 to a max of 8000. 20. In cells D4:D8, complete a series of substitutions starting at 3 in increments of 1 to a max of 7. 21. Complete the two-variable variable table for Loan Amount and Years by adding a reference to the Payment in cell D3.