Financial Amortization Schedule

As anyone a good idea on how to create or better import a complete financial amortization schedule (including future payments) inside Manager to “activate” from time to time?

Which module would you use as a workaround (since I know there’s not a specifics area for this kind of things)?

Maybe @lubos may introduce a Module similar to Quotes but for Journal Entries just to take note of present and future transactions with no effects on current BS and P&L until copied into Journal Entries? Journal Notes?

Have you considered using “Recurring Journal Entries” to implement the schedule.

Unfortunately it is not for me. The amounts to be paid vary with each time based on the signed plan.

I would like to recreate these list in Excel, to batch create them in Manager and use them when each payment becames effective.

That’s why I though that @lubos can create a tool similar to quotes but for Journal Entries. I thought it could be called Journal Notes.

Like Quotes these writings would not have any effect on General Ledger Accounts. Once they become effective one could push Copy to Journal Entry, eventually correct them with the effective numbers and save them in Journal Entry: the job is done.

What about

  • Batch create on “Amortization Entries” tab. Future dating entries could then create any schedule you want to calculate in a spreadsheet program.

  • Alternatively have a batch create in “Settings” -> Recurring … as well as support and “Interval” of “only once / delete after use”. Again an arbitrarily schedule could be created in a spreadsheet program with the user being prompted when a new schedule entry was required.

I though of that too but first of all it works only with intangible assets (I know, I can register financings as negative intangible assets but then I will have to do intensive testing to see if they don’t come up with strange behaviours) and secondly I cannot store the interests but only the loan repayment amounts.

This can be a better workaround but… in Recurring Journal Entry you cannot set expiration date like under invoices. So it would be very high the risk of forgetting what to delete.

Keep in mind that I’m not talking of two writings a month. I’m trying to automate something that is quite massive. So I want it to be error proof before implementing it.

I think that until there will not be a specific module for financing (like in many other accounting software) it is better if made a specific tool in Excel for loan mortgage and import journal entries on monthly basis with batch create.

@Brucanna, @Patch,

I’ve almost finalized a tool in Excel. Tomorrow I’ll share some screenshots.

Here it is. I’ve create an Excel Files with a sheet for each financing. In each of these identical sheets I covered all the possible variables that I can get in a loan agreement. Based on the parameters I set the description of each line is automatically generated. I also create a space insert final data from the banks without altering the automatic formulas. In this case the remaining loan mortgage plan automatically updates the remaining installments.

Then I created a reporting tool in a specific sheet that gets data through a VLOOKUP in all those sheets. I set the referal date and I set the code of each financing I want to query (the name of the excel sheet). The tool reminds me all the linked expiring loans at that date.

Each installment is composed of for lines of a journal entry so I linked them to UUIDs of Manager’s Chart of Accounts (and also to the UUID of the tracking codes):

It all ends up with an automatically generated batch create sheet:

with one line for each financing that can be pasted directly in Manager.

Two days of work that will save us at least half day of work each month with manual entry that we all know is very error prone!

1 Like