logeshrbr

New Member
Joined
Sep 20, 2016
Messages
4
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....
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
amountinv datepaidinteresttotallet interest per day =0.01this is cell K1
100001/07/201616/07/201601000
100001/07/201626/07/20160.251000.25
100001/07/201629/08/20160.591000.59
D1 (interest = 0)
=IF(C2-B2<20,0,(C2-B2)*$K$1)

<colgroup><col><col span="2"><col span="11"></colgroup><tbody>
</tbody>
 
Upvote 0
Thank you oldbrewer for taking effort and replying with the answer. Here is a detailed vision on the problem for your reference.


S.NoOrder DateBILL NO Sale Value Balance Due Date Amount Received Date of paymentDays of DelayROI Interest Amount
10 16.00%
129-10-2014 4 10,17,430.88 10,17,430.8807-11-2014 5,16,135.0018-11-20142116% 4,751.27 4,95,900.00
205-11-2014 5 8,58,950.00 13,54,850.0014-11-2014 3,00,000.0025-11-20142116% 2,761.64 10,49,000.00
3 - 10,49,000.00 116% - -
4 - - 116% - -

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
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...
 
Upvote 0
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
 
Upvote 0
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....
 
Upvote 0
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...
 
Upvote 0
so client1 can have 2 bills, client2 3 bills etc?

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

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top