Calculate Progressive Interest

arpan_de

New member
Joined
Sep 26, 2014
Messages
4
I have got the table below which shows the amount of money A has given to B and the amount of money B has returned to A. I would like to calculate the interest (compounded annually) at 5% pa on each transaction that A will be charging B. For e.g. on 02.09.2009, A gave Rs. 75,000 to B which B returned on 27.01.2010. I can very well calculate the interest on that but what about the interest on the rest of the transactions? That is where I am getting stuck. Can someone please suggest me how to go about it? Thanks

DATEPAYMENT FROM A TO BPAYMENT FROM B TO A
02-09-200975,000.00
14-09-200930,000.00
27-01-201075,000.00
25-02-2010​
25,000.00
25-02-2010​
25,000.00
25-02-2010​
25,000.00
25-02-201027,000.00
08-03-201020,000.00
10-03-201020,000.00
10-03-201030,000.00
15-03-201032,000.00
26-03-201025,000.00
29-03-20105,000.00
15-04-2010​
2,000.00
15-06-20101,30,000.00
13-12-201050,000.00
09-02-201180,000.00
 

tkhunny

Moderator
Staff member
Joined
Apr 12, 2005
Messages
10,098
"I can very well calculate the interest on that..."

Please demonstrate.
 

arpan_de

New member
Joined
Sep 26, 2014
Messages
4
I know the no. of days that have elapsed between the payment made by A to B & the payment made by B to A which is 148. That divided by 365 will give the no. of years which is 0.4. I also know the rate of interest. So by using the compound interest formula i.e.

Amount=75000(1+5/100)^0.4

I get the interest using the formula

Interest=Amount-75000

I hope I am clear.

Thanks
 

tkhunny

Moderator
Staff member
Joined
Apr 12, 2005
Messages
10,098
I know the no. of days that have elapsed between the payment made by A to B & the payment made by B to A which is 148. That divided by 365 will give the no. of years which is 0.4. I also know the rate of interest. So by using the compound interest formula i.e.

Amount=75000(1+5/100)^0.4

I get the interest using the formula

Interest=Amount-75000

I hope I am clear.

Thanks
Couple of things.

1) That may be "Compounded Annually". It depends on what you do next.
2) 148/365 = 0.405479. Rounding might make a difference.

--- $75,000 * 1.05^0.4 = $76,478.08
--- $75,000 * 1.05^0.405479 = $76,798.53 -- $20 more.

You'll have to decide what sort of precision is needed in your application.

3) You'll probably have to split things up a little more usefully.
14-09-200930,000.00
27-01-201075,000.00
25-02-201025,000.00
25-02-201025,000.00
25-02-201025,000.00
25-02-201027,000.00
08-03-201020,000.00
10-03-201020,000.00
The problem is that 14-09-2009 $30,000 never comes back as $30,000. Split it up like it does come back..

A to B
14-09-2009 $20,000 = P1Out
14-09-2009 $10,000 = P2Out ==> Total P1Out + P2Out = $30,000
25-02-2010 $10,000 = P3Out
25-02-2010 $15,000 = P4Out ==> Total P3Out + P4Out = $25,000

B to A
08-03-2010 $20,000 = P1In
10-03-2010 $10,000 = P2In ==> Total P1In + P2In = $30,000
10-03-2010 $10,000 = P3In
P4In will have to be on some other date.

P1Out to P1In will have its own interest charge.
P2Out to P2In will have its own interest charge.
P3Out to P3In will have its own interest charge.

There are other ways to proceed. This may prove sufficient.
 

arpan_de

New member
Joined
Sep 26, 2014
Messages
4
Thanks for your response. Yeah splitting the figures looks like a good solution but I guess it would turn out to be a tedious solution as well (not that I am belittling your efforts). Any other ways to proceed??

Thanks once again
 

tkhunny

Moderator
Staff member
Joined
Apr 12, 2005
Messages
10,098
Thanks for your response. Yeah splitting the figures looks like a good solution but I guess it would turn out to be a tedious solution as well (not that I am belittling your efforts). Any other ways to proceed??

Thanks once again
haha No belittling taken.

It depends on what kinds of records you want to keep. In particular, if you don't need to attach the interest to a specific loan, you can just accumulate the interest on the B side.
 
Top