the amortization schedule formula

What is amortization?

The term amortization is defined as the process of paying off a loan (or mortgage) over time through regular specifically structured payments. Each payment is calculated with the previous remaining balance amount and is split into two portions. The first portion of a payment goes towards interest while the remaining amount is applied towards principal balance. The loan amortization schedule determines each payment's interest percentage and principal percentage. Usually every payment should be the same amount, but the last one may vary just a little bit. If the loan cannot be paid off within a short period, lending institutions and lenders will amortize your loan. Because of the model's simplicity, mortgages are typically amortized loans.

While calculating an amortizing loan the main variable of every installment is the principal portion and from every periodic payment the remainder portion goes to interest. Interest is charged for the use of the lender's money until you repay your debt and the remaining balance is the amount that you actually owe. The exact monetary amount put towards the principal balance is revealed by an amortization schedule as well as the specific amount put towards interest. A larger portion of each payment is devoted to interest in the beginning of the loan's life and as the loan matures, more money goes towards paying off the principal.

What is the amortization formula?

This is the loan amortization formula used to calculate periodic payments:
Amortization formula

Once we have determined the periodic payment amount using this loan amortization formula, we have to determine the allocation of each payment's principal amount and interest amount. Subtracting the amount of interest paid on the remaining balance from the periodic payment amount gives us the principal amount paid for that period. For this we will have to determine the amount of interest due for the first payment period by the following three steps:

By repeating these calculation steps for every payment (of course we will use the remaining balance for the next interest due calculation as the outstanding principal) we will generate the amortization schedule of an amortizing loan.

What is an amortization schedule?

A table detailing every single payment portions during the life of the loan is called an amortization schedule and is created by an amortization calculator. The amortization schedule generated for a loan or mortgage is also referred as an amortization table or an amortization chart (view an online mortgage amortization calculator with extra payments). There are plenty of online amortization schedule calculators available on the Internet for free use, but we have collected the best ones on our amortization schedule calculators page. Also this payment calculator website offers a mortgage payment calculator a loan payment calculator and an auto payment calculator as well. Chronological order is important during the calculation as the first payment takes place exactly one payment period (e.g.: a month) later than the loan's starting date (there are no payments for a whole period). Interest-paid-to-date and principal-paid-to-date should be also reveled by the amortization calculators.

excel amortization formula

How to create an amortization schedule in Microsoft Office Excel?

First to determine the periodic payment amount we will use Excel's PMT function. Since we don't know the payment periodicity, we will need to adjust the periods number and the interest rate to the adequate values within the PMT function itself. In a new spreadsheet we will enter the following data: in the C3 cell the loan amount (e.g.: $100,000), in the C4 cell the loan length (e.g.: 30 for a 30-year mortgage), in the C5 cell the APR (annual interest rate) (e.g.: 6.75%) and in the C6 cell the payments number per year (e.g.: 12 for monthly payments). The PMT function now looks like PMT(Rate,NPER,PV,FV,Type) where Rate is C5/C6 and NPER is C4*C6 and PV should be entered as a negative number (e.g.: -100,000). In our example Rate (C5/C6) is 0.5625% or 0.005625 and NPER (C4*C6) is 360. PV is entered as -C3 (so the answer will be positive). The calculated PMT(C5/C6;C4*C6;-C3) monthly payment will be $648.60.

To calculate the interest and principal components we will use some built-in functions. The
Monthly Interest Payment = Principal Balance * Monthly Interest Rate,
Monthly Principal Payment = Monthly Payment - Monthly Interest Payment
With these loan amortization formulas the interest portion of the first period will be $100,000 * 0.005625 = $562 and the principal portion will be $648.60 - $562 = $86.10. The second payment should be calculated with the remaining principal balance (in our case $100,000 - $86.10 = $99,913.90), the third periodic payment with $99827.32 remaining balance and so on. Note that the last payment will be a slightly different.

openoffice amortization formula

How to create an amortization schedule in OpenOffice Calc?

Thanks to the compability of OpenOffice with Excel, the amortization formula for the OpenOffice Calc is the exactly same as the one for Excel. If you do not wish to enter all the formulas and details separately you can always download a free pre-made amortization calculator for OpenOffice Calc from the website and this is the direct link to the OpenOffice amortization calculator template itself.