FX revaluation of open AR and AP in Excel with Copilot, Claude or ChatGPT
Month-end FX revaluation restates every open foreign-currency receivable and payable at the closing exchange rate and books the difference as an unrealized gain or loss. Under IAS 21 The Effects of Changes in Foreign Exchange Rates and under ASC 830-20 Foreign Currency Matters, Foreign Currency Transactions, receivables and payables are monetary items, so their functional-currency carrying amount must follow the rate at the reporting date, and the exchange difference goes to profit or loss in the period it arises. A US company with euro, sterling and Canadian dollar invoices open at October 31 therefore has a revaluation entry to post before it can close October.
The calculation is short, but it fails in predictable ways: a rate quoted the wrong way round, a payable revalued with the sign of a receivable, an average rate used where the closing rate is required, or last month's revaluation counted twice because it was never reversed. Copilot in Excel, Claude for Excel and ChatGPT for Excel can build the revaluation sheet and the summary entry from an aging export in a few minutes, and they make exactly these mistakes when the prompt leaves room for them. The worked example below uses seven open items, three currencies and illustrative rates, and gives the correct result to the cent: a net unrealized gain of USD 806.35, made of a USD 68.50 loss on receivables and a USD 874.85 gain on payables.
··········
WHAT FX REVALUATION OF OPEN AR AND AP DOES AT MONTH-END.
A foreign-currency invoice is recorded in the functional currency at the spot rate on the transaction date. While the invoice stays open, the amount in foreign currency does not change, but its value in US dollars moves with the exchange rate. At each reporting date the open balance is retranslated at the closing rate, and the change since the last measurement is recognized as a transaction gain or loss. When the invoice is finally paid, the difference between the settlement rate and the last carrying rate becomes a realized gain or loss.
The direction of the result depends on the side of the balance sheet. A receivable in euros is worth more in dollars when the euro strengthens, so a higher EUR/USD rate gives a gain on AR. A payable in euros costs more in dollars when the euro strengthens, so the same rate move gives a loss on AP. A common error in AI-built revaluation sheets is treating both sides with one formula.
Two scoping points decide which balances belong in the sheet. Only monetary items are revalued: open invoices, foreign-currency bank balances, loans and accrued amounts to be settled in a fixed number of currency units. Advance payments to suppliers and advances received from customers are generally treated as non-monetary items and stay at the historical rate, a point IFRIC 22 Foreign Currency Transactions and Advance Consideration addresses directly. Intercompany balances whose settlement is neither planned nor likely to occur in the foreseeable future follow the net investment rules of IAS 21 and the long-term-investment treatment of ASC 830 in consolidated statements, so they need a separate decision before the revaluation runs. The accounting background for both frameworks is covered in how foreign currency transactions are accounted for and revalued and, for the translation of foreign subsidiaries, in foreign currency translation under IAS 21 and ASC 830.
··········
COPILOT, CLAUDE AND CHATGPT IN EXCEL: DOCUMENTED REQUIREMENTS.
All three assistants work inside the workbook, which keeps the revaluation in a reviewable spreadsheet instead of a chat transcript. The requirements below were checked on the vendors' official documentation on November 8, 2026.
........
Tool | Plans documented | Excel versions | Documented limits relevant to FX revaluation |
Copilot in Excel | Commercial Microsoft Copilot subscription, Copilot Chat-eligible Microsoft 365 business or enterprise subscription, Microsoft 365 Premium, or Personal/Family with an AI credits plan | Windows, Mac, web, iPad, iPhone, Android (edit mode on Windows, Mac and web) | Calculation must be set to Automatic for edits; Microsoft advises against relying on it for finance decisions without review |
Claude for Excel | Claude Pro, Max, Team and Enterprise | Excel on the web, Windows (Microsoft 365, build 16.0.13127.20296 or later), Mac 16.46 or later | Not recommended for audit-critical calculations without verification; no data tables, macros or VBA; not on iPad or Android |
ChatGPT for Excel | Generally available across ChatGPT plans since May 5, 2026; off by default in Enterprise, Edu and Teacher workspaces until an admin enables it | Not listed on the announcement page | OpenAI notes that complex formulas or edge cases may still need manual refinement; asks permission before editing |
........
Copilot in Excel can now run on Claude or GPT models, and Microsoft documents that enterprise customers need an admin to enable Anthropic as a sub-processor before Claude models are available there. Claude for Excel answers with cell-level citations, which helps when a reviewer wants to see which rate a total used. On data handling, Anthropic states that Claude for Excel inputs and outputs are deleted on the backend within 30 days except in the cases its privacy policy lists, and that the add-in does not inherit an organization's custom retention settings. OpenAI states that data shared with ChatGPT Enterprise is not used to train its models by default. Customer names and invoice numbers in an AR aging are ordinary business data, but the retention terms should be cleared with IT before the export goes into any of these tools.
··········
FX REVALUATION WORKED EXAMPLE: OPEN ITEMS AT OCTOBER 31.
The dataset is illustrative. The company's functional currency is the US dollar and every rate is expressed as US dollars per one unit of foreign currency. The booked rate is the rate at which each item currently sits in the ledger, either the invoice-date rate or the rate of the last revaluation if that was not reversed.
........
Document | Type | Currency | Open amount (FC) | Booked rate | Booked USD |
AR-1041 | AR | EUR | 120,000.00 | 1.0850 | 130,200.00 |
AR-1057 | AR | EUR | 45,500.00 | 1.0920 | 49,686.00 |
AR-2013 | AR | GBP | 80,000.00 | 1.2700 | 101,600.00 |
AR-3008 | AR | CAD | 150,000.00 | 0.7350 | 110,250.00 |
AP-5520 | AP | EUR | 60,000.00 | 1.1010 | 66,060.00 |
AP-5534 | AP | GBP | 32,750.00 | 1.2650 | 41,428.75 |
AP-6102 | AP | CAD | 98,400.00 | 0.7300 | 71,832.00 |
........
The closing rates sit on a separate tab called Rates, with the currency code in column A and the rate in column B. They are illustrative figures, not market data; in practice they come from the rate source named in the company's accounting policy, captured at the same cut-off time every month.
........
Currency | Closing rate October 31 (USD per unit) |
EUR | 1.1050 |
GBP | 1.2580 |
CAD | 0.7210 |
........
With the open items in columns A to F of the main sheet, the revaluation needs four formula columns. The booked USD in column F should come from the subledger itself in a real close; it is recomputed here only so the example is self-contained.
G2 Closing rate =XLOOKUP(C2,Rates!$A$2:$A$4,Rates!$B$2:$B$4)
H2 Revalued USD =ROUND(D2*G2,2)
I2 Change in USD =H2-F2
J2 Gain / (loss) =IF(B2="AR",I2,-I2)Column I is the change in the carrying amount of the balance, which is what the entry posts to the AR or AP control account. Column J converts that change into its effect on profit: an increase in a receivable is a gain, an increase in a payable is a loss.
··········
PROMPTS FOR COPILOT, CLAUDE OR CHATGPT IN EXCEL.
The prompt has to fix every convention the model would otherwise guess: the rate direction, the sign by side, the rounding and the output layout. The first prompt builds the sheet; the second asks for the entry from the finished columns.
The table on this sheet lists open AR and AP items at October 31.
Functional currency is USD. Rates on the Rates tab are USD per 1 unit
of foreign currency (EUR 1.1050 means 1 EUR = 1.1050 USD).
Add four columns using formulas, not typed values:
G Closing rate: look up column C on the Rates tab.
H Revalued USD: open FC amount x closing rate, rounded to 2 decimals.
I Change in USD: H minus booked USD in F.
J Gain/(loss): equal to I for AR rows and to minus I for AP rows.
Do not change columns A to F. Add totals for AR, AP and net under
the table, and list any row whose currency is missing from Rates.From columns I and J, prepare the October 31 FX revaluation journal
entry in a new sheet: one line for the AR control account, one for
the AP control account and one for Unrealized FX gain/loss.
Show debits and credits in separate columns and confirm that total
debits equal total credits. Then prepare the reversing entry dated
November 1. Explain each amount by referring to the cells you used.Asking the assistant to explain each amount by cell reference turns its answer into a review trail. Claude for Excel returns cell-level citations by design; with Copilot and ChatGPT the request has to be explicit.
··········
EXPECTED RESULT OF THE FX REVALUATION.
These are the correct figures for the example, computed independently in Python. An AI-built sheet that does not match them to the cent has a formula problem.
........
Document | Revalued USD | Change in USD | Gain / (loss) |
AR-1041 | 132,600.00 | 2,400.00 | 2,400.00 |
AR-1057 | 50,277.50 | 591.50 | 591.50 |
AR-2013 | 100,640.00 | -960.00 | -960.00 |
AR-3008 | 108,150.00 | -2,100.00 | -2,100.00 |
AP-5520 | 66,300.00 | 240.00 | -240.00 |
AP-5534 | 41,199.50 | -229.25 | 229.25 |
AP-6102 | 70,946.40 | -885.60 | 885.60 |
Total AR | 391,667.50 | -68.50 | -68.50 |
Total AP | 178,445.90 | -874.85 | 874.85 |
Net | 806.35 |
........
By currency, the euro items produce a net gain of USD 2,751.50, sterling a net loss of USD 730.75 and the Canadian dollar a net loss of USD 1,214.40, which again totals a net gain of USD 806.35. Gross gains are USD 4,106.35 and gross losses USD 3,300.00; both figures are useful if the company presents or discloses gains and losses separately.
The October 31 entry posts the net change to each control account:
........
Account | Debit (USD) | Credit (USD) |
Accounts payable (control) | 874.85 | |
Accounts receivable (control) | 68.50 | |
Unrealized FX gain/loss | 806.35 | |
Total | 874.85 | 874.85 |
........
Many teams reverse the revaluation on the first day of the next period, so that open items return to their invoice-date rate and next month's revaluation starts again from the booked amounts:
........
Account | Debit (USD) | Credit (USD) |
Accounts receivable (control) | 68.50 | |
Unrealized FX gain/loss | 806.35 | |
Accounts payable (control) | 874.85 | |
Total | 874.85 | 874.85 |
........
The reversal method changes the split between realized and unrealized results, never the total. Suppose the customer pays AR-1041 on November 18 when the rate is 1.0990. The dollars received are 120,000 x 1.0990 = USD 131,880.00. After the reversal the receivable sits at its invoice amount of USD 130,200.00, so the settlement records a realized gain of USD 1,680.00:
........
Account | Debit (USD) | Credit (USD) |
Cash | 131,880.00 | |
Accounts receivable | 130,200.00 | |
Realized FX gain/loss | 1,680.00 | |
Total | 131,880.00 | 131,880.00 |
........
Without a reversal, the receivable would still carry the October 31 value of USD 132,600.00 and the settlement would show a realized loss of USD 720.00. Across October and November the result is identical in both methods: USD 2,400.00 recognized in October minus USD 720.00 in November equals the USD 1,680.00 recognized on settlement under the reversal method. What a team cannot do is mix the two, for example by not reversing in the ledger while the AI sheet uses invoice-date rates as the booked rate. That combination counts the October revaluation twice.
··········
WHERE AI GETS FX REVALUATION WRONG AND HOW TO CHECK IT.
Rate direction is the first thing to check. If the model divides by the rate instead of multiplying, AR-1041 comes out at USD 108,597.29 instead of USD 132,600.00, a difference large enough to spot, but a smaller currency pair can hide it. A quick test is to compare one revalued row with a hand calculation and to check that every revalued amount sits within a few percent of its booked amount.
The sign error on payables is harder to see because the totals still look plausible. If column J applies the receivable logic to every row, the net result in the example becomes a loss of USD 943.35 instead of a gain of USD 806.35. The control is a single reasonableness test: when a currency strengthens against the dollar, the AR rows in that currency must show gains and the AP rows must show losses.
The third error is the rate itself. A model asked for "the October rate" may use the monthly average, which belongs to income statement translation of foreign operations and has no place in the revaluation of monetary items. With an average EUR rate of 1.0980, AR-1041 would be revalued to USD 131,760.00 and the gain understated by USD 840.00. Keep the rates on a separate tab, labelled with the source and the date, and tell the assistant to use only that tab.
Scope errors come next: a customer deposit, a supplier prepayment or a long-term intercompany loan included in the sheet because it sits in the same aging export. The review step is to filter the export by account before the revaluation and to keep a list of accounts excluded on purpose. Rounding is the last check. Revaluing each document and rounding at row level, as the formulas above do, ties the sheet to the subledger; computing a single total at currency level can differ by a few cents, and the difference will show up in the AR or AP reconciliation.
Before posting, the reviewer should confirm four tie-outs: the open foreign-currency totals agree with the AR and AP agings by currency; the booked USD totals agree with the control accounts in the general ledger; total debits equal total credits in the entry; and the closing rates match the documented source. Drafting the entry with an assistant and then checking it follows the same discipline described in journal entries with Copilot, Claude or ChatGPT, and the revaluation fits naturally as one line of the month-end close checklist with Copilot in Excel, Claude and ChatGPT.
··········
FX REVALUATION IN EXCEL WITH AI: WHAT THE ASSISTANTS CAN AND CANNOT DECIDE.
The mechanics of the revaluation are fixed by the standards and leave little room for interpretation: monetary items at the closing rate, exchange differences in profit or loss, non-monetary advances at the historical rate. Within those rules Copilot in Excel, Claude for Excel and ChatGPT for Excel can build the lookup, the revaluation columns, the summary by currency and the draft entry, and they do it reliably when the prompt states the rate convention and the sign rule for each side of the balance sheet.
The decisions that stay with the accounting team are the ones the spreadsheet cannot see: which rate source and cut-off time the policy requires, which balances are monetary, whether an intercompany balance is part of a net investment, and whether the ledger reverses revaluations or carries them forward. The vendors' own documentation is consistent on review: Microsoft advises against relying on Copilot for finance decisions without verification, Anthropic does not recommend Claude for Excel for audit-critical calculations without verification, and OpenAI notes that complex formulas may still need manual refinement. In the example, a reviewer who knows that the correct answer is a net gain of USD 806.35, with a USD 874.85 debit to payables and a USD 68.50 credit to receivables, can approve the entry in minutes; a reviewer who does not know the expected direction of each line cannot tell a correct sheet from one with the payable sign reversed.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
