When you calculate a home loan, you first want to determine what the monthly payment will be. After you have that, you can calculate the interest, principal and balance for each payment period. The result of these calculations for the entire term of the loan is an amortization table. With this amortization table, you can make informed and sound financial decisions, including what is the best way to pay off the loan early and how to save on interest over the life of the loan.
Convert your loan parameters into the necessary units for the calculations. The loan amount should be in dollars. Divide the annual percentage rate (APR) by the number of loan payments in a year. For a monthly payment plan, divide by 12, but for a biweekly payment plan, divide by 26. Convert the loan term into the total number of payments expected. For example, a 30 year mortgage on a monthly payment plan would have 360 total payments.
Example: a 30 year loan for $200,000 with monthly payments at 6 % APR Amount (L) = 200,00 Period interest (c) = 0.06/12 = 0.005 Total payments (n) = 30*12 = 360
Calculate the monthly payment (P) using the payment equation and the data you just calculated.
P = L * [c(1+c)^n] / [(1+c^n) - 1] P = 200000 * [0.005(1+0.005)^360] / [(1+0.005)^360 - 1] P = $1199.10
Calculate the interest for the first payment by multiplying the loan amount by the period interest rate. Then calculate the principal in the first payment by subtracting the interest from the payment amount.
Interest = 200000 * 0.005 = $1000 Principal = 1199.10 - 1000 = $199.10
Calculate the new starting balance for the next payment by subtracting the principal from the previous payment form the previous balance.
Balance = 200000 - 199.10 = $199,800.90
Repeat step 3 using the new balance.
Interest = 199,800.90 * 0.005 = $999.00 Principal = 1199.10 - 999 = $200.10
Calculate the next new balance as in step 4 and keep repeating until you have calculated all the payments, which means the balance will be zero.
You can easily adapt this procedure to any spreadsheet program. You can also make the initial payment calculation easier by breaking it up into parts and calculating the numerator and denominator separately, then divide.