Bank Revaluation in XERO: Calculating Accurate Balances for Foreign Currency Account Using API

Let’s first understand the context: How I met this challenge in the bank’s revaluation of balances held in foreign currency within Xero.

I was developing an application that helps track Nonprofit Organizations (NPOs) income and expenses for each donor (which is a customer record in Xero).

NPOs typically rely on donations, grants, and crowdfunding to fund their causes. Income in this context refers to grants, donations, and funds received. Expenses for NPOs include costs related to labour or consultant time, material expenses, and other costs incurred during social campaigns.

In countries like the United States and the United Kingdom, NPOs have reporting and transparency requirements with their donors and government bodies (HMRC and IRS). They need to provide donation receipts, financial statements, annual reports, program updates, transparency information, and impact stories to fulfill these requirements for their donors. These reports must provide a donor organization with a clear idea of how grants were used, how much balance is still sitting in the bank, and where the grants were used, along with receipts for each expense and bill.

Our team developed an application that extracts general ledger transaction data from Xero using an API. The web application focuses on generating Profit and Loss report (P&L) and Balance Sheet (BS) reports for each donor.

Furthermore, our application offers a user-friendly interface where users can interact with the reports. In the P&L report, users can click on specific accounts and amounts to access a transaction list. This feature provides a detailed breakdown of transactions, including dates, amounts, and corresponding transaction receipts.

To enhance transparency and donor engagement, we built a custom dynamic system that ensures each donor can only view their grant-specific data in the format of a profit and loss statement and balance sheet report. In order to recreate both reports, we downloaded all of Xero’s GL transactions via API into database tables. Then We used Xero’s tracking category feature to bifurcate the data between each donor. And map the actual donor to Xero’s tracking category. Please check the above screenshot for more clarity on flow.

A Custom donor’s Portal improves transparency and enables donors to track the impact of their contributions. By developing this dynamic system around Xero, I aimed to provide NPOs with a user-friendly platform for financial reporting and donor engagement in XERO Accounting.

I came across a challenge related to bank revaluations in XERO, which was a new concept for me as a XERO API Integration Programmer.

If you’re already familiar conceptually with bank revaluation, then you can skip the next few paragraphs.

Bank revaluation is the adjustment of bank account balances to reflect changes in exchange rates or other factors. In Xero, bank revaluations enable businesses to update their bank account balances accurately, especially when dealing with multiple currencies. This process helps maintain accurate financial reporting and facilitates informed decision-making.

In the context of Xero, which is accounting software, bank revaluations are a feature that allows users to automatically update their bank account balances to reflect changes in currency exchange rates. Xero provides tools and functionalities to perform this revaluation process efficiently and accurately.

By using Xero’s bank revaluations feature, businesses can ensure that their financial records are up-to-date and accurately reflect the current value of their bank accounts, especially when dealing with multi-currency bank accounts. This process helps maintain accurate financial reporting and enables businesses to make informed decisions based on real-time financial data.

If you have any further questions or need more specific information, feel free to ask our experienced Xero API developers!

In Xero’s existing Foreign Currency Gains and Losses report, there is a feature that shows bank revaluation. However, without obtaining the specific exchange rate for a given date, I cannot accurately populate the revalued amount for foreign currency banks using the API.

To ensure the correct revaluation of foreign currency bank accounts, I need access to the exact exchange rate data for the relevant period. This information is crucial for accurately reflecting the updated values in the reports.

By obtaining precise foreign exchange rate data, I can calculate the Foreign currency balance in Base currency. But…

In order to display the same bank-revalued balance as Xero, I initially considered using the Xe.com paid exchange rate service that Xero uses. 

However, it was costly for my client, and there was no guarantee that I would obtain the exact exchange rate that Xero uses. This is because Xero’s method of using the FX (foreign exchange) rate differs from how I needed to use it. I had to use the end-of-day exchange rate instead of real-time rates.

“This situation created complexity in our system. The balance sheet report filtered by the donor created within Xero showed different amounts compared to the custom report created in the donor’s portal. This discrepancy was unacceptable to the client because the amounts had to match Xero’s records.”

To tackle this challenge, I conducted extensive searches on Google and even sought assistance from Open AI Chatgpt. I have seen a number of threads under the Xero Community on unrealized foreign currency calculations on foreign currency bank accounts, but unfortunately, none of the solutions worked. Eventually, I decided to take a different approach.

To calculate bank revaluations with Xero, follow these steps:

Go to login.xero.com and select the accounting section.

Choose a bank account that is in foreign currency (follow the steps below)

1: Go to the Reports menu.

2: Find the Account transaction detail report.

3: Accounts dropdown Choose a bank account that has foreign currency transactions and submit.

Select the foreign currency bank account and month to review the credits and debits for that specific period. (here, you can also choose any month based on your requirements.)

Calculate the closing balance for the current month by using the formula

Based on the below example, let’s calculate Bank Revaluation data:

Closing balance = Opening balance + Receipts – Payments
Closing balance = $0 + 1,071.84 – $862.16
Closing balance= $209.68

Also, In Revalued balances, calculate the balance and differentiate between the Opening balance and the Closing balance. 

Net Movement = Opening Balance- Closing Balance
Net Movement = $0 – $209.68
Net Movement= $218.79

Go to reporting.xero.com and select the client report for the chosen month. Check the Unrealized Gain, which in this example is $9.11

In accounting, Go to the income statement. Check the Bank Revaluation Data.

Navigate to the transaction report and calculate the bank revaluation data by subtracting the Net Movement from the Closing Balance.

Go to the transaction report and we calculate

Bank Revaluation Data = Net Movement – Closing Balance
=$218.79-209.68
=$9.11

Xero Programmer’s guide to calculate bank revaluation outside of Xero within your own databases.

Based on the above step by step formula which was used to decode the logic used by XERO to calculate Bank Revaluation.

Using Xero’s API, you can retrieve various financial transactions such as invoices, bills, bank transactions, and credit notes. This data forms the basis for your bank’s revaluation process.

You generate a balance sheet report using Xero’s API for a specific month. This report provides information about your assets, liabilities, and equity. You extract the relevant amount from this report.

By subtracting the balance sheet amount of the previous month from the balance sheet amount of the reporting month, you calculate the difference.

You retrieve all credit and debit transactions that occurred during the reporting period from the General Ledger database table. This typically involves selecting a specific month (e.g., June) and collecting transactions like bank transactions, invoices, and bills.

You calculate the difference between the total debits and total credits for the transactions obtained in Step 4. This difference represents the net effect of financial transactions during the specified period.

Finally, you compare the difference obtained in Step 3 (difference in balance sheet amounts) with the difference obtained in Step 5 (net effect of transactions). The resulting difference reflects the impact of currency fluctuations on your financial transactions, leading to the bank revaluation amount.

Remember that International currency exchange rates and financial transactions can be complex, and factors such as exchange rate fluctuations, multi-currency transactions, and other financial nuances need to be considered. Additionally, accurate implementation requires thorough testing and consideration of any specific business requirements or scenarios.

It’s worth noting that financial processes can vary, and if you’re dealing with sensitive financial data, you should ensure that you’re following best practices for data security and compliance.

Conclusion 

In conclusion, By following the steps outlined, users can calculate Bank Revaluations in XERO and ensure alignment with the platform’s reporting. This helps maintain accurate financial records and facilitates informed decision-making for businesses.

Overall, the application developed for NPOs, combined with the calculated bank revaluation in Xero, offers a user-friendly platform for financial reporting and donor engagement.

If you have any further questions or need additional assistance with the Custom XERO Integration Services, please feel free to ask!