This page illustrates how to compute taxes, cash flows and the balance sheet when taxes are computed in different currency than the primary currency of the model. I suggest carefully setting up an exchange rate and then compute the taxes (with NOL and FX adjustments) in the local currency. The first step is to compute EBITDA, depreciation expense, interest expense and other items that affect taxes in the local currency. If the debt is in a different currency (e.g. Euro or USD), then you can compute the FX loss (when the exchange rate increases) or the FX gain from the debt balance. You can compute the percent change in the exchange rate and multiply that percent by the opening balance of the debt (in Euro or USD) to compute the FX adjustment. With the FX adjustment, you can compute taxes in the local currency.
The process of computing taxes in the local currency and deriving the FX loss is illustrated in the file that you can download below and the associated video. A couple of files with examples of multiple currency are attached to the files below:
Step 1: Working with Forward Exchange Rates and Computing Implied Interest Rates
When working with multiple currencies, you can first put the currencies at the top of the sheet and evaluate the implied inflation rates. In the example below, we have gathered the forward exchange rate and then used the INTERPOLATE function. Note that the cash flow is stated in USD and the debt service is also in USD. But we assume that the loan is in EUR. In rows 17 to 19 we assume that the interest rate in EUR is 1.5% which you can use to compute the implied interest rate in USD. To do this you can:
- Compute the change in the exchange rate – could be devaluation or appreciation
- Add 1 to the loan interest rate in the alternative currency that is not the currency of the model
- The implied interest rate in the base currency is the 1+rate from step 2 divided by the devaluation or the appreciation.
Step 2: Demonstration of IRR in Model Currency
Put the loan in both currencies with the translation adjustment. You can use the following steps:
- Compute a standard debt table/balance in the currency of the loan
- Translate the repayment and the interest to the loan currency — these are the cash flows on the loan
- Compute the IRR on the cash flow of the loan in local currency — this should be something like the interest rates that you have computed above.
Step 3: Compute the Loan Balance with a Translation Adjustment to Verify the Interest Rate and the Translation Adjustment Method
Step 2: Compute the Exchange Rate on the Opening Balance
Here, I am illustrating my method for evaluating a formula. You need to have the GENERIC MACROS file open and then you can press ALT, u.
Once you compute the translation adjustment you can put it on the income statement. When you attach the cash flow to the profit and loss you can then convert the draws into the currency of the debt. This is illustrated in the example below. The file with the example is attached.
Finding Forward Rates
The screenshot below illustrates how you can find the forward rates which can then be used as the basis for computing the different interest rates and the different implied inflation rates derived from purchasing power parity.