Mortgage spreadsheets
Discussion
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...
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...
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
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
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...
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 importantHere 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
Gassing Station | Finance | Top of Page | What's New | My Stuff