How to Create an Amortization Schedule

How to Create an Amortization Schedule
••• krisanapong detraphiphat/Moment/GettyImages

When it comes to loans, amortization is the division of loan payments divided across a specific number of payments over the life of the loan, which is a set length of time. Each loan's lifetime is known information and the payment amounts between the beginning balance and ending balance are tracked through an amortization schedule.

What Is a Loan Amortization Schedule and How Is It Used?

The benefit of taking out a loan is immediate access to capital that can be repaid over time. Installment loans are commonly used to pay for big-ticket items like a house or car. Loans are also commonly used to finance education for college. When a borrower gets approved for a specific loan amount, that loan will come with terms such as the annual interest rate and minimum payment amounts.

Loans are structured so that a portion of each monthly payment installment will go toward both the principal and the interest. Terms will also be determined by the type of loan.

Not all loans are amortized. For example, credit cards offer short-term loans, but they are not amortized. Rather, they utilize revolving debt. This means the balance of debt can carry over each month.

How Do Lenders Like Sallie Mae Set Loan Terms?

Sallie Mae is one consumer banking company that offers private student loans. Sallie Mae affords many students the ability to attend college by borrowing money now and repaying the money at a later date, typically with a monthly payment frequency. When repaying the money, the student must pay both interest and principal. Student loans may be federal or private like those offered by Sallie Mae.

Eligibility for private student loans is similar to other private loans, like auto loans. They are based on creditworthiness, which means a student may need a co-signer. Sallie Mae recommends checking with an educational institution for a list of recommended lenders. You will want to know the interest rate, whether it is fixed or variable, associated fees, if there is a grace period or benefits and whether the lender is credible.

FinAid.org recommends maxing out available federal Stafford loans before seeking private loans. This is because private loans can often come with fees and longer repayment schedules, which can have the effect of increasing interest payments and increasing the total amount repaid. Private loans may have a payment period between five to 20 years, while federal loans are typically repaid in 10 and may be subsidized or unsubsidized based on need.

How Can I Make My Own Loan Amortization Schedule?

An amortization schedule can help you see how much you will spend on interest and principal payments. You can also use this information to examine the total interest and total payments that you will spend repaying a loan.

After creating your loan amortization table, you can see how changing your monthly payment will affect your loan balance. This is especially helpful for looking at how student loan deferment may impact your loan period and how extra payments will affect the loan balance.

You can create a simple amortization table using software. This will be helpful to calculate the number of months or payments until it is paid off.

How to Create an Amortization Table in Excel

Microsoft Excel has amortization schedule templates that can be customized. Alternatively, you can create one in a workbook rather than use the Excel template. Either way, the table will provide you with the necessary information regarding paying down a loan or even refinancing.

  • Set ranges in a column for the present value, percent rate and the number of payments, then label them.
  • Use the PMT function to calculate payments using recurrent interest and payment amounts.
  • The PPMT function will calculate the principal for each payment.
  • The IPMT function will calculate the interest formula, beginning balance and ending balance at each payment date with a summary overview of the loan.
  • Set the formulas for the balance and payment numbers.