Part 1. Create a loan amortization table. You are considering purchasing a car with a sticker price of $40,000 (nonnegotiable with no down payment required). You wish to make monthly payments for five years and the most you can afford to pay is $900 a month. The credit union has agreed to loan you the money at a 6% annual interest rate. Answer the following two questions a) What is your monthly payment and can you afford to purchase the car? b) Instead of making the regular payment that you solved for in part a, if you make monthly payments of $900 how soon will you be able to pay off the car or will you be paying less than your monthly required payment? Make sure to use the =pmt" formula command in Excel to solve this problem. Also, formally provide answers to part a and b. Do NOT simply solve this on your calculator and input the payment value in Excel. In order to solve parts a and b you are required to create two loan amortization spreadsheets. o One which shows a loan amortization table for the amount the bank wants you to pay (i.e., your =pmt" amount). You place this information in the first tab of your Excel file (name the tab Bank PMT'). o One which shows a loan amortization table for the actual $900 you can afford to pay (i.e., your maximum payment). You can place this information in the second tab of your Excel file (name the tab $900 PMT). o If your monthly payment of $900 exceeds your required payment then you will end up paying off the loan early. When you create the "$900 PMT table make sure to create an IF statement to determine what the last payment needs to be so that you do not have a negative balance. Put this IF statement in the entire range of cells in the "monthly payment" column and make sure that it works so that payments after this one will be zero and payments prior to this one will be $900. Use cell references in your IF statement (i.e., do not use trial and error to put a hard-coded number into the IF statement). Failure to use the IF statement will result in a significant loss of points. Part 1. Create a loan amortization table. You are considering purchasing a car with a sticker price of $40,000 (nonnegotiable with no down payment required). You wish to make monthly payments for five years and the most you can afford to pay is $900 a month. The credit union has agreed to loan you the money at a 6% annual interest rate. Answer the following two questions a) What is your monthly payment and can you afford to purchase the car? b) Instead of making the regular payment that you solved for in part a, if you make monthly payments of $900 how soon will you be able to pay off the car or will you be paying less than your monthly required payment? Make sure to use the =pmt" formula command in Excel to solve this problem. Also, formally provide answers to part a and b. Do NOT simply solve this on your calculator and input the payment value in Excel. In order to solve parts a and b you are required to create two loan amortization spreadsheets. o One which shows a loan amortization table for the amount the bank wants you to pay (i.e., your =pmt" amount). You place this information in the first tab of your Excel file (name the tab Bank PMT'). o One which shows a loan amortization table for the actual $900 you can afford to pay (i.e., your maximum payment). You can place this information in the second tab of your Excel file (name the tab $900 PMT). o If your monthly payment of $900 exceeds your required payment then you will end up paying off the loan early. When you create the "$900 PMT table make sure to create an IF statement to determine what the last payment needs to be so that you do not have a negative balance. Put this IF statement in the entire range of cells in the "monthly payment" column and make sure that it works so that payments after this one will be zero and payments prior to this one will be $900. Use cell references in your IF statement (i.e., do not use trial and error to put a hard-coded number into the IF statement). Failure to use the IF statement will result in a significant loss of points