My Excel mortgage spreadsheet

1424344454648»

Comments

  • Locoblade
    Locoblade Posts: 795
    Name Dropper First Post First Anniversary
    Forumite
    Hi Locoblade, I am trying to use your fantastic spreadsheet but have a three problems that I just cannot sort out. 
    Firstly, the initial payment for our mortgage was different to the rest of the payments and also on a different day of the month.  Secondly, the monthly amount calculated from the details given is actually different to the monthly amount we are paying.
    Thirdly, we are making overpayments of the same amount each month but no overpayments were made with the initial payment.
    I hope you can help me.
    Hi

    Payments made on different days of the month doesn't matter as the spreadsheet only calculates monthly anyway, which is also the main reason it will never exactly equate to the sums you're actually paying as your mortgage companies use their own formulas, not the boggo standard monthly amortization function built into Excel.

    If you enter your headline numbers on the Key Figures page, select "Manual" for input 10 then enter your actual monthly payment (Input 11) rather than the calculated one, that should start you off on the right foot. The Monthly tab is where you can then do some tweaking to make it as accurate as possible. For the first month if say you paid £1500 rather than the normal monthly £1000, just add the £500 difference into the Monthly Overpayment column for the first month to make that adjustment. Likewise use the same column to add your subsequent monthly overpayments (copy the cell down to the ones below if the payments are the same each month) and you can even use this column with + or - numbers to tweak the monthly sum remaining if thats the main thing you want to track so you're effectively correcting the calculation error each month, although obviously that will affect the accuracy of the "Sum of all payments" type numbers. HTH
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • bhjm
    bhjm Posts: 341
    First Anniversary First Post
    Forumite
    dear locoblade,

    thank you very much for this super excel sheet !! I cannot endorse you enough for that. As we are a community and not just finding bugs, you also invite us for suggestion on how to improve the sheet. Please allow me to submit my ideas/request. Apologies if they have been answered in the 40+pages already !

    let me start with the perhaps the biggest, when it comes to rework and formulas
    - payment schedule options. Is there a chance we could have a a weekly or bi-weekly payment option on top of the monthly?
    - the graph sheets, could you perhaps combine them? or add a kind of comparison chart in the comparison 
    - the graph charts, would it be possible to adjust automatically the length of the mortgage based on the input? (input 15)
    - include the property & deposit amount
    - if including the above, it would also enable to include a simple LTV calculation :-)
    - expand the saving calculator, with ETF etc being popular, could you make or include a calculation, if paying into a saving account / S&S ISA would be better than overpayments etc?


    apologies for any grammar or spelling mistakes, English isn`t my first language.
  • Locoblade
    Locoblade Posts: 795
    Name Dropper First Post First Anniversary
    Forumite
    edited 25 August 2022 at 8:31PM
    bhjm said:
    dear locoblade,

    thank you very much for this super excel sheet !! I cannot endorse you enough for that. As we are a community and not just finding bugs, you also invite us for suggestion on how to improve the sheet. Please allow me to submit my ideas/request. Apologies if they have been answered in the 40+pages already !

    apologies for any grammar or spelling mistakes, English isn`t my first language.
    Hi

    Thanks for the kind words. I've not done very much to the spreadsheet for over 10 years to be honest and there's no plans to develop it further in the near future at least, but just to answer some of your questions specifically....

    - payment schedule options. Is there a chance we could have a a weekly or bi-weekly payment option on top of the monthly?
    Unfortunately any change in calculation frequency would basically need the entire spreadsheet re-writing and I'm not sure Excel would handle it really, I did try making a daily interest version about 10 years ago when I was actively developing the spreadsheet but the inbuilt amortization function that's the backbone of the calculations didn't handle it and it just became unmanageable with over 18,000 lines of calculations rather than the current 600

    - the graph sheets, could you perhaps combine them? or add a kind of comparison chart in the comparison 
    - include the property & deposit amount
    - if including the above, it would also enable to include a simple LTV calculation :-)
    I think what people want from graphs varies so much that it's better to keep to the bare minimum so as not to clutter it up too much. You can download the version with extra sheets though and create your own charts & calculations etc

    - the graph charts, would it be possible to adjust automatically the length of the mortgage based on the input? (input 15)
    There may be a way to do it and IIRC I did try and do it back in the day but an Excel sets the axis length based on the data you're feeding it and the spreadsheet data is based on 600 monthly (50 years) rows of calculations so that's what I have to feed the graph. Unless there's a way to dynamically reduce the X axis data based only on values above zero then it's not straightforward for it to show anything other than 50 years. As above though, you can add your own charts etc with the extra sheet version based on your own data over a shorter period.

    - expand the saving calculator, with ETF etc being popular, could you make or include a calculation, if paying into a saving account / S&S ISA would be better than overpayments etc?
    I'm not clear what extra functionality you're envisaging to be honest as you can already compare putting money into savings against overpayments to see which is cheaper? Whether its regular savings or S&S doesn't make any difference in this context, just put in the amount you're investing and the estimated % growth and it will compare that investment with paying the same amount as overpayments.


    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173

  • Your spreadsheet has proven to be so useful - thank you for sharing it.
    I was wondering when it was last updated (I can't see a date stamp in v1.14)?
    As there has been a change in tax on interest on savings according to tax bracket, is it easy to update it to reflect this please?
    Thanks

    VK
  • bhjm
    bhjm Posts: 341
    First Anniversary First Post
    Forumite
    can I ask for a small favour? 

    can you add something like LTV columns?

    currently, only the mortgage amount is entered, but not the house price or the deposit. If this could be added, that would be nice (at least for me)

    I like to see things like how much equity to buy each month with paying off your mortgage, how your LTV reduces etc. below a small screenshot. And I think I don`t want to go to much into detail with things like annual house price growth etc.This would be then also a nice addition to the "other charts" worksheet

    Asking for your assistance, as the sheet is protected ;-)




    Thank you :-)
  • bhjm
    bhjm Posts: 341
    First Anniversary First Post
    Forumite
    allow me to ask - whats the different if I add an single overpayment in the overpayment column vs as a negative in the additional borrowing? 
Meet your Ambassadors

Categories

  • All Categories
  • 341.6K Banking & Borrowing
  • 249.7K Reduce Debt & Boost Income
  • 449.1K Spending & Discounts
  • 233.7K Work, Benefits & Business
  • 605.8K Mortgages, Homes & Bills
  • 172.4K Life & Family
  • 246.6K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.8K Discuss & Feedback
  • 15.1K Coronavirus Support Boards