Formula to calculate Monthly payment.

maveli

I looked at several example but couldn't work out how the capital repaymeny part is calculated.
Could anyone tell me the formula to calculate monthly mortgage payment ?

Say
L is the loan amount
I the interest
Y the term in years and
M the monthly payment


Thanks
Maveli

danm

you can do it in excel using the PMT function. In your case it would be

=-PMT(I/12,Y*12,L)

you can do it manually, but the formula is tedious and near impossible to write in a straight line.

danm

to work out the repayment part you then need to
1) work out the interest, so in your case

=L * (I/12)

then subtract this from the first equation

MickHG

Hi,This might be of some help.

Mortgage/Loan Repayment Calculations

P = Principle (Amount borrowed)....... r = Annual Interest Rate......R = (1+ r).
S = Periodic repayment....... n = Period (mth etc.)
Periodic Repayments = S = P ( R^(n+1) - R^n )
.......................................................( R^n) - 1
Note:- When dealing with Loans and Mortgages the normal repayment period is a month.
In these cases the 12 root of R would be used to represent a period of one month

In the example above if "R" = 1.1 it would be replaced by 1.1^(1/12) = 1.00797414
The period "n" would read 36 equalling 3 x 12.
In the case (n + 1). 1 is always 1 irrespective of whether the periods are months or years.
NB:- For Payments in advance, alter the bottom line of the equation to:-
R^(n+1) -R

Regards Mick

maveli

danm/Mick
Thanks very much.

Regards
Maveli

TighterThanTwoCoatsOfPain

danm wrote: »

to work out the repayment part you then need to
1) work out the interest, so in your case

=L * (I/12)

then subtract this from the first equation

Alternative would be to use the excel function =ipmt(). Syntax is very similar to the PMT() above... Dont have access to XL atm, but if you suss the PMT() one then check out the help files you're breeze it..