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 labor 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.

To Streamline The NPO’s Reporting Process,

Our Xero development 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. 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.

The Challenges Faced in XERO Bank Revaluations Are:

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

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

What is a Bank Revaluation?

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!

For more information, see Xero’s official article: https://central.xero.com/s/article/Foreign-currency-accounts-in-the-chart-of-accounts

Also Read: Why your business needs a robust Chart of Accounts now more than ever

Xero’s API Limitations:

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…

It’s clear that Xero by default uses the Xe.com exchange rate service.

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.

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.

Below is the Process I Followed to Reproduce the Formula for Bank Foreign Currency Revaluation in XERO:

Solutions:

To calculate bank revaluations with Xero, follow these steps:

Step 1:

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.

Step 2:

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.)

Step 3:

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

Step 4:

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

Step 5:

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

Step 6:

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.

Now, I replicate the same logic for my Custom Database on General ledger.

Let’s Break Down Each Step in More Detail

Step 1: Download Transactions from Xero via API

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.

Step 2: Generate Balance Sheet and Get Amount

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.

Step 3: Calculate the Difference for Previous Month and Report Month

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

Step 4: Get Credit and Debit Transactions for Report Period

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.

Step 5: Calculate the Difference between Debits and Credits

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.

Step 6: Compare Step 3 and Step 5 Differences

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 re-evaluations 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!