Calculate Interest on an Amount Between two Dates using SQL Server

We were working on a financial calculation recently where given two dates, the interest had to be calculated for a given amount. Thank to my colleague Satyam to help me out with the query

Here we are calculating the interest accumulated on the amount 395.80 at the rate of 15% per annum between 09-22-2009 and 07-13-2010. Please note that the interest rate calculated here is based on the business rules we had and the output expected. It may be a different in your case, for which you will have to modify the query.

The formula is as follows

Interest = Amount * InterestRate^(Days/365.25)

Query

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @Amount Decimal(6,3)

SET @StartDate = '2009-09-22'
SET @EndDate = '2010-07-13'
SET @Amount = 395.80


SELECT
@Amount*(POWER(1.1500, CONVERT(NUMERIC(8,3),
DATEDIFF(d, @StartDate, @EndDate)/365.25))) - @Amount
as TotalInterest

In the query shown above, we are using the POWER function which returns the value of the given expression to the specified power. Here are Convert the Date Difference into numeric value to provide it to the POWER function which comes out to be .805 i.e POWER(1.1500, .805). The interest rate is 15%,

Effectively this query comes out to be 

SELECT @Amount * (POWER(1.1500, .805)) - @Amount
as TotalInterest

or

SELECT @Amount * 1.1191 - @Amount

OUTPUT

image

at

2 comments:

  1. HCNNovember 24, 2016 at 9:16 PM

    Dear,
    could you please explain more, why you divide 365.25 ? i have the case like this: Customer has installment to pay for company
    ex:
    Require date - Require amt
    - 01/01 10
    - 05/01 10
    - 10/01 10
    in those time customer does Pay/withdraw
    Payment date - Amt
    - 01/01 5
    - 03/01 7
    - 07/01 -3
    - 10/01 10
    - 15/01 11
    i would like to calculate interest for each day that customer hay pay for late with 1% each day on every ins no.
    look for your help.

    ReplyDelete
  2. braamFebruary 12, 2017 at 11:58 AM

    I am looking for a way to have the following calculation in SQL:

    Example:

    Start amount: 900
    Annual Interest: 7.45%
    Loan Start Date = April 20 2015
    Capitalize interest every last day of the month for the month
    First payment date is May 31 2015
    Loan duration is 36 months

    The first 10 days between April 20 and April 30 needs to be capitalized

    Then on May 31 the interest for may needs to be capitalized but the installment from the 1st payment date on May 31 must reduce the interest for the 1 day because the capital part of the installment reduce the capital for the day

    Based on the all these calculation the monthly installments needs to be calculated

    ReplyDelete