Closing Balance as per System

[jukkis69]

Trying to figure out (with Mint or standard ERPnext), why
Closing balance as per system = -£2,414.98 (negative)
When bank balance = 9744.65

If I export 1210 GL to excel, and sum up the Debit & Credits, the 1210 is £9744.65 on credit, surely Closing Balance as per system should be the same?
Please note that I have not got any bank transactions (or deleted all).

I thought 'Closing Balance as per System' should match the trial balance? It does not.

Or if the closing balance should be the sum of bank statement values loaded, then why is it negative -£2,414 when I have no bank entries. In this case it should be surely 0 as a starting point ?

OK -on bank reconciliation statement see this.

I have no idea what would be 'Outstanding Cheques and Deposits to clear' ?

Now my real goal is:-
We have been running ERPnext 14 (just upgraded to 15) for few years. Using for accounts, sales and purchase invoices, manufacturing etc.. However the Bank Reconciliation has been done externally with Excel (bank reconc did not find matching entries with 4 decimals, so we aborted using it). Now we would like to start to use ERPnext for bank reconciliation from 1 November 2025. Trial balance for 31st of October 2025 matches the bank balance (externally reconciled). So how could I set then Closing Balance as Per System to match the Bank Balance (reconciled externally).

[jukkis69]

Also when doing reconciliation manually using 'force clear' - the balances do not seem to change.

Furthermore, the more worrying is that Purchase Invoice (in foreign currency) that have been paid using Tickbox paid, e.g. ACC-PINV-2022-06047 do not appear in the Bank Clearance Summary At all (there is no matching payment entry against these). Only Payment entries and Journal entries are shown. Is this a bug?

Actually none of the ACC-PINV-xxx appear on the on the Bank Clearance Summary seem to appear. Certainly looks like bug when PI is paid by ticking the box 'paid'. (in base currency or foreign currency)

It looks like bug in ERPnext, not in the Mint. But if Mint becomes part of the ERPnext it will be eben bigger issue.

[nikkothari22]

The "Closing Balance as per system" is not the balance as per the GL. It's what the system thinks that the balance on the bank statement is - it calculates this by looking at the GL account balance but then subtracting all the "uncleared"/unreconciled entries. At the end of the bank rec process, the closing balance as per the system and the bank statement balance should be the same - hence the difference should be 0.

I would suggest the following if you are starting bank reconciliation midway - Set the start and end date to 1st November and see what the bank balance as per the system is - my hunch is that this does not match your bank statement's opening balance as of the 1st of November. To make it match, you would need to clear all the entries till the 1st of Nov - the bank clearance tool can help you do this in bulk. I'll fix the issue where force clearing does not update the balance at the top. Missed that when I made the feature.

[nikkothari22]

Also, let me check the Bank Clearance Summary report. I'll update on this thread when I have more information.

[jukkis69]

I dont really understand where the 'Outstanding Cheques and Deposits to Clear' numbers in Bank reconciliation come from. In my example Debits on this entry match debits on the GL Bank account entry. However Credits are different £38,890.36 vs. actual on the 1210 GL Bank Account of £41,305.34. So what causes the credits number be different?

More generically, shouldn't the idea of the Bank Reconciliation to be match the entries on the GL Bank Account with the Entries on the actual physical bank account?

If this is the case, why would there not be an entry in the database for each GL Bank Entry defining if the item has been reconciled or not (maybe there is). Then all what needs to happen is to upload the actual physical bank statement and match the physical bank account statement entries to the GL Bank Entry. If matched, GL Entry becomes reconciled. If GL entry is not found then an accounting entry needs to be made. "Closing Balance as per System" would be sum of the GL Entries that are reconciled (ticked) within the time period.

(e.g. dont' undertstand 'Outstanding Cheques and Deposits to Clear'. If posting and clearance dates for accounting payment entry are within the period, there are no outstanding... unless it means Outstanding line items in the actual bank statement --> but then why in my case thee would be any values if none is cleared, should be £0. ).

The above is the way how e.g Sage Accounting works.

Have raised fault report on ERPnext regarding Bank Clearance not showing ACC-PINV-xxx with paid flag.
frappe/erpnext#51552

[nikkothari22]

"Outstanding Cheques and Deposits to Clear" - this is the sum of all debits/credits where the voucher does not have a clearance date. Clearance date can be set either via the bank clearance tool, or when you reconcile a voucher to a bank transaction.

More generically, shouldn't the idea of the Bank Reconciliation to be match the entries on the GL Bank Account with the Entries on the actual physical bank account?

Yes, that is what the bank reconciliation tool does. When a entry on the GL Bank Account is not matched, it adds up to the "Outstanding Cheques and Deposits to Clear" value.

If this is the case, why would there not be an entry in the database for each GL Bank Entry defining if the item has been reconciled or not (maybe there is).

There is. Every voucher that makes an entry to the GL Bank Account (Payment Entry, Journal Entry, Purchase Invoice, Sales Invoice, so on) - has a date field called "Clearance Date". If this date is set, then it's marked as reconciled. The Bank Clearance Summary shows this clearance date.

"Closing Balance as per System" would be sum of the GL Entries that are reconciled (ticked) within the time period.

Yes.

If needed, I can get on a call with you to check your exact issue.

[jukkis69]

Do you think this the below is an issue generally or somehow specific to our installation? If it is generic issue (which I would find suprising as for sure many people use Purchase Invoices to enter sundry costs to system), then maybe it could be fixed first. Without it cannot Force Clear all of the GL Bank Entries to get the numbers to match.

Have raised fault report on ERPnext regarding Bank Clearance not showing ACC-PINV-xxx with paid flag.
frappe/erpnext#51552

Happy to do e.g. Teams to show the issues, but if the above is 'real' then fix first would be appreciated :-)

[nikkothari22]

I checked the "Bank Clearance Summary" and "Bank Reconciliation Statement" report - this only fetches JE and PE, not SI/PI - unlike the Bank Clearance tool which does. So definitely a bug here in ERPNext which I will fix soon.

[jukkis69]

@nikkothari22 any news to be able to see 'POS entries' ie. Purchase Invoices paid by setting 'Paid' flag?

[nikkothari22]

Hey @jukkis69 Yes I'll be working on it this week.

[jukkis69]

@nikkothari22 please note that on the ERPnext if you check the 'Include POS Transactions' flag, and if the PINV is in foreign currency (in this case USD), the Bank Clearance pulls the currency value, not the Bank Account (in this case sterling) value.

[nikkothari22]

I checked the "Bank Clearance Summary" and "Bank Reconciliation Statement" report - this only fetches JE and PE, not SI/PI - unlike the Bank Clearance tool which does. So definitely a bug here in ERPNext which I will fix soon.

Fixed here: frappe/erpnext#52675 - waiting review.