Link data file: https://drive.google.com/file/d/1O1jOVXlzeBDvSsu0-UbwGDUoFA3jZ0aR/view?usp=sharing

Consumer Financial

Diane Haas, a financial officer at Consumer Financial located in Gulfport, Mississippi, is preparing to analyze loans with her customer to discuss the impact of different interest rates when purchasing a home. She wants to use one-variable and two-variable data tables that explore how modifying the interest rate and length of the loan affects the cost of the loan. She also wants to create scenarios based on the current interest rates for a 15-year and a 30-year fixed loan. She has asked you to help develop the workbook.

Complete the following:

1. Open theLoan.xlsxdata file. Rename the filexx_Loan Analysis.xlsx(replace "xx" with your initials).

2. In the Documentation worksheet, enter your name and the current date.

3. In cells B6 and B8, calculate the interest rate per month (period rate) and calculate the total number of payments over those 30 years.

4. In cell B10, calculate the monthly payment. Display a positive payment.

5. In cell B11, calculate the total amount that will be paid on the loan. (Hint:Monthly payments multiplied by the NPER.)

6. In cell B12, calculate the cost of the loan by subtracting the Total Payments in cell B11 from the Loan Amount in cell B3. (Hint:This number will be negative and will represent what you will have to pay to the bank for borrowing $350,000.)

7. In the range D3:G25, complete the following steps to create a one-variable data table that shows the financial values for different annual interest rates:

a. In the range of D4:G4, enter references to the loan's annual interest rate, monthly payment, total payments, and cost of the loan.

b. Based on the given Interest rates in the Loan Table area, build a one-variable data table in the range of E5:G16 to display what will happen with the Monthly and Total Payments along with the Cost of the Loan if Interest Rates drop.

8. In the range I4:M16, complete the following steps to create a two-variable data table that shows the total cost of the loan for different combinations of interest rates and years until repayment:

a. In cell I4, enter a reference to cell B12 containing the cost of the loan. Apply a custom format to cell I4 to display the wordInterest Rateinstead of the value in cell I4.

b. In the range J5:M16, create a two-variable data table to display the total loan cost in the table.

9. Create a Scatter with Straight Lines chart of the data in the two-variable data table in the range I5:M16. Use the Select Data command to name the four data series based on the Year values in the range J4:M4.

10. Move and resize the chart to cover the range A14:B25. Format the chart to make the content easily visible and understandable.

11. Diane has a client considering purchasing a new home. The more time given to repay a loan, the more the loan costs, but shorter loans usually have lower interest rates. Use the Scenario Manager to create the scenarios outlined below based on the current interest rates for a 15-year and a 30-year mortgage.

a. Generate a scenario summary report that shows the values of the monthly payment, total payments, and cost of the loan under each scenario.

b. In cell B17 of the Scenario Summary, looking at the Cost of the Loan, calculate how much the consumer will save if choosing a 15-year fixed loan vs the 30-year fixed loan.

Scenario

Annual Interest Rate

Years

Loan Amount

30 Year Fixed

3.25 %

30

$400,000

15 Year Fixed

2.8 %

15

$400,000

12. From the Loan Analysis sheet create a Scenario PivotTable report showing only the cost of the loan under the two scenarios provided. Format the values using the Currency format with negative values displayed in red and enclosed in parentheses. Remove any filters from the PivotTable.

13. Add a PivotChart to the report displaying the cost of the loan as a clustered column chart. Format the chart appropriately to make the information clear and legible. [Hint:The chart will be inverted as it's showing negative numbers.]

14. Save and submit the workbook as directed by your instructor.

11. Diane has a client considering purchasing a new home. The more time given to repay a loan, the more the loan costs, but shorter loans usually have lower interest rates. Use the Scenario Manager to create the scenarios outlined below based on the current interest rates for a 15-year and a 30-year mortgage. a. Generate a scenario summary report that shows the values of the monthly payment, total payments, and cost of the loan under each scenario. b. In cell B17 of the Scenario Summary, looking at the Cost of the Loan, calculate how much the consumer will save if choosing a 15-year fixed loan vs the 30-year fixed loan. Scenario Annual Interest Rate Years Loan Amount 30 Year Fixed 3.25% 30 $400,000 15 Year Fixed 2.8% 15 $400,000