How to Create a Loan Amortization Table in Excel

Written by james highland
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Create a Loan Amortization Table in Excel
Loans may be easily analysed by Microsoft Excel. (sign. loan sale image by L. Shat from Fotolia.com)

Loan financing is a standard aspect of many people's lives. Borrowing the funds to acquire an item is a convenient way to pay for the luxury of not having to save up in advance. Many factors affect how a loan is paid off. The interest rate and payment schedule can vary widely resulting in considerably different styles of financing. A loan amortisation table makes it easy to compare different scenarios and see how small changes in one variable can affect the total payment amount. These tables offer a big picture view of the life cycle of a loan and its long-term effects. Microsoft Excel is aptly suited to creating tables of this kind, as the program lays out data in a table style by default. The ease with which the program creates formulas makes it a logical choice for creating loan amortisation tables.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Microsoft Excel
  • Web browser with Internet connection

Show MoreHide

Instructions

  1. 1

    Download the free Amortization Schedule for Excel 1.6 spreadsheet program available by Vertex42 and CNET. The program is designed for the layman and makes it easy to create a loan amortisation table that quickly visualises the relationships between a loan's principal, its time period and interest rate. Once created, the table may be changed by adjusting any of these variables on-the-fly. The flexibility of this template makes it easy to see how the loan is affected by the occasional extra payment. It also demonstrates the consequences of a missed payment. The table supports all the popular payment schedules, from weekly to annual payments. It optionally allows payment information to be rounded.

  2. 2

    Download a Microsoft template for loan amortisation. The company that makes Excel also makes its own amortisation tables available to users. These are provided for free from the Microsoft Office website. The template takes advantage of Microsoft's own financial formulas built in to the Excel program. The variables for a fixed interest rate, payment schedule and loan principal are input and the template creates an interactive amortisation table to show all the relationships. In addition to the table itself, the Microsoft template also creates a loan summary section where the overall parameters and outcomes of the loan are spelt out. This includes the total amount of interest paid after the loan cycle completes.

  3. 3

    Buy a more robust loan amortisation package from a company such as the Spreadsheet Store, which provides a set of six Excel loan analysis spreadsheets for a fee of £16 as of March 2010. While all loans have similar parameters, the Loan Calculator for Excel package creates an amortisation schedule for many different scenarios for added flexibility to nearly any loan situation. Some loans follow a "balloon" style which requires a large lump sum at the end of the payment term. Other loans break the loan cycle up into a graduated repayment plan where the initial round of payments cover the interest only. Still other loans do not use a fixed interest rate and thus the payment sizes can change periodically. This package of spreadsheets covers all these loan situations and more so the user is ready to visualise any credit opportunity with greater clarity. In addition to the loan amortisation tables, these spreadsheets also create graphs that depict the loan parameters in a particularly visual layout.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.