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


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

2 comments:

HCN said...

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.

braam said...

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