In this workout, we're told that a US company has taken out a seven year bullet repayment loan, which is Euro denominated with a principle of 1,400 Euros and a 2% interest rate.

We're asked to use the assumptions provided to calculate the loan balance and net finance expense in US dollars through to maturity, and to use this information to complete the cashflow statement forecasts below, we're gonna calculate interest using the average loan balance, and we've been given forward euro to US dollar FX rates, and we can see that the forward rate falls in years one to three.

So that means the Euro is weakening against the US dollar, but then subsequently the forward rate increases, which means that the Euro is then strengthening against CUS dollar.

Let's start by calculating the loan balance in US dollars through to maturity.

The initial loan balance is 1,400 Euros.

So to convert this into US dollars, we'll multiply the 1,400 by the initial FX rate, giving a balance of 1612.8.

This then becomes a beginning balance in the first forecast year.

We then need to calculate the FX gain or loss in the first year that results from a change in the forward FX rate during that year.

This is calculated by taking the loan principle and we're gonna lock this cell reference and then multiplying this by the delta.

That's the change in the FX rate during the year.

Now, as the euro has weakened against the US dollar during this year, the loan balance will decrease.

Hence why this is showing as a negative in our calculations.

We're then gonna need to include any debt issuance or repayments, and that's showing here is zero through until maturity.

We can then sum our base calculation to provide the ending balance on our loan in US dollar terms in the first forecast year.

We can now copy all our calculations over to the right for subsequent forecast years in the final year.

We also need to amend our formula so that the repayment in US dollars reflects the opening balance plus the FX gain or loss in that year, so that the closing balance in the final year is zero.

The next step is to calculate net finance expense on the loan, and as we can see from the headings, this will have two components, the FX gain or loss in each year, and the interest expense on the loan.

Let's start with the FX gain or loss on the loan, as we've already calculated this.

So we just need to grab this from our base calculations above, and to remember to multiply it by minus one as an FX gain will reduce the loan balance, but needs to be shown as an income item, which is positive in the income statement.

Now, let's calculate the interest Expense on the loan, which we're gonna calculate using the average loan balance in US dollars.

So in the first forecast year, we're gonna take the interest rate assumption of 2%, and we need to lock this cell reference and multiply that by the average balance on the loan from the previous year and the first forecast year.

I'm going to multiply this by minus one as we want to show an expense as a negative number in the income statement.

The final step is to add these together to give net finance income or expense each year.

We can then copy this calculation over to the right to complete our calculations for all remaining forecast years.

Once we do this, we can see that the FX gain in the early years more than offsets the interest expense, whereas once the forward rate increases, the FX loss then adds to the interest expense.

The next step is then to include this in the income statement here.

I can just link this to our calculations above, but if we were building a full three statement model, we would create a circular reference when putting interest in our income statement.

So we would need to consider using a circular switch.

Now, the FX gain or loss is not a cash item.

It reflects the decrease or increase in the loan amount, which will eventually need to be repaid.

So we need to think about how this will impact the cashflow statement.

So let's have a look at that.

Now, as usual, our cashflow statement starts with net income, which includes the FX gain or loss, which is a non-cash item.

So the first thing to do here is to add back an FX loss or deduct an FX gain.

The next and final step is to calculate the financing cash flows.

However, we can't just take the change in the ending balance on the loan each year because this includes the FX gain or loss, which is non-cash.

So instead, we're gonna grab the repayment on the loan from the base calculation.

Once we've done this, we've completed all the foreign currency debt calculations for this workout.