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

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