Accrued interest Calculation

[logeshrbr]

Hi all,

I have been trying to generate interest calculation automatically for my business. For example if we take one client at a time my business norms is if the balance is unpaid for 10 days then interest is applicable from the date of purchase. II have entered the formula for this and it is working perfectly. For instance we shall take two bills bill1 and bill2. when the payment is made after 20 days from bill1 and 15 days from bill2 the interest has to be calculated for 20 days for bill1 and 15 days for bill2. Can anyone help me with this problem.

Thanks in advance....

 

[oldbrewer]

[TABLE="width: 910"]
<colgroup><col><col span="2"><col span="11"></colgroup><tbody>[TR]
[TD]amount[/TD]
[TD]inv date[/TD]
[TD]paid[/TD]
[TD]interest[/TD]
[TD]total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]let interest per day =[/TD]
[TD="align: right"]0.01[/TD]
[TD="colspan: 2"]this is cell K1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]01/07/2016[/TD]
[TD="align: right"]16/07/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]01/07/2016[/TD]
[TD="align: right"]26/07/2016[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]1000.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]01/07/2016[/TD]
[TD="align: right"]29/08/2016[/TD]
[TD="align: right"]0.59[/TD]
[TD="align: right"]1000.59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]D1 (interest = 0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=IF(C2-B2<20,0,(C2-B2)*$K$1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[logeshrbr]

Thank you oldbrewer for taking effort and replying with the answer. Here is a detailed vision on the problem for your reference.


[TABLE="width: 1246"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]S.No[/TD]
[TD]Order Date[/TD]
[TD]BILL NO[/TD]
[TD] Sale Value [/TD]
[TD] Balance [/TD]
[TD]Due Date[/TD]
[TD] Amount Received [/TD]
[TD]Date of payment[/TD]
[TD]Days of Delay[/TD]
[TD]ROI[/TD]
[TD] Interest Amount [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]16.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]29-10-2014[/TD]
[TD] 4[/TD]
[TD] 10,17,430.88[/TD]
[TD] 10,17,430.88[/TD]
[TD="align: right"]07-11-2014[/TD]
[TD] 5,16,135.00[/TD]
[TD="align: right"]18-11-2014[/TD]
[TD]21[/TD]
[TD]16%[/TD]
[TD] 4,751.27[/TD]
[TD] 4,95,900.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]05-11-2014[/TD]
[TD] 5[/TD]
[TD] 8,58,950.00[/TD]
[TD] 13,54,850.00[/TD]
[TD="align: right"]14-11-2014[/TD]
[TD] 3,00,000.00[/TD]
[TD="align: right"]25-11-2014[/TD]
[TD]21[/TD]
[TD]16%[/TD]
[TD] 2,761.64[/TD]
[TD] 10,49,000.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] 10,49,000.00[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]16%[/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]16%[/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
</tbody>[/TABLE]
This is an example. When the interest is calculated in the second row it has to calculate the pending amount of bill no. 4 from 29-10-2014 and the billno. 5 from 05-11-2014.

Thank you all...

 

[oldbrewer]

in my example add column after interest called cumulative interest with a formula of the type =sum(a$1:a4)

note the $ on the first 1

 

[logeshrbr]

Thank you Sir.
and sorry for bothering you.

The given answer is single dimensional. That is it works fine for a bill wise entry.
My problem is that at the same time there will be two bills pending. For example if there is pending in the bill1 1000 and bill2 1000 and the client has paid 2000 by a single payment then at the day it should calculate interest for both the bills with their respective dates. It should first finish off the oldest pending bill and then the remaining in the new bill.

Thank you....

 

[logeshrbr]

Is there any way to assign a value for a cell. Eg., if i can assign a the bill value to my bill no then by the use of logical formula the solution can be cracked I think...

 

[oldbrewer]

so client1 can have 2 bills, client2 3 bills etc?

are the bills paid off in one payment or are we talking monthly repayments ?