Loan Amortisation Schedule using R and Python

In this post, we will explain how you can calculate your monthly loan instalments the way bank calculates using R and Python. In financial world, analysts generally use MS Excel software for calculating principal and interest portion of instalment using PPMT, IPMT functions. As data science is growing and trending these days, it is important to know how you can do the same using popular data science programming languages such as R and Python.

When you take a loan from bank at x% annual interest rate for N number of years. Bank calculates monthly (or quarterly) instalments based on the following factors :

  • Loan Amount
  • Annual Interest Rate
  • Number of payments per year
  • Number of years for loan to be repaid in instalments

Loan Amortisation Schedule

It refers to table of periodic loan payments explaining the breakup of principal and interest in each instalment/EMI until the loan is repaid at the end of its stipulated term. Monthly instalments are generally same every month throughout term if interest and term is not changed. Sometimes bank restructures loan portfolio and reduce interest rate but increase terms (i.e. number of years you need to pay monthly instalments) so monthly instalment gets changed.

How much principal and interest in each instalment?

We generally pay high interest rate initially and it goes down after that in successive months. It is because it depends on loan balance. Once you pay first monthly instalment, your loan balance goes down from original loan amount (i.e origination loan amount) to (original loan amount - principal amount you paid in first instalment).

Principal part in instalment goes up every month. In other words, Principal amount increases in following months. Since instalment is summation of principal and interest amount, when principal amount goes up, interest goes down to balance out.

Example : You took a personal loan of 50,000 over a period of 6 years at 8.5% per annum paid monthly (12 payments per year)
The table below shows amortisation schedule of first year. Similarly you have for 5 more years as term is 6 years.
Amortisation Schedule R and Python
Monthly instalment is calculated based on the following formula assuming constant payment and constant interest rate. It is the same formula used in PMT function in MS Excel.
PMT = (rate*(fv+pv*(1+ rate)^nper))/((1+rate*type)*(1-(1+ rate)^nper))
Here rate refers to interest rate per month (if monthly instalment). nper means number of payments for loan. pv refers to loan amount. fv refers to future value after the full loan is repaid. It is generally zero. type=0 means payments are due at the end of the period. type = 1 means payments are due at the beginning of the period.

Interest portion of monthly instalment can be easily calculated using IPMT function in MS Excel. Calculation behind this function is dependent on PMT function.

IPMT = -( ((1+rate)^(per-1)) * (pv*rate + PMT(rate, nper,pv, fv=0, type=0)) - PMT(rate, nper,pv, fv=0, type=0))
Here per means nth period. Suppose you are calculating interest of second instalment. It will be 2.

In Excel, PPMT function returns principal portion of instalment. It is the difference between instalment amount and interest amount.

PPMT = PMT(rate, nper,pv, fv=0, type=0) - IPMT(rate, per, nper, pv, fv=0, type=0)

R Code

# Instalment of Loan
PMT <- function(rate, nper,pv, fv=0, type=0){
  pmt = ifelse(rate!=0,
               (rate*(fv+pv*(1+ rate)^nper))/((1+rate*type)*(1-(1+ rate)^nper)),
               (-1*(fv+pv)/nper )
  )
  
  return(pmt)
}

# Principal portion of instalment in each period
PPMT <- function(rate, per, nper, pv, fv=0, type=0){
  ppmt = PMT(rate, nper,pv, fv=0, type=0) - IPMT(rate, per, nper, pv, fv=0, type=0)
  return(ppmt)
}

# Interest portion of instalment in each period
IPMT <- function(rate, per, nper, pv, fv=0, type=0){
  ipmt = -( ((1+rate)^(per-1)) * (pv*rate + PMT(rate, nper,pv, fv=0, type=0)) - PMT(rate, nper,pv, fv=0, type=0))
  return(ipmt)
}

Python Code

def PMT(rate, nper,pv, fv=0, type=0):
    if rate!=0:
               pmt = (rate*(fv+pv*(1+ rate)**nper))/((1+rate*type)*(1-(1+ rate)**nper))
    else:
               pmt = (-1*(fv+pv)/nper)  
    return(pmt)


def IPMT(rate, per, nper,pv, fv=0, type=0):
  ipmt = -( ((1+rate)**(per-1)) * (pv*rate + PMT(rate, nper,pv, fv=0, type=0)) - PMT(rate, nper,pv, fv=0, type=0))
  return(ipmt)


def PPMT(rate, per, nper,pv, fv=0, type=0):
  ppmt = PMT(rate, nper,pv, fv=0, type=0) - IPMT(rate, per, nper, pv, fv=0, type=0)
  return(ppmt)
How to run these functions
PMT(0.085/12, 12*6, 50000)

# First Period 
IPMT(0.085/12, 1, 12*6, 50000)
PPMT(0.085/12, 1, 12*6, 50000)
Generate Loan Amortisation Schedule
To calculate interest and principal amount of instalment of each period, we need to loop PPMT and IPMT functions over sequence of periods of loan payment.

R Code

library(tidyverse)

amortisationschedule <- function(amount, annualinterestrate, paymentsperyear, years) {
    
    nper = paymentsperyear * years
    rate = annualinterestrate / paymentsperyear
    
    AmortisationSchedule <- tibble(
      Principal = map_dbl(1:nper, function(x)
        PPMT(rate,
             x,
             nper,
             amount)),
      
      Interest = map_dbl(1:nper, function(x)
        IPMT(rate,
             x,
             nper,
             amount))
    ) %>% mutate(Instalment = Principal + Interest,
                 Balance = round(amount + cumsum(Principal),2))
    
    return(AmortisationSchedule)
  }

df = amortisationschedule(50000, 0.085, 12, 6)

Python Code

import numpy as np
import pandas as pd

def amortisation_schedule(amount, annualinterestrate, paymentsperyear, years):

    df = pd.DataFrame({'Principal' :[PPMT(annualinterestrate/paymentsperyear, i+1, paymentsperyear*years, amount) for i in range(paymentsperyear*years)],
                                 'Interest' :[IPMT(annualinterestrate/paymentsperyear, i+1, paymentsperyear*years, amount) for i in range(paymentsperyear*years)]})
    
    df['Instalment'] = df.Principal + df.Interest
    df['Balance'] = amount + np.cumsum(df.Principal)
    return(df)

df = amortisation_schedule(50000, 0.085, 12, 6)
Related Posts
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.

2 Responses to "Loan Amortisation Schedule using R and Python"
  1. nice information on data science has given thank you very much.
    Data Science Training in Hyderabad

    ReplyDelete
  2. good article about data science has given it is very nice thank you for sharing.
    Data Science Training in Hyderabad

    ReplyDelete

Next →
Love this Post? Spread the Word!
Share