Loan Amortization Schedule using R code

Posted on June 9, 2022 by sang-heon lee in R bloggers | 0 Comments

[This article was first published on K & L Fintech Modeling, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

This post explains and implements major three types of loan amortization or repayment schedule using R code: 1) bullet or balloon payment, 2) equal total payment, and 3) equal principal payment.

Loan Amortization or Repayment Schedule

There are many types of loan amortization or repayment and among them we deal with popular three cases : 1) bullet or balloon payment. 2) equal principal payment, and 3) equal total payment. We use the following notations.

$P_n$ : principal payment part
$I_n$ : interest due in each periods
$PI_n$ : payments (=interest + principal)
$B_n$ : remaining balance due after each periods
A : loan amount at origination
N : number of annual payments
n (=1,2,…,N) : index of payment dates

Bullet Payment

In bullet payment loans, the principal amount are fully repaid at maturity and interest payments are occurred at each payment dates.

\[\begin{align} P_N &= A \text{ and } P_1, P_2, …, P_{N-1} = 0 \\ B_n &= B_{n-1} – P_n, B_0 = A \\ I_n &= B_{n-1} \times i \end{align}\]

Loan amortization schedule: bullet payment (Loan amount $10,000, annual interest rate 10%, 10 annual payments)

Loan Amortization or Repayment using R code

Equal Principal Payment

This loan is repaid in equal amounts of principal. The installments are unequal since the remaining balance decreases with time. In this equal principal payment loans, the interest payment is largest in the earlier year and become smaller as the principal is gradually repaid.

\[\begin{align} P_n &= \frac{A}{N} \\ B_n &= B_{n-1} – P_n, B_0 = A \\ I_n &= B_{n-1} \times i \end{align}\]

Loan amortization schedule: equal principal payment (Loan amount $10,000, annual interest rate 10%, 10 annual payments)

Equal Total Payment

This loan is repaid in equal installments (=interest + part of principal). The amount applied to principal is smaller in the earlier year, then the same payments to principal gradually increases with time. The decreasing payment on interests equalizes the uneven payments on principal.

Then how do we determine the equal installments?

Let’s consider two installments at time 1 and 2 which should be the same amount.

\[\begin{align} & B_0 \times i + P_1 = (B_0 – P_1) \times i + P_2 \\ \rightarrow & P_1 (1+i) = P_2 \\ \rightarrow & \frac{P_2}{P_1} = 1+i \\ \rightarrow & P_2 = P_1(1+i) \end{align}\]
From the above relationship, we can infer that the ratio of consecutive principal payments is $1+i$. Without loss of generality, we can deduce the follwing result.

\[\begin{align} P_3 &= P_2(1+i)=P_1(1+i)^2 \\ P_4 &= P_3(1+i)=P_2(1+i)^2 = P_1(1+i)^3 \\ &… \\ P_{10} &= P_9(1+i)=P_8(1+i)^2=…=P_1(1+i)^9 \end{align}\]

As $A$ is the sum of all repayments, $P_1$ is determined in the following way.

\[\begin{align} &P_1 + P_2 + … + P_{10} = A \\ &\rightarrow P_1(1+(1+i)+(1+i)^2+…+(1+i)^9) = A \\ &\rightarrow \frac{P_1((1+i)^{10}-1)}{i} = A \\ &\rightarrow P_1 = i\frac{A}{(1+i)^{10}-1} \end{align}\]

When deriving the equation for $P_1$, the geometric sequence formula is used.

\[\begin{align} \frac{a(r^n-1)}{r-1} = a+ar+ar^2 + ar^3 + … + ar^{n-1} \end{align}\]

Now $P_2, P_3, …, P_{10}$ are determined sequentially with $P_1$ since $P_{n} = P_{n-1}(1+i)$ holds true. Finally, principal payment part ($P_n$), interest due in each periods ($I_n$), and the remaining balance due after each periods ($B_n$) are as follows.

\[\begin{align} P_1 &= i\frac{A}{(1+i)^{10}-1}, P_{n} = P_{n-1}(1+i) \\ B_n &= B_{n-1} – P_n, B_0 = A \\ I_n &= B_{n-1} \times i \end{align}\]

Loan amortization schedule: equal total payment (Loan amount $10,000, annual interest rate 10%, 10 annual payments)

R code

The following R code implements three type of loan amortization schedule.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
#========================================================#
# Quantitative ALM, Financial Econometrics & Derivatives 
# ML/DL using R, Python, Tensorflow by Sang-Heon Lee 
#
# https://kiandlee.blogspot.com
#——————————————————–#
# Loan Amortization Schedule
#========================================================#
 
graphics.off(); rm(list = ls())
 
#=======================================================
# 1. Input
#=======================================================
i <– 0.1
A <– 10000
 
#=======================================================
# 2. Loan Amortization – Bullet Payment
#=======================================================
 
df      <– data.frame(n = 0:10)
df$B    <– df$P <– df$I <– df$PI  <– 0
df$B[1] <– A # Balance at origination
 
for(t in 2:11) {
    
    # interest
    df$I[t]  <– df$B[t–1]*i 
    # principal at maturity
    df$P[t]  <– ifelse(t==11, A, 0)            
    # remaining balance
    df$B[t]  <– df$B[t–1] – df$P[t]
    # payments = principal + interest
    df$PI[t] <– df$P[t] + df$I[t]
}
(df.bullet <– df)
 
#=======================================================
# 3. Loan Amortization – Equal Principal Payment
#=======================================================
 
df      <– data.frame(n = 0:10)
df$B    <– df$P <– df$I <– df$PI  <– 0
df$B[1] <– A # Balance at origination
 
for(t in 2:11) {
    
    # interest
    df$I[t]  <– df$B[t–1]*i 
    # principal
    df$P[t]  <– A/10 
    # remaining balance
    df$B[t]  <– df$B[t–1] – df$P[t]
    # payments = principal + interest
    df$PI[t] <– df$P[t] + df$I[t]
}
(df.equal_principal <– df)
 
#=======================================================
# 4. Loan Amortization – Equal Total Payment
#=======================================================
 
df      <– data.frame(n = 0:10)
df$B    <– df$P <– df$I <– df$PI  <– 0
df$B[1] <– A # Balance at origination
 
for(t in 2:11) {
    
    # interest
    df$I[t]  <– df$B[t–1]*i 
    # principal
    if(t==2) {
        df$P[t] <– i*A/((1+i)^10–1)
    } else {
        df$P[t] <– (1+i)*df$P[t–1]
    }
    
    # remaining balance
    df$B[t]  <– df$B[t–1] – df$P[t]
    # payments = principal + interest
    df$PI[t] <– df$P[t] + df$I[t]
}
(df.equal_total <– df)
 
Colored by Color Scripter
cs

The following estimation results show the above three types of loan amortization schedules.

Concluding Remarks

This post implements R code to generate three popular types of loan amortization schedules. Next time we will investigate how to incorporate prepayment rate into these cash flow schedule. $\blacksquare$

To leave a comment for the author, please follow the link and comment on their blog: K & L Fintech Modeling.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

R-bloggers

R news and tutorials contributed by hundreds of R bloggers

Loan Amortization Schedule using R code