Accrued Simple and Compound Interest Calculator

WorkAid presents a calculator to compute simple or compound interest for creating recurring transactions in your accounting system.

The problem is that loan contracts and convertible notes require you to pay the minimum interest the IRS requires for loans between friendly companies. The regulators do not allow interest-free loans! So, the loan contracts have clauses that spell out that interest is to be computed daily but only need to be paid back to the lender at the end date of the contract.

But in accrual accounting, you must report the accrued interest at regular intervals. The normal interval to report interest is at the end of a month since you need to show the accrued interest on the balance sheet each month.

There are three parts of the simple interest problem

1. Calculate simple daily interest rate based on a yearly interest divided by a 365-day year
2. Report the total interest for the month on the last day of the month based on the number of days in the month
3. Make the process SOMEWHAT automated so you do not forget to do this every month for every loan

For compound interest there is an additional complication

1. The computed monthly interest amount increases each month since the interest is added to the principle

There are also three other issues with the calculated interest

1. The interest reported in the first month is based on the start date of the loan, so it may be less than a full month's interest
2. The interest reported in the last month of the loan may be less than a whole month based on the end date of the loan
3. The total interest required to is calculated based on the total number of days of the loan. But the total of the month interest reported may not be the same due to rounding month interest to the nearest penny

I created this calculator because QuickBooks Online does not allow a recurring transaction that calculates the interest for a month with a different number of days. QBO also does not let me create a single recurring transaction that handles months with 28, 29, 31, or 30 days.

Because of that limitation in QBO, with simple interest, there need to be 12 recurring transactions, one for each month, with the correct amount to be added to the accrued interest at the end of each month with an additional transaction for leap years.

For compound interest, the interest amount changes every month, and you need to keep a table of amounts to report each month.

I created a calculator to compute the correct interest for each end of the month based on the daily interest rate, rounded to the nearest penny.

Hope you enjoy it

Update 10/6/2022 - fixed errors in the calculation. Added Number of Months, Column labels, and export to CSV file
Update 10/31/2022 - fixed error that did not add in the first day in the first month interest
Update 11/06/2022 - put in an end date for the loan since many contracts and notes end on a different day than the start day
Update 11/23/2022 - added a checkbox to calculate monthly compound interest table