# two tier compound interest problem

I am trying to make sense of how mortgage repayment figures are calculated for two tier mortgages, where you fix your interest rate for a certain period and after that period, you fall back to the SVR rate.

I use the following as the building block:

chargeInterestAndRepay[debt_, interestRate_, repayment_]:= (debt*(1+interestRate))  - repayment


and I use:

mortgageBalanceOverTime[loan_, annualFixedRate_, fixedLength_, annualSvrRate_, termLength_] := Block[
{
svrLength = termLength - fixedLength,

numberOfFixedMonths = 12* (fixedLength),
numberOfSvrMonths = 12 * svrLength,

monthlySvrRate = annualSvrRate / 12,
monthlyFixedRate = annualFixedRate/12
},

fixedPeriodBalance = NestList[chargeInterestAndRepay[#, monthlyFixedRate, initialPayment]&,loan ,numberOfFixedMonths];
svrPeriodBalance = Drop[NestList[chargeInterestAndRepay[#, monthlySvrRate, followingPayment]&, Last[fixedPeriodBalance] , numberOfSvrMonths], 1];
solns = NSolve[Rationalize[Last[svrPeriodBalance]==0] &&followingPayment> 0 &&followingPayment > initialPayment && initialPayment > 0, { followingPayment, initialPayment}];
Join[fixedPeriodBalance, svrPeriodBalance] /. solns
]


instead of giving me actual numerical values, it's giving me polynomials in initialPayment of the form:

blah blah.... if initialPayment > 1234


what am I doing wrong?

• You've not defined svrLength, annualSvrRate,annualFixedRate,initialPayment,followingPayment etc.. and some of your local variables are not in the Block. – flinty Jul 8 '20 at 0:24
• sorry about that. should be fine now. – Shb Jul 8 '20 at 0:26
• There are still some missing variables : initialPayment and followingPayment. Also use a Module instead of a Block and add fixedPeriodBalance, svrPeriodBalance, solns to the variable list. – flinty Jul 8 '20 at 0:32
• Oh I see - well yes there is Annuity which despite its name can also represent a mortgage – flinty Jul 8 '20 at 0:34
• Endorse the comment on Annuity; it's a very powerful function and shows Mathematica at its best. – PaulCommentary Jul 8 '20 at 1:14

Assuming the payments change when the rate changes. Then using an example of a 100k mortgage on 4% (nominal) for two years, then 6% for three more years. (Formula derivations shown below.)

Note, if using an effective annual rate use r = (1 + 0.04)^(1/12) - 1.

s = principal
r = periodic rate
n = number of periods
d = periodic payment

s = 100000;
r = 0.04/12;
n = 60;
d = s r (1 + 1/((1 + r)^n - 1))        [Formula 1]

1841.65


Monthly payments for the first two years are 1841.65

Also via Annuity

Solve[TimeValue[Annuity[pmt, 5, 1/12], EffectiveInterest[0.04, 1/12], 0] == 100000, pmt]

{{pmt -> 1841.65}}


Balance in month x is

b = (d + (1 + r)^x (r s - d))/r        [Formula 2]


E.g. final balance after 60 months is zero.

b = (d + (1 + r)^60 (r s - d))/r = 0.


Balance after two years

b = (d + (1 + r)^24 (r s - d))/r

62378.17


Recalculating payments

s = b;
r = 0.06/12;
n = 36;
d = s r (1 + 1/((1 + r)^n - 1))

1897.66


Monthly payments for the remaining 3 years are 1897.66

Chaining the calculations together can produce a direct formula for the 2nd payments value.

Clear[s, n, x, b]
d1 = s r1 (1 + 1/((1 + r1)^n - 1));
b = (d1 + (1 + r1)^x (r1 s - d1))/r1;
d2 = b r2 (1 + 1/((1 + r2)^(n - x) - 1));
d2 = FullSimplify[d2]

(((1 + r1)^n - (1 + r1)^x) r2 (1 + r2)^n s)/
((-1 + (1 + r1)^n) ((1 + r2)^n - (1 + r2)^x))

s = 100000;
r1 = 0.04/12;
n = 60;
x = 24;
r2 = 0.06/12;

{d1, d2}

{1841.65, 1897.66}


Implementation of OP's function

A demonstration of how the OP's function could be implemented.

mortgageBalanceOverTime[loan_, annualFixedRate_, fixedLength_,
annualSvrRate_, termLength_] := Module[{},
s = loan;
r1 = annualFixedRate/12;
n = 12 (fixedLength + termLength);
x = 12 fixedLength;
r2 = annualSvrRate/12;
d1 = s r1 (1 + 1/((1 + r1)^n - 1));

fixedPeriodBalance = Table[(d1 + (1 + r1)^k (r1 s - d1))/r1, {k, 0, x}];
b = Last[fixedPeriodBalance];
d2 = b r2 (1 + 1/((1 + r2)^(n - x) - 1));
svrPeriodBalance = Table[(d2 + (1 + r2)^k (r2 b - d2))/r2, {k, n - x}];
Join[fixedPeriodBalance, svrPeriodBalance]]


Or alternatively, using the OP's subroutine.

mortgageBalanceOverTime[loan_, annualFixedRate_, fixedLength_,
annualSvrRate_, termLength_] := Module[{},
s = loan;
r1 = annualFixedRate/12;
n = 12 (fixedLength + termLength);
x = 12 fixedLength;
r2 = annualSvrRate/12;
d1 = s r1 (1 + 1/((1 + r1)^n - 1));

fixedPeriodBalance = NestList[chargeInterestAndRepay[#, r1, d1] &, s, x];
b = Last[fixedPeriodBalance];
d2 = b r2 (1 + 1/((1 + r2)^(n - x) - 1));
svrPeriodBalance = Rest@NestList[chargeInterestAndRepay[#, r2, d2] &, b, n - x];
Join[fixedPeriodBalance, svrPeriodBalance]]


This reveals that the main point of difference between these and the OP's original function is the use of formula 1 to obtain the payment amount.

Applying either modified version to the demo input figures.

ListPlot[mortgageBalanceOverTime[100000, 0.04, 2, 0.06, 3],
DataRange -> {0, 5}, AxesLabel -> {"Years"}]


Formulae used in above calculations

1. Formula for periodic payment - loan payment formula

Derived from the sum of the discounted payments being equal to the principal.

Clear[d]
d = First[d /. FullSimplify@Solve[s == Sum[d/(1 + r)^k, {k, 1, n}], d]]

r (1 + 1/(-1 + (1 + r)^n)) s

1. Formula for loan balance - inhomogeneous difference equation (Arne Jensen, Aalborg Uni.)

FullSimplify[RSolve[{q[n + 1] == (1 + r) q[n] - d, q[0] == s}, q[n], n]][[1, 1]]

q[n] -> (d + (1 + r)^n (-d + r s))/r

• revisited this recently and thought I'd summarize your solution in a new answer. curious to know if you see if you find it readable. – Shb May 17 at 2:12

TLDR version of Chris's solution above:

Using this equation:

CompoundInterest[p_, r_, n_]:=CompoundInterest[p,r,n]= q[n + 1] == (1 + r) q[n] - p


The pmt function can be used to find the monthly payments.

(*rate: interest rate for loan*)
(*nper: total number of payments for the loan.*)
(*pv: present value.*)
(*fv: The future value, or a cash balance you want to attain after the last payment is made.*)

Pmt[rate_, nper_, pv_, fv_]:= p /.Solve[
qn == q[n] /. RSolve[{CompoundInterest[p, r, n], q[0] == q0}, q[n], n],
p
][[1,1]] /. {n-> nper, r -> rate, qn ->fv, q0 -> pv};


Once pmt is calculated, we can use it to create a table of the balance after each payment:

Balance[pv_,rate_,pmt_,N_]:= RecurrenceTable[{CompoundInterest[pmt,rate, n], q[0]==pv}, q, {n, 0, N}];