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. Monthly instalment is calculated based on the following formula assuming constant payment and constant interest rate. It is the same formula used in
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)
# 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) }
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.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)
Hi 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!
ReplyDeletehow to run this code?can anyone provide full code ASAP please....
ReplyDeleteI think this is wrong. The functions straight copy pasted from here gives PMT = 4261.985905792999 IPMT = 4250.0 PPMT = 11.985905792998892
ReplyDeleteare 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?
DeleteYou can still use numpy financial with python
ReplyDeleteYes, definitely. These functions help you understand the mathematics behind them. Cheers!
Delete