Mortgage Payment Calculation

Q: I know that Excel's PMT, IPMT, and PPMT will calculate Mortgage Payment, Interest Payment, and Pricipal Payment correctly to the penny. I am trying to find out how to duplicate these calculations without the built-in functions. Can somebody tell me how it is calculated, or at least let me know where I can research this. This is driving me crazy because I want to figure it out, but it is taking me a long time. I have a mortgage of $158,381, and interest rate of 7.625%, 180 payments at the beginning of the month. I calculate the interest payment as (158,381 * (1.07625^(1/12)-1) ) = $972.83, which is close, but not the same as $1,006.38. I tried substituting the 1/12 for 30/365, which alters the result, but not much. Then, I do not even know how to calculate the principal payment. Any suggestions?

A: The way how the interest is calculated is that the first month, you compute the interest by interest rate * principal. Subtract that from your payment, and the difference is how much of the loan you've paid off that month. So now the principal is different for the following month, and you use that new value to calculate the interest, and again, the difference is how much of the loan you've paid off that month. For your case: $158,381 * (0.07625 / 12) = $1006.38 payment - interest = $1479.48 - $1006.38 = $473.10 So now you've paid off $473.10 of your loan, and you only owe $158,381 - $473.10 = $157,907.90 So the following month, the interest is $157,907.90 * (0.07625 / 12) = $1003.37. Payment - interest = $1479.48 - $1003.37 = $476.11, which is the amount of your principal you've paid off the second month. Let's go through it and do the math and see what we get. Defining variables: M = monthly payment P = principal of the loan i = interest rate per payment period (usually per month) n = number of period to pay off loan (in your case, months) The first month, you're paying off (M-Pi) of the loan--we'll call the amount you pay off each month E_i for equity bought. The second month, the principal has decreased by E_1, so you're paying off: E_2 = M - (P - E_1)i = M - [P - (M-Pi)]i = (M-Pi)(1+i) If you

follow this logic, so the following month you pay off E_3 = M - (P - E_1 - E_2)i = M - [(M-Pi)*(1+i) - (M-Pi)] i you'll find that E_k = (M-Pi)*(1+i)*(k-1) You also know that by the end of the loan, when you've made your last payment, you've paid off all the principal, i.e., P = sum (k=1,n) E_k = sum(k=1,n) (M-Pi)*(1+i)^(k-1). I'm going to invoke a little magic here and tell you that the sum = [(1+i)^n - 1] / i So now you've got: P = (M-Pi)*[(1+i)^n - 1] / i which you can rearrange to solve for M, the monthly payment, in terms of the principal, the interest rate, and the number of payments to be made. When you do that, you'll find: M = Pi (1+i)^n / [(1+i)^n - 1] .