What formula do I use in excel to work out the monthly repayment of a mortgage? PMT does not give me the month

Question

PMT does not give me the monthly rate. My mortgae is £435,269.00 and the interest rate is 12.60% the priod of repayment will be 15 years or 180 months

Answer 1

you may want to created the calculations using excel based on the following formula:

m=12 for monthly compounded
yr=15 amortized
%int=12.6 annual interest rate
pv=435269 outstanding balance of mortgage

n=m*yr
=12*15
=180
int=%int/100/m
=12.6/100/12
=0.0105
F1=interest portion of monthly payment
=(pv)*[(1+int)^(m/12)-1]
=(435269)*[(1+0.0105)^(12/12)-1]
=4570.32
F2= monthly equal payment factor
=[1-(1+int)^(-n)]
=[1-(1+0.0105)^(-180)]
=0.84743223
pmt= monthly equal payment
pmt=interest portion + principal portion
pmt=F1/F2
=4570.32/0.84743223
=5393.14

Answer 2

Excel has included a Loan Amortization template since at least Excel 97. Look in:

C:\Program Files\
Microsoft Office\
Templates\
1033\

The file name is Loan Amortization.xlt Just double-click on it to launch a new workbook using that template and plug in the numbers.

Answer 3

There suitable mortgage calculators on some web pages attempt them for a greater precise wager yet its approximately 2,000 in keeping with month, yet for sure you will get decreased fixed costs etc Oh yeah and thats activity merely not reimbursement or endowment etc you will have greater on suitable for that............thats over 25 years i'd positioned an further a million,000 on for reimbursement

Answer 4

Whereas I agree that you should have your own amortization calculator and the ability to use it, you didn't ask that question. The answer to your question with the data you supplied is:

5393 per month (wow, that's a lot of money) for principal and interest (not including taxes and insurance and PMI if necessary)

Good luck!

Answer 5

OH my god that is one stiff payment and a very high rate is it comercial and if your there is better money at lower prices I would consider refinancing at 8 to 10% it is available . anyway look for a mortgage calculater on the internet and cutr and paste inffo in your excell file its the easiest of them all

Answer 6

I would download a mortgage amortization template from microsoft online!

Answer 7

Here is the formula

=PMT(12.6%/12,15*12,,-435269)

Answer = 822.82 /month

Answer 8

That's enough to give even men PMT...