Exercise $1$

An industrial Company located in Antwerp prepared an offer to a customer in Italy for $2,000,000$ EUR. The two companies agreed on that the amount would be paid in two constant payments, the first one of them next year, and the second one the year after.

In order to be able to get that project, the Belgian Co$.$ needs to first spend $1,000,000$ EUR in it $($raw material, workforce...$),$ now. Since the Co$.$ has some problems of liquidity, they decide to raise those funds. The Co$.$ has different possibilities:

Option A:

$-$ to borrow the $1,000,000$ EUR

$-$ at a fix interest rate of $5\%$

$-$ to be paid back in constant monthly payments during the next two years

Option B:

$-$ to borrow the $1,000,000$ EUR

$-$ at a variable interest rate of Euribor $+0\mathrm{.}5\%$

$-$ to be paid back in constant monthly payments during the next two years

Question $1$a: If the Belgian Company goes for Option A$,$ what is the monthly payment the company will have to pay back to the bank? And how much will they have paid back in total after the two years?

Question $1$b: If the Belgian Company goes for Option A$,$ what is the debt of the company with the bank after having paid half of the payments $($the first $12$ payments$)?$

Question $1$c: If the Belgian Company goes for Option B$,$ what is the monthly payment the company will have to pay back to the bank during the first year? $($Euribor today is $3\mathrm{.}8\%)$

Question $1$d: If the Belgian Company goes for Option B$,$ they risk that next year the Euribor rises too much. If they are offered a collar contract with rMax$=5\mathrm{.}5\%$ and rmin$=1\mathrm{.}5\%,$ what is the monthly payment the company will have to pay back to the bank if Euribor rises to $4\mathrm{.}2\%?$

Question $1$e: And if Euribor rises to $5\mathrm{.}2\%?$

Question $1$f: If the Belgian company goes for Option A$,$ and it agrees on a waiting period of $6$ months with the bank, what is the monthly payment the company will have to pay back to the bank? And how much will they have paid back in total after the two years?tables. form a table, and give solutions, on excel, use function, and please precise the working or function separately.