My Excel mortgage spreadsheet

14243444648

Comments

  • Hi,

    Is there a way to see how two mortgages compare if your over payment is a percentage of the balance?

    For example, I would like to compare two mortgages from HSBC; one has a 5 year fixed term with no fee and other one is 2 year fixed with a fee. So far, I can input this easily. HSBC allows me to do over payment. They check the balance at the beginning of the year and in that year I can overpay up to 10% of the balance without incurring a fee. I would like to include the maximum over payment allowed in the calculation and see which deal is better. Overpayment allowed would be different every year since it will be a percentage of the balance.

    Thank you.
  • Hi


    Yep you can do that. In the Montly Table tab - Monthly Overpayment column, add an equation to work out 10% of the current balance for the month you want to do the overpayment.

    Here's an example of a 10% overpayment done on the 12th month of the mortgage, done by adding the equation "=K23*10%" to the 12th overpayment cell where K23 is the corresponding cell where the current balance is (the box highlighted blue in the second column)



    BAdCvLnl.jpg


    Once you have the correct month populated, you can then copy that equation down every 12 rows so it recalculates once each year.



    JnF4VFsl.jpg
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade wrote: »
    Hi


    Yep you can do that. In the Montly Table tab - Monthly Overpayment column, add an equation to work out 10% of the current balance for the month you want to do the overpayment....

    Thank you for your quick reply locoblade. The solution you suggestion is good except that over-payment will be made monthly (total allowed for that year divided by 12). If I add it all once per year, I do not think it will calculate the interest properly as monthly payment is fixed but interest is based on balance. I will need to add monthly payments for every month; different for that year. It is not a big deal but thought I would run it by you to make sure I am doing it right.

    Thanks.
  • getmore4less
    getmore4less Posts: 46,882
    Name Dropper First Anniversary First Post I've helped Parliament
    Forumite
    DJDJDJDJ wrote: »
    Thank you for your quick reply locoblade. The solution you suggestion is good except that over-payment will be made monthly (total allowed for that year divided by 12). If I add it all once per year, I do not think it will calculate the interest properly as monthly payment is fixed but interest is based on balance. I will need to add monthly payments for every month; different for that year. It is not a big deal but thought I would run it by you to make sure I am doing it right.

    Thanks.

    You can just adjust the cells to make the extra payment monthly at 1/12 of the initial calculation


    Depending on lender you may need other adjustments as some lenders don't allow the max overpayment and keep the payment the same.
  • You can just adjust the cells to make the extra payment monthly at 1/12 of the initial calculation


    Depending on lender you may need other adjustments as some lenders don't allow the max overpayment and keep the payment the same.

    Thank you for your reply. I will need to figure out what I need to do here. I think I am a bit exception to the rule here. I have a regular over payment that goes every month with normal payment. I calculate at the beginning of the year how much over payment that would be. Then I take the remaining over payment allowed and pay the in the first 6 months so that by the end of the year all over payment allowed is made. I guess every bank allows different over payments. I will do the calculations manually and see which one works out better.

    Thank you.
  • getmore4less
    getmore4less Posts: 46,882
    Name Dropper First Anniversary First Post I've helped Parliament
    Forumite
    pay the money as soon as you can that is always better.

    If you can hit the limit early in the ERC free year then consider a 0% purchase credit card to free up cash flow in the early months till you hit the limit then pay off the card with the money you would have used to pay the mortgage.
  • Locoblade
    Locoblade Posts: 795
    Name Dropper First Post First Anniversary
    Forumite
    edited 18 September 2019 at 7:23AM
    Change the "=K23*10%" equation to "=(K$23*10%)/6" and then copy it down to the next 5 months below the first equation. If you want to repay it over 4 months change the divider from 6 to 4 and only copy down another 3 cells etc.

    Because you're now using the $ sign to lock the equation to row 23 even when dragged down a few rows, it won't now copy/paste down to future years so you'll need to manually change the row number for each year you want to calculate. There's probably a way to write the equation so it will change correctly as you copy it down but can't think of a way off the top of my head.

    Edit: Actually if you make the first cell "=(K23*10%)/6" (without the dollar sign) and then the 5 cells below it just type in =P23 (the cell where the equation is in), then you can copy and paste those cells down to subsequent years and it will automatically change.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Hi, Can I just say firstly that this is a masterpiece! Really clever on the solutions you came up with on how to produce certain outcomes

    Just a question on some formulas I noticed on the 'Mortgage 1 Monthly Table' Sheet. Particularly on Column D (End of Year). There's a whole bunch of them in between the year numbers and it doesn't seem to mean anything?

    For example, If I go to 'Mortgage 1 Info and Key Figures' and enter 2020 for the year and Feb as the month, then go to the 'Mortgage 1 Monthly Table' Sheet - I can see an error on Cell D53. The formula there seems to be referencing a validation to return month + 1 if True?
    =IF('Mortgage 1 Variables'!C49=1,F43+1,"")

    And there's a whole lot more along Column D that you don't see all as the validation is returning "" if FALSE.

    Wondering if you could enlighten for my own learning?

    Thanks
  • Hi

    Thanks for the compliments! Most of it was written over 10 years ago now so to be perfectly honest I'm a little rusty on some of the underlying workings myself now but I think it's some left over code from when I was trying to make the year column stop counting up when the mortgage was paid off but I ended up not bothering and setting them statically for a maximum of 50 years so should have deleted those formulas as they're not doing anything useful now! Good spot :rotfl:
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • jimb0d
    jimb0d Posts: 39
    First Anniversary First Post
    Forumite
    Bloomin' excellent this - I think the main mortgage calculators should link here and the point needs to be made that more affordable monthly payments or total cost over the initial terms are not the only considerations when picking a mortgage.

    This is one of the only tools I could find in a week of googling to compare those costs to the capital and overall cost of the mortgage. I think MSE should include this information in the general mortgage guides.
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