Mortgage Calculator

My daughter and her boyfriend recently bought a house. They wanted to have a clear understanding of possible future scenarios when borrowing a large lump sum to fund the purchase, e.g. ‘how will my payments change if interest rates change to x% in y months time?’, how much can my payments be reduced by if I pay a lump sum of £x in y months time?’, ‘How many months could I shorten my repayment term by if I pay a lump sum of £x in y months time?’

Additionally they were keen to notionally split the mortgage between them so their different deposit contributions, monthly payments and differing lump sum payments over the period could be modelled.

This link goes to a Google Sheets spreadsheet I put together to answer all the above questions and more. Although it allows for splitting of the loan between two people this isn't necessary. Simply set the Person B share % and deposit to £0 if this isn't required.

The calculations accrue interest on a day by day basis, allowing up to one change of interest rate per month, one lump sum payment per month, and one payment level or payment date change per month. Note that the payment level is restricted such that you can’t set a payment amount from any point in time that would not clear the loan in the balance of the outstanding term.

I hope the spreadsheet is self-explanatory, but please contact me with any queries or suggestions on how to improve it.

Input is restricted so that the formulae don’t get inadvertently corrupted in the Google Sheets version the link leads to, if you want to keep personalised scenarios just save a copy to your own device.

Missed the link to the spreadsheet? CLICK HERE