Excel Database Assignment (15 points) This material is covered in the Microsoft Office Excel 2019 e-Book: Chapter 11, pages 673-674 Instructions 1. Open the start file - Excel_Database_Assign_Start.xlsx 2. Use the =pmt() function to calculate the monthly payment amount in cell G3. In the function reference the following: Rate - yearly or annual interest rate. Must be converted to the amount of interest per month. Term - number of years to pay off the loan. Must be converted to the number of months to pay off the loan. Principal - amount of the loan. Make the Principal a negative value so the function will return the monthly payment amount has a positive number. 3. Name the range A2:G25 database 4. Name the range A27:628 criteria 5. In cell D28 type the word Student 6. Use database functions: -daverage(...) Edsum(...), Edmax(...), etc., to calculate the statistics in cells 13 through J7 7. Format all numbers as shown in the completed version on the next page. Use currency format for all numbers represent money. Use percent format with 1 decimal for the Average Interest Rate. Use comma format for the Average Term. Spring 2017 B D F G H E Analysis of Consumer Loans 1 Principal $525,000 $10,500 $475,000 $35,000 $250,000 $35,000 $10,000 Rate 5.0% 6.0% 5.4% 5.0% 7.3% 5.7% Branch Opa Locka Kendall North Miami Kendall Opa Locka North Miami North Miami North Miami Opa Locka North Miami Kendall Kendall Kendall Kendall Kendall Payment $2,818.31 $202.99 $3,855.99 $660.49 $1,713.93 $817.17 $295.24 $145,59 Summary Statistics Average Principal: $32,428,57 Total Principal: $227,000.00 Largest Principal: $56,000.00 Average Interest rate: 6.7% Average Term: 3.29 4.0% $12,000 2 Loan ID 3 L0001 4 L0002 5 L0003 6 L0004 7 L0005 8 L0006 9 L0007 10 L0008 11 L0009 12 L0010 13 L0011 14 L0012 15 L0013 10 16 L0014 17 L0015 18 L0016 19 L0017 20 L0018 21 L0019 22 L0020 23 L0021 24 L0022 25 L0023 26 27 Loan ID 28 29 $789.19 Type Mortgage Car Mortgage Car Mortgage Student Student Car Student Car Student Mortgage Student Mortgage Mortgage Mortgage Car Mortgage Mortgage Student Student Mortgage Car $25,000 $20,000 $56,000 $50,000 Coro $35,000 $150,000 $75,000 $200,000 $15,000 $350,000 $275,000 $25.000 $41.000 $75,000 $15,000 8.0% 8.5% 8.0% 6.0% 7.0% 7.5% 6.0% 5.5% 6.5% 6.5% 6.7% 7.7% 7.0% 8.4% 6.0% 5.0% Term 30 5 15 5 30 4 3 10 3 5 3 15 3 15 30 15 5 30 15 4 3 15 3 Opa Locka Kendall $405.53 $1,703.63 $449.41 $1,088.72 $1,265.79 $425.84 $1,742.21 $293.49 $2,258,47 $2,580.64 $598.66 $1.292.37 $632 89 $449.56 North Miami Kendall Kendall Opa Locka Kendall Kendall Branch Principal Rate Type Student Term Payment
