My Excel mortgage spreadsheet

Options
1…1718202223…48

Comments

  • majorzott
    majorzott Posts: 35 Forumite
    First Anniversary Combo Breaker
    Options
    Great spreadsheet
    Small query:
    If cell F6 is bigger than F8 the loan period is too short. (I was inputting the direct line mortgage figures in).
    6.89 first two years then 2 therafter.
    Maybe I'm doing something wrong !!
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 7 October 2009 at 10:36AM
    Options
    majorzott wrote: »
    Great spreadsheet
    Small query:
    If cell F6 is bigger than F8 the loan period is too short. (I was inputting the direct line mortgage figures in).
    6.89 first two years then 2 therafter.
    Maybe I'm doing something wrong !!

    Hi

    Its most likely because you've left F22 (Overpayment/Offset effect on payments) as its default of "Keep Same". What this does is carry on paying the mortgage at the same monthly amount as you were paying when on 6.89%, it hasn't reduced the payments in line with the reduced interest rate so you're making significant overpayments each month, hence the reduced term. If you look on the monthly table sheet, using an example of £100k over 25 years you'll notice Total Monthly Payment (Column N) carries on paying £699 a month until the loan is repayed over about 16 years.

    If you want the mortgage to go full 25 year term with a reduced payment each month from month 25 onwards, you need to change F22 to "Reduce Monthly", then you'll notice in the monthly table the payments will drop from month 24 onwards to £437 and it will be repayed over the full 25 year period

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 7 October 2009 at 10:39AM
    Options
    Mrs_Monkey_Boy wrote: »
    Hi - thanks for this, it's a brilliant tool to have!

    Just a quick query - we've just moved to a new mortgage deal with a 2 year fixed rate. When I put the figure in, the initial monthly loan repayment figure is different to what I have from the bank.

    We have 19 yrs 2 months left to go so I put in 19.17 into the loan period - is this correct?

    The difference is only a couple of pounds but I'd like to know what I've been doing wrong!!

    Thanks again,

    Laura

    Hi Laura

    If its only out by a couple of quid, its most likely that your lender simply calculates your monthly payment slightly differently. The spreadsheet uses monthly calculations to work out the interest owed but if your lender uses daily calculation for example (which a lot do), and/or makes this calculation a month in arrears (such as First Direct) then the figures they calculate will be slightly different. You can always add in the monthly payment manually though, putting in the exact amount your bank are charging you.

    When I first started writing the spreadsheet I did try to do daily calculations but it got way too complicated so I had to just use monthly calculations.

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • deefadog
    deefadog Posts: 2,190 Forumite
    Name Dropper First Post First Anniversary Combo Breaker
    Options
    Hi Locoblade! I have upgraded to Office 2008 on the mac and i still can't edit those overpayment cells :)

    This is a perfect spreadsheet for me, is there any chance you could email me a completely unprotected version? or just the mortgage 1 monthly table?

    Please help me out :A
  • sevi_2
    sevi_2 Posts: 1 Newbie
    Options
    Does the Cumulative Interest column on the "Mortgage 1 Info and Key Figures" tab work correctly?

    I used the following inputs:
    Mortgage amount: £100,000.00
    Introductory/Initial Interest Rate: 6%
    Period of Introductory Rate (years): 1
    Interest Rate after Intro Period: 6%
    Loan period (years): 25

    The Interest Paid column returns a figure of £5,951.58 (in cell G37, i.e. total interest paid in year 1), but yet the Cumulative Interest cell gives a figure of £7,240.18 (in cell I37) - these two should be equal in Year 1 no?

    Great tool by the way...!
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    Options
    Just replied to both your PM's, both issues hopefully fixed in v1.8 when I get round to it :)

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    Options
    Hi All

    Ive just uploaded version 1.8 which Fixes Cumulative Interest calculation that was previously miscalculating the first year, and have uploaded a modified version which I think should work for Mac and older Excel, but this hasn't really been tested so use with caution please.

    Ive updated the first post so please download from there.

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • deefadog
    deefadog Posts: 2,190 Forumite
    Name Dropper First Post First Anniversary Combo Breaker
    Options
    :beer::beer::beer::beer::beer::beer:

    Just to confirm, the mac version works great :j

    :beer::beer::beer::beer::beer::beer:

    Yes i am please Lol - many, many thanks on your efforts!
  • cs101
    cs101 Posts: 73 Forumite
    First Post First Anniversary
    Options
    Great :beer:
    LIVE AND LET LIVE
  • deefadog
    deefadog Posts: 2,190 Forumite
    Name Dropper First Post First Anniversary Combo Breaker
    Options
    Just found a bug (if you can call it that). In the mac version (and maybe all) when you put more that 46000 in the monthly overpayments MANUAL column, it all gets screwed up?

    Just thought i'd let you know!
★ ★ ★ Meet your Ambassadors

Categories

  • All Categories
  • 343.2K Banking & Borrowing
  • 250.1K Reduce Debt & Boost Income
  • 449.7K Spending & Discounts
  • 235.3K Work, Benefits & Business
  • 608.1K Mortgages, Homes & Bills
  • 173.1K Life & Family
  • 247.9K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards