Amortization Payment Calculator by maryfuson


									                        Amortization Schedule Websites, choose Mortgage Loan Calculator

              Amortization Calculation Formula
                                             - Jon Wittwer
                                             Vertex42, LLC

The formula for amortization calculation is really quite simple. I'll try to explain it without too much
financial or accounting jargon.

When talking about loans, amortization is the process of paying off a loan. Each time you make a
payment you pay some interest along with a part of the principal. The principal is the original loan
amount, or the balance that you must pay off. By making regular periodic payments, the principal
gradually decreases, and when it reaches zero, you're done.

Usually, whether you can afford a loan depends on whether you can afford the payment each period
(commonly a monthly payment). So, the main amortization formula is the calculation of the payment
amount per period.

Amortization Calculation
The formula for calculating the "Payment Amount per Period" (A) is based upon the "Loan Amount"
(P), the "Interest Rate per Period" (r), and the "Total Number of Payments or Periods" (n). The
calculator below uses the following amortization formula:
                       Amortization Schedule Websites

                                Amortization Payment Calculator
                                  Loan Amount, P      20000.00

                                   Interest Rate, r   0.00625
                                                                 0.01 = 1%
                          Number of Payments, n       60

                              Payment Amount, A

Example: What would the monthly payment be on a 5-year, $20,000 car loan with a 7.5% annual
interest rate? We'll assume that the original price was $21,000 and that you've made a $1,000 down

1 Period = 1 Month
P = $20,000
r = 7.5% per year / 12 months = 0.625% per period
n = 5 years * 12 months = 60 total periods

Using the amortization calculator, the Payment Amount (A) is $400.76 per month.

Calculating the Monthly Payment in Excel
Microsoft Excel has a number of built-in functions for amortization formulas. The function
corresponding to the formula above is the PMT function. In Excel, you could calculate the monthly
payment using the following formula:

                          =PMT(r,n,P) or =PMT(0.075/12,5*60,20000)

Calculations in an Amortization Schedule
When you know the regular payment amount, it is easy to create an amortization schedule. The
example below shows the first 3 and last 3 payments for the above example. Notice how much more
interest you pay in the beginning than at the end of the loan!

The Interest portion of the payment is calculated as the rate (r) times the previous balance, and is
usually rounded to the nearest cent. The Principal portion of the payment is calculated as Amount -
                           Amortization Schedule Websites
Interest. The new Balance is calculated by subtracting the Principal from the previous balance. The
last payment amount may need to be adjusted to account for the rounding.

A good amortization schedule will show you how much interest and principal you are paying each
period, and usually an amortization calculator will also calculate the total interest paid over the life of
the loan. Besides considering the monthly payment, you should consider the term of the loan (the
number of years required to pay it off if you make regular payments). The longer you stretch out the
loan, the more interest you'll end up paying in the end. Usually you must make a trade-off between
the monthly payment and the total amount of interest.

You can use the Simple Interest Amortization spreadsheet to create your own amortization schedule
and see how the interest rate, payment period, and length of the loan affect the amount of interest
that you pay.

Note: This article is meant for educational purposes only. We believe the calculations to be correct, but please
consult your financial advisor or lending institution before making any final financial decisions.

Web-based Amortization Calculator with schedule.
Auto Loan Amortization Calculator
Balloon Loan Amortization
Home Equity Loan Calculator


Amortization Calculator, From

                   Amortization Formulas in Excel
                                                   - Jon Wittwer
                                                   Vertex42, LLC

This article lists some of the built-in Excel formulas that can be used for amortization calculations.
You can download the Excel templates to see examples of how the formulas might be used. Most of
the examples apply to loans, because amortization generally refers to paying off a loan through
regular installments (payments). In the beginning, you are mostly paying interest, but as the principal
(the amount still owed) decreases, more of the payment is going towards paying off the principal
rather than interest.

List of Excel Amortization Formulas
Excel's help file does a good job of explaining the following functions, but the spreadsheet examples
will demonstrate how these formulas might be used.

        ISPMT(rate,per,nper,pv) - The amount of interest paid during a specific period.
        PMT(rate,nper,pv) - The amount of the periodic payment
        NPER(rate,pmt,pv) - The number of payments
        *CUMIPMT(rate,nper,pv,n1,n2,0) - Cumulative interest payment for the periods n1 through
                          Amortization Schedule Websites
       *CUMPRINC(rate,nper,pv,n1,n2,0) - Cumulative principal payment for the periods n1
        through n2

* These formulas require you to install the Analysis ToolPak, which comes with Excel but is often not installed
automatically. To install the add-in, open up Excel and go to the Tools menu > Add-Ins... and check the box next
to "Analysis ToolPak".

       rate - The interest rate per period.
       per, n1, n2 - Specific period (between 1 and nper).
       nper - The number of payment periods.
       pv - The present value of the loan (i.e. the loan amount)
       pmt - The payment per period.

Example Amortization Spreadsheets
I generally do not like to use built-in formulas unless I understand how they work. For amortization
formulas, I think the best way to understand the equations is to create amortization tables or
schedules to see what is actually going on from one payment period to the next.

To get started, the following Excel spreadsheet creates a very basic amortization table. In this
worksheet, the only special Excel formula that is used is the PMT function to determine the monthly

                           Download the Simple Amortization Spreadsheet

One thing that you should do with the above spreadsheet is look at what happens as you change the
term of the loan. Pay particular attention to the graph that compares the cumulative interest vs.
principal paid.

The following spreadsheet was made specifically to provide an example of using the PMT, NPER,
CUMIPMT, and CUMPRINC formulas. It includes two amortization tables. The first shows the
monthly payments and the second shows the cumulative sum of interest and principal from year to
year. Payments are being made monthly, but the CUMIPMT and CUMPRINC functions can be used to
calculate the cumulative totals if the interest rate is fixed and the payments are constant (assuming
no extra payments are being made).

                            Example Loan Amortization                              Download Now
                            Formulas in Excel
                                                                                File Type: .xls
                            A fixed-rate loan amortization calculator           Size: ~100 KB
                            that creates a payment schedule for monthly         Required: Microsoft
                            payments on a simple home mortgage or other         Excel 2000/XP/2003
                            loan with a term between 1 and 30 years.            License: Free (For
                                                                                Personal Use)
                            (Contains no macros)

Note: This spreadsheet and documentaion on this page are meant for educational purposes only.
We believe the calculations to be correct, but do not guarantee the results. Please consult your
financial advisor or lending institution before making any final financial decisions.

To top