*BYU Student Author*: @TylerBooth

*Reviewers*: @Parker_Sherwood, @Jonathan_Weston, @Donovon

*Estimated Time to Solve:* 25 Minutes

We provide the solution to this challenge using:

- Excel

Need a program? Click here.

**Overview**

You are considering purchasing a home and would like to understand how different down payments, interest rates, and loan periods can impact the cost of the home in the short term and long term. For your analysis, you have downloaded data from the St. Louis Federal Reserve Bank regarding average mortgage rates for 30-year and 15-year loans. Now you will need to build a model that allows you to enter values as inputs and have dynamic formulas generate outputs to answer your questions.

**Instructions**

Using the Excel file provided, complete the “Model” tab to answer the questions provided on the sheet. Make note of the following items:

- Green cells should contain manual inputs and will not contain formulas. The data for these manual inputs is provided at the beginning of the questions to the right of the amortization table.
- Blue cells should contain dynamic formulas. Use as many cell references as possible.
- Do not change gray cells.
- Enter your answers in the red cells.
- Be careful that percentages are reflected property (6.49 is not the same as 6.49%).
- Be careful that positive or negative dollar amounts flow through the calculations properly.

**Data Files**

## Suggestions and Hints

- There are many possible ways to build this model. If you choose to use an XLOOKUP formula, the match mode should equal “Exact match or next smaller item.”

## Amortization Table Hint: Payment

- You can calculate the mortgage payment using the =PMT() formula. In the amortization table, the payment column should always be the same value.

## Amortization Table Hint: Interest

- The interest portion of the mortgage payment is calculated as the monthly interest rate multiplied by the prior loan balance.

## Amortization Table Hint: Principal

- This is the portion of the mortgage payment applied against the remaining loan balance. This is calculated as the mortgage payment minus the amount paid as interest.

## Amortization Table Hint: Balance

- This is the remaining loan balance after the current month mortgage payment. It is calculated as the prior month loan balance minus the principal payment and any extra payments.

**Solution**

Challenge98_Solution.xlsx

*Solution Video:* Challenge 98|EXCEL – Mortgage Modeling