Mortgage
You are planning to buy a holiday villa in Spain which costs £300,000. You have a deposit of £30,000 and want to pay the rest through a mortgage. Your local bank offers you a 3.99% fixed rate for 5 years.
a) Compute the monthly repayments that you will have to make if you want to repay the mortgage in full in 25 years using the PMT function in Excel. [5]
b) Check your answer to part (a) by using Goal Seek. [1 0]
c) What would the outstanding balance be when you have to remortgage your loan after 5 years? [2]
d) If you think you can afford a monthly repayment of £2000, how long will it take you to repay the mortgage? [5]
e) Build a two-way data table to show how the monthly payment varies with changes in the down payment and in the term of the loan. Use values between 20,000 and 40,000 in increments of 5,000 for the down payment and values between 15 and 30 years in steps of 5 for the term of the loan. [8]