A mortgage is a loan from a bank or lender to help finance the purchase of a home. This is paid back with interest through a series of foced periodic payments over several years. One way to measure the amount of the home that the borrower owns at some point in time is by calculating equity in the home. Equity may be calculated as the difference between the value of the asset and the amount still owed on the Ioan. This is often expressed as a percentage to the value of the asset. We will assume the value of the asset is equal to the original mortgage amount). We will explore these ideas in the problems below. Since it is important to not round until the final step of the calculations, the spreadsheet applet above and its built-in formulas will be very helpful answering the questions below. Mr. Lahey needs a loan of $144,780 to purchase a house. He opts for a 29 year mortgage with the Sunnyale Credit Union with a 6.327% interest rate compounded monthly. (a) First determine the "future value" of the loan, from the perspective of Sunnyvale Credit Union. To the president of Sunnyvale Credit Union when an amount of $144,780 is compounded monthly for 29 years, what is the future value of the money? To use the spreadsheet, enter the interest per period in cell 84 . You should be entering a fraction here. NOT a rounded number. Remember to arrow out of the cell instead of hitting enter. Then enter the total number of compounding periods for the duration of the loan in B5. The future value of the loan will be calculated in cell B6. We will round this value to the nearest cent and use the rounded value for future calculations. From the perspective of Sunnyvale Credit Union the Future Value of the loan will be \$ (round to the nearest cent). (b) From the list below, identify the formula used by the spreadsheet to compute the answer to part (a). A. rE=(1+nr)n1 B. F=P(1+nr)nt C. F=(nr)P((1+n)nt1) D. F=P(1+rt) E. F=Pert F. None of these (c) Fill in the blanks below with the values that should be used for each variable in the formula chosen in part (b). If a letter is listed that is not part of the formula, enter "NONE' in the blank. t=P=n=r=m= (d) Now, when preparing the mortgage agreements for the closing. Sunnyale Credit Union needs to determine what Mr. Lahey's payments will be in order to repay the loan. From the perspective of Sunnyvale Credit Union, they can think of the payments as an ordinary annuity? with a goal of reaching the future value found in part (a). To use the spreadsheet follow these steps. (i) Enter the future value of the annuity in cell B10 (using value from part (a), rounded to the nearest cent). (ii) Enter the interest per period in 811 as a fraction not a rounded value. Use the same formula you used in B4; do not use the rounded value displayed in the applet. (iii) Enter the total number of periods (i.e., total number of payments) in B12. Then, the payment will display in B13. Sunnyvale Credit Union will require a monthly payment of \$ (round to the nearest cent) in their mortgage contract with Mr. Lahey. (e) From the list below. identify the formula used by the spreadsheet to compute the answer to part (d). A. F=P(1+nr)nt B. rB=(1+nrn)n1 C. F=(nr)P((1+nr)nt1) D. F=Pert E. F=P(1+rt) F. None of these (f) Fill in the blanis below with the values that should be used for each variable in the formula chosen in part (J). If a letter is listed that is not part of the formula, enter "NONE" in the blank. t=n=r=F= (g) Now, we switch to Mr. Lahey's perspective on the mortgage. He will receive a lump sum of $144,780 from Sunnyvale Credit Union in order to purchase the house. Then he will make pre-determined fixed monthly payments (the same payment every month) for the full 29 year term of the mortgage agreement. These are referred to as amortization payments. The amount of each payment will be the amount found above in part (d). Determine all of the following information about Mr. Lahey's loan. For each step, use the value of the payment rounded to the nearest cent. Total value of the house (amount of onginal loan) is ? Mr. Lahey's monthly amortization payments will each be : The total number of amortization payments over the 29 year term will be The total amount that Mr. Lahey will pay back to Sunnyvale Credit Union over the full 29 years will be: The total amount of interest that Mr. Lahey will pay over the term of the loan will be : (h) Now, for this last part we will assume that 19 years have passed. Mr. Lahey has made all of his monthly payments throughout those 19 years. So, the total amount that he has paid to Sunnyvale Credit Union so far is 5 (to the nearest cent). If Mr. Lahey continues to make monthly payments for the remaining 10 years, then he will pay a total of $ (to the nearest cent) to the bank over those 10 years. However, if Mr. Lahey decides to pay off the remaining loan in one day (at the end of 19 years), then he would not need to pay the remaining 10 years of interest, just the remaining balance due on the house. We can use a different formula to compute this. We want to find the remaining balance owed on the loan when there are 10 years remaining and P is the amount of each payment (as found above in part (g)). So, we compute RemainingBalance=P(nr1(1+nr)nt)=P(120.05371(1+120.06127)1210) Thus, the balance remaining on the loan after 19 years will be s (to the nearest cent). Now, recall that the original value of the house (the original amount of the loan) was ! Thus, after 19 years, Mr, Lahey's equity in the house is 5 (to the nearest cent). In other words. Mr. Lahey's equity in the house is \% (round percent to 3 decimal places) after 19 years