I am looking for a formula to calculate the repayment terms for an interest and capital mortgage.?

Question

I am looking to remortgage (UK) my home and am looking for a formula I can put in a spreadsheet to calculate the monthly repayments. The loan amount is £158,000, the term is 21 years and the interest rate is 6%. I would like a formula that I can vary the term to reduce the interest paid. It is an interest and capital repayment mortgage. Thanks

Answer 1

Ok i did this yesterday, here goes:

Using excel type in your figures in columns
Then go to insert on the tool bar and click on function, then click on PMT enter your figures in the correct boxes and click ok you've got your formula! If you have any problems click on the help link on the bottom left.

Answer 2

Most spread sheet software have functions established to make the calculation simple. With my software the function is
PMT(principle, rate,term) where if you are making monthly payments you divide the rate by 12. The term is the number of payments you will be making.

The formula is as follows:

i= interest rate
n=number of years

i/(1-(1+i)**-n) multiplied by the amount of the loan

Answer 3

http://www.mortgagesorter.co.uk/mortgage

Answer 4

Try this website they have all kinds of great calculators.

http://www.dinkytown.com

good luck