Amortization Schedule Websites
http://ray.met.fsu.edu/~bret/amortize.html http://www.amortization-calc.com/ http://www.vertex42.com/Calculators/loan-amortization-calculator.html http://www.vertex42.com/, choose Mortgage Loan Calculator
http://www.webmath.com/amort.html
http://www.locallender.info/consumer-banking/mortgage/amortizationschedule.asp http://www.forbeginners.info/mortgage/amorthttp://www.forbeginners.info/mortga ge/amortization-schedule.htmization-schedule.htm http://www.vertex42.com/ExcelArticles/amortization-calculation.html
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 Interest Rate, r Number of Payments, n
20000.00 0.00625
0.01 = 1%
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 payment. 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.
SEE ALSO:
Web-based Amortization Calculator with schedule. Auto Loan Amortization Calculator Balloon Loan Amortization Home Equity Loan Calculator
REFERENCES:
Amortization Calculator, From Wikipedia.com.
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 n2
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 payment. 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 Formulas in Excel
A fixed-rate loan amortization calculator that creates a payment schedule for monthly payments on a simple home mortgage or other loan with a term between 1 and 30 years. (Contains no macros)
Download Now File Type: .xls Size: ~100 KB Required: Microsoft Excel 2000/XP/2003 License: Free (For Personal Use)
Screenshot
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.