Show using Excel

2. Mortgage Do the following question on a worksheet titled "Mortgage". Jim and Pam are considering buying a house with a mortgage loan of $400,000 and a term of 20 years. They will pay the mortgage back using constant annual payments. The mortgage interest is currently 1.75% with semi-annual compounding. (a) Make up the annual mortgage repayment scheme. The scheme should be well designed with appropriate formatting. (b) Suppose that Jim and Pam make semi-monthly payments instead of annual payments. How much do they pay at each period? What is the correspond- ing yearly payment? Compare it with the yearly payment from part a and comment. (c) What is the future value of the semi-monthly payments? Is it more or less than $400,000? Why? (d) What mortgage loan can be obtained with constant semi-monthly payments of $950 over 20 years? Why is it more or less than $400,000? (e) What mortgage loan can be obtained with constant annual payments of $22,800 over 20 years? Why is it more or less than $400,000? Do your answers for parts d and e differ (despite paying the same annual payment)? Explain why they differ or why they do not differ. (f) For what interest rate a loan can be repaid by semi-monthly payments of $1,000 over 20 years? Is it more or less than the original interest rate (annual 1.75% with semi-annual compounding)? Comment on your findings. (g) Now, assume that Jim and Pam will make annual payments but the interest rate will vary over time. After the first ten years, the interest rate will change to an annual 2.5% with semi-annual compounding. After the following five years, the interest rate will change to annual an 3% with semi-annual compounding. Make up the corresponding mortgage repayment scheme. 2. Mortgage Do the following question on a worksheet titled "Mortgage". Jim and Pam are considering buying a house with a mortgage loan of $400,000 and a term of 20 years. They will pay the mortgage back using constant annual payments. The mortgage interest is currently 1.75% with semi-annual compounding. (a) Make up the annual mortgage repayment scheme. The scheme should be well designed with appropriate formatting. (b) Suppose that Jim and Pam make semi-monthly payments instead of annual payments. How much do they pay at each period? What is the correspond- ing yearly payment? Compare it with the yearly payment from part a and comment. (c) What is the future value of the semi-monthly payments? Is it more or less than $400,000? Why? (d) What mortgage loan can be obtained with constant semi-monthly payments of $950 over 20 years? Why is it more or less than $400,000? (e) What mortgage loan can be obtained with constant annual payments of $22,800 over 20 years? Why is it more or less than $400,000? Do your answers for parts d and e differ (despite paying the same annual payment)? Explain why they differ or why they do not differ. (f) For what interest rate a loan can be repaid by semi-monthly payments of $1,000 over 20 years? Is it more or less than the original interest rate (annual 1.75% with semi-annual compounding)? Comment on your findings. (g) Now, assume that Jim and Pam will make annual payments but the interest rate will vary over time. After the first ten years, the interest rate will change to an annual 2.5% with semi-annual compounding. After the following five years, the interest rate will change to annual an 3% with semi-annual compounding. Make up the corresponding mortgage repayment scheme