Ask for Formula for Mortgage Interests

in this web http://www.fonerbooks.com/interest.htm, it has example to calculate mortgage.

for example: the loan = 100.000 interest 5%/year payment : 12.开发者_StackOverflow000/month

we got this table (on the web, 3rd table)

Year     Principal       Interest    Payment
One     100,000     5,000           12,000
Two     93,000          4,650           12,000
Three   85,650          4,282.5     12,000
Four    77,932.5    3,896.63    12,000
Five    69,829.13   3,491.46    12,000
Six     61,320.58   3,066.03    12,000
Seven   52,386.61   2,619.33    12,000
Eight   43,005.94   2,150.3     12,000
Nine    33,156.24   1657.81     12,000
Ten     22,814.05   1,140.7     12,000 

My question:

• What is the formula for calculating interest in the X th year. For example: I want to know how much interest I have to pay in the seventh year.

• What is the formula for calculating from year x to y for example: I want to calculate sum of interest from first year to seventh year.

This is pretty easy to solve by considering how the principal changes from year to year, so let p_n be the principal after n years (so p₀=100000 in this case). The amount that the principal decreases in a year is the payment minus the interest. The interest to be paid on year n is 0.05 * p_n-1, so the principal decreases by 12000 - 0.05 * p_n-1. So we finally get the formula

p_n = p_n-1 - (12000 - 0.05 * p_n-1) = 1.05 * p_n-1 - 12000

as a recursive equation for the amount of principal after n years.

Now, plugging into this equation the value for p_n-1 we can unfold it one step to express p_n in terms of p_n-2. Continuing on, it's pretty easy to see that

p_n = 1.05ⁿ * p₀ - 12000 * (sum of 1.05ⁱ as i goes from 0 to n-1) = 1.05ⁿ * p₀ - 12000 * (1.05ⁿ - 1) / 0.05 = 1.05ⁿ * (p₀ - 12000/0.05) + 12000/0.05.

The last comes from the well-known formula for a geometric sum and then rearranging the terms a bit. Now you have the amount of principal after n years expressed in terms of the initial loan, interest rate, and yearly payment (I didn't compute the 12000/0.05 to show how the individual number figure in the formula). Then, the interest to be paid on year n is simply 0.05 * p_n-1.

Computing the sum from year x to year y is now very simple, since there is only one term that varies as the year varies and that becomes a geometric sum.

There are all sorts of formulae for mortgage calculations here.

I know it's not quite what you asked for, but I've used these spreadsheets in the past to do my mortgage calculations. You might be able to extract the formulae from them.