Amortization Schedule Formula

Amortization Schedule Formula

amortization schedule formula constructionAmortization 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.

amortization schedule formula

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).

Amortization Schedule Excel Template Download

Instead of creating an amortization schedule using the formula mentioned above, we suggest you to use the ready-made built in amortization schedule templates which comes pre-loaded with your copy of Microsoft excel 2007 or 2010. We have earlier carried a blog post on this site each details all the steps acquired to load the amortization schedule excel template and use it by customizing it as per your needs. Go to our post on amortization schedule excel template download.

 

Leave a Reply

Contact Us

Contact Page: Click here
Email: info@loancalculators.in
DMCA.com

Disclaimer

Lots of approximations and assumptions have been made while developing the calculators.

Please make your own calculations before making any financial decision.