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
- Add 1 to the EUR interest rate
- The implied interest rate is the USD Exchange Rate divided by the 1+EUR index
- Compute the PV of debt Service in USD
- Compute the debt service in EUR
- Convert the initial loan into EUR — from the 634 to 530
- Check the IRR in EUR — It should be the interest rate
Put the translation adjustment in USD. You can use the following steps:
- Take the model currency (in the case below, it is LKR) and divide it by the currency of the debt which is USD
- Compute the change in the ratio
- Multiply the change by the opening balance stated in the local currency for the model and do not put it in the debt schedule for the currency.
Note that when you do this the closing balance of the loan in EUR — the currency of the loan — and in USD — the currency of the model — are both zero.
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.