3
$\begingroup$

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?

$\endgroup$
6
  • 1
    $\begingroup$ You've not defined svrLength, annualSvrRate,annualFixedRate,initialPayment,followingPayment etc.. and some of your local variables are not in the Block. $\endgroup$ – flinty Jul 8 '20 at 0:24
  • $\begingroup$ sorry about that. should be fine now. $\endgroup$ – Shb Jul 8 '20 at 0:26
  • $\begingroup$ 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. $\endgroup$ – flinty Jul 8 '20 at 0:32
  • 1
    $\begingroup$ Oh I see - well yes there is Annuity which despite its name can also represent a mortgage $\endgroup$ – flinty Jul 8 '20 at 0:34
  • 2
    $\begingroup$ Endorse the comment on Annuity; it's a very powerful function and shows Mathematica at its best. $\endgroup$ – PaulCommentary Jul 8 '20 at 1:14
6
$\begingroup$

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"}]

enter image description here

Formulae used in above calculations

  1. Formula for periodic payment - loan payment formula

enter image description here

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.)

enter image description here

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
$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$ – Shb May 17 at 2:12
0
$\begingroup$

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}];
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.