Step 2: Enter =-PV(C1, C2, C3) in Cell B6 (We add the - before the PV formula to make the mortgage balance positive) Step 3: Enter =$C$3 in Cell C6. Step 4: Enter =$C$1*B6 in Cell D6 (Interest=Current Balance* Interest Rate). Step 5: Enter =C6-D6 in Cell E6 (Monthly Payment-Interest Payment=Principal Payment). Step 6: Enter =B6-E6 in Cell F6 (E6 reduces the remaining Mortgage Balance (Principal)).

Do you know why we used absolute reference ($ sign) in step 3 and 4 but not in step 5 and 6? Step 7: Enter =F6 in Cell B7 (Beginning balance = Ending Balance) Step 8 Autofill the remaining cells (B7-F185). Save the template as Mini Project 1-1-YourName. Scenario 1: I was shopping for a mortgage for my condo when I was writing this project, and the loan I was willing to take on was $106,000. Assuming I could afford up to a $1,500 monthly mortgage payment without being a house-poor and the best offer available was 3% annual interest rate over 84 months, should I accept this offer? Step 1: Open Mini Project 1-1-YourName and save it as Mini Project 1-2-YourName Step 2: Enter 84 in Cell C2. Step 3: Enter =0.03/12 in Cell C1. Note that C1 should be the interest rate for one term (monthly interest). Step 4: Go to Data->What-if Analysis->Goal Seek Enter the following information. Hit ok twice. The entire schedule should be filled. As you can see, the offer required a monthly payment of $1,400.61. I should be able to afford that. Note C89 or Term #84 is 0, which indicates the last payment would occur in Term (month) #84. Step 5: Delete A90-F185. Step 6: Journalize the following transactions (anywhere on the Journal Entry tab): 1. At the inception of the mortgage, before the first payment. 2. Mortgage payment made in Term (month) #56. Step 7: Journalize the following transactions for the loan provider (anywhere on the Journal Entry tab):

1. At the inception of the mortgage, before receiving the first payment. 2. Mortgage payment received in the Term (month) #56. Save your work as Mini Project 1-2-YourName. Scenario 2: With everything else being equal, how much would I have to pay each period if I chose to make bi-weekly payments instead of monthly payments? How much interest expenses would I be able to save by paying more frequently? Build a new schedule and saved your work as Mini Project 1-3-YourName. Hint: You will need to add more rows.

Scenario 3: Continuing with Scenario 1, what would be the monthly payment if the loan provider wanted payments to be made at the beginning of each period instead? Build a new schedule

term

rate

payment

term/ mortage/ balance/monthly payment/interest payment/principal payment/ending mortage principal

1-32