Mortgage Repayment Calculator                         Version 1.14
       Download the latest version here
       MoneySavingExpert download / support thread here
       Copyright Chris Gamlin 2019
       Buy Me a Coffee :)
       If you find this spreadsheet useful and want to say thanks then perhaps consider buying 
       me a coffee, a beer or even a penny chew by clicking on the cup above which will take 
       you to a Paypal donation page. All contributions however small are gratefully received and 
       if I've already had my caffiene fix, donations will go towards paying for the web hosting 
       required to keep up with the demand for this spreadsheet (averaging 800 - 1,000 
       downloads per month and No.1 on Google when searching "mortgage spreadsheet"!), 
       plus it might also encourage me to continue updating and improving the spreadsheet.
       PLEASE NOTE: This spreadsheet requires Excel 2007 for Windows or later. This 
       version WON'T WORK properly with Excel 95/97 or 2000/2003 and may not work 
       with Excel for Apple Mac (even newer versions). It almost certainly won't work with 
       Open Office, Google Sheets or Apple Numbers etc, basically anything that's not 
       Microsoft Excel is unlikely to work due to the Excel specific formulas used. 
       However, I've modified a version of this spreadsheet that may work on some of 
       these so please download the Mac version from the link on the website (link at top 
       of page)
       The serious legal bit:
       This spreadsheet is for personal use only. It cannot be reproduced, sold, hosted or 
       otherwise used for any commercial purpose without my express permission. 
       No guarantee can be made for the accuracy of the calculations or the figures generated. 
       The spreadsheet uses standard inbuilt Excel financial equations but the way these 
       generate repayment/interest/future value calculations may differ slightly to what your 
       lender uses to calculate repayments. For instance, the calculations are done on a monthly 
       basis with consistent monthly repayments regardless of the number of days in each 
       month. If your lender calculates interest daily and takes into account the varying days 
       during each monthly payment, the figures will be slightly different.
       Please contact me if you have any suggestions for improvements or bug-fixes relating to 
       this spreadsheet:
       excel@locostfireblade.co.uk
       This spreadsheet started off life being based on the standard Microsoft Amortization 
       Schedule spreadsheet template available for download from the following location:
       http://office.microsoft.com/en-us/templates/TC010566201033.aspx
       Version information
       Version 1.14
       Minor updates
      Version 1.12
      Fixed calculation of interest saved when adding offset options
      Version 1.11
      Fixed Mortgage 2 which accidentally inherited some of Mortgage 1's settings such as start 
      month. It only took 2.5 years for somebody to find that bug! Also unprotected the chart 
      pages so they can be customised.
      Version 1.10
      Added the ability to resize column widths and hide columns you don't want to see. To do 
      this you'll first need to un-hide the headings in the view menu (in options on older Excel 
      versions)
      Version 1.09
      Updated fix for Cumulative Interest calculation that was still miscalculating the first year for 
      certain scenarios. Also added Paypal Donate button if you're feeling generous :)
      Version 1.08
      Fixed Cumulative Interest calculation that was previously miscalculating the first year.
      Version 1.07
      Fixed the total cost / total capital remaining calculation in the Comparison sheet. 
      Previously the second mortgage sheet calculations weren't correct if the introduction 
      period was different to mortgage 1
      Version 1.06
      Fixed the additional borrowing functionality
      Version 1.05 Changes
      Break Even Month calculation now takes into account upfront and completion fees
      Version 1.04 Changes
      Comparison for savings account/ISA against offset/overpayments on Mortgage 1
      Comparison of two mortgages with differing fees added to loan, showing break even month
      Seperated out fees added to loan
      Version 1.03 Changes
      Early Repayment Charges and Completion Fees calculation added.
      Version 1.02 Changes
      Mortgage 2 feature added to allow comparisons between two loans
      Total cost to end of introductory period added in Key Figures
      Capital Remaining at end of introductory period added in Key Figures
      Version 1.01 changes
      Upfront Fees option added to Input Selections
      End of Year Schedule of Balances and Repayments - added information boxes to each 
      heading and an additional column to take upfront fees into consideration
      End of Year Schedule of Balances and Repayments bug fix, now calculates annual 
      payments correctly
                   Mortgage Repayment Calculator                             If you want to compare two mortgages, use both Mortgage 1 and 
                                                                             Mortgage 2 input sheets, then compare using the Comparison sheet. For a 
                                                                             single mortgage prediction you can ignore Mortgage 2 and the 
                                                                             Comparison sheets
               Inputs                          Highlight Cells for more Info.               Key Figures
               1  Mortgage amount                            £100,000.00                    Average Capital Loan Annual Payments
               2  Mortgage Type                              Repayment                      Initial Repayment Monthly Loan Payment
               3  Introductory/Initial Interest Rate         6.00%                          Average Interest Only Annual Payments
               4  Period of Introductory Rate (years)        4.0                            Initial Interest Only Monthly Payment
               5  Interest Rate after Intro Period           6.00%                          Interest over term of loan
               6  Loan period (years)                        25.0                           Interest Saved by Overpayment/Offset
               7  Starting Year of Loan                      2008                           Sum of all payments
               8  Starting Month of Loan                     Nov                            Mortgage Repayment time in Years
               ###Payment Calculated / Manually added? Calculated                           Total cost up to the end of year 4
               ###Manual Input Initial Monthly Payment       N/A                            Total capital remaining at the end of year 4
               ###Upfront Fees                                                              Overpayments against Savings at end of year 4
               ###Fees added to the loan                                                    Offset Key Figures
               ###Early Repayment Charge Rate (%)                                           Offset Savings equal Loan after (years)
               ###Early Repayment Charge Period (years)                                     Maximum Offset Savings Value
               ###Closure / Completion Fee
               Overpayments (optional)
               ###Monthly Overpayment Amount                                                  To list ad-hoc monthly overpayments, offset savings and 
               ###Overpayment/Offset effect on PaymentsReduce Monthly                       changes in interest rates etc, edit the appropriate cells in the 
               Offset Mortgage (optional)                                                                  "Detailed Monthly Table" sheet.
               ###Average Offset Current Account Balance
               ###Initial Offset Savings Balance
               ###Monthly Payment into Offset Savings
               Compare Offset/overpayment against savings or ISA
               ###Savings account Gross rate for comparison
               ###Select tax band for savings                ISA/No Tax               0.00 Scroll down for annual figures
               End of Year Schedule of Balances and Payments
                                  Start                       Capital Paid      Interest      Cumulative 
                    Year         Balance        Payment            Off            Paid        Capital Paid 
                                                                                                   Off
                     1         £100,000.00      £7,731.62       £1,780.04      £5,951.58       £1,780.04
                     2         £98,219.96       £7,731.62       £1,889.83      £5,841.79       £3,669.87
                     3         £96,330.13       £7,731.62       £2,006.39      £5,725.23       £5,676.26
                     4         £94,323.74       £7,731.62       £2,130.14      £5,601.48       £7,806.39
                     5         £92,193.61       £7,731.62       £2,261.52      £5,470.10       £10,067.91
                     6         £89,932.09       £7,731.62       £2,401.01      £5,330.61       £12,468.92
                     7         £87,531.08       £7,731.62       £2,549.09      £5,182.52       £15,018.01
                     8         £84,981.99       £7,731.62       £2,706.32      £5,025.30       £17,724.33
                     9         £82,275.67       £7,731.62       £2,873.24      £4,858.38       £20,597.57
                     10        £79,402.43       £7,731.62       £3,050.45      £4,681.16       £23,648.02
                     11        £76,351.98       £7,731.62       £3,238.60      £4,493.02       £26,886.62
                     12        £73,113.38       £7,731.62       £3,438.35      £4,293.27       £30,324.96
                     13        £69,675.04       £7,731.62       £3,650.42      £4,081.20       £33,975.38
                     14        £66,024.62       £7,731.62       £3,875.57      £3,856.05       £37,850.95
                     15        £62,149.05       £7,731.62       £4,114.60      £3,617.01       £41,965.55
                     16        £58,034.45       £7,731.62       £4,368.38      £3,363.23       £46,333.93
                     17        £53,666.07       £7,731.62       £4,637.81      £3,093.80       £50,971.74
                     18        £49,028.26       £7,731.62       £4,923.86      £2,807.75       £55,895.61
                 19       £44,104.39    £7,731.62    £5,227.56    £2,504.06    £61,123.17
                 20       £38,876.83    £7,731.62    £5,549.98    £2,181.63    £66,673.15
                 21       £33,326.85    £7,731.62    £5,892.29    £1,839.32    £72,565.44
                 22       £27,434.56    £7,731.62    £6,255.72    £1,475.90    £78,821.16
                 23       £21,178.84    £7,731.62    £6,641.56    £1,090.06    £85,462.71
                 24       £14,537.29    £7,731.62    £7,051.19     £680.42     £92,513.91
                 25        £7,486.09    £7,731.62    £7,486.09     £245.52    £100,000.00