Part 1: Create a loan amortization table ( 50 points) - 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 payment that you can afford is $700. The credit union has agreed to loan you the money at a 6.69% annual interest rate, which is the average rate charged on new car for those with good credit. - Part A: Create a loan amortization schedule using the template in the "5 yr loan" 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 (e.g., calculate the total interest throughout the life of the loan in cell B14). Do NOT simply solve this on your calculator and input the payment value in Excel. You will also need to use cell at all times. This means that you only want to type numerical values into cells when they are input variables. For example, you need to type in "5" for the duration 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 payments in cell B10. - 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? - Instead of making the regular payment for the six-year loan, you discover that you can actually afford monthly payments of $800. Using your amortization schedule, determine how long will it take you to pay off the loan if you make the $800 monthly payments or will you be paying less than your monthly required payment? you take the five-year loan or the six-year loan if you can afford the $800 monthly payment? Using your amortization table, formally answer these questions - How long will it take you to pay off the 6-year loan? - How much interest do you pay with the 6-year loan? - Assuming you can make the $800 monthly payment, should you get the 5- or the 6-year loan? Bank Payment Vehicle Price Percentage down payment Down payment Loan amount \#N/A Loan duration in years Payment frequency Annual interest rate Monthly rate Total monthly payments \#N/A Actual monthly payment \#N/A Desired monthly payment Total principal paid \#N/A Total interest paid \#N/A Total payments made \#N/A Can you afford the payment? \#N/A \begin{tabular}{|ccccc} \hline Month Number & Beginning Balance & Payment & Interest & Principal \\ \hline \end{tabular} Bank Payment Vehicle Price Percentage down payment Down payment Loan amount \#N/A Loan duration in years Payment frequency Annual interest rate Monthly rate Total monthly payments \#N/A Actual monthly payment (6-yr loan) \#N/A Actual monthly payment (5-yr loan) \#N/A Desired monthly payment