Morgage amortization

zzzt

Is it impossible to calculate how much interest and how much capital I pay off on my mortgage each month and each year?

I have looked up formulae for it on the internet, but I get different results to what my mortgage tells me online. I also found various spreadsheets, including one that someone made here, but it gives the same results that I calculated myself. In fact, all the results are consistent, except with what my lender says. So am I missing something?

I've tried to take into account fees and applying interest in arrears for the first month (or part of the month), but I can find no starting amount that matches up, and it is unclear from my documentation what my fees actually were. If I adjust the starting amount to get one of the months to match, then the others are off. It's only off by a few pounds each month. Is this just statistical anomaly?

It bothers me because there were many fees listed when I opened the mortgage, but none of them match the fee I actually paid, and I'd like to know what fees were added to the mortgage (if any).

Is there any maths or spreadsheet wizards willing to take a look at my figures? Or do you think if I wrote to the bank they would give me this kind of information? All I can see is the ability to request a Certificate of Interest for the year, which won't really help me. I want to know the value of the mortgage was after 0 months, 1 month etc.

ACG

http://www.amortization-calc.com/

It does not take into account the fee, but ou should have the mortgage offer which confirms the fees and which were added.

zzzt

Yes I have seen that. As I said, the problem is it doesn't match up with the actual values. The fees listed in the mortgage offer don't make sense, and it looks like one of them is listed twice.

ACG

You need to speak to the lender and find out what fees were added. That way you can alter the loan amount accordingly.

Thrugelmir

zzzt wrote: »

Is it impossible to calculate how much interest and how much capital I pay off on my mortgage each month and each year?

You'll need to use a spreadsheet. In order to factor in the variable days in a month (including leap years).

getmore4less

the issue is the calculators(and the way they set your nominal payment) all assume every month is equal and the payments happen at the same time.
back in the real world of daily interest banks and weekends things work differently.

Even if you get(or work out) the banks rate to the correct daily/monthly/compounding the above real world just messes it up compared to the calculators, you can model in a spreadsheet.

28-31 days in a month.
payment falls on a Fri/Sat/Sun can really happen Mon/Tue and in extreme cases a Wed.

ACG

But never a thursday?

getmore4less

It should not be hard to work out what cash you paid out and what debt you started with and where all that money went.

The statement from the convayancer should show most of that.
anything added to the debt by the mortgage company after drawdown should be documented.

zzzt

Well, it was hard, and I gave up.

But now I have received my statement for the first year, which has the interest and credit repayment for every month. So using this information I had another go at working it all out.

The first mystery was that the first interest payment was much higher than usual, meaning that when I make my first payment I was making a payment on a sum greater than the mortgage value. I figured that must have been fees, but it turns out no. There were no fees added to my mortgage from what I can see. I also worked out that the fees would have had to have been over £2000 to make the first payment that high.

What seemed to be the case was that the first month there was no repayment and only interest accrued. But I still couldn't get it to calculate the right amount.

Then, with some reading around on the internet and a bit of am epiphany, I managed to calculate that first interest figure. It was basically interest calculated daily, multiplied by 31 (the number of days between the start of the mortgage and day the interest was added on). Previously, I had used monthly interest, and so did all the calculators and spreadsheets online.

Unfortunately, when I copied that formula down, the results did not match up. After many hours of fiddling the formulae, my next epiphany was that the interest was being added a few days before my repayment was taken, therefore there were a few days per month where the interest was calculated on a higher balance.

Doing this allowed me to calculate the second interest amount, although however I did it, I was off by ±1p. Furthermore, when I copied this formula down, it fell apart and the values were wrong for every subsequent month.

So I spent some more hours fiddling, and I realised that the date the interest and the repayment are taken vary, I guess due to weekends and bank holidays. So there are some months where the value would be higher. Then I noticed I was using the wrong dates. I redid it, and this time I got the exact right amount for the first three interest amounts, but then it once again broke down and got it completely wrong.

The place it broke down was the first date where interest was added on the "wrong" day (over New Years). It turns out even though the payment was taken many days earlier than usual, the interest was still the same, so that's where my formula broke down.

I changed it so that the interest was always calculated as if it had been done on the normal day (even if that was a weekend), but the interest still goes up by more if the repayment comes later. This works, and it's right most of the time. There are a few days where it goes ±1p. The end result after 12 months is off by 4p.

This is pretty much useless as far as amortization goes, because I need to know whether a repayment date falls over a weekend or bank holiday, so every entry needs to be manually entered. So it's difficult to project 20 years into the future.