My Excel mortgage spreadsheet

1202123252648

Comments

  • JonnyBravo
    JonnyBravo Posts: 4,103
    Mortgage-free Glee!
    Forumite
    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?
  • crex
    crex Posts: 26
    First Post First Anniversary Combo Breaker
    Forumite
    Jonny

    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.

    Thanks
  • 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?!
    Thanks

    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?

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade
    Locoblade Posts: 795
    Name Dropper First Post First Anniversary
    Forumite
    edited 3 December 2009 at 11:04PM
    Version 1.9 has now been uploaded, see first page of this thread for the download links.
    http://forums.moneysavingexpert.com/showthread.html?t=1157173

    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.

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • 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.
  • Joe_Bloggs
    Joe_Bloggs Posts: 4,535 Forumite
    @Markie76 Version 1.9 works with your figures. I am using the 'Mac'/older office version with Openoffice.org 3.1 for pc.
    J_B.
  • 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?

    11jb1x0.jpg

    Thanks,

    Mark.
  • Gorgeous_George
    Gorgeous_George Posts: 7,964
    First Anniversary Combo Breaker
    Forumite
    edited 15 December 2009 at 8: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.

    GG
    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.

    Thanks,

    Mark.
  • Joe_Bloggs
    Joe_Bloggs Posts: 4,535 Forumite
    @Markie76 how did you get that screen shot of the spreadsheet and what did you have to to to post it ?
    J_B.
Meet your Ambassadors

Categories

  • All Categories
  • 342.5K Banking & Borrowing
  • 249.9K Reduce Debt & Boost Income
  • 449.4K Spending & Discounts
  • 234.6K Work, Benefits & Business
  • 607.1K Mortgages, Homes & Bills
  • 172.8K Life & Family
  • 247.4K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.8K Discuss & Feedback
  • 15.1K Coronavirus Support Boards