Search This Blog

Wednesday 27 February 2019

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.

23 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. This comment has been removed by a blog administrator.

      Delete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. Helpful artical.
    Thanks for a well written and informative post.
    10 Key Steps to Getting a Small Business Funding

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. This comment has been removed by a blog administrator.

    ReplyDelete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. This comment has been removed by a blog administrator.

    ReplyDelete
  13. solution to all problems15 January 2020 at 06:01

    This comment has been removed by a blog administrator.

    ReplyDelete
  14. This comment has been removed by a blog administrator.

    ReplyDelete
  15. This comment has been removed by a blog administrator.

    ReplyDelete
  16. This comment has been removed by a blog administrator.

    ReplyDelete
  17. This comment has been removed by a blog administrator.

    ReplyDelete
  18. This comment has been removed by a blog administrator.

    ReplyDelete
  19. This comment has been removed by a blog administrator.

    ReplyDelete
  20. This comment has been removed by a blog administrator.

    ReplyDelete
  21. HOw can i Calculate quaterly reducing loan,,.. can u help me please?

    ReplyDelete
  22. NICE QUERY TO CALCULATE EMI

    THANKS

    ReplyDelete