My Excel mortgage spreadsheet

Options
1363739414248

Comments

  • Locoblade
    Options
    dimwit. wrote: »
    what if the ERC differs each year of the fixed year period how can that be reflected in the spreadsheet please?

    I'm afraid it can't, you'll just need to type it in at the different levels when wanting to see how much it will cost for a particular year.

    Sorry I didn't respond before, the forum normally emails me whenever there's a post here but seemingly it's no longer working for this thread!
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade
    Options
    JD2112 wrote: »
    Hi,
    I am currently a third year maths student, and am studying the cost of money for my dissertation.
    Having struggled to find any formula for calculating the cost of a loan or mortgage for two different interest rates i have found this spreadsheet.
    The speadsheet is truly brilliant, the time and effort must have been extraordinary, and the output is exact with the online mortgage calculators.
    Having read some of your recent responses I appreciate this may be a long shot, but was there anyway I could have a copy of your formulas if you have them anywhere?
    You would of course be credited in my work for the formulas and information, and I am more than happy to pay a contribution into your paypal account.
    If you could do this it would be most appreciated as I need to show how the mortgages are calculated, and not just the output. I also understand if you would rather not disclose this information.

    Thanks.

    Hi

    All the monthly calculations are done using the standard Excel PMT function for monthly loan calculations, and the spreadsheet was originally based on the Amortization template that Microsoft supply here.

    The guts of my spreadsheet would be impossible to follow though. A lot of the complexity is for formatting purposes rather than raw number crunching with loads of nested functions that display different things depending on different inputs / options selected. For example, something simple might be in a cell that calculates that month's new capital remaining, using the following logic to display the end result:
    If this is a repayment mortgage subtract the capital for that month from last months total as well as the interest, if interest only just subtract the interest, and if the total is less than the final payment (i.e. the final month of payment), then zero the cell rather than make it negative.
    

    Even something like that requires several repeats of the same equation to provide a result for all permutations, so to follow it through just from reading the code would be almost impossible, I struggle going back through it now 5 years later and there's elements I can't remember exactly why I did what I did or how it does what it does! :)
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade
    Options
    Is anyone smart enough to demonstrate how to change the spreadsheet to accommodate:

    2.58% with 17 years and 4 months left with a balance of £83,150?

    Thanks in advance

    4 months is 1/3 of a year so just put 17.33 in for the years, change the interest rates to 2.58% (both of them - introductory period and after) and change the amount borrowed to £83150
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • fayre24
    Options
    Hi,

    Great spreadsheet.

    Just trying to get it work for an offset mortgage.

    Mortgage is £100,000 for 25 years at 4%. Payment is 527.84. When I enter initial offset balance of £20,000 and chose to reduce monthly payments (input 18) it doesn't seem to do this though I'm sure I'm misunderstanding something. The spreadsheets initial payment is still 527.84, dropping the amount gradually after that. Using the Woolwich Offset calculator, it shows (though in much less detail) that with this initial offset balance, the first payment should be £461. Why is there the difference?

    Many thanks
  • JD2112
    Options
    Cheers, thanks a lot.
    Since posting this I have looked to formulate the equations myself; not with a great amount of luck I may add. However, I have found your spreadsheet very useful indeed to check my calculations.
    So thank you for that, it's been a great help.
  • gli3731
    gli3731 Posts: 1 Newbie
    edited 6 April 2014 at 12:49PM
    Options
    Thank you for the comprehensive spreadsheet, couldn't come at a better time.

    I've tried searching the thread but haven't had much luck- a bit of a daft question but if Input 22 and 23 are about comparing the difference between an interest bearing savings account vs putting that money in an offset account, where does it spit out the comparison exactly? I must not be seeing it, could someone screenshot it with a big arrow? Nevermind, I worked out that if I left the overpayment bit at 0 then all the effect of the offset would show as either a positive value or negative value (depending on gain or loss) in the cumulative overpayment vs savings column on the first page!

    Big question though: Does this take into account compounding interest in an interest-generating savings account? It looks like it should, surely that'd be the logical thing to do, but just checking.

    Also is there a way to change the tax you'd pay on the savings interest income to exact numbers rather than the drop down menu options?

    Immensely grateful for any help!
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    Options
    gli3731 wrote: »
    Thank you for the comprehensive spreadsheet, couldn't come at a better time.

    I've tried searching the thread but haven't had much luck- a bit of a daft question but if Input 22 and 23 are about comparing the difference between an interest bearing savings account vs putting that money in an offset account, where does it spit out the comparison exactly? I must not be seeing it, could someone screenshot it with a big arrow? Nevermind, I worked out that if I left the overpayment bit at 0 then all the effect of the offset would show as either a positive value or negative value (depending on gain or loss) in the cumulative overpayment vs savings column on the first page!

    Big question though: Does this take into account compounding interest in an interest-generating savings account? It looks like it should, surely that'd be the logical thing to do, but just checking.

    Also is there a way to change the tax you'd pay on the savings interest income to exact numbers rather than the drop down menu options?

    Immensely grateful for any help!

    Hi

    Savings are calculated in the same way as a loan, so yes it is compound interest. The comparison figure is shown in the column you identified and at the bottom of the "Key Figures" table on the same page "Overpayments against savings at end of year...". The year in question is however many years you've put the Introductory Rate as.

    No way to change the tax options I'm afraid, due to all the various tax cutover points its not as simple as typing in a number so I had to make it a dropdown. In fact that reminds me that probably needs tweaking to bring it up to date as its probably got tax boundaries from a couple of years ago in there, so wont be as accurate as it could be.

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • panjamuntis
    Options
    Thanks for this awesome spreadsheet, I've searched for ages for a comprehensive calculator. This one is the best by far.

    Just a quick question, is it possible to calculate for a loan that is 'interest only fixed rate' for the first two years, then have it become a principle and interest variable rate for the remaining years?

    thanks in advance.
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 22 April 2014 at 3:12PM
    Options
    Try setting the spreadsheet to Repayment and then in the "Monthly Overpayment" column (in the monthly tab) put in a minus figure that's equal to the "Monthly Capital Paid Off" column for that month, so its effectively zeroing the capital paid that month. Do that for the 24 months to cover the 2 years, then it should in theory have the same capital remaining but the interest paid.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • panjamuntis
    Options
    Thanks for your prompt response!

    I tried doing what you suggested, but it seemed to have caused a problem with the 'cumulative offset savings' column. (The figures in that entire column remains the same, rather than accumulating an extra $2500 per month as it was previously) i.e. remaining at $65000 and not increasing an extra $2500 per month.

    Did I miss something?

    thanks again
Meet your Ambassadors

Categories

  • All Categories
  • 343.8K Banking & Borrowing
  • 250.3K Reduce Debt & Boost Income
  • 450K Spending & Discounts
  • 235.9K Work, Benefits & Business
  • 609K Mortgages, Homes & Bills
  • 173.4K Life & Family
  • 248.5K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards