Mortgage spreadsheets

[David A]

Am after a spreadsheet where I can:

1. Enter a loan amount
2. Interest rate
3. See how much is interest / how much paid off
4. See effect of overpayments at various times on the length of the loan(we have an annual cap on overpayments)

Or some guidance on how to construct the formula (I'm pretty nifty in Excel but a bit rubbish at interest rate calcs!)

Any ideas/thoughts? Was thinking something that had either 25 (years) rows or 25*12 (years*months) rows and showed the amount of interest, repayment net each month, then the ability to plug in an overpayment for certain months...

[j3ffers]

There was a great one on Moneysavingexpert - back in a mo for you...

Here it is - works a treat - allows overpayments in the detailed monthly tab
http://forums.moneysavingexpert.com/showthread.htm...


And here is a formula that should help to calculate a Repayment type mortgage payment, where the 5.5% would be the interest rate, 25 is the number of years, and 100000 could be the loan size.
=PMT(5.5%/12,(25*12),100000)

For interest-only, to calculate the monthly interest payment, you would use =100000*5.5/100/12

At least - that's how I use them... caveat, caveat, etc

Edited by j3ffers on Tuesday 2nd March 13:26

Edited by j3ffers on Tuesday 2nd March 13:31

[David A]

Exactly what I'm after (I think )

Cheers

Dave

[vinnie83]

Amortize is a free app on the iphone which does that if you have access to an ipohone

[FlashmanChop]

j3ffers said:

There was a great one on Moneysavingexpert - back in a mo for you...

Here it is - works a treat - allows overpayments in the detailed monthly tab
http://forums.moneysavingexpert.com/showthread.htm...

Edited by j3ffers on Tuesday 2nd March 13:26

Edited by j3ffers on Tuesday 2nd March 13:31

That link is amazing. well for me atleast. use it all the time and have it linked up to a home brewed property/rent spreadsheet so can keep track of all things important