How to Make EMI For Loan in SQL Server

In this article you will learn how to make Equated Monthly Installments (EMIs) for a loan in SQL Server.

Introduction

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

EMI

An Equated Monthly Installment (EMI) is a monthly basis repayment of the loan amount taken. A loan amount, whether it is 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 the 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 time period. That is, if the loan amount increases then the EMI amount increases too and if the time period increases then the EMI amount decreases. It does not mean you must pay less but rather the monthly amount is decreased since the total number of months for repayment has increased.

How to calculate the EMI?

EMI is made up of two variable components, the principal amount and the interest rate. The EMI is fixed but not the components. The component of the interest amount is higher in the initial years and decreases over the years. The component of the principal amount is lower in the initial years and increases 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 1 year with a flat interest rate of 20%. The EMI he must pay is Rs 10,000.

table

Here is the Solution:

  1. Create PROC [dbo].[Sp_GetEmi]   
  2. @LoanAmount decimal(18,2),   
  3. @InterestRate decimal(18,2),   
  4. @LoanPeriod Int,   
  5. @StartPaymentDate DATETIME  
  6. AS  
  7. BEGIN  
  8. SET NOCOUNT ON  
  9.   
  10. DECLARE   
  11.   
  12. @Payment decimal(12,2),   
  13. @Period FLOAT,   
  14. @Payment2 decimal(12,2),  
  15. @TotalPayment decimal(12,2),  
  16. @FinanceCharges FLOAT,  
  17. @CompoundingPeriod FLOAT,  
  18. @CompoundingInterest FLOAT,  
  19. @CurrentBalance decimal(12,2),  
  20. @Principal FLOAT,  
  21. @Interest FLOAT,  
  22. @LoanPaymentEndDate DATETIME,  
  23. @LoanPayDate DATETIME,  
  24. @LoanDueDate DATETIME   
  25.   
  26.   
  27.   
  28. SET @InterestRate = @InterestRate/100   
  29.   
  30. SET @CompoundingPeriod = 12   
  31.   
  32.   
  33. /*** END USER VARIABLES ***/   
  34.   
  35. SET @CompoundingInterest = @InterestRate/@CompoundingPeriod   
  36.   
  37. SET @Payment = ROUND((((@InterestRate/12) * @LoanAmount)/(1- ( POWER( (1 + (@InterestRate/12)),(-1 * @LoanPeriod) )))),2)   
  38.   
  39. SET @TotalPayment = @Payment * @LoanPeriod   
  40.   
  41. SET @FinanceCharges = @TotalPayment - @LoanAmount   
  42.   
  43. IF EXISTS(SELECT object_id FROM tempdb.sys.objects WHERE name LIKE '#EMI%')   
  44.   
  45. BEGIN   
  46.   
  47. DROP TABLE #EMI   
  48.   
  49. END   
  50.   
  51. /*** IT'S A TEMPORERY TABLE ***/   
  52.   
  53. CREATE TABLE #EMI(   
  54.   
  55.  PERIOD INT   
  56.   
  57. ,PAYDATE SMALLDATETIME   
  58.   
  59. ,PAYMENT decimal(12,2)   
  60.   
  61. ,CURRENT_BALANCE decimal(12,2)   
  62.   
  63. ,INTEREST decimal(12,2)   
  64.   
  65. ,PRINCIPAL decimal(12,2)   
  66.   
  67. )   
  68.   
  69. SET @Period = 1   
  70.   
  71. SET @LoanPaymentEndDate = DATEADD(month,@LoanPeriod,@StartPaymentDate)   
  72.   
  73. SET @LoanPayDate = @StartPaymentDate  
  74.   
  75. BEGIN   
  76.   
  77. WHILE (@Period < = @LoanPeriod)   
  78.   
  79. BEGIN   
  80.   
  81. SET @CurrentBalance = ROUND (@LoanAmount * POWER( (1+ @CompoundingInterest) , @Period ) - ( (ROUND(@Payment,2)/@CompoundingInterest) * (POWER((1 + @CompoundingInterest),@Period ) - 1)),0)   
  82.   
  83. SET @Principal =   
  84. CASE   
  85. WHEN @Period = 1   
  86. THEN   
  87. ROUND((ROUND(@LoanAmount,0) - ROUND(@CurrentBalance,0)),0)   
  88. ELSE   
  89. ROUND ((SELECT ABS(ROUND(CURRENT_BALANCE,0) - ROUND(@CurrentBalance,0))   
  90. FROM #EMI   
  91. WHERE PERIOD = @Period -1),2)   
  92. END   
  93.   
  94. SET @Interest = ROUND(ABS(ROUND(@Payment,2) - ROUND(@Principal,2)),2)   
  95.   
  96. SET @LoanDueDate = @LoanPayDate   
  97.   
  98. INSERT   
  99. #EMI  
  100.   
  101. SELECT   
  102.   
  103. @Period,   
  104. @LoanDueDate,   
  105. @Payment,   
  106. @CurrentBalance,   
  107. @Interest,   
  108. @Principal   
  109.   
  110. SET @Period = @Period + 1   
  111.   
  112. SET @LoanPayDate = DATEADD(MM,1,@LoanPayDate)   
  113.   
  114. END   
  115.   
  116. END   
  117.   
  118. SELECT * FROM #EMI  
  119. END  
Note: This procedure is calculating the EMI for months only, if you want a weekly or daily then please change it as your need.