How to Make EMI For Loan in SQL Server

Introduction

I will explain how to make an Equated Monthly Installment (EMI) for your loan amount in SQL Server using a Stored Procedure in this article.

What is EMI?

An Equated Monthly Installment (EMI) is a monthly basis repayment of the loan amount taken. A loan amount, whether a home loan, a car loan or a personal loan, is paid back using a series of monthly payments. The monthly payment is in the form of postdated cheques drawn in favor of the lender. EMIs are paid until the total amount due is paid up. The EMI is directly proportional to the loan amount taken and inversely proportional to the period. That is, if the loan amount increases, then the EMI amount increases too and if the period increases, then the EMI amount decreases. It does not mean you must pay less, but the monthly amount is decreased since the total number of months for repayment has increased.

How to calculate the EMI?

EMI comprises two variable components: the principal amount and the interest rate. The EMI is fixed, but not the components. The element of the principal amount is lower in the initial years and increases over the years. The element of the interest amount is higher in the initial years and decreases over the years.

For this reason, if you consider pre-payment, you should do it in the early years since you save on the interest rate.

Illustration,  Ashwin takes out a loan of Rs 1 lacs for one year with a flat interest rate of 20%. The EMI he must pay is Rs 10,000.

table

Here is the Solution

Create PROC [dbo].[Sp_GetEmi]   
@LoanAmount decimal(18,2),   
@InterestRate decimal(18,2),   
@LoanPeriod Int,   
@StartPaymentDate DATETIME  
AS  
BEGIN  
SET NOCOUNT ON  
  
DECLARE   
  
@Payment decimal(12,2),   
@Period FLOAT,   
@Payment2 decimal(12,2),  
@TotalPayment decimal(12,2),  
@FinanceCharges FLOAT,  
@CompoundingPeriod FLOAT,  
@CompoundingInterest FLOAT,  
@CurrentBalance decimal(12,2),  
@Principal FLOAT,  
@Interest FLOAT,  
@LoanPaymentEndDate DATETIME,  
@LoanPayDate DATETIME,  
@LoanDueDate DATETIME   
     
SET @InterestRate = @InterestRate/100   
SET @CompoundingPeriod = 12   
  
/*** END USER VARIABLES ***/   
SET @CompoundingInterest = @InterestRate/@CompoundingPeriod   
SET @Payment = ROUND((((@InterestRate/12) * @LoanAmount)/(1- ( POWER( (1 + (@InterestRate/12)),(-1 * @LoanPeriod) )))),2)   
  
SET @TotalPayment = @Payment * @LoanPeriod     
SET @FinanceCharges = @TotalPayment - @LoanAmount   
IF EXISTS(SELECT object_id FROM tempdb.sys.objects WHERE name LIKE '#EMI%') 
    
BEGIN     
DROP TABLE #EMI     
END   
  
/*** IT'S A TEMPORERY TABLE ***/   
  
CREATE TABLE #EMI(     
 PERIOD INT     
,PAYDATE SMALLDATETIME     
,PAYMENT decimal(12,2)     
,CURRENT_BALANCE decimal(12,2)     
,INTEREST decimal(12,2)     
,PRINCIPAL decimal(12,2)     
)   
  
SET @Period = 1     
SET @LoanPaymentEndDate = DATEADD(month,@LoanPeriod,@StartPaymentDate)     
SET @LoanPayDate = @StartPaymentDate
    
BEGIN     
WHILE (@Period < = @LoanPeriod)    
BEGIN   
  
SET @CurrentBalance = ROUND (@LoanAmount * POWER( (1+ @CompoundingInterest) , @Period ) - ( (ROUND(@Payment,2)/@CompoundingInterest) * (POWER((1 + @CompoundingInterest),@Period ) - 1)),0)   
  
SET @Principal =   
CASE   
WHEN @Period = 1   
THEN   
ROUND((ROUND(@LoanAmount,0) - ROUND(@CurrentBalance,0)),0)   
ELSE   
ROUND ((SELECT ABS(ROUND(CURRENT_BALANCE,0) - ROUND(@CurrentBalance,0))   
FROM #EMI   
WHERE PERIOD = @Period -1),2)   
END   
  
SET @Interest = ROUND(ABS(ROUND(@Payment,2) - ROUND(@Principal,2)),2)     
SET @LoanDueDate = @LoanPayDate   
  
INSERT   
#EMI  
  
SELECT   
  
@Period,   
@LoanDueDate,   
@Payment,   
@CurrentBalance,   
@Interest,   
@Principal   
  
SET @Period = @Period + 1     
SET @LoanPayDate = DATEADD(MM,1,@LoanPayDate)     
END     
END   
  
SELECT * FROM #EMI  
END  

Note

This procedure calculates the EMI for months only; if you want a weekly or daily, please change it as needed.

Conclusion

In this article, we taught how to make an Equated Monthly Installment (EMI) for your loan amount in SQL Server using a Stored Procedure in this article.


Similar Articles