"real" overpayment spreadsheets

Lister_2

Now our new mortgage is in place I want to start tracking it accurately in a spreadsheet.(to the penny if poss). I've played with lots of overpayment spreadsheets which are great for seeing the effect of theoretical overpayments, but now I need something a little more accurate and comprehensive.

Basically I need something that lets me add ad-hoc / differening overpayments on specific dates, and it works out things like:
Capital remaining,
Interest saved by that overpayment
Interest saved to date
Current daily interest
etc etc

I was thinking a graph would be nice too showing standard term and, not just the current reduced term, but how that term has reduced over time (maybe, bit sketchy on that :rotfl:). Would just be nice to see an overview of how the balance has reduced over time.

Now I'm not afraid of making spreadsheets. Indeed, I seem to be developing a strange addiction to them which is apparently a hazard of hanging around these parts for too long. However, my knowledge of how mortgage companies work out interest etc is sketchy to say the least, so I was wondering if there are already any all singing and dancing spreadsheets out there that I can either use, or plunder for formulas (yes, I'm lazy )

One I keep hearing mentioned is Stuart's behemoth. Does this do this kind of thing? (Probly a silly question )

Many thanks.

getmore4less

The actual calculation will vary between companies so you may need to reverse engineer from data or ask you lender what they do to get 0.00p acurate calcs.

I get daily interest calculations on the statements from my lender and even these have minor monthly adjustment(1p) every now and then.

blueberrypie

Lister wrote: »

One I keep hearing mentioned is Stuart's behemoth. Does this do this kind of thing? (Probly a silly question )

It does most of what you're asking for, and I don't think it would be difficult to add in a column or two for the bits you want that aren't already in it. Why don't you have a look at it and see how it works for you? Even if you don't use it in the end, I'm sure it would help you develop your own.

Trying_to_be_good

If you want a challenge and you're working on Excel, the function for a payment is PMT, the interest is simply the outstanding balance at day x multiplied by annual interest rate/365 multiplied by (date payment made - day x).

The fab way to do an easy graph on Excel is to highlight the data you want to use and press F11. Then you can adjust the graph as you like - it's a brilliant shortcut if you have to do quick data interpretation for presentations!

I find it good exercise for my brain!

Stuart GMC is known for his spreadsheet, though I use my own one built up over the last four years (and not fit for others' eyes, unfortunately - too many weird set offs in it!).

determined1_2

Lister, I'll PM you tonight.

My SS will do just what you need but will need a little tailoring