Jump to content
House Price Crash Forum


This topic is now archived and is closed to further replies.

Cheston Pelvis

Cheston's Repayment Mortgage Calculator V1.00

Recommended Posts

Good afternoon, HPC-ers.

I posted a reply to Without A Paddle yesterday regarding the likely repayments for a 10-year fixed mortgage and came up with some "fag-packet" figures to show the effect of overpaying. I knew they weren’t terribly accurate, however, and that got me thinking...

I have since developed a small spreadsheet that simulates the likely payments one would have to make for a "common or garden" repayment mortgage (the kind that I would likely plump for). You are able to simulate monthly and/or annual overpayments to view the effects on repayments.

I'll admit it's not terribly sophisticated, but I thought it may be of interest to the forum. If anyone has any corrections or improvements they would like to see made they are more than welcome to post here and request them (though I might need a little help on the implementation side!)

NOTE: As the upload doesn't allow files with the extension ".xls" you will have to change the file extension from ".txt" to ".xls" once you have downloaded it. While I believe the spreadsheet to be virus-free, I would still recommend users up Excel's macro security to High before attempting to use the spreadsheet - just to be on the safe side. If the mods can vouch for the file's safety it would be most appreciated. :)

Kind regards,


P.S. If anyone knows of a more comprehensive spreadsheet available on the 'net could they please provide links. Thanks in advance.

Version History:


v1.00 - Initial version

v1.01 - Removed a few pesky macros - this is one sheet of a larger workbook of mine. Apologies for giving anyone a scare! :(



Share this post

Link to post
Share on other sites

How to use the calculator:

There are basically 4 cells into which you can enter figures:

Amount Borrowed – enter the loan amount here.

Rate – enter the mortgage rate here.

(The Estimated Mortgage Repayment cell is automatically calculated through Excel’s PMT formula based on a 25 year loan period)

Monthly Overpayment Rate – if the mortgage product allows you to overpay a certain percentage each month, enter this in here to see the effects on the repayments.

Annual Overpayment Rate – if, however, the mortgage product allows you to overpay a certain percentage of the loan balance each year enter the rate in this cell.

You can scroll down the results to see how the loan reduces each month, and how soon the loan is cleared if you stick to overpaying it.

To help you compare the effects of overpaying the mortgage there is a cell that totals the interest you are likely to pay.

Tweaking the Spreadsheet:

If you want to tweak the spreadsheet to reflect different loan periods you can play with the formula in the Estimated Mortgage Repayment cell:

=PMT(R[-1]C/12, 300,R[-2]C) * -1

Simply change the 300 figure to the number of months you require and press enter. The change should be applied automatically by Excel.

If you want to see a breakdown of terms longer than 25 years you will need to copy and paste the formulas in the last 12 rows down the spreadsheet as many times as necessary. (Remember to re-label the year numbers so as to avoid confusion!)

Have fun!

Share this post

Link to post
Share on other sites

  • Recently Browsing   0 members

    No registered users viewing this page.

  • 312 Brexit, House prices and Summer 2020

    1. 1. Including the effects Brexit, where do you think average UK house prices will be relative to now in June 2020?

      • down 5% +
      • down 2.5%
      • Even
      • up 2.5%
      • up 5%

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.