Repayment Mortgage Calculator

nostobbi

Many thanks to all who've posted in response to my query. I haven't tried the formulas suggested yet - that's this weekend's task. Once again - thanks for the interest.

Nix

Howdy all,

I'm coming in on this a bit late (just saw this thread today), but would like to endorse the superb site at Mortgages Exposed. On the basis of the formulas that this site gave, I built my own calculator (Buxtonlife.com) which was able to give 'interest only' and 'repayment' comparisons with the option of overpayments and/or one-off payments.

Never really used Excel before but that spreadsheet solution sounds interesting - will give it a go!

david78

Hi Nix.

I've had a play with your mortgage calculator. Very good.

How about a calculator for doing the IRR calculation to compare mortgage deals -- there's a spreadsheet at mortgages exposed but its a bit hard to use.

Do you think understand the maths behind your mortgage has helped you to save money?

David.

Nix

Hi David,

It wasn't so much understanding the maths that really helped but actually building the calculator and then setting up various scenarios. Certainly made me appreciate how much could be saved by adjusting certain aspects of our mortgage eg overpayments/one-offs etc. It's truly amazing how such a small xtra amount per month can save you on your final interest figure!

Re the IRR calculation, I'll go and have a look at it later. The reason I built my own calc was that I couldn't find anyone else that did adjustments to the 'interest only' type of mortgages. If there are other sites offering a good calc for this IRR, is it worth doing another? What say you?

Nix

SBM_2

Most impressed with these calculations, i have been trying to work out my own spread sheet for the last few weeks, with some succes, but not got any where as near as your formula.
Is it possible to adjust this formula, to calculate the remaining capital at the end of a given month, but where the mortgage is split into part repayment and part Interest only.

Cheers Sbm

david78

Yes,

Let
r = average interest rate as a fraction (so 6% means r = 0.06)
m = term of mortgage IN MONTHS (so m = 300 for 25 year loan)
n = time into mortgage IN MONTHS (so n = 84 for seven years)
B0_rp = initial mortgage amount on repayment basis
B0_io = initial mortgage amount on interest only basis
Bn = total amount of principal owing after n months
Bn_rp = amount of principal owing after n months in repayment part
Bn_io = amount of principal owing after n months in interest only part

(be sure to put m and n in months!)

Then calculate:

rm = r/12
a = (1+rm)^m/((1+rm)^m-1)
b = (1+rm)^(m-n)/((1+rm)^(m-n)-1)

Bn = Bn_rp + Bn_op
Bn_rp = B0_rp * (a/b)
Bn_io = B0_io // this doesn't change //


The monthly Payments are:

P = P_rp + P_io
P_rp = (B0_rp-B0_io)*rm/((1+rm)^m-1) + (B0_rp-B0_io)*rm
P_io = B0_io*rm

ambercelery

Hi everyone.

I've been looking at several mortgage calculators and have been trying to find one (or even actually create an excel spreadsheet myself). Rather than just taking in the amount of the mortgage loan, the interest rate and the term - to show the current repayment and interest monthly payments - I would like to be able to enter dates between the start/end of the mortgage term - whereby some kind of activity has occurred. For example :

Mortgage start date 01/01/2004
25 years
Mortgage amount £100000
Interest rate 4.7%
(these values are used to produce currently monthy repayment/interest payment - outstanding mortgage amount - and predicted end date)

But then on say 17/04/2004 - interest rate changed to 4.8% etc - so i;d like to enter this new rate, showing the new monthly repayment/interest payment

Also, perhaps on 23/04/2004 - I make a £10000 repayment, I'd also like this to again update my monthly repayment/interest and predicted end date figures etc.
(obviously if it was a repayment of £10000 then the end date wouldnt change - just the monthly amount would reduce, but I would also like the option to enter say £10000 into an offset account - so that the calculator can show the benefit by havin this money in the offset account etc)

I know that I could do this by simply - filling in the start details. Writing down the remaining mortgage amount - then starting again with new interest rate or whatever, but i would prefer to have one spreadsheet showing all mortgage actvitiy for the whole mortgage term and how different things have affected the mortgage repayments. The current way i have of doing it - only allows full months too - so isnt as accurate as i would like - i.e. if the mortgage rate changed mid month - my calculation would have to wait until the month end - which can affect the output a lot.

Any ideas/help or links to existing tools would be greatly appreciated?

Darryl

answer for ambercelery

http://www.mortgagesexposed.com/Book_Contents/spreadsheet_summary.htm

1. download the Current Account mortgage spreadsheet
2. enter 0 as the figure in the net monthly income and expenditure cells - assuming you're not using a current account/offset mortgage
3. enter the interest rates in the Int Rate p.a column, changing it in the months that your actual mortgage rate changes
4. enter your actual payments against each month
5. add the extra lump sums you are paying in to the actual payments for that month

I've used this to track the benefits of overpayments I make into our flexible mortgage. It shows how much 'reserve' cash you have at any time, and the effect this has in decreasing the interest and/or time taken to pay off the mortgage.

Darryl.

david78

Darryl beat me to it. The mortgagesexposed site rocks. If you really want to be able to make adjustments (extra payments, changes in interest rates) part way through a month post back and I will try to explain.

I just noticed this thread's got a rating tick!

ambercelery

Thanks Darryl and David,

That spreadsheet on mortgagesexposed is almost what I am after.
I would be grateful to hear your thoughts on making payments or interest rate changes part way through a month though, as this is what i am ideally after.

Thanks