How to make EMI Loan Amortization in SQL server

DECLARE

 @LoanAmount MONEY = '5000000.00'

,@ROI FLOAT = 11

,@Tenure NUMERIC(18,2) = 12

,@Period INT

,@Principal MONEY

,@Interest MONEY

,@EMI MONEY

,@AMT MONEY

SET @Period = 1

SET @ROI = (@ROI/100)/12

DECLARE @EMI_TABLE TABLE

(PERIOD INT,PAYDATE SMALLDATETIME,EMI MONEY,PRINCIPAL MONEY,INTEREST MONEY ,BALANCE MONEY)  

--EMI = [P x R x (1 + R)^N] / [(1 + R)^N - 1]

SELECT @EMI = ROUND((@LoanAmount * @ROI * POWER((1 + @ROI), @Tenure))  / (POWER((1 + @ROI), @Tenure) - 1),2)

WHILE @Period <= @Tenure

BEGIN

      --INTEREST = [P x R]

      SELECT @Interest = ROUND((@LoanAmount * @ROI),2)

      --PRINCIPAL = [EMI - INTEREST]

      SELECT @Principal = ROUND((@EMI - @Interest),2)

      --OUTSTANDING LOAN AMOUNT = [@LoanAmount - Principal]

      SELECT @LoanAmount = ROUND((@LoanAmount - @Principal),2)

      INSERT INTO @EMI_TABLE VALUES(@Period,DATEADD(MM,@Period,GETDATE()),@EMI,@Principal,@Interest,@LoanAmount)

      SET @Period = @Period + 1

END

SELECT * FROM @EMI_TABLE

Output

Note:-

P - Loan Amount
R - Rate of Interest
N - Tenure/Loan Period

You will get different in paisa. Need to manage as per requirement.

Calculate Loan Amount from EMI in SQL server

DECLARE

 @LoanAmount MONEY = '7537500.00'

,@ROI FLOAT = 11

,@Tenure NUMERIC(18,2) = 12

,@EMI MONEY

,@AMT MONEY

SET @ROI = (@ROI/100)/12

--EMI = [P x R x (1 + R)^N] / [(1 + R)^N - 1]

SELECT @EMI = ROUND((@LoanAmount * @ROI * POWER((1 + @ROI), @Tenure))  / (POWER((1 + @ROI), @Tenure) - 1),3) 

SELECT @EMI

Output(EMI)

666176.751

First Query return the monthly installment of Loan Amount i.e. EMI.

--AMT = [EMI x (1 + R)^N - 1] / [R x (1 + R)^N]

SELECT @AMT = ROUND(@EMI * (POWER((1 + @ROI), @Tenure) - 1) / (@ROI * POWER((1 + @ROI), @Tenure)),2)

SELECT @AMT

Output

7537500.00

Second Query return the Loan Amount based on your monthly installment(EMI).

Note:-

P - Loan Amount
R - Rate of Interest
N - Tenure/Loan Period
EMI - Estimated Monthly Installment


Depend upon your requirement, you may change round off figure.

EMI Calculation formula in SQL server

DECLARE

 @LoanAmount MONEY = '7537500.00'

,@ROI FLOAT = 11

,@Tenure NUMERIC(18,2) = 12

,@EMI MONEY

SET @ROI = (@ROI/100)/12

--EMI = [P x R x (1 + R)^N] / [(1 + R)^N - 1]

SELECT @EMI = ROUND((@LoanAmount * @ROI * POWER((1 + @ROI), @Tenure))  / (POWER((1 + @ROI), @Tenure) - 1),3) 

SELECT @EMI

Output

666176.751

Note:-

P - Loan Amount

R - Rate of Interest

N - Tenure/Loan Period

Depend upon your requirement, you may change round off figure.