Question

Question 2 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 spreadsheet formula that you used to calculate the monthly payment amount. Explain each part of the expression (state what is stored in any reference cells used in the formula). b. Give the monthly payment and the total interest paid on a house of R750 000 at 9% interest over 20 years if no deposit is put down. (1) c. How does the answer change (the monthly payment and the total interest paid) if a deposit of R20 000 on the price of the house is put down? d. Perform a sensitivity analysis to model the effect of a change in interest rate on the monthly payment amount. Use a house value of R750 000 over 20 years with a deposit of R20 000 on the price of the house. Use interest rate values from 6% to 10% in increments of 0.5%. Use your sensitivity analysis table to answer the following questions. i Give the monthly payment amount corresponding to an interest rate of 8.5%. ii At what interest rate will the monthly payment amount be more than R6 000?

Question 2
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 spreadsheet formula that you used to calculate the monthly payment amount. Explain each part of the expression (state what is stored in any reference cells used in the formula).
b. Give the monthly payment and the total interest paid on a house of R750 000 at 9% interest over 20 years if no deposit is put down. (1)
c. How does the answer change (the monthly payment and the total interest paid) if a deposit of R20 000 on the price of the house is put down?
d. Perform a sensitivity analysis to model the effect of a change in interest rate on the monthly payment amount. Use a house value of R750 000 over 20 years with a deposit of R20 000 on the price of the house. Use interest rate values from 6% to 10% in increments of 0.5%. Use your sensitivity analysis table to answer the following questions.

i Give the monthly payment amount corresponding to an interest rate of 8.5%.
ii At what interest rate will the monthly payment amount be more than R6 000?

Added by Patricia N.

Question

Please give Ace some feedback