Forum Home» Mortgages & Endowments

My Excel mortgage spreadsheet - Page 23

New Post Advanced Search

My Excel mortgage spreadsheet

edited 7 May 2010 at 9:29PM in Mortgages & Endowments
460 replies 393.5K views


  • I get £1856, £4584 and £7239 for the first scenario (ie 1.98% first year then 2.99%)
    I get £2601, £5135 and £7599 for the second scenario (ie 2.77% for the entirity)

    Are these rates right? You mention they are trackers. Are these the rates or are they the additions to the base rates?
  • crexcrex Forumite
    9 posts

    These are the actual rates, going by the current base rate of 0.5%. I probably won't get a chance to double-check the figures till I get home from work this evening.

  • LocobladeLocoblade Forumite
    769 posts
    Part of the Furniture
    crex wrote: »
    Thanks very much for all the work you've put into this spreadsheet. As has already been posted, it's a fantastic tool.
    I've been using it to compare 2 lifetime teackers on a £95k remortgage over 27 years. The first has a discount rate of 1.98% for the first year, then 2.99 for the remainder of the term. The second is 2.77% for the whole term. Fee for both is £999. Now, I am clearly misunderstanding something, as I don't follow the considerable discrepancy between the cumulative interest columns in the comparison tab for these 2 mortgages. For the first it is approx. £7.5k, 10.2k and 12.9k for the first 3 years; for the second it is £2.6k, 5.2k and £7.7k for the same period. This pattern continues for the remainder of the term. Can someone explain this please?!

    I get the same figures as JonnyBravo, which seem right as the first is lower interest for the first year then gradually starts costing more as the years go on.

    I suspect what you've done is mis-typed and entered 7.98% for the first year on mortgage 1 rather than 1.98%, as that gives figures similar to what you're seeing, ie £7543, £10,273 and £12,825?

    My Excel Mortgage Calculator Spreadsheet:
  • edited 4 December 2009 at 12:04AM
    LocobladeLocoblade Forumite
    769 posts
    Part of the Furniture
    edited 4 December 2009 at 12:04AM
    Version 1.9 has now been uploaded, see first page of this thread for the download links.

    Version 1.8 was supposed to fix the first year cumulative interest error in previous versions, but although it partially worked, it still didnt work in certain circumstances such as when paying interest only, so hopefully this is now fixed for all permutations.

    My Excel Mortgage Calculator Spreadsheet:
  • Firstly let me say this is a fantastic spreadsheet.

    My issue, which might be me but I have read the guidance and am sure I am entering everything correctly.

    I am trying to work out the following mortgage deal;

    intoductory rate: 6.99%
    introductory term: 2 years
    standard rate: 2.50%
    mortgage term: 25 years

    the spreadsheet analysis only goes upto 15 yr 6 mths on repayment and 19 yr 9 mths on interest only.

    Is this an error? If the term is 25 yrs it should work it out over that?

    If I flip those rates around (2.5% intro reverting to 6.99%) on repayment it seems to work fine going upto full 25 yrs. However, if I set it to interest only it produces calculations for 50 yrs! but term still input as 25.

    I am running Excel 2007 and get similar behaviour on both versions of the spreadsheet.

    Any help appreciated.
  • @Markie76 Version 1.9 works with your figures. I am using the 'Mac'/older office version with 3.1 for pc.
  • Hi Joe,

    Ok, I have re-downloaded from the link at the top of the thread and tried it in Excel 2002 on a different pc and it still only goes upto 15.5 yrs where the mortgage is paid off.

    Then I downloaded the mac compatible version and opened it in Openoffice and same result 15.5 yrs.

    Pls have a look at the screenshot on the link below - what am I doing wrong?



  • edited 15 December 2009 at 9:53PM
    Gorgeous_GeorgeGorgeous_George Forumite
    8K posts
    edited 15 December 2009 at 9:53PM
    The problem is that when your rate drops, your payments remain at the original level.

    It could where Line 18 says 'Keep same' but I'll need to download the spreadsheet to confirm (back in a tick)

    ETA: Yup, that was it.

    There are 10 types of people in this world. Those who understand binary and those that don't.
  • Hi George,

    You're absolutely right, if you set line 18 to 'reduce monthly' it seems to work.


  • @Markie76 how did you get that screen shot of the spreadsheet and what did you have to to to post it ?
Sign In or Register to comment.

Quick links

Essential Money | Who & Where are you? | Work & Benefits | Household and travel | Shopping & Freebies | About MSE | The MoneySavers Arms | Covid-19 & Coronavirus Support