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.

The table below shows amortisation schedule of first year. Similarly you have for 5 more years as term is 6 years.

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.

**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)

Thanks, Deepanshu for sharing an easily understandable piece.

ReplyDeleteHi Deepanshu,could you also provide these codes in SAS as you have mentioned above in R and Python

ReplyDeletethank you, very useful! really enjoying reading through articles on this website!

ReplyDelete