1. Create a financial model for a loan. For the base model make the following assumptions:

• You plan on purchasing a $250,000 home in 4 years.
• During the next 4 years you plan on putting $3,500 into a savings account once every quarter (beginning immediately). The expected annual interest rate on your savings account is 6% and interest is compounded quarterly. You will use this money as a down payment on your house.
• In order to finance the remaining balance, you plan on taking out a 30-year home loan with an annual interest rate of 5% (compounded monthly) and payments due at the end of every month.

2. In the worksheet, use the Excel functions PMT, IPMT, and PPMT to compute the Payment and Interest and Repayment of Principal column entries, respectively.

3. Create a loan amortization table for the loan. Use IF statements in the amortization table so that changes in the Number of Years and/or Payments per Year input variables will be correctly incorporated into the cells. In other words, if the loan is a two-year, monthly payment loan, then all table entries beyond the 24^th period should be blank.

4. Format all numbers properly (i.e. use dollar signs, percent signs, an appropriate number of decimal places, consistent formatting throughout the document, etc.).

4. Make sure all your variables are linked. When you are finished you should be able to change any input variable in the model and all the output variables will automatically update.

6. Suppose you decided to sell your house after making 5 years of payments and the house has retained its original value. How much home equity have you built up so far (i.e. The amount of money you would get to keep if you sold the house. The rest would go the bank to pay off the remaining loan amount.)? How much home equity will you have built up after 20 years? Explain why the 20-year equity value is higher?
   • Note: you do need to show your work in this section, but the numbers do not have to be linked to the model.

7. Check figures:
8. 1. BASE CASE values:
      • Total interest paid = $173,731.10
      • Periodic loan payment = $1,000.07

1. 2. If you decided to buy a $150,000 house in 3 years and only plan in saving $1,000 a quarter for the next 3 years (beginning immediately).
      • Total interest paid = $127,539.57
      • Periodic loan payment = $734.17

1. 3. If you decide to use this model for a 4-year $20,000 car loan with an annual interest rate of 3%. Payments are made monthly and there is no down payment.
      • Total interest paid = $1,248.95
      • Periodic loan payment = $442.69