Contingencies & Modelling: Excel Modelling Assessment

The question is as follows:

1. Create a model and write an audit trail for it which would allow you to answer each part (a to f) of the following question. Your model should allow the model to be easily updated for changes in assumptions for timings, amounts etc. i.e. you should make use of parameters and limit hardcoding.

A borrower takes a loan of £500,000 over 30 years to purchase a property. Repayments are made annually in arrear, and payments increase by 10% of the first payment at the end of each of years 2 to 10. The lender charges 6% interest per annum effective.

a. Calculate the amount of the fifth payment made by the borrower.

b. Calculate the outstanding loan immediately after each payment is made. 

c. Calculate the interest content and capital expected to be repaid in each repayment.

The borrower finds it difficult to meet the increase in payments. Immediately before the 8th payment is due he asks the lender if he can increase his repayments as planned at the 8th anniversary of the loan but then keep repayments at this level thereafter. The lender agrees to the request on condition that the term of the loan is extended. The lender charges interest at 8% per annum effective on the rescheduled loan. The lender also charges a fee of £750 immediately. The fee will be added to the loan.

d. Calculate the revised outstanding term of the mortgage.

e. Determine the capital and interest content for each of the payments.

f. Calculate how much extra the borrower will pay under the rescheduled loan (including the fee) compared to the original loan.

HINT: You should build a model that includes the calculation of;

a. The amount of the original loan

b. The loan outstanding after each instalment

c. The capital repaid in each instalment

To complete parts d, e and f you should duplicate the original model on a new worksheet and then adjust for the new scenario.