logo-v-light

Our Service

Creating an Amortization Schedule in Excel

Amortization is the process of paying off a debt over time through regular payments. An amortization schedule is a table that shows the breakdown of each payment, including the amount that goes towards the principal and the interest. Creating an amortization schedule in Excel is a simple and effective way to keep track of your loan payments and ensure that you stay on track with your debt repayment plan.

Step 1: Gather Your Loan Information

The first step in creating an amortization schedule in Excel is to gather all the necessary information about your loan. This includes the loan amount, interest rate, loan term, and payment frequency. You will also need to know the start date of your loan and the first payment date.

Step 2: Set Up Your Excel Spreadsheet

Once you have all the necessary information, it’s time to set up your Excel spreadsheet. Start by opening a new workbook and creating a new worksheet. In the first row of the worksheet, create headers for the following columns: Payment Number, Payment Date, Beginning Balance, Payment, Interest, Principal, and Ending Balance.

Step 3: Calculate the Payment Amount

The next step is to calculate the payment amount. You can use the PMT function in Excel to do this. The PMT function requires three arguments: the interest rate, the number of payments, and the loan amount. For example, if you have a loan amount of $10,000, an interest rate of 5%, and a loan term of 5 years (60 months), the formula would be:

=PMT(5%/12,60,-10000)

This will give you the monthly payment amount, which you can round to the nearest cent.

Step 4: Fill in the Beginning Balance

The beginning balance for the first payment is simply the loan amount. For the second payment, the beginning balance will be the ending balance from the first payment. To calculate the ending balance for each payment, you will subtract the principal payment from the beginning balance.

Step 5: Calculate the Interest and Principal Payments

To calculate the interest payment for each payment, you will multiply the beginning balance by the monthly interest rate. To calculate the principal payment, you will subtract the interest payment from the total payment amount.

Step 6: Fill in the Payment Date

The payment date for the first payment is the start date of the loan. For each subsequent payment, you will add one month to the previous payment date.

Step 7: Fill in the Payment Number

The payment number is simply the number of the payment. For example, the first payment will be payment number 1, the second payment will be payment number 2, and so on.

Step 8: Fill in the Ending Balance

The ending balance for each payment is simply the beginning balance minus the principal payment. The ending balance for the final payment should be zero.

Step 9: Format the Spreadsheet

Once you have filled in all the necessary information, you can format the spreadsheet to make it easier to read. You can add borders to the cells, change the font and font size, and add color to the headers.

Step 10: Save and Print the Amortization Schedule

Finally, you can save the amortization schedule as an Excel file and print it out for your records. You can also make changes to the schedule as needed, such as adjusting the payment amount or adding extra payments.

Creating an amortization schedule in Excel is a simple and effective way to keep track of your loan payments and ensure that you stay on track with your debt repayment plan. By following these steps, you can create a customized schedule that meets your specific needs and helps you achieve your financial goals.

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.