Loan Amortisation Schedule using R and Python

Deepanshu Bhalla 6 Comments , ,
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
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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

6 Responses to "Loan Amortisation Schedule using R and Python"
  1. Thanks, Deepanshu for sharing an easily understandable piece.

    ReplyDelete
  2. Hi Deepanshu,could you also provide these codes in SAS as you have mentioned above in R and Python

    ReplyDelete
  3. thank you, very useful! really enjoying reading through articles on this website!

    ReplyDelete
  4. how to run this code?can anyone provide full code ASAP please....

    ReplyDelete
  5. I think this is wrong. The functions straight copy pasted from here gives PMT = 4261.985905792999 IPMT = 4250.0 PPMT = 11.985905792998892

    ReplyDelete
    Replies
    1. are you using R code? or Python? What are the arguments you specified in PMT, IPMT and PPMT functions? can you restart your R/Python interface and try it once again?

      Delete
Next → ← Prev