Refund plan

In basic financial mathematics , a repayment plan determines, on a constant monthly loan , the relationship between the capital borrowed, the interest rate , the amount of the repayments and the duration of the loan.

Mathematical implementation

A capital C borrowed at a monthly rate t and repaid in monthly installments M leads to the construction of an arithmetic-geometric sequence . Yes{\ displaystyle R_ {n}}represents the remaining capital due after n monthly installments, following{\ displaystyle (R_ {n}) \,}is defined by the recurrence relation:

{\ displaystyle R_ {n + 1} = (1 + t) R_ {n} -M \,}.

Indeed, like any debt, for a month, the capital {\ displaystyle R_ {n}} will increase by {\ displaystyle t \ times R_ {n}}if t is the monthly interest rate. As, after one month, it intervenes a refund of an amount M , the capital remaining due at the end of the n + 1 e month is therefore{\ displaystyle R_ {n + 1} = R_ {n} + tR_ {n} -M}.

A first common sense remark is that monthly payments must be greater than {\ displaystyle t \ times R_ {n}} so in particular to {\ displaystyle t \ times C}to have a chance to see the debt decrease.

Variables

  • C : borrowed capital
  • t : rate of the period
  • M : amount of the due date
  • n : number of deadlines

The formulas

A study of the arithmetic-geometric suite allows to give {\ displaystyle R_ {n}} as a function of C, M, n and t.

{\ displaystyle R_ {n} = (1 + t) ^ {n} \ left (C – {\ frac {M} {t}} \ right) + {\ frac {M} {t}}}

Since the goal is to repay the sum after N monthly payments, the relationship existing between C , M , t and N is:

{\ displaystyle (1 + t) ^ {N} = {\ frac {M / C} {M / Ct}} = {\ frac {M} {M-Ct}}}

Number of deadlines

We can deduce, according to M / C and t , the number of necessary monthly payments:

{\ displaystyle N = {\ frac {\ ln (M / C) – \ ln (M / Ct)} {\ ln (1 + t)}} = {\ frac {\ ln (M) – \ ln (M -ct)} {\ ln (1 + t)}}}.
Example: if you borrow 1,000 euros at 0.5% monthly interest (approximately 6% annual interest) and you repay 10 euros per month, you must

{\ displaystyle {\ frac {\ ln (0,01) – \ ln (0,005)} {\ ln (1,005)}} = 139} monthly payments or {\ displaystyle {\ frac {\ ln (10) – \ ln (10-5)} {\ ln (1,005)}} = 139} monthly payments

That’s 11 years and 7 months.

Amount of the maturity

Depending on the duration of the loan, you can also determine the amount of the monthly payments:

{\ displaystyle M = C {\ frac {t} {1- (1 + t) ^ {- N}}}}

We often prefer to speak in number of years A and in annual rate {\ displaystyle i}. For low rates (see geometric suite ), we can use the following approximation{\ displaystyle t = i / 12} and then we get the following formula

{\ displaystyle M = {\ frac {C (i / 12)} {1- (1 + i / 12) ^ {- N}}}}
Example a sum of 1000 euros, borrowed over 10 years, so 120 months, at an annual rate of 4.8% requires a monthly repayment of

{\ displaystyle {\ frac {1000 \ times (0,048 / 12)} {1- (1 + 0,048 / 12) ^ {- 10 * 12}}} = {\ frac {4} {1-1,004 ^ {- 120 }}}} = 10.51 euros.

Capital borrowed

It is possible to determine the amount of capital borrowed depending on the duration of the loan, the rate and the amount of maturities:

{\ displaystyle C = M {\ frac {1- (1 + t) ^ {- N}} {t}}}

Finally, we can determine the sum actually reimbursed, based on the amount borrowed C , the duration of the loan N in months and the monthly interest rate t .

{\ displaystyle S = N \ times M = C {\ frac {Nt} {1- (1 + t) ^ {- N}}}}
In the previous example, the sum actually reimbursed is 1,261 euros .

Refund table

When the amount borrowed, the interest rate and the loan term are decided, the amount of the monthly payments is then fixed. A table is then presented which specifies, month by month, the capital remaining due and the share, in repayment , of the repayment of interest and depreciation . This table allows you to know, at any time, the state of his account and the amount to pay in case of early repayment.

Example with a borrowed sum of 1,000 euros , a loan term of 10 years, an interest rate of 4.8% and a monthly rate of 0.4%:

[FIRST VERSION] – Redemption plan made on spreadsheet .
AT B C D E F
1 Month Capital of Repayed interest Repayed capital Rate 0,004
2 1 1000,00 € € 4.00 $ 6.51 Monthly fees € 10.51
3 2 € 993.49 $ 3.97 $ 6.54
4 3 € 986.95 $ 3.95 € 6.56
5 4 $ 980.39 $ 3.92 $ 6.59
6 5 $ 973.80 € 3.90 € 6.61
7 6 € 967.19 $ 3.87 € 6.64
122 120 $ 10.32 $ 0.04 $ 10.47
In cell B3 is entered the formula ” = B2 * (1 + $ F $ 1) – $ F $ 2 “
In cell C2 is entered the formula ” = B2 * $ F $ 1 “
In cell D2 is entered the formula ” = B2-B3 “
[SECOND VERSION] – Redemption plan made on spreadsheet .
AT B C D E F
1 Amount: 1000,00 €
2 Rate: 4.80% Monthly rate: 0.40%
3 Nb periods: 120
4 Monthly payments: € 10.51
5
6 Month Based Interest Amortization Monthly fees End value
7 January 1000,00 € € 4.00 € 6.51 € 10.51 993.49 €
8 February 993.49 € 3,97 € € 6.54 € 10.51 986.96 €
9 March 986.96 € € 3.95 € 6.56 € 10.51 980.39 €
10 April 980.39 € € 3.92 € 6.59 € 10.51 973.81 €
11 May 973.81 € € 3.90 € 6.61 € 10.51 967.19 €
12 June 967.19 € € 3.87 € 6.64 € 10.51 960.55 €
125 November 20,89 € € 0.08 € 10.43 € 10.51 € 10.47
126 December € 10.47 € 0.04 € 10.47 € 10.51 € 0.00
In cell B4 is entered the formula ” = B1 * D2 / (1- (1 + D2) ^ – B3) “
In cell D2 is entered the formula ” = B2 / 12 “
In cell B7 is entered the formula ” = $ B $ 1 “
In cell C7 is entered the formula ” = B7 * $ D $ 2 ” (To be stretched down)
In cell D7 is entered the formula ” = E7-C7 ” (To be stretched down)
In cell E7 is entered the formula ” = $ B $ 4 ” (To be stretched down)
In cell F7 is entered the formula ” = B7-D7 ” (To stretch down)
In cell B8 is entered the formula ” = F7 ” (To be stretched down)

Leave a Reply

Your email address will not be published. Required fields are marked *