Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@.

Search
  • FIRST POST
    • Locoblade
    • By Locoblade 14th Sep 08, 8:10 PM
    • 760Posts
    • 572Thanks
    Locoblade
    My Excel mortgage spreadsheet (now Mac Compatible)
    • #1
    • 14th Sep 08, 8:10 PM
    My Excel mortgage spreadsheet (now Mac Compatible) 14th Sep 08 at 8:10 PM
    Hi All


    Over the past couple of weeks Ive given a few people on here some figures to compare mortgages with including the capital left after certain timeframes and the savings of offsetting certain sums. This was done using a heavily modified version of the Microsoft Amortization Schedule spreadsheet that Ive re-written to allow several things that most of the existing online/spreadsheet calculators didn't seem to be able to do, such as:
    • Interest only / capital repayment mortgages with option to part pay
    • Offset mortgages, including average offset current account balance
    • Calculation when offset pot could pay off the mortgage remainder.
    • Monthly and one off overpayments / offset payments
    • Change of rates after introductory period expires
    • Ad-hoc rate changes for variable/tracker mortgages]
    • Additional borrowing at any time
    • A few pretty graphs
    • Edit: v1.02 allows you to compare two different mortgage products to see which is cheapest for you.
    • Edit: v1.03 adds in early redemption charges and exit fees.
    • Edit: v1.04 Comparison for savings account/ISA against offset/overpayments on Mortgage 1
      Comparison of two mortgages with differing fees added to loan, showing break even month
      Seperate fees added to loan box.
    • Edit: v1.05 Break even month now takes into consideration the upfront fees and also completion fee to give a more accurate figure.
    • Edit: v1.06 Fixes the additional borrowing feature, it now works!
    • Edit: v1.07 Fixes the total cost / total capital remaining calculation in the Comparison sheet.
    • Edit: v1.08 Fixes Cumulative Interest calculation that was previously miscalculating the first year. Also uploaded a modified version for Mac and older Excel.
    • Edit: v1.09 Fixes the same bug as 1.8 tried unsuccessfully to fix in certain circumstances, hopefully second time lucky! I've also started naming the files without the version number so that for future releases, those following direct links to my web space (rather than clicking the links here) will also get the latest version by default. Ive also added an entirely optional donate button as a few appreciative users asked/suggested this, any donations will go towards my web hosting which I've recently had to expand to accomodate the average 800-1000 downloads per month!
    • Edit: v1.10 Changed the formatting to allow end users to change column widths and hide columns if required.
    I have where possible compared the spreadsheet against other online calculators so I hope its all correct. Please only download the Mac version if the main one in the first link doesn't work with your version of Excel.

    Both downloads can be accessed here:

    http://spreadsheet.locostfireblade.co.uk

    I've put the links on a simple web page now as I intend on eventually expanding it to include stuff like an FAQ to answer some of the more common questions that get asked on here and via e-mail.

    Any feedback most welcome
    Last edited by Locoblade; 07-05-2010 at 9:29 PM. Reason: Upload of v1.10
Page 7
  • benjdr
    Great spreadsheet .

    However, I've noticed that if you set the interest rate after intro period to a lower value than the initial interest rate, mortgage repayment time goes down. Also monthly repayment period starts decreasing, so overall repayment period is less than specified.

    In your example I have selected 5.5% interest rate after 2 years - this gives mortgage repayment period of 23.1 years.

    Can you please check this?
    Originally posted by Kalimero70
    Set input 18 to "Reduce Monthly"?
    • Locoblade
    • By Locoblade 9th Jan 09, 6:53 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    I've encountered a situation I cannot replicate in the spreadsheet.

    Lets say I start a mortgage for 100k on Janurary 15th, 2009.
    For easy of numbers, the monthly payment will be 500, and are made on the first of each month.
    However, the first months payment will always be higher, because of the interest accrued from Jan 15th to Feb. i.e. Feb Feb 1st's payment will be 800, or whatever, including the 300 interest from Jan 15th to Feb.

    How do I replicate this in the spreadsheet?
    Originally posted by benjdr
    Hi Ben

    The only way to do this is to put the payments in as 500 a month and then put an overpayment in the first month only of 300. The spreadsheet works on monthly interest rate calculations not daily though, so you cant put in a mid-month starting point, and because its only monthly calculation it will never be 100% accurate to the nearest few pounds and pence unfortunately.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Locoblade
    • By Locoblade 9th Jan 09, 6:58 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Great spreadsheet .

    However, I've noticed that if you set the interest rate after intro period to a lower value than the initial interest rate, mortgage repayment time goes down. Also monthly repayment period starts decreasing, so overall repayment period is less than specified.

    In your example I have selected 5.5% interest rate after 2 years - this gives mortgage repayment period of 23.1 years.

    Can you please check this?
    Originally posted by Kalimero70
    Yep, its because you've chosen to keep the payments the same following minimum payment drops. For example if you're on an initial rate of 6% giving a monthly payment of 800, then after 2 years it drops to 5% giving a payment of 700, when keep-same is selected you'll continue paying 800 a month, i.e a 100 a month overpayment hence why it pays it off quicker
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Kalimero70
    Thanks a lot
    • dazzaofdagenham
    • By dazzaofdagenham 12th Jan 09, 2:18 PM
    • 1,233 Posts
    • 400 Thanks
    dazzaofdagenham
    a excellent spreadsheet...many many thanks

    darren
    • deefadog
    • By deefadog 13th Jan 09, 1:34 PM
    • 2,145 Posts
    • 619 Thanks
    deefadog
    Again, thanks very detailed spread sheet!

    One question, and i may have overlooked this but is there anyway i can take a lump sum off the mortgage in say year 3 of a 25 year mortgage?

  • TrikerAndBiker
    Fantastic spreadsheet, thank you
    Happy riding on two or three

    "We're not complete idiots, we do have some parts missing!"
  • Palm Centro
    Is it right that if I have a 25 year mortgage I cannot enter 25 years into the initial rate field?
    • hpuse
    • By hpuse 17th Jan 09, 12:06 PM
    • 1,093 Posts
    • 154 Thanks
    hpuse
    very simple, yet elegant and easy to use spreadsheet. Appreciate your humble and helpful support as well. cheers.
    HL
    • hpuse
    • By hpuse 17th Jan 09, 12:27 PM
    • 1,093 Posts
    • 154 Thanks
    hpuse
    May I suggest, for the ease of offset mortgage account holders, as well as to make the best use of savings on interest payable to taxman (basic and high rate tax payers)
    It will make it easier if you could please incorporate the following information in some way in the spreadsheet without affecting the calculations.

    a) recommended yearly offset saving balance (% total of the outstanding mortg balance)
    b) recommended overpayment ( I personally prefer amounts in their nearest hundreds, since it makes it easy to plan rather than a percentage )

    I'm not a technical person, so I have no idea how to suggest a solution !
    • dazzaofdagenham
    • By dazzaofdagenham 18th Jan 09, 1:00 PM
    • 1,233 Posts
    • 400 Thanks
    dazzaofdagenham
    My initial period of loan was for 18 months....how can i input that to show an accuratE repayment ?



    Thanks again
    • dazzaofdagenham
    • By dazzaofdagenham 18th Jan 09, 2:45 PM
    • 1,233 Posts
    • 400 Thanks
    dazzaofdagenham
    can you do another calculation for me ?

    The initial loan was for 155K over 20 years.
    18 Payments @ 6.59% from Nov 2007 giving payment @ 1169


    Then 221 payments @ Bank of england base rate + 1.99%

    Interested in what the new lower payment will be from april this year

    Thanks
    • Locoblade
    • By Locoblade 18th Jan 09, 9:08 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Is it right that if I have a 25 year mortgage I cannot enter 25 years into the initial rate field?
    Originally posted by Palm Centro
    Yep afraid so, because its a requirement of the calculations to have an into period smaller than the total period, but as the help bubble on that cell says, if you have a straight through mortgage with no interest variation or intro period it doesnt matter what the intro period is set to, just put the introductory interest rate and the "after" interest rate the same, then the intro period can be any number you like because it won't make a difference to the calculations.

    For example, if you pay 6% interest for the life of the mortgage, just put 6% into box 3 and box 5, then leave the intro period (box 4) as 1, or 5, or whatever, as long as its under 25.

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Locoblade
    • By Locoblade 18th Jan 09, 9:19 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    May I suggest, for the ease of offset mortgage account holders, as well as to make the best use of savings on interest payable to taxman (basic and high rate tax payers)
    It will make it easier if you could please incorporate the following information in some way in the spreadsheet without affecting the calculations.

    a) recommended yearly offset saving balance (% total of the outstanding mortg balance)
    b) recommended overpayment ( I personally prefer amounts in their nearest hundreds, since it makes it easy to plan rather than a percentage )

    I'm not a technical person, so I have no idea how to suggest a solution !
    Originally posted by hpuse
    Hiya

    Im probably misunderstanding but wouldn't those figures be subjective rather than numbers that could definitively be calculated? From a mortgage point of view the more you offset and the more you overpay the better, so there is no recommended figures other than offset/overpay the whole lot!

    Taking savings into account, the only rule you can really apply is if your savings rate is lower than your mortgage rate its always going to be better to offset all the money rather than save it. If the savings (after tax) is higher than the mortgage rate then its going to be better to save it all rather than offset. The spreadsheet already does this though, telling you how much you're saving/losing compared to a savings account you input.

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Locoblade
    • By Locoblade 18th Jan 09, 9:25 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    My initial period of loan was for 18 months....how can i input that to show an accuratE repayment ?

    Thanks again
    Originally posted by dazzaofdagenham
    Hi

    If you set the into period to 1 year then you can go into the mortgage monthly table tab and enter the intro interest rate manually for the 6 months following the first year so its calculating the intoductory rate for 18 months in total.

    HTH
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • formulaonefan
    Absolutely brilliant spreadsheet. well done and thanks for this great tool
  • _dave_
    ... The initial loan was for 155K over 20 years.
    18 Payments @ 6.59% ... Then 221 payments @ Bank of england base rate + 1.99%
    Interested in what the new lower payment will be from april this year...
    Originally posted by dazzaofdagenham
    Looks like it might drop to around 920, if base stays at 1.5% :-

    http://calc-calc-calc.net/get/calc/Mortgage-Comparison/v1/?L=155650&I1=6.59&Yrs1=1.5&SVR1=3.49&Term1=20&Yrs2 =19.99999&SVR2=6.59

    [I entered 155,650 to get your monthly payment of 1169, for better comparison...]

    HTH!
    • hpuse
    • By hpuse 19th Jan 09, 11:37 AM
    • 1,093 Posts
    • 154 Thanks
    hpuse
    Good day Locoblade!
    Yes, I didn't put my point across clearly, which I think is better demonstrated with an example.

    Assume the scenario of 100K outstanding balance on an offset mortgage for 20 year term, and the current offset interest rate is 3.5% (assume this is fixed for the sake of simplicity). Also assume that there is 50K in savings and 4.5% is the best interest rate for N years locked in a fixed deposit by a different non-offset provider (which of course is taxable for both low and high rate payers)

    after year1 --> Saving of 20K offsetted saves you X giving to the taxman
    after year2 --> Saving of 30K offsetted saves you XX giving to the taxman
    after year3 --> Saving of 40K offsetted saves you XXX <-- this could be the recommended one since year 3 is greater than or equal to N year fixed rate yeild.

    after year4 --> Saving of 50K offsetted saves you YYY in tax ( this may not be the best for the saver since 4 could be greater than N !).

    You could run the above computation iteratively for different amounts and years till best figures for both saving and no: of years that beats the taxman in the shortest possible term (for 50K for N year fixed in the example). I think you can easily generalise this comparison since you know your savings amount and the number of year - both are fixed. Atleast offsetters would be expected to know.

    Its a Monday morning and thanks for adjusting with the situation of my brains !

    Hiya

    Im probably misunderstanding but wouldn't those figures be subjective rather than numbers that could definitively be calculated? From a mortgage point of view the more you offset and the more you overpay the better, so there is no recommended figures other than offset/overpay the whole lot!

    Taking savings into account, the only rule you can really apply is if your savings rate is lower than your mortgage rate its always going to be better to offset all the money rather than save it. If the savings (after tax) is higher than the mortgage rate then its going to be better to save it all rather than offset. The spreadsheet already does this though, telling you how much you're saving/losing compared to a savings account you input.

    cheers
    Originally posted by Locoblade
    • Locoblade
    • By Locoblade 23rd Jan 09, 11:59 AM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Hi hpuse

    Its now Friday and I've read the above a couple of times but I still don't fully understand the objective.

    Surely regardless of what you pay to the tax man, if you earn more after tax in your savings account than you save by offsetting on the mortgage, then the money is best off saved.

    In your example above with 50k savings at 4.5% taxable, if you're a basic rate tax payer you'll actually get 3.6% return, so you're slightly better off having the entire 50k in the savings account. If you're a higher rate tax payer though, you'll only get 2.7% return, so the entire 50k is better off in the offset.

    It seems what you're actually trying to achieve is to calculate some kind of minimum contribution to the tax man whilst still saving as much money overall, but Im not sure why you'd want to do that if you can actually earn more overall by saving the whole lot, despite paying more tax?
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • deefadog
    • By deefadog 23rd Jan 09, 1:14 PM
    • 2,145 Posts
    • 619 Thanks
    deefadog
    Hi mate - One question, and i may have overlooked this but is there anyway i can take a lump sum off the mortgage in say year 3 of a 25 year mortgage?

    As i have done this and want this to reflect in the spread sheet?

    Thanks

Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

139Posts Today

1,463Users online

Martin's Twitter