Free Excel templates

Foreign Currency Translation and CTA Model

Rates and local trial balances in; translated and remeasured statements, the CTA step by step and its roll-forward out, with a Checks tab.

Type the rate table and the local trial balances once, and the workbook converts both subsidiaries: a euro sales company translated at the current rate, with its translation adjustment proved step by step, and a plant that keeps its books in a weakening local currency, remeasured at historical rates with the gain proved from its net monetary position. A long-term intercompany loan feeds the roll-forward of the accumulated adjustment, year by year. In the worked case the 2026 translation adjustment is a reduction of $344,975.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 183 free models.

Foreign_Currency_Model.xlsx (30 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The Foreign Currency Accounting Handbook, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 184 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

9 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Rates
Closing, average, historical and dated rates for the euro and the local currency, and the inflation rates, in yellow cells.
Transactions
The parent's euro receivable, payable and loan interest, the subsidiary's dollar payable, the parent's side of each intercompany balance and the aggregate gain or loss in net income.
Loan
The long-term intercompany loan in the parent's dollar books, with its designation year by year: other comprehensive income or net income.
Translate
The euro trial balance translated by the current rate method, equity at historical rates, the dividend at the rate of its date, and the balancing figure.
CTA
The translation adjustment rebuilt from opening net assets, net income and the dividend, and the roll-forward of the accumulated amount.
Remeasure
The local-currency trial balance remeasured at historical rates, the gain proved from the net monetary position, and a lower of cost and net realizable value test in dollars.
Inflation
The three-year cumulative inflation test against the reference point of about 100 percent.
Sale
An illustrative sale: the accumulated adjustment released into the gain.
Checks
Thirteen ties, twelve of them able to fail on a wrong input (the step-by-step proof catches a formula typed over): opening balances against the signed prior year, the two proofs, inventory layers and five intercompany eliminations.

What the worked case returns

MeasureValue
Translation adjustment 2026, step by step: a reduction (US dollars)344,975
of which opening net assets at the fall in the euro: a reduction (US dollars)360,000
of which the dividend at its own rate, given back$40,000
Translated net assets of the euro subsidiary, 31 December 2026$6,357,425
Long-term loan: 2026 loss in other comprehensive income (US dollars)300,000
Accumulated translation adjustment, 31 December 2025: a credit$570,000
Accumulated translation adjustment, 31 December 2026: a debit (US dollars)74,975
Remeasurement gain 2026, proved from the net monetary position$55,750
Aggregate foreign currency result in 2026 net income: a loss (US dollars)53,850
Cumulative inflation of the plant's economy, 2024 to 202661.0%

The 2026 translation adjustment is a reduction of $344,975: the opening net assets lose $360,000 as the euro falls from 1.1500 to 1.0900, net income translated at the average adds a reduction of $24,975, and the dividend, which left at 1.1300, gives back $40,000. The same figure is the change in the balancing figure of the translated balance sheet: that agreement is the proof.

How to use it on your own case

  1. Enter the closing, average and historical rates, and the rate of each dated transaction, in the yellow cells of Rates.
  2. Enter the local trial balances on Translate and Remeasure, and the parent's side of each intercompany balance on Transactions.
  3. Read Translate and CTA: the balancing figure and the step-by-step build must give the same translation adjustment.
  4. Read Remeasure: the gain from the balancing figure must equal the gain proved from the net monetary position.
  5. Check that the Checks tab reads ALL PASS before you take any figure into a note or a memo.

Questions people ask about it

Why prove the translation adjustment at all?

Because it is a balancing figure: any error elsewhere in the translation lands in it. Rebuilding it from opening net assets, net income and dividends at the gap between their rates and the closing rate shows whether the number has a cause. In the case the build gives a reduction of $344,975 for 2026.

Why do the loan's exchange differences stay in equity?

Because settlement of the loan is neither planned nor anticipated, so it is of a long-term investment nature and its differences are reported like translation adjustments in the consolidated statements. Set the 2026 designation to 0 on the Loan tab and only the 2026 difference moves to net income.

Does the model decide my functional currency?

No. It converts the figures once the functional currency is decided. The decision rests on the indicators and the memo that weighs them, which the book covers in its own chapter.

Go further

The free workbooks of The Foreign Currency Accounting Handbook go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Foreign Currency Accounting Handbook. See the book.