# Calculation of monthly interest and capital repayment in a variable rate mortgage

In my exercise, I take out a 240,000€ mortgage from a bank which I pay off over a period of 30 years. The interest rate is a market index plus a spread. Initally the market index is 1% and the spread is 0.99%. After 1 year the market spread rises 0.10% and continuously does so until the last year, where my market index is 3.90% and my spread is 0.99%.

How can I calculate the interest and capital repayment for each month?

As far as I know the monthly interest is the following one where D is the debt which is still to be paid and R the year's interest (index + spread).

$$D = \frac{R}{12}$$

As for the capital repayment in a fixed rate mortgage the first month would be:

$$X = \frac{D \frac{R}{12}}{(1 + \frac{R}{12})^{12T} - 1}$$

But I'm afraid this is not valid anymore because as you compound it by the increasingly high interest rate you finish paying it before those 30 years.

However, the task says the first 12 installments should be the same as in the previous part of the assignment which is the same but with a fixed interest rate 1.99%.

So how do I start?

• please edit and clarify: "market spread rises 0.10%" did you mean the market index, or your spread on top of the index? Jan 15 at 14:56

I don't quite understand all the details in your question, but I'll try to answer anyway.

Suppose, for starters, that you have some debt instrument, that initially the notional is $$L$$ and that your interest rate is some constant $$r$$ in each period, and then you're trying to solve for the payment amount $$P$$ so that the debt is paid off in exctly $$n$$ payments.

In your numerical example, $$L= €240,000$$, and $$n=12*30$$. Your $$r$$ is not constant, but we'll address that shortly.

For a constant $$r$$, you may recall a closed-form formula $$P=L \frac{c(1+c)^n}{(1+c)^n-1}$$, or something like this (check). However if you can't remember or derive the closed-form formula, you can anyway use a calculator such as Excel to solve for $$P$$ numerically. You set up the formulas for each period, simply saying that you pay $$P$$ every period, that the interest is $$r \times$$ remaining notional, and that the next period's remaining notional is reduced by $$P-\mathrm{interest}$$. Then you run a solver to find the $$P$$ that makes the remaining notional exactly 0 after $$n$$ periods. If everything works correctly, then the number from the solver should match the number from the closed-form formula.

Now suppose that $$r_i$$ is known at inception, but not constant - depends rather on the time $$i$$. (For example, you pay $$3\%$$ interest during the first 5 years and $$4\%$$ during the remaining years.) If you're given a formula for $$r_i$$, you may still be able to figure out a closed-form formula for $$P$$, but that seems like an pointless exercise in algebra. Just use a numerical solver as in the previous pagraph, using $$r_i$$ in every period instead of a constant $$r$$. Likewise, a numerical solver will still work if your coupon periods do not have the same length.

But if $$r_i$$ are not known at inception, for example, $$r_i$$ depend on risk-free interest rates, such as €STR observed during i'th period, then you simply can't solve for $$P$$. You can still project what $$r_i$$ would be, for example, if forwards become realized, and solve for $$P$$ with this assumption, but it is very likely that in reality the rates will be something else, your interest willbe something other than what you project, and you won't have exctly zero remaining notional in $$n$$ periods unless you adjust the $$P$$.

It sounds you're told to assume that you know at inception the future evolution of the interest rate curve, so there really no uncertainty about what your $$r_i$$'s will be.