Interest Calculation By Date

Question

I am now in a very tricky situation. Using MSSQL 2012 Express.

I have a voucher table from which I am retrieving following details for PARTY A for example.

In following query result, I am getting all details successfully except last column INTEREST. In last column, I need to find INTEREST AMOUNT of previous row BALANCE.

EXPLANATION

PARTY A did our JOBWORK worth of RS. 10000.00 on date 01-01-2016. COMPANY CASH pays PARTY A RS 5000.00 on date 05-01-2016. So in INTEREST column, I need to calculate interest of RS. 10000 for 4 days at an assumed interest rate of 12%. And so on.

VOUCHER_NO        VOUCHER_DATE        FROM        TO        DR        CR        BALANCE        INTEREST
--------------------------------------------------------------------------------------------------------
1                 01-01-2016          PARTY A     JOBWORK             10000.00  -10000.00
2                 05-01-2016          CASH        PARTY A   5000.00             -5000.00       NEED INTEREST OF 10000.00 HERE
3                 15-01-2016          CASH        PARTY A   4000.00             -1000.00       NEED INTEREST OF 5000.00 HERE

@GordonLinoff, The shown structure is the required result. In actual table, only Balance and Interest columns are missing.I am able to calculate Balance successfully, but no success with Interest column as it is needed in next row. — Hemal, Feb 23 '16 at 12:01
You can use `LAG(BALANCE)` to access the previous row's data — dnoeth, Feb 23 '16 at 12:08
1] There aren't always 365 days in a year, what happens to the rate during leap years? In such a case, what happens when you travel over the year boundary? 2] Shouldn't the interest be calculated on the balance, **plus** the interest from the last period? Note that interest often isn't _due_ until a specific date, often monthly (or every 30 days). — Clockwork-Muse, Feb 23 '16 at 12:31
@Clockwork-Muse, You are correct for your second opinion but its with banks. In private firms, they are calculation interest based on number of days. So I need to calculate interest on days — Hemal, Feb 23 '16 at 12:33
...so does the interest compound (added to the balance) or not? If it compounds, is it daily? — Clockwork-Muse, Feb 23 '16 at 12:51
Not added to the balance, and not daily, but when voucher date changes, at that row, i need interest of previous row balance. — Hemal, Feb 23 '16 at 13:09
Yes LAG worked for me, I changed it according to my need. Still facing some problem with other area but will try my best to solve it on my own, otherwise will post a question. — Hemal, Feb 24 '16 at 12:40

TT. · Accepted Answer · 2016-02-23T12:20:30.530

1

This one calculates with 365.25 days/year. Change that number to whatever you need:

SELECT*INTO #bal FROM(VALUES(1,{d'2016-01-01'},CAST(-10000.00 AS DECIMAL(28,2))),(2,{d '2016-01-05'},CAST(-5000.00 AS DECIMAL(28,2))),(3,{d '2016-01-15'},CAST(-1000.00 AS DECIMAL(28,2))))AS b(vno,dt,bal);
SELECT 
    *,
    interest=ROUND((LAG(bal)OVER(ORDER BY vno))*(.12*DATEDIFF(DAY,LAG(dt)OVER(ORDER BY vno),dt)/365.25),2)
FROM #bal;
DROP TABLE #bal;

Result:

+-----+-------------------------+-----------+------------+
| vno |           dt            |    bal    |  interest  |
+-----+-------------------------+-----------+------------+
|   1 | 2016-01-01 00:00:00.000 | -10000.00 | NULL       |
|   2 | 2016-01-05 00:00:00.000 | -5000.00  | -13.140000 |
|   3 | 2016-01-15 00:00:00.000 | -1000.00  | -16.430000 |
+-----+-------------------------+-----------+------------+

edited Feb 23 '16 at 12:20

answered Feb 23 '16 at 12:08

TT.

15,774
6
47
88

Let me check on my side – Hemal Feb 23 '16 at 12:09
@Hermal Hmm interest seems kind of high =) I might be off a factor of 10. My math sucks =) – TT. Feb 23 '16 at 12:13
NO. 1) You can't just use the "average" value, you need the actual number of days. 2) You **ABSOLUTELY** can't use floats in monetary calculations, since stuff like `.1` can't be represented exactly, among other things. – Clockwork-Muse Feb 23 '16 at 12:18
@Clockwork-Muse This snippet is just to show the basics of lagging to get the previous balance and date. AFAICT you can calculate using floats, it's when you store the results that should be done as decimal type. Otherwise any accountant using a calculator would be wrong. – TT. Feb 23 '16 at 12:29
Is there any other way to do this without using `LAG`? – Hemal Feb 23 '16 at 12:31
@Hemal You can self-join (LEFT JOIN) linking with the previous voucher number. You have a problem with `LAG`? – TT. Feb 23 '16 at 12:32
I am actually getting the `BALANCE` with the help of `SUM OVER (ORDER BY VOUCHERNO)` and using `LAG` outside it gives me error of `WINDOWED FUNCTION CANNOT BE USED....` – Hemal Feb 23 '16 at 12:35
Populate your balance table to an intermediary table (a temporary table by way of `SELECT ... INTO #balance FROM ...`), and use the temporary table would be one way. Don't forget to drop the temporary table after the selection. – TT. Feb 23 '16 at 12:36
...calculation **requires** storing the value off - not into the database, but into other variables. You don't even have complete control over it, either. At that point, things go out the window. Depending on the numbers involved, decimal places to 6 digits isn't unheard of (say, currency conversion). Which, if the numbers are large enough, will give you strange results real fast. Be disciplined, and use only decimals for monetary calculations. – Clockwork-Muse Feb 23 '16 at 13:03

Interest Calculation By Date

1 Answers1