My Excel mortgage spreadsheet

14244464748

Comments

  • sieuph
    sieuph Posts: 17 Forumite
    First Anniversary Combo Breaker
    Thanks for this - Brilliant to be able to compare mortgages with different rates and fees.
    June 2020 - Flack DVD Box Set
    May 2020 - Tarmac Pen
    April 2020 - Midweek Break to Blackpool
  • ccbrowning
    ccbrowning Posts: 426 Forumite
    First Anniversary Name Dropper First Post
    The SVR bit doesn't seem to work? When I look at the monthly table, the amount never changes after the fixed term period ends and stays the original low, fixed amount. Using Excel 2016... If I manually edit the interest rate starting at the month the SVR kicks in, it sort of works for that worksheet, though.

    Seems amazing for the other bits, though.
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 14 July 2016 at 10:26PM
    Hi

    It does work but one of the options set in a certain way can affect the end result. Input 18 on the front page (Overpayment/Offset effect...) has an effect when the interest rate change, if it's set to "Keep Same" it will keep the same monthly payment even when the interest rate changes if the SVR is lower than the introductory rate. Below is how you should set it so it always follows the minimum amount to be paid.

    cheers

    msgcxw.jpg


    vytidl.jpg
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Hello Locoblade,


    Great SS. It has been invaluable for me comparing my reportage offers. So many thanks.


    Now I am not sure if I am doing something wrong (most probably) but I am putting in a predicted lump sum overpayment into the sheet and it is not reducing the overall term of the mortgage. When I put in a monthly overpayment via the info and key figures tab it does reduce the overall term.


    I am putting in my lump sum over payment in the Mortgage X Monthly Table part way through my 5 year fixed example in column L ( Monthly Overpayment) - when I put it here nothing changes on the term.
    If I put in a regular payment into Mortgage X Info and Key Figures Cell F21 the term reduces.


    I would add screen shots but unsure how that works on here.....


    Am I doing something wrong or is there an error ?


    Many thanks

    Dustiebin
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 26 July 2016 at 8:44PM
    Hiya

    Its the same setting as mentioned above to the previous question, this needs to be changed to "Keep Same" so subsequent monthly payments after the big overpayment arent reduced to retain the term. The reason it reduces the term regardless of that setting when a "Monthly Overpayment" amount is set just above it is because that box adds an overpayment every single month, so reducing the monthly each month whilst upping the monthly with a regular overpayment each month would cancel each other out and be pointless really.

    msgcxw.jpg

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • I was wondering if you could share the password by any chance?

    Thankyou again for sharing this work.
  • twarde
    twarde Posts: 23 Forumite
    First Anniversary Combo Breaker
    Locoblade,
    I was looking at your spreadsheet to calculate the roll up interest on a non paying equity (or is called lifetime) mortgage. So using interest only option but I would like to be able to change the interest rate as its linked to BOE base rate. Would the calculations take account of more than two interest rate changes over the life of the loan?

    Or is there a better solution for this type of mortgage?

    Thanks
  • penst0ne
    penst0ne Posts: 9 Forumite
    First Post Combo Breaker First Anniversary
    Thanks, just what I wanted.
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    twarde wrote: »
    Locoblade,
    I was looking at your spreadsheet to calculate the roll up interest on a non paying equity (or is called lifetime) mortgage. So using interest only option but I would like to be able to change the interest rate as its linked to BOE base rate. Would the calculations take account of more than two interest rate changes over the life of the loan?

    Or is there a better solution for this type of mortgage?

    Thanks

    Hi

    Sorry just catching up here as the forum seems to have stopped notifying me of new messages. You can change the interest rate every month if you like by editing it in the appropriate column in the Monthly tab, so should do what you need.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • jlwhite
    jlwhite Posts: 121 Forumite
    Thanks for the spreadsheet, I know this forum is old but was wondering if someone could answer a question for me.

    Mortgage

    Inputs Highlight Cells for more Info.
    1 Mortgage amount £185,999.00
    2 Mortgage Type Repayment
    3 Introductory/Initial Interest Rate 1.59%
    4 Period of Introductory Rate (years) 2.0
    5 Interest Rate after Intro Period 4.74%
    6 Loan period (years) 24.0
    7 Starting Year of Loan 2017
    8 Starting Month of Loan May
    10 Payment Calculated / Manually added? Calculated
    11 Manual Input Initial Monthly Payment N/A
    12 Upfront Fees
    13 Fees added to the loan
    14 Early Repayment Charge Rate (%) 0.03%
    15 Early Repayment Charge Period (years) 2.0
    16 Closure / Completion Fee
    Overpayments (optional)
    17 Monthly Overpayment Amount £200.00
    18 Overpayment/Offset effect on Payments Keep Same
    Offset Mortgage (optional)
    19 Average Offset Current Account Balance
    20 Initial Offset Savings Balance
    21 Monthly Payment into Offset Savings
    Compare Offset/overpayment against savings or ISA
    22 Savings account Gross rate for comparison
    23 Select tax band for savings ISA/No Tax

    I was trying to work out how much I would save if I overpayed by £200 per month (reducing term) and it says I would save ££31,676.77 in interest but if I just saved that money I worked out that over 24 years I would have £57,600 saved. I can't get my head round this. Can anyone help explain please?
Meet your Ambassadors

Categories

  • All Categories
  • 343.1K Banking & Borrowing
  • 250.1K Reduce Debt & Boost Income
  • 449.7K Spending & Discounts
  • 235.2K Work, Benefits & Business
  • 607.9K Mortgages, Homes & Bills
  • 173K Life & Family
  • 247.8K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards