payment . Finish the calculations by using the appropriate functions to complete the Loan Statistics summary area of the worksheet . A . Use a VLOOKUP function to determine the interest rates in column D . There is a tutorial video on creating and using VLookup tables at the end of the Lesson page . B . Calculate the down payment by multiplying the results of a VLOOKUP function by the selling price . Enter the formula in column E . C . Calculate the amount to be financed by subtracting the down payment from the selling price . Enter the formula in column F . D . Use a PMT function to determine the monthly payments in column G . You should assume that the payment is being made at the beginning of the month . Remember that the Interest Rate being shown is an annual percentage rate ( APR ), yet the payments are being made monthly . Be sure to make your adjustment to the interest rate for this . In addition , the term of the loan is being shown in years . Be sure to adjust the years to months in your PMT function argument . E . Use appropriate formulas and functions to calculate our loan statistics for the month .
• Number of Loans
• Lowest Amount Financed
• Total Amount Financed
• Highest Amount Financed Step 3 : Format the Worksheet Now that you have finished the calculations , you must format the worksheet in a professional manner suitable for delivering to the Board of Directors . Your final worksheet will look something like the worksheet below . A . Format all money figures as currency with two decimals and all percentages as percents . B . Adjust columns sizes to fit the data . C . Merge and center titles and size appropriately . D . Change font color to dark blue . E . Add a small graphic appropriate for the purpose of the worksheet . F . Separate sections of the worksheet and provide borders as appropriate .