top of page
  • Writer's pictureAdmin

Loan Amortization Template- Add it to your list of tools



AM Schedule Template
.xlsx
Download XLSX • 42KB

It's very likely you have outstanding long-term debt that spans across multiple years. A portion of that debt is due within 12 months (or one year) and therefore meets the definition a current liability. Without running an amortization schedule, you won't know what that balance is. But you're in luck, you now have a template to help you with that!


Loan Data

1) Loan amount- Enter the total amount of the loan


2) Annual interest rate- Interest rate on the loan


3) Loan period (in years)- Your loan documents may tell you the duration of the loan by

number of payments. Here, you'll need to input the duration in terms of years. So, with 24 payments, you have a two year loan.


4) Number of payments (per year)- It's likely that you will be making a payment every month, so this is likely to be 12 in every case unless there's a rare exception.


5) Start date of loan- This is the date the first loan payment is due.


Loan Summary

6) Scheduled payment- This should be the total amount paid every month to the bank or finance company. The formulas below will calculate principal and interest for you.


Once you've inputted all the data, the formulas should do most of the work for you.


To simplify this lesson, we'll focus on a few key calculations.


Column "Principal"- This is one of the most important columns here because it goes into your calculation of what the current portion of the liability is. Remember, short-term notes payable is concerned with the principal amount due, not interest. Interest should never be applied to the principal amount.


Column "Interest"- This column will show the amount of interest per payment. It's likely it won't agree exactly to your interest expense on your books but it should come close. Always check to make sure you're recording interest expense correctly.


Summary

I've included a sample below with made up figures.


In this example the current portion of the debt as of 12/31/23 is $41.18, and long-term debt is $18.45. 2024, which is one year after 2023, is the current amount and 2025, which is greater than one year is long-term.


It's likely you have one note payable account for each loan so it's a good idea to create two new general ledger accounts to account for the short-term and long-term portions of all your debt. Create them using the liability number series you already have. One account can be "21** short-term debt" and grouped with current liabilities, the other can be "22** current portion of long-term debt" and grouped with long-term liabilities, or the loan balances that show the total principal due.


This journal entry should help you get the balances in the correct places:

Debit/(Credit)

21*** short-term debt (41.18)

22** long-term debt 41.18


In the end the current and long-term portions should equal the total amount due on the loan, which is $59.63 at 12/31/23.



Take care! See you all in the next lesson!



22 views0 comments

Comments


bottom of page