The goal for this workshop is to construct an amortization schedule for a mortgage loan similar to the one in Exhibit 5 of Chapter 3 of the CFA textbook. Consider a 30-year (360-month), $100,000 mortgage with an 8.125% APR mortgage rate. Create a set of input cells for maturity, face value, and mortgage rate using a different color for each. Assume that you use this loan to finance 80% of the purchase of a house with a $125,000 price tag, and that you pay the remaining 20% in cash as a down payment.

1. What is the monthly mortgage payment in dollars using the annuity formula? Which Excel function do you use to get this answer?

2. Generate a table similar to Exhibit 5 in Chapter 3. Create 6 columns for Month, Beginning Balance, Mortgage Payment, Interest Payment, Principal Payment, and Ending Balance. Use the following formulas to calculate interest and principal payments for each month (rows): Interest Payment = Monthly Mortgage Rate Beginning Balance Principal Payment = Mortgage Payment Interest Payment Ending Balance = Beginning Balance Principal Payment Beginning Balance = Ending Balance in Previous Month Calculate the values for the first two rows of your table and then copy the formula for the entire row by highlighting the second row and then dragging down the tiny square located at the bottom right of the highlighted area or simply by double clicking on it after you generate the entire Month column.

3. Verify your calculation by comparing the first two and last two rows of your table versus Exhibit 5. If your calculation is right, the ending balance on the last row of the table must be equal to zero.

4. Graph Mortgage Payment, Interest and Principal against Month. Explain the pattern.

5. Calculate the total interest and total principal paid over the life of the loan by summing up the Interest and Principal columns.

6. Assume that you decide to pay down the mortgage faster by adding an additional $100 to your monthly payment. How soon you can pay down the entire balance of $100,000 in this case? How much is total interest you would pay under this scenario? By how much would you need to increase your monthly payment to cut the repayment period in half?

7. Suppose you receive a call from a second loan officer offering you $3,000 cash back upfront in return for raising your mortgage rate by 0.5%. Would you take this offer?

8. Suppose interest rates are on a downward trend. The current mortgage rate is down by 2.5% compared to when you were contemplating buying a house. Assuming that you can keep paying the same monthly mortgage payments and the same down payment as before, what would be the price of the house that you could now afford?

9. What is the set of risks you face vs. the ones your lender faces in this transaction?