How to Create an Amortization Schedule
(If you want to do this the easy way, go straight to LoanSpread Amortization Calculator)
An amortization schedule is simply a chart that shows you how a loan is paid off over time. Amortization schedules allow you to see the progress made with each passing payment. It also shows you the amounts of each payment that goes to pay down the principal amount and the interest the lender is receiving.
Making Amortization Schedules Should Be Easy
In a simple world, a mortgage would be made by taking the amount of money borrowed, adding in an amount for interest and then dividing the total by the number of payments. For example, in a simple world if you borrowed $100,000 at 8% for 30 years, you're monthly payment would be (100,000 + 8,000) divided by 360. If it worked this way, your monthly payment would be $300.00 per month. Unfortunately, it doesn't work that way.
Amortization Schedule Math
Here's how it really works. Your monthly payment for a $100,000 mortgage at 8% for 30 years is $733.76. Instead of paying $8000 in interest, a real mortgage will cost you $164,155.25! That's a good bit more than the amount you borrowed! If payoff the loan, you will pay a total of $264,155.25.
Each payment you make is made of two parts, the interest and the principal. The way the payment is split between these two portions varies over the term of the loan. Of course, the lender is going to get their money first. The largest part of the first payment (almost all of it, in fact) is interest. The largest part of the final payment is principal.
How this portioning of the monthly payment into interest and principal happens over time is determined by a mathematical formula more complex than the "simple world" example offered above. The formula for monthly payments is
This monthly payment formula calculates the total monthly payment which includes principal and interest. In order to determine how much of each payment is actually portioned to interest and how much to principal, other financial formulas are involved.
Make an Amortization Schedule
To create an amortization schedule these calculations must be done for each payment. It's an iterative process. For a 30 year loan, that's 360 monthly calculations to determine the amount to add to the accumulating interest and to subtract from the declining principal.
Here's part of the process that must be repeated. This formula calculates for a particular payment how much of that payment goes to interest. The difference between the monthly payment and the amount that goes to interest in a given month is the amount that remains to pay down the principal.
Interest portion of this payment = ( Principal remaining after previous payment * interest rate per payment )
Notice, this formula requires that you know how much principal remains after your previous payment! You would know that from your previous month's calculations.
Spreadsheets are handy for this sort of repeated calculations. In a nutshell, you will need create a spreadsheet for your amortization table of 360 rows for a 30 year loan.
The Easy Way to Create an Amortization Schedule
Amortization Schedules are easy to create with LoanSpread.
Try LoanSpread™ and see how quickly you can create amortization schedules!