98|EXCEL – Mortgage Modeling

Challenges
, , ,
1

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

2

Here is my solution. I am not completely sure about the payoff portion, but I think I was able to get it.
Challenge98_Data - Daniel Case.xlsx (95.4 KB)

3

This was a great challenge to explore more about the XLOOKUP functionality! Overall, it seemed as though the key was knowing where to find the data and then referencing it correctly. At first, finding the home value was tedious through a manual process, though I found that using appropriate summing and formulas also made short work of that. Thank you once again for this! Here is my solution file:

Challenge98–Tyler Booth Case.xlsx (103.4 KB)

4

Here is my solution. Interesting challenge to better understand excel modeling.

~$Challenge98_Data_Jaxson Thomas.xlsx (165 Bytes)

5

This was fun to brush up my VLookup formula. It took me around an hour, but it was satisfying to see myself automate way more than I initially started to once I realized it wasn’t a race to answer the questions. Great challenge!
Challenge98_Data.xlsx (103.0 KB)

6

I enjoyed this challenge and the chance to practice using excel.
Challenge98_Data.xlsx (102.8 KB)

7

Lab Study.xlsx (102.9 KB)
This was interesting to show how useful Excel can be. With a little bit of work up front, it can save lots of time later.

8

Challenge98_Data_Nielson_1211.xlsx (102.7 KB)
I enjoyed this challenge and appreciated the clear explanation in the solution video

9

Time to complete 30 Minutes
Difficulty: Medium
Challenge98_Data.xlsx (102.9 KB)

10

Time: 30 mins
Difficulty: Intermediate
Challenge98_Data.xlsx (103.7 KB)