Forum Home» Mortgages & Endowments

My Excel mortgage spreadsheet - Page 10

New Post Advanced Search

My Excel mortgage spreadsheet

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

Replies

  • AlbeeAlbee Forumite
    13 posts
    Hi,

    Fantastic spreadsheet! It'll make my search for a decent remortgae in the next couple of months a lot easier! Cheers!

    In an effort to get a proper handle on my financial circumstances following an accident in July that's left me unable to work I used your spreadsheet to calculate how much I owe NR as I took out a £85,5000 mortgage and £9,500 as an unsecured loan in late Oct 2005 at a fixed 5.99% for 3 years the onto SVR (7.48%). This year is the first time I have been able to make over payments and have paid £770 so far (not alot of money to some - but alot to me!). Anyway, using the spread sheet my present debt to NR should be £91,252 but according to NR I owe them £93,209:confused:

    Is there a problem with the spread sheet or have NR got their figures wrong and I need to question it? Any advice would be much appreciated.
  • Great spreadsheet! Amazing to see just how effective even small overpayments are.
  • LocobladeLocoblade Forumite
    769 posts
    Part of the Furniture
    ✭✭✭
    wayno wrote: »
    Locoblade I've been searching the web for a spreadsheet like yours for a while now and yours is by far the best i have personally come across.
    While navigating around the sheet, learning how it works. I tried using the addtional borrowing column, each time i place a figure in th cell it doesn't alter the cumulative overpayment or end balance. (this was after having paid in more over payments than wishing to draw on addtional borrowing). Was just wondering if you can let me know how this part works?

    Great spreadsheet!

    Hi

    Unfortunately, as I found out a week or so ago, that bit doesn't seem to work at the moment, and having been studying for an exam recently I haven't had time to look into why and fix it.

    I'll make sure I do that before I release the next version though, for now what you could do is put in a one-off additional payment as a negative number (e.g. -£20,000) for the month you want that additional borrowing, as I think that should do prety much the same thing in terms of repayment calculations etc.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • LocobladeLocoblade Forumite
    769 posts
    Part of the Furniture
    ✭✭✭
    Locoblade,

    Great spreadsheet, many thanks. If I compare the effect of adding a monthly overpayment vs the effect of monthly offset savings, as expected the reduction in the time to pay off the loan is greater using overpayment. However, the total interest is marginally higher (about 7%). Interest rate was fixed throughout and I excluded all fees, to keep the comparison simple. Intuitively I would not not expected this. Any thoughts on this?


    Hi

    Could you maybe PM me the exact figures you added in so I can have a look please, as when I use the default settings as downloaded (£100k loan over 25 years etc), and put in a £300 monthly overpayment, or a £300 monthly offset, the amount of interest is virtually the same with either. There will be a slight error/variation in there compared to overpayments because of the limitations on how it can all be calculated. When offsetting for example, the spreadsheet looks at the offset total and the remaining capital, and if the former is larger than the latter, it sets the interest rate to zero and starts drawing the monthly payment out of the offset. Obviously if the offset is £1 under at the end of a month, it will go through another full monthly cycle of putting money into the offset before it starts the above process. If it was £1 over, then it will obviously kick in straight away so will pay things off a month earlier which will affect the figures you see. I can't think why it would be as much as 7% out though.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • LocobladeLocoblade Forumite
    769 posts
    Part of the Furniture
    ✭✭✭
    Albee wrote: »
    Hi,

    Fantastic spreadsheet! It'll make my search for a decent remortgae in the next couple of months a lot easier! Cheers!

    In an effort to get a proper handle on my financial circumstances following an accident in July that's left me unable to work I used your spreadsheet to calculate how much I owe NR as I took out a £85,5000 mortgage and £9,500 as an unsecured loan in late Oct 2005 at a fixed 5.99% for 3 years the onto SVR (7.48%). This year is the first time I have been able to make over payments and have paid £770 so far (not alot of money to some - but alot to me!). Anyway, using the spread sheet my present debt to NR should be £91,252 but according to NR I owe them £93,209:confused:

    Is there a problem with the spread sheet or have NR got their figures wrong and I need to question it? Any advice would be much appreciated.

    Hi

    Are you sure they aren't giving you a figure including any early redemption fees, or fees that were added at the start of the mortgage? Also are you sure the unsecured loan is also at the same rate as the loan?

    I can't guarantee the spreadsheet is correct obviously, but I've compared it as much as possible with other mortgage calculators online etc, so I believe it should be correct, at least for the fairly simple calculations like yours.
    (edited to add) - The spreadsheet does only do monthly compound interest calculations though, so if your lender uses daily interest etc it will be slightly different, although only by a few pounds per month max.

    Can you send me the remainder of your details that you put in, how long the loan is for, when you made overpayments and how much etc? :)
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • JettJett Forumite
    20 posts
    wow, very impressive
  • Been thinking about attempting to write a spreadsheet like this for ages. Glad I found this and saved the time.
  • benjdrbenjdr Forumite
    219 posts
    Just a bump and a thank you. This thing rocks.
  • LocobladeLocoblade Forumite
    769 posts
    Part of the Furniture
    ✭✭✭
    No probs Ben, the only bug I know of which Ive yet to get around to fixing is mentioned above, namely adding something into the additional borrowing column doesnt work, but the workaround if you wish to look at additional borrowing is to either add the additional borrowing into the overpayments column as a negative number, or use the second loan spreadsheet to calculate it seperately.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Stu78Stu78 Forumite
    25 posts
    I am getting a "the cell you are trying to change is protected" error when i try to put in manual over payments or extra borrowing.

    Is it possible that you inadvertantly protected all the cells, and not just the ones you need to protect?

    Cheers

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