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.