please provide the answers for each cell in excel

You are considering purchasing a SUV with a sticker price $48,000, which is the average cost of a new vehicle today. The SUV price is nonnegotiable and requires a 20% down payment. You have cash to cover the down payment and you want to make monthly payments over five years to cover the remainder of the cost. The highest monthly paymeat that you can afford is 5700 . The credit union has agreed to loan you the money at a 6.69% annual interest rate, which is the average rale charged on oew car loans for those with good credit. Part A: Create a loan amortization schedule using the template in the "5 yr loas" tab. - Make sure to use the "PMT" formula command in Excel to solve for the monthly payment and fill in all relevant cells in the template (eg, calculate the total interest paid throughout the life of the loan in cell B14). Do NOT simply solve this on your calculator and input the pryment value in Excel. You will also need to use cell references at all times. This means that you only want to type numerical valoes into cells when they are input variables. For example, you need to type in "5" for the duration of the loan and "12" for the number of payments per year, but you will multiply the cell references (B6 B7) instead of simply typing "60" to calculate the total monthly payments in celf B 10. - Can you afford the payment? Create an IF statement in Excel to answer this question in cell B16 Part B: Instead of taking out a 5-year loan, you take out a 6-year loan at 7.25% interest. Create a loan amortization table using the "6 yr loan" tab. What is your monthly payment and compared to the 5-year loan, how much more interest are you charged over the life of the 6-year loan? - Instcad of making the regular payment for the six-ycar loan, you discover that you can actually afford monthly payments of 5800 . Using your amortization schedule, determine how long will it take you to pay off the losn if you make the 5800 monthly payments or will you be paying less than your monthly required payment? Stoald you take the five-year loan or the six-year loan if you can afford the 5800 monthly payment? Using your amortizabion table, formally answer these questions below: - How long will it take you to pay off the 6-year loan? - How much intereat do you pay with the 6-ycar loas? - Assuming you can make the 5800 monthly payment, shoald you get the 5- or the 6-year loan?f \begin{tabular}{|l|l|l|l|} \hline 1 & \multicolumn{2}{|c|}{ Bank Payment } \\ \hline 2 & Vehicle Price & & \\ \hline 3 & Percentage down payment & \\ \hline 4 & Down payment & HN/A \\ \hline 5 & Loan amount & & \\ 6 & Loan duration in years & \\ \hline 7 & Payment frequency & HN/A \\ \hline 8 & Annual interest rate & HN/A & \\ \hline 9 & Monthly rate & HN/A \\ \hline 10 & Total monthly payments & HN/A \\ \hline 11 & Actual monthly payment & HN/A \\ \hline 12 & Desired monthly payment & HN/A \\ \hline 13 & Total principal paid & HN/A \\ \hline 14 & Total interest paid & Beginning Balance & Payment \\ \hline 15 & Total payments made & Interest \\ \hline 16 & Can you afford the payment? & Principal \\ \hline \hline 18 & Monding Balance \\ \hline \end{tabular} You are considering purchasing a SUV with a sticker price $48,000, which is the average cost of a new vehicle today. The SUV price is nonnegotiable and requires a 20% down payment. You have cash to cover the down payment and you want to make monthly payments over five years to cover the remainder of the cost. The highest monthly paymeat that you can afford is 5700 . The credit union has agreed to loan you the money at a 6.69% annual interest rate, which is the average rale charged on oew car loans for those with good credit. Part A: Create a loan amortization schedule using the template in the "5 yr loas" tab. - Make sure to use the "PMT" formula command in Excel to solve for the monthly payment and fill in all relevant cells in the template (eg, calculate the total interest paid throughout the life of the loan in cell B14). Do NOT simply solve this on your calculator and input the pryment value in Excel. You will also need to use cell references at all times. This means that you only want to type numerical valoes into cells when they are input variables. For example, you need to type in "5" for the duration of the loan and "12" for the number of payments per year, but you will multiply the cell references (B6 B7) instead of simply typing "60" to calculate the total monthly payments in celf B 10. - Can you afford the payment? Create an IF statement in Excel to answer this question in cell B16 Part B: Instead of taking out a 5-year loan, you take out a 6-year loan at 7.25% interest. Create a loan amortization table using the "6 yr loan" tab. What is your monthly payment and compared to the 5-year loan, how much more interest are you charged over the life of the 6-year loan? - Instcad of making the regular payment for the six-ycar loan, you discover that you can actually afford monthly payments of 5800 . Using your amortization schedule, determine how long will it take you to pay off the losn if you make the 5800 monthly payments or will you be paying less than your monthly required payment? Stoald you take the five-year loan or the six-year loan if you can afford the 5800 monthly payment? Using your amortizabion table, formally answer these questions below: - How long will it take you to pay off the 6-year loan? - How much intereat do you pay with the 6-ycar loas? - Assuming you can make the 5800 monthly payment, shoald you get the 5- or the 6-year loan?f \begin{tabular}{|l|l|l|l|} \hline 1 & \multicolumn{2}{|c|}{ Bank Payment } \\ \hline 2 & Vehicle Price & & \\ \hline 3 & Percentage down payment & \\ \hline 4 & Down payment & HN/A \\ \hline 5 & Loan amount & & \\ 6 & Loan duration in years & \\ \hline 7 & Payment frequency & HN/A \\ \hline 8 & Annual interest rate & HN/A & \\ \hline 9 & Monthly rate & HN/A \\ \hline 10 & Total monthly payments & HN/A \\ \hline 11 & Actual monthly payment & HN/A \\ \hline 12 & Desired monthly payment & HN/A \\ \hline 13 & Total principal paid & HN/A \\ \hline 14 & Total interest paid & Beginning Balance & Payment \\ \hline 15 & Total payments made & Interest \\ \hline 16 & Can you afford the payment? & Principal \\ \hline \hline 18 & Monding Balance \\ \hline \end{tabular}