Solved

Recurring entry for monthly loan payments where principal and interest distributions determined from amortization schedule

  • 25 May 2023
  • 2 replies
  • 131 views

Userlevel 1

Situation - a business has a long term loan, a mortgage loan, where the monthly payment is fixed and the split between principal and interest varies each month based on the mortgage amortization schedule. The monthly loan payments to the lender are set up to be made via PAD direct from the company bank account.

I can create the first monthly mortgage payment as a journal transaction and then create a recurring transaction from the journal transaction.  Generate recurring transactions will create the monthly payment journal transactions, but only the 1st payment will be correct. The finance user will need to edit the 2nd and future journal transactions and correct the principal and interest distribution amounts manually, according to the amounts from the amortization schedule.

Is there a better way to configure this?

I suppose the Balanced 1st journal transaction could be exported to Excel and the future monthly payment transactions be added into the Excel sheet with the correct split between principal and interest. Then, open the future periods and import the transactions as future period journal transactions. Finally, deactivate the future periods. The future period transactions will remain as balanced and can be released in future periods as they are opened.

It would be nice if the recurring transaction could use the amortization schedule as a source for future GL account distributions.  Something to add to the Idea topic section.

 

icon

Best answer by jdresel 5 December 2023, 22:20

View original

2 replies

Userlevel 2

Hello,

I take my yearly interest cost and divide over the year equally.  its not perfect, but it balances at the end.  Then on a yearly basis I review my transactions, tie off to statements/loan balances and re-adjust my recuring transactions for the upcoming year. 

 

Thanks,
Jeremy

Userlevel 1

Hi Jeremy,

Thanks, your suggestion is good.

What I used to do with Sage 300 was the following:

Use an Excel Amortization Schedule for each mortgage loan (payable and receivable) that calculated the P+I amounts for the term of the loan.

In a separate tab, build an import layout for the year’s payments. 

Open all the fiscal periods for the current fiscal year, then import all payments for all months in the year. Sage 300 is batch-based, so I would just post the batch into the current and future periods. 

Finally, close / lock the future periods.

For monthly loan payments that were fixed principal + interest at a variable rate, I would set up a recurring entry, but leave the interest distribution amount as $0.00. When the payment cleared the bank, or when I received the payment notification from the lender, I edited the interest distribution for the correct interest then posted.

I can do the same in Acumatica. 

I was just wondering if anyone knew of a way to create a recurring entry where the allocation of the amounts across GL accounts varied month to month.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved