When you book a receivable at one rate and settle at another, the difference is FX gain or loss.
fxGainLossCalc writes amountForeign × (settlementRate − bookingRate), rounded to cents.
Positive means settlement rate produced more reporting currency than booked — confirm sign with your controller.
Rates must use the same direction as your reporting conversion multipliers.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| FxTransactions | A id | Txn id |
| FxTransactions | B amountForeign | Foreign notional |
| FxTransactions | C bookingRate | Rate at booking |
| FxTransactions | D settlementRate | Rate at settle |
| FxTransactions | E gainLoss | Output |
What this script does
Per-transaction FX gain/loss from rate delta.
Prerequisites
Booking and settlement rates captured; consistent rate direction.
- Controller sign-off on formula
- Cents rounding
- Unsettled rows may lack settlementRate
Walkthrough
amt 100, book 1.0, settle 1.1 → gain 10.
Edge cases
Zero settlement rate yields negative of booked reporting amount — filter unsettled first.
- Sign convention critical
- No currency code used in math
How to test
Symmetric rate drop produces negative gainLoss.
Hardening for production
Separate realized vs unrealized; revalue open items monthly.
Variations
Use mid-market vs bank rates columns.
Full code: fxGainLossCalc()
Populate booking and settlement rates, then run fxGainLossCalc() for realized FX.
/**
* Compute FX gain/loss between booking rate and settlement rate.
*/
const TX = "FxTransactions";
// A id, B amountForeign, C bookingRate, D settlementRate, E gainLoss
function fxGainLossCalc() {
const sheet = SpreadsheetApp.getActive().getSheetByName(TX);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const amt = Number(values[i][1]) || 0;
const book = Number(values[i][2]) || 0;
const settle = Number(values[i][3]) || 0;
// positive gain when settlement converts to more reporting currency
const gainLoss = Math.round(amt * (settle - book) * 100) / 100;
values[i][4] = gainLoss;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 15: Gain/loss in reporting currency.
- Line 5: Column E output.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: FX gain/loss
- 1Rate direction matches conversion scripts
- 2Unsettled rows filtered or settlementRate blank-handled
- 3Sign convention approved by finance
- 4Amounts in foreign units not already converted
- 5Spot-check against GL entries
- 6Archive monthly results
Frequently asked questions
This assumes settlementRate exists (realized). Unrealized uses period-end rate instead.
You can — difference of reporting amounts equals this if rates apply the same way.
Keep all rates toward the entity functional/reporting currency.
Out of scope — track instruments separately.
Number('')→0 can distort — skip rows missing settlementRate.
Store both rates and timestamps on the row.
Ask finance — script only computes the delta.