I have gotten numerous requests from individuals wondering what the simple formula is for calculating the monthly payment and also how to generate the amortization table, including the accrued interest and extra principal payments. Instead of just showing some boring source code, I thought I would try to more fully explain it.
Spreadsheet (Excel, Lotus, Quattro) users should look here
NEW! Want to see how this is derived? Find a full derivation here! (Thanks goes to "Hans" Gurdip Singh.)
NOTE: This first part is for United States mortgages. Look here for the Canadian formula.
First you must define some variables to make it easier to set up:
|
The following assumes a typical conventional loan where the interest is compounded monthly. Okay now for the big monthly payment (M) formula, it is:
J
M = P x ------------------------
1 - ( 1 + J ) ^ -N
where 1 is the number one (it does not appear too clearly on some browsers)
|
So to calculate it, you would first calculate 1 + J then take that to the -N (minus N) power, subtract that from the number 1. Now take the inverse of that (if you have a 1/X button on your calculator push that). Then multiply the result times J and then times P. Sorry, for the long way of explaining it, but I just wanted to be clear for everybody.
The one-liner for a program would be (adjust for your favorite language):
M = P * ( J / (1 - (1 + J) ** -N))
So now you should be able to calculate the monthly payment, M. To calculate the amortization table you need to do some iteration (i.e. a simple loop). I will tell you the simple steps :
Step 1:
Calculate H = P x J, this is your current monthly interest
Step 2:
Calculate C = M - H, this is your monthly payment minus
your monthly interest, so it is the amount of principal you pay for that month
Step 3: Calculate Q = P - C, this is the new balance of
your principal of your loan.
Step 4: Set P equal to Q and go back to Step 1: You thusly loop around until the value Q (and hence P)
goes to zero.
Programmers will see how this makes a trivial little loop to code, but I have found that many people now surfing on the Internet are NOT programmers and still want to calculate their mortgages! So this page was dedicated more to the latter. If you have any further questions you can contact me for more info.
n = - (LN(1-(B/m)*(r/q)))/LN(1+(r/q))
# years = - 1/q * (LN(1-(B/m)*(r/q)))/LN(1+(r/q))
Where:
P = P * (1 - ((1 + J) ** t - 1) / ((1 + J) ** N - 1))where:
min_rate = 0; max_rate = 100; # Set Maximum and minimum rate
while (min_rate < max_rate - 0.0001)
{
mid_rate = (min_rate + max_rate) / 2; # Divide by 2 to find midpoint
J = mid_rate / 1200; # Convert to monthly decimal percentage
# calculate payment based on this interest, term of F and loan_amt
guessed_pmt = loan_amt * ( J / (1 - (1 + J) ** -N ));
if (guessed_pmt > actual_payment)
{
max_rate = mid_rate; # current rate is new maximum
}
else
{
min_rate = mid_rate; # current rate is new minimum
}
}
print " The Rate is ", mid_rate;
On any modern computer this runs pretty much instantaneously.
This was contributed to me by: Mike Morley (morleym@interlog.com)
Canadian mortgages are compounded semi-annually instead of monthly like
US
mortgages.
Monthly Pmt =
(P*(((1+i/200)^(1/6)-1))/(1-(((1+i/200)^(1/6)))^-(n*12)))
Where:
P = principal outstanding
i = annual interest rate percentage
n = number of years
Here is a easier to read representation:
i 1/6
( 1 + --- ) - 1
200
Pmt = Principal x ------------------------
i 1/6 -12 x n
1 - [ (1 + --- ) ]
200
Or to convert canadian interest rates to US interest rates:
Can. Rate 1/6
US Rate = 1200 x [ ( 1 + --------- ) - 1 ]
200
or as a formula, US Rate = 1200 * ((1 + Can.Rate/200)^(1/6) - 1)
You'll note if you plug this into the US formula you get the above formula for payment.
Back to Mortgage Calculator Home Page