How To Prepare a Loan Amortization Schedule

Amortization schedule

When buying a house through a mortgage, financial planning will not end with the purchase. While the loan is being paid, you as a homeowner should be aware of scheduling your payments through preparing a loan amortization schedule. A loan amortization schedule lets you see important facts that may influence the steps that you will make in your financial decisions. Aside from this, you will be able to see if there is extra money that is readily available. These are just a few of the advantages of having a loan amortization schedule.

Here are the things that you should do in preparing your own loan amortization schedule manually:

  1. Calculate the payments to be made using a mortgage calculator. Use the payment function in Excel to calculate the payments that will be made during the period of the mortgage. The formula should be =PMT(Rate, NPER;PV;FV) where Rate is the rate of interest per period, NPER is the payment period in which the annuity is paid, PV is the present value of a series of payments, and FV is the future value to be attained after the final payment. For example, for a ten year loan of $100,000 at 9% interest per annum, the formula should give you a $15, 582.01 annual payment.
  2. Prepare the loan amortization table. The table should have 11 rows and 5 columns. In Row 1 put the following heading per column: Year, Payment, Principle, Interest, and Balance. Under the Year heading, put the numbers 0 to 10 in each row. In Year 0, no other value should be inserted, except under the Balance heading. Put in the $15, 582.01 on each row under the Payment heading. Under the Interest heading for Year 1, put in the following formula =.09*100000. The value 100000 is the balance of the mortgage from Year 0. Under the Principle heading, just enter the same payment formula less interest. Then under the Balance heading, use the formula to take away the principle amount from the balance. Just copy and paste the formula in each row to apply to the next rows and get the values for the succeeding years.
  3. Compute for total interest. Add up all the values in the Interest column to see the total interest incurred during the mortgage period.

Alternatively, you can use the online mortgage payment calculator that is readily available at Enter all the values in each specific field and click the Calculate button. To show the loan amortization schedule table, click the Show/Recalculate Amortization Table button and a table will appear underneath.

Another option is to install an amortization schedule software program in your computer. This would be very useful especially for bigger and longer mortgage periods. An example of this is the LoanAmortizer Professional Edition for Windows powered computers.

Preparing your own loan amortization schedule will help prevent you from being charged unnecessary fees that your lender may hide in the rather complicated loan amortization schedule that they prepare. Having your own schedule can let you make cross-references and have your finances work for you instead of working for your bank.


Share this article!

Follow us!

Find more helpful articles: