Enter Loan Interest Expense every month or at year-end?
While it is not wrong to book your loan interest expense at year end, my preference is to have that interest spread throughout the year.
I created a spreadsheet to summarize the monthly journal entry to record loan interest expense.
This spread sheet allows me to enter a date on the spreadsheet and then enter ONE journal entry per month to record loan interest.
To get the spreadsheet, CLICK HERE to download. (Note: This is an Excel .xlsx file.)
The spread sheet is set up for 5 separate loans but it’s easy to create new loans by duplicating the loan tabs. Each loan tab contains a different amortization table. There’s nothing fancy about these amortization tables. I used the Amortization spreadsheet that you can download from within Microsoft Excel.
The only thing remotely clever about this Excel workbook is the “Interest Journal Entry” tab. This worksheet summarizes all the monthly loan interest expense into one location for easy input.
Below is an image of the details entered into the “loan_1” tab:
Entering Loan Details
Go to the “loan_1” tab. Enter the following details in the first column under “Enter Values” on the Loan Amortization Schedule:
- Loan amount
- Annual interest rate
- Loan Period in years
- Number of payments per year
- Start date of loan
- Optional extra payments
Everything else in the worksheet should automatically populate.
Unfortunately, getting loan details from your clients may be easier said than done. However, if you can get a close estimate, your total monthly loan interest expense should not be extremely far off the mark.
Typically, I use the first day of the month in which the loan originated as the “Start date of loan” for ALL the loans tracked. Later on we will see why this is important.
This might throw off the interest a little, but for the most part, you shouldn’t be off more than a dollar per month.
Now let’s look at the “Interest Journal Entry” tab.
Important Considerations when using this spreadsheet
The values on this “Interest Journal Entry” worksheet should automatically populate once you enter a date in the yellow box.
However, there are a couple of things you must take into consideration to get the correct loan interest expense to appear.
First, the “Date” box has to have the same day of the month as the “Start date of Loan” for all loans. This is why I typically use the first day of the month when entering the “Start date of Loan.” It works and keeps life simple.
For example, you will see I used the first day of the month for each loan:
- loan_1: 3/1/2020
- loan_2: 8/1/2020
- loan_3: 1/1/2021
- loan_4: 4/1/2021
- loan_5: 6/1/2021
So, if you were entering the interest expense for October of 2021, you would enter “10/1/2021” in the highlighted yellow “Date” box. This is the ONLY way the formulas will work.
I’m sure there are formulas that could be used so any date you enter would work. But, I find simpler often works better and it is easier to troubleshoot if problems occur.
The other lines on the “Interest Journal Entry” tab (see below) can be named as how they appear on your chart of accounts. None of the formulas in this workbook rely on the values in these cells. So feel free to put anything you want in these cells.
Final Thoughts
Remember, nothing is ever perfect in the world of accounting so expect to have a year-end entry that adjusts the loan balance to the statement. It will happen. There is no point in worrying about whether your monthly loan interest expense is exact to the penny.
The goal here is to have a general idea of your monthly interest expense so you can do other things, like estimating your year-end net income for tax purposes. Getting lost in a few pennies here, or a few dollars there, is of no benefit to you or your client.
I hope you find this spreadsheet helpful. Please feel free to leave your thoughts and questions in the comment section below.