Repayment Mortgage Calculator

Darryl

Thanks ambercelery. I agree, any chance of the tweak to make adjustments (extra payments, changes in interest rates) part way through a month, David?

I would give it a try, but my A in 'O' level Maths seems like a long time ago...

Darryl.

david78

OK I'll try to explain how to do this in Excel. You will probably be able to adapt this to other spreadsheet (works, openoffice etc) without too many changes though. It will probably take you half an hour or so to set this up but you will find it worth it. I use this procedure to check statements and manage overpayments/underpayments on my flexible mortgage. Its usually
correct to a few pence a year.

You might like to print out the instructions before you start (or copy/paste them to wordpad or whatever so you can go off-line).

If you don't have a flexible mortgage or one which allows withdrawls of capital put the withdrawl column in anyway (you just won't use it). Also the spreadsheet you will build allows for MIRAS relief (ah the good ol' days) which isn't available anymore but hey you might want to use the spreadsheet to check very old statements so I have left it in. (you won't use it normally).

(1) First open a new workbook with an empty worksheet (the easy part!).

(2) Put in some headings in row 1 as follows (headings are important):

In cell A1 put date
In cell B1 put activity code
In cell C1 put withdrawl
In cell D1 put gross interest
In cell E1 put miras
In cell F1 put payment
In cell G1 put balance
In cell H1 put interest rate
In cell I1 put miras rate

Under these headings we will put the formulae which will do the
calculations. I'll say some more of what the activity code means
later. Most of the other headings should be easy to understand.

(3) Dig out an old statement and in cell A2 put in a date. e.g. put 8/9/2001
Excel will store this as a date so you can also put 8 September 2001
but I find the number notation aligns better.

(4) In cell B2 enter your first "activity code". For the start of the
calculation this should always be the letters "bf" without the quotes.
This means "brought forward". Leave cells C2, D2, E2 and F2 blank and
colour these in grey (we are not going to use them on this first row.)
For the date you have entered in step (4) enter the starting balance
in cell G1, the interest rate in cell H1 and the miras rate in cell I1. The
interest rates need to be entered as a fraction but can be formatted
by excel to look like a percent if you like. (e.g 0.06 would look like 6%).
On later versions of Excel you can just enter the number as a percent
value and this is done automatically for you. On Excel 97 and earlier
you need to format it explicitly.

(5) You are nearly ready to put the formulas in rows 3 onwards but first it
is worth noting the activity codes which you can put in column B.
The activity codes can be one of the following:

code meaning

bf brought forward
bas interest rate change
int interest accumulated
pyt payment made
adv withdrawl of capital

(6) Now you can enter the formulas for row 3 but you will copy these
down for the other rows too. It is worth noting that the formulas
go in columns D, E, and G only. The other columns A, B, C, F, H, and
I will either be left empty or will have a value typed in depending on
the particular activity code. For example for activity code "pyt" a
value will be entered in the payment column (column F). It is worth
highlighting rows A, B, C, F and H by colouring them in light yellow
to remind you these are data columns not formulas.

Here are the formulae. These "test" the value of the activity code
and do different things depending on the activity code you enter in
column B.

Enter the following in cell D3 to calculate the interest (note I have
wrapped this over two lines but you need to enter it on one line
in the formula bar.)

=ROUND(IF(B2<>"int",G2*(H2*((A3-A2)/365))
+D2,G2*(H2*((A3-A2)/365))),2)

Enter the following in cell E3 to calculate the miras interest.

=ROUND(IF(B2<>"int",MIN(G2,30000)*(H2*I2*((A3-A2)/365))
+E2,MIN(G2,30000)*(H2*I2*((A3-A2)/365))),2)

Enter the following in cell G3 to calculate the balance on your
mortgage.

=IF(B3="pyt",G2-F3,IF(B3="int",G2+D3-E3,IF(B3="adv",G2+C3,G2)))


Thats it!

To use you just fill in the data columns and copy the cells down from
row 3. There are a number of things to be aware off though.

(a) The dates in column A must be in date order obviously.
(b) when entering "bas" as the activity code, enter the new
interest rate on that day in column H.
(c) The miras rate in column I has been various values in the past
20%, 10% and is now 0%. Enter 0% or leave blank usually.
(d) Interest is accumulated continuously but is added to the
mortgage balance when the payment code is "int". You
should generally enter both "int" and a "pyt" activity codes
on successive lines for the same date which corresponds to
your monthly payment. This is very important. My monthly
payment is on the 8th of the month so I always have each
month a couple of entries:
8/*/** int
8/*/** pyt ... etc.
(e) The formulae don't deal with leap years very well. I find I have
to change the value 365 to 366 in the formula for those rows
which fall in a leap year for the spreadsheet to work.

Thats enough for now. I hope it works for you. Hopefully you will be able
to get most of it typed in first. Post back if you need help, it should be
easy to fix problems once you have the basic thing typed in.

david78

I should have said that the spreadsheet above allows extra payments to be credited on any day on the month in addition to the normal monthly payment. And that interest
rates can be changed at any time.

If you have a flexible mortgage you can also make withdrawls on any day too.

ambercelery

Many thanks for the reply David.
I have looked into creating the spreadsheet I require and will contact you if I have any problems