Part 2

Download the file named Excel Spreadsheet namedHA3 StartFile2.xlsx.This is your "start file" for Project 1 Part 2. Save the file as HA3_LastNameFirstName_2 and complete "Project Requirements" detailed below. DO NOT CHANGE THE FORMAT or MOVE ROWS OR COLUMNS. All numbers must be properly formatted (i.e. currency, percentage etc.).

Once you have completed the steps below, save the Excel file.On mycourses.unh.edu go toProject 1 Part 2 Submissionto to answer the multiple choice questions associated with the project and to submit your worksheet.

Project Requirements:

Sports Plus is a sporting goods retail store. They want to use Excel to help them decide the best way to fund a major building expansion.They have found two banks that will lend them the money for the expansion.The terms from each bank are listed in the start file.

1.Calculate the monthly payment amount and the total amount of interest paid for each option in the green cells.Be sure the amounts in the calculated cells will change if the input values change. (Notes: For the terms offered by Bank 1, you must consider that the 0% Down Payment is an input and may change, so it should be included in the subsequent calculations of the monthly payment and total interest paid.Also be sure that the Bank 1 Annual Interest Rate is 3.5% and the Bank 2 Annual Interest Rate is 4.0%)

2.For the terms offered by Bank 1 complete the one-variable data table to show the impact of changes in the Down Payment on the Monthly Payment.

3.For the terms offered by Bank 2 complete the two-variable data table to show the sensitivity of the total interest paid amount to changes in interest rates and changes in loan terms.Format the results of the two-variable data table to currency and round to the nearest dollar.

4.Assume Sports Plus can negotiate the interest rate from Bank 2. What interest rate should they request to make the total interest paid in the loan from Bank 2 equal to the total interest paid in the loan from Bank 1?

Sports Plus Loan Option Analysis Bank 1 Loan Amount $250,000 Bank 1 Down Payment 0% Sensitivity of the Monthly Annual Interest Rate 3.5% Payment to changes in the Term of Loan (in years) Down Payment Number of payments per year 12 0% Monthly Payment 5% Total Interest Paid 10% Total Payment 15% Bank 2 Loan Amount $ 200,000 Down Payment 5% Annual Interest Rate 4.0% Term of Loan (in years) 10 Number of payments per year 12 Monthly Payment Total Interest Paid Total Payment Bank 2 Sensitivity of the Total Interest Paid to Changes in Interest Rate and Loan Term Term of Loan (in years) 4 5 6 8 9 10 3.00% 3.25% 3.50% Interest Rate 3.75% 4.00% 4.25% 1.50% 4.75% 5.00% 5.25% 5.50%