Amortization Schedule Formula

Amortization Schedule Formula Amortization is the process of reduction in the outstanding loan balance with each successive monthly payment made by the borrower to the lender. An amortization schedule gives you valuable insights on how the monthly payments are being utilized for the repayment of the loan and how much interest you are paying each period to service your loan.

Amortization schedule also gives you information about the total interest paid till date to the lender. They can also be suitably modified to show the effects of extra payments – whether monthly along with the scheduled monthly payments or in the form of lump-sum payments at unspecified time intervals.

How To Create An Amortization Schedule

In case you are not very comfortable using spreadsheets like MS Excel, then we suggest you to use any of the amortization schedule calculators which are freely available on several websites on Internet. You just have to enter simple values like total loan amount, interest rate and loan period and the calculators will create a schedule mentioning exact dates on which the monthly payments are due to be paid.

But if you are more inclined towards doing it yourself then you must know the exact amortization schedule formula, that will enable you to calculate all the successive monthly payments and their components like interest payment component and principal repayment component.

Following is the amortization schedule formula which can be used in the annuity – which is same as the amount of monthly payment. Where,

• A = periodic payment amount
• P = amount of principal, net of initial payments, meaning “subtract any down-payments”
• I = periodic interest rate
• N = total number of payments

Formula For Amortization Schedule In MS Excel

Instead of using such an elaborate mathematical formula as given above, Microsoft Excel has some built in functions which can be used directly to calculate the monthly payments in a mortgage loan. The formula for the monthly payment is as given below.

Monthly payment = PMT(rate, nper, pv, fv)

Where:

• Rate = the interest rate applicable on per period basis (you have to divide the annual interest rate by 12 to arrive at the applicable monthly interest rate)
• Nper = number of periods (number of loan duration expressed in number of months)
• PV = present value of the annuity (the total outstanding loan balance in the beginning of the period)
• FV = future value of the annuity (the final value of the annuity, which is mostly zero because the final outstanding balance has to be zero).