Loan Amortization Schedule using R code

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 is 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, \quad B_0 = A \\ I_n &= iB_{n-1} \end{align}\]

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

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 payments are larger in the earlier year and become smaller as the principal is gradually repaid.

\[\begin{align} P_n &= A/N \\ B_n &= B_{n-1} - P_n, \quad B_0 = A \\ I_n &= iB_{n-1} \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_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 = \frac{iA}{(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 = \frac{iA}{(1+i)^{N}-1}, \quad P_{n} = P_{n-1}(1+i) \\ &B_n = B_{n-1} - P_n, \quad B_0 = A \\ &I_n = iB_{n-1} \end{align}\]

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

In particular, the equal total payment is essentially an annuity (\(PI_n = P_n + I_n\)) which is represented as

\[\begin{align} PI_n &= (A - P_1) \times i + P_2 \\ & = \left(A - \frac{iA}{(1+i)^{N}-1} \right) \times i + \frac{i(1+i)A}{(1+i)^{N}-1} \\ & = \frac{(1+i)^{N}iA }{(1+i)^{N}-1} \\ \rightarrow PI_n &= \frac{iA}{1-(1+i)^{-N}} = \frac{0.1 \times 10000}{1-(1+0.1)^{-10}} \\ &= 1627.45395 \end{align}\]

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://shleeai.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 the prepayment rate into these cash flow schedule. \(\blacksquare\)