My Excel mortgage spreadsheet

edited 7 May 2010 at 9:29PM in Mortgages & endowments
471 replies 418.2K views


  • LocobladeLocoblade Forumite
    783 Posts
    Part of the Furniture 500 Posts Name Dropper
    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.

    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:
Sign In or Register to comment.
Latest MSE News and Guides

Fans demand refunds from Leeds Festival

...after Rage Against The Machine pulls out

MSE News

The 'Ask An Expert' event

Check out last week's energy-themed Q&A with MSE's experts

MSE Forum

Free museums & galleries

Where to get your culture fix across the UK

MSE Deals