I need help. I have created a spreadsheet to calculate interest.

[deeepan2005]

I need help. I have created a spreadsheet to calculate interest.
the problem is that I need to be able to differ between interest rates for each invoice.
the interest rates ie 18%/Yearly I will divide the percentage by 365 to accurately calculate daily rate.

I want the program to calculate from the first day it becomes past due until it is paid.

I am having two range of data's 1. invoice date and invoices amount 2. amount paid and paid date

i need to calculate the interest for ageing 60 days as overdue daily interest to be adjusted by next payment made carry forwards the outstanding as overdue.



Kindly help.

 

[Dermot]

It isn't quite clear to me how you want this to work

can you lay out an example, perhaps?

 

[deeepan2005]

Bill date	Bill Amount		Payment Date	Payment Amount
29/07/2011	205410.00		11/08/2011	742421
29/07/2011	571188.00		22/08/2011	766523
29/07/2011	347552.00		23/08/2011	323780
29/07/2011	385640.00		30/08/2011	776598
29/07/2011	323780.00		14/09/2011	733192
29/07/2011	415884.00		01/10/2011	655188
29/07/2011	766523.00		13/10/2011	415884
29/07/2011	742421.00		27/10/2011	400000
30/07/2011	45776.00		16/11/2011	595423
30/07/2011	609412.00		17/11/2011	400120
20/08/2011	995423.00		01/02/2012	400000

this above data's to be calculated into below format.

Invoice date	Invoice Amount	Due date	Payment Amount	Date paid	Balance O/S	Interest rate	Interest	Balance Outstanding With Interest
04/01/2012​	150000​	05/31/2012​	100000.00​	06/10/2012​	50000.00​	18.00%​	493.15​	50493.15​
04/01/2012​			493.15​	15/10/2012	49506.85​	18.00%​		49506.85​
04/01/2012​		05/31/2012	49506.85​	10/15/2012		18.00%​	3344.76​	3344.76​
		10/15/2012	3344.00​	10/25/2012		18.00%​	16.49​	16.49​
04/10/2012​	200000​	06/09/2012​	100000​	08/08/2012​	100000​	18.00%​	2958.90​	102958.90​

 

[deeepan2005]

It isn't quite clear to me how you want this to work

can you lay out an example, perhaps?

hi,

i have shared the sample file please help me out.

Filebin :: bin 6iy5056nysjpv86v

Upload files and make them available for your friends. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

 

[Fluff]

Cross posted need to calculate interest on invoices each day past due until paid and leaving the int

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Dermot]

None of the figures in the top table correspond to figures in the bottom table, so I'm afraid it isn't any clearer yet

 

[deeepan2005]

Filebin :: bin 6iy5056nysjpv86v

Upload files and make them available for your friends. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

None of the figures in the top table correspond to figures in the bottom table, so I'm afraid it isn't any clearer yet

the sample file is having details.

 

[Dermot]

the sheets in that sample file don't connect with each other either, so it's not clear where the figures in the sample sheet come from

I'm sorry, I don't think I can help you any more.