Solved

Calculate Interest By Day with Month End Balance

Posted on 2006-06-29
9
813 Views
Last Modified: 2012-05-05
I have the following data in 1 table:

Account      Transaction        Transaction
                Date                   Amount
1               05/01/2006        1000.00
1               05/05/2006         3000.00
1               05/20/2006         -500.00
1               05/21/2006         4000.00
1               05/24/2006         -200.00

I have the ending balance in another table:
Ending Balance      6300.00

WHAT I NEED TO GET TO IS:

                Transaction                         Ending        (.045 * Balance)/365
Account      Date                Amount        Balance       Interest
1               05/01/2006      1000.00      3800.00       .05
1               05/02/2006                        3800.00       .05
1               05/03/2006                        3800.00       .05
1               05/04/2006                        3800.00       .05
1               05/05/2006      -800.00       3000.00       .04
1               05/06/2006                        3000.00       .04
1               05/07/2006                        3000.00       .04
1               05/08/2006                        3000.00       .04
1               05/09/2006                        3000.00       .04
1               05/10/2006                        3000.00       .04
1               05/11/2006                        3000.00       .04
1               05/12/2006                        3000.00       .04
1               05/13/2006                        3000.00       .04
1               05/14/2006                        3000.00       .04
1               05/15/2006                        3000.00       .04
1               05/16/2006                        3000.00       .04
1               05/17/2006                        3000.00       .04
1               05/18/2006                        3000.00       .04
1               05/19/2006                        3000.00       .04
1               05/20/2006      -500.00       2500.00       .03
1               05/21/2006      4000.00      6500.00       .08
1               05/22/2006                        6500.00       .08
1               05/23/2006                        6500.00       .08
1               05/24/2006      -200.00       6300.00       .08
1               05/25/2006                        6300.00       .08
1               05/26/2006                        6300.00       .08
1               05/27/2006                        6300.00       .08
1               05/28/2006                        6300.00       .08
1               05/29/2006                        6300.00       .08
1               05/30/2006                        6300.00       .08

Total Monthly Interest 1.63

Any help on this is greatly appreciated!!
0
Comment
Question by:Angela_Wilcox
  • 4
  • 4
9 Comments
 
LVL 5

Expert Comment

by:MageDribble
ID: 17012191
you're best bet is to create a view.  Add a column called interest earned and put your formula as the column.

you can then summarize the data as needed.

Did you want more detail?
0
 

Author Comment

by:Angela_Wilcox
ID: 17012342
Please!  I can't figure out an easy way to generate the days that I don't have transactions for - and to calcuate the balance for each day using the ending balance for the month (I cant' get the starting balance).
0
 

Author Comment

by:Angela_Wilcox
ID: 17012413
I should have noted that there can be multiple transactions on a date (not represented in my example) and I only want to calculate interest on the daily ending balance.
0
 
LVL 13

Accepted Solution

by:
devsolns earned 500 total points
ID: 17013269
This took me FORVER!  But I think it is what you need.




--Temp table to hold the data
CREATE TABLE #NewTable
(
MyDate datetime not null,
TranAmount int not null,
NewBalance int not null,
Interest decimal(16,0) not null,
)



DECLARE @myDateCounter int
DECLARE @EndBalance int
DECLARE @StartBalance int
DECLARE @Interest decimal(16,4)

--Will get the next months dates
SET @myDateCounter = DatePart(day,DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, getdate()) + 1, 0))) - DatePart(day,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

--Get the balance from another table.....SELECT @EndBalance = mybalance FROM mytable
SET @EndBalance = 6500

WHILE (@myDateCounter != -1)
BEGIN

SET @StartBalance =       ISNULL((
                                    SELECT Sum(TransAmt)
                                    FROM myTransactions
                                    WHERE Convert(varchar,TransDate,101) = Convert(varchar,DateAdd(day, @myDateCounter, DateAdd(month, DateDiff(month, 0, getdate()) + 1, 0)),101)
                                    GROUP BY TransDate
                                    ),0)
SET @Interest = (.045 * @EndBalance)/365


      INSERT INTO #NewTable
      VALUES(
      Convert(varchar,DateAdd(day, @myDateCounter, DateAdd(month, DateDiff(month, 0, getdate()) + 1, 0)),101),
      @StartBalance,
      @EndBalance - @StartBalance,
      @Interest
      )
      SET @myDateCounter = @myDateCounter - 1
      SET @EndBalance = @EndBalance - @StartBalance
END

SELECT * FROM #NewTable

DROP Table #NewTable



Hope it helps!

--Dustin Sterkenburg
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 13

Expert Comment

by:devsolns
ID: 17013302
By the way...... myTransactions is just a table with a TransDate and TransAmt column with a bunch of data.  

--DS
0
 

Author Comment

by:Angela_Wilcox
ID: 17013351
I'll give it a try using my tables.  Thank you!
0
 
LVL 13

Expert Comment

by:devsolns
ID: 17013467
No problem.......glad to help.

--DS
0
 
LVL 13

Expert Comment

by:devsolns
ID: 17013514
I just realized that this will give you all the dates for next month based on your system date....you just have to change it a little to get this months.....   :o)
0
 

Author Comment

by:Angela_Wilcox
ID: 17043301
With some modification - I was able to get the example to work with my database and views.  I'm working on improving the performance as it is very slow...
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now