Invantive Control has a popular Excel formula I_EOL_BAL_PDER to retrieve the cumulative balance of a General Ledger account for a specific period. This article explains how to use it to retrieve the cumulative profit/loss across all General Ledger accounts, without specifying each of them individually.
Perform the following steps:
Log on to Exact Online.
Select one company.
Enter the formula: I_EOL_BAL_PDER(, 2017, 1, 7,,, "W")
The cumulative profit/loss for period 1 upto 7 for fiscal year 2017 is displayed in the cell.
Besides specifying “W” for profit/loss, you can also specify “B” to limit the outcome to the balance sheet. However, in that case note that you need to include the opening balance too. Instead of summing the opening balance and cumulative balance you might want to use the more powerful formula I_EOL_BAL_PDE_CLOSE.
Other filters are available as parameters, such as General Ledger account type (“solely include bank transactions”). Press the Excel function wizard button [f(x)] to get an overview when you have the cursor positioned on the formula.
The following picture shows the figures on a demo company in 2017:
Using the following formula you can retrieve the cumulative profit and loss totals per period: