Mortgage repayments, Excel spreadsheet

denisiw wrote:

Early in March I downloaded an Excel spreadsheet from MSE. A couple of weeks ago my hard drive packed up and I lost it. I've been searching on here for two hours and can't find it! I've searched on mortgage amortization as that was the term that was used, no luck.

Would someone point it out for me please?

Thank you.

Was it the budget planner here ?

http://www.moneysavingexpert.com/cgi-bin/viewnews.cgi?newsid1089226742,17582,

If you want to work out the monthly payment on a repayment mortgage use:

if a is annual rate in %

Monthly rate = 1 + a / 1200

For a loan of L

and n payments (300 for a 25 year term)

m = L * r^n * (r - 1 ) / (r^n -1)

Alternatively, if you want to work out how long it will take to pay off a loan of L when you can afford m per month:

n = log (m / ( m - L*(r-1))) / log (r)

Making a direct spreadsheet is very easy.
Take total amount owed at end of month, multiply by r and then subract monthly payment. Repeat for each month.