Question 2 [8] Most people, when buying a house of their own, have to take out a home loan to cover the cost of buying the house. Create a spreadsheet model that calculates the monthly payment that will be required on a home loan. Include the following input values in the model: Price of the house The annual interest rate on the loan (compounded monthly) Initial deposit (This is the amount that you put down and will be subtracted from the price of the house to determine the loan amount.) Term (number of years to pay off the loan) Use formulas to calculate the following output values: The monthly payment amount (Hint: Use the Excel PMT formula.) The total amount paid (the monthly payments multiplied by the months of the term) . The interest paid (the total amount paid minus the loan amount) Use your model to answer the following questions: a. Give the monthly payment and the total interest paid on a house of R550 000 at 7.5% interest over 20 years if no deposit is put down. (2) b. How does the answer change the monthly payment and the total interest paid) if a deposit of R10 000 is put down? (2) c. Perform a sensitivity analysis to model the effect of a change in interest rate on the total amount paid on the bond. Use a house value of R550 000 over 20 years with a deposit of R10 000. Use interest rate values from 6% to 8% in increments of 0.25%. Use your sensitivity analysis table to answer the following questions. i Give the total amount paid corresponding to an interest rate of 6.75%. ii What range of interest rates will result in a total amount paid that is over R1 million? (2) Question 2 [8] Most people, when buying a house of their own, have to take out a home loan to cover the cost of buying the house. Create a spreadsheet model that calculates the monthly payment that will be required on a home loan. Include the following input values in the model: Price of the house The annual interest rate on the loan (compounded monthly) Initial deposit (This is the amount that you put down and will be subtracted from the price of the house to determine the loan amount.) Term (number of years to pay off the loan) Use formulas to calculate the following output values: The monthly payment amount (Hint: Use the Excel PMT formula.) The total amount paid (the monthly payments multiplied by the months of the term) . The interest paid (the total amount paid minus the loan amount) Use your model to answer the following questions: a. Give the monthly payment and the total interest paid on a house of R550 000 at 7.5% interest over 20 years if no deposit is put down. (2) b. How does the answer change the monthly payment and the total interest paid) if a deposit of R10 000 is put down? (2) c. Perform a sensitivity analysis to model the effect of a change in interest rate on the total amount paid on the bond. Use a house value of R550 000 over 20 years with a deposit of R10 000. Use interest rate values from 6% to 8% in increments of 0.25%. Use your sensitivity analysis table to answer the following questions. i Give the total amount paid corresponding to an interest rate of 6.75%. ii What range of interest rates will result in a total amount paid that is over R1 million? (2)
