Spreadsheet to show mortgage payments

byb3

I came across this spreadsheet, from Microsoft of all people which calculates what is known as the 'Amortized' payment. To keep it short, this is the monthly payment that you make towards a mortgage and stays the same throughout the whole term. At the start of a mortgage your payments are interest heavy and are paying off minimal capital, but towards the end it is the opposite way round.

The beauty of this spreadsheet is that it shows you how much capital you will be paying off and how much of it will be interest. It also shows how much of a difference a larger deposit makes on your mortgage.

http://office.microsoft.com/en-us/templates/TC010197771033.aspx
http://office.microsoft.com/en-us/templates/TC010566201033.aspx

They both do the same thing, but the first one shows you in more detail. The second one is better for the bigger picture.

It is also very useful if you want to alternate the interest rate to see how the payments will rise if interest rates also rise.

pandamonia

byb3 wrote: »

I came across this spreadsheet, from Microsoft of all people which calculates what is known as the 'Amortized' payment. To keep it short, this is the monthly payment that you make towards a mortgage and stays the same throughout the whole term. At the start of a mortgage your payments are interest heavy and are paying off minimal capital, but towards the end it is the opposite way round.

The beauty of this spreadsheet is that it shows you how much capital you will be paying off and how much of it will be interest. It also shows how much of a difference a larger deposit makes on your mortgage.

http://office.microsoft.com/en-us/templates/TC010197771033.aspx
http://office.microsoft.com/en-us/templates/TC010566201033.aspx

They both do the same thing, but the first one shows you in more detail. The second one is better for the bigger picture.

It is also very useful if you want to alternate the interest rate to see how the payments will rise if interest rates also rise.

is mortgage interest top loaded?

how does that work?

if you move 5 times you would pay a fortune in interest

bristol_pilot

pandamonia wrote: »

if you move 5 times you would pay a fortune in interest

Yes you will if you keep taking out a new 25-year mortgage for the same amount every time you move.

The Excel functions PMT(), IPMT() and PPMT() can be used to create your own spreadsheet of how much of your monthly mortgage payment each month (PMT) is interest (IPMT) and how much is repaying the captital (PPMT). [Don't forget to divide the interest rate by 12 and multiply the number of years by 12 - 'cos mortgages are paid monthly not yearly). You will soon see that you will have paid virtually nothing off your mortgage after 5 years.

byb3

pandamonia wrote: »

is mortgage interest top loaded?

how does that work?

if you move 5 times you would pay a fortune in interest

I don't understand what you are asking?

For simplicity sakes, say you have a £100,000 mortgage on a fixed rate of 5% for a 25 year term (yes that is unlikely).

Each month you would pay the same amount of £584.59, but in the first month this is paying off £416.67 in interest and £167.92 in capital.

After five years, that £584.59 would be paying off £369.98 in interest and £214.61 in capital.

Your last payment on the mortgage of £584.59 would only be paying £2.43 interest with the remainder all going towards capital!

Mort_Gage

If you thought the MS mortgage spreadsheet was useful you should really take a look at the spreadsheet written by MSE Locoblade.........it is far more impressive.

See http://forums.moneysavingexpert.com/showthread.html?t=1157173