• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Values based on ageing

Navi

Navi

Member
Hi all,

Need a help in calculating few ageing.

Scenario. there are many invoices with dates and which are not yet closed more than 10 months. Each invoices crossing 45-60 days we have 1% interest, 61-80 - 2% and ,more than 80 days is 3%.

Currently need a formula so that in one cell we can know the total value of interest we have paid for an invoice.

Regards,
Naveen
 
Last edited by a moderator:
@Navi
If someone write a formula here ...
Do You think that it would be even 99% suitable with Your scenario?
ReRead 'Tip:' (2nd line) and try to do so ...
 
actually am unable to set the formula as it should add month on month interest with different rate. hence unable to get idea on unique value. so updated here so can get an idea
 
okay ... You asked this:
Currently need a formula so that in one cell we can know the total value of interest we have paid for an invoice.
I would have that total value in Cell[A1] and it's formula is =sum(E:E)
1%?
 
Thanks for your post. My query is different.
A1 has the value. eg. $ 10000 with date 11/30/16
on 31st Jan interest of 0.1% will be charged. i.e, $ 10
on 28th Feb interest of 0.2% will be charged i.e $ 20
on 31st Mar interest of 0.35 will be charged i.e $ 30
curretnly need to update a fomulae in one cell so that it checks for the date of bill and calculates the cumulative interest from Jan till now and gives value
 
... if You have something in A1 ...
then why You won't upload that file here?
hmmm?
Interesting ... A1 has the value ... with date?
 
Hi, attached sample file.
column A has the invoice number. B date and c amount.
invoice ageing between 61-90 will have 1% interest and above will have 2 % interest. Column H is the total interest paid till March. Now what i wanted is a formula to directly give me the total of interest paid without Column E, F G which has to be calculated.
 

Attachments

  • Sample.xlsx
    8.7 KB · Views: 5
@Navi
Of course those interests are yearly values as usual and
'dates' are end of this years beginning of March or how?
.. and You sample values are just samples (not real values),
because AA1's the last day was the end of Feb 2017.
 
Bills which are created are only with month end date. for instance bill created on 28th Feb 2016 will take minimum 10 months to get paid. so after 30 days from the bill date we will have interest to be paid. hence if i calculate on october 2016 i need to know the interest rate paid from March till October.
 
@Navi
1) You cannot answer even my simply question, hmm?
2) Ten months and still want ONLY ONE formula. So You like looooong formulas.
3) And now You maybe try to tell, that someone will pay only interest in the last date. That means, invoices sum will stay same whole time.
4) Is there still something else, which should I need to know?
 
I feel your calculation in the worksheet you uploaded is incorrect as it does not calculate 3.5% for more than 120 days as mentioned in your criteria. Secondly, why are you subtracting by 61 and 91 instead of 60 and 90 ?

Anyways, I have given you both the ways. Enter the formula which suits your requirement and copy down.

SUMPRODUCT(--(D11>{61,91,121}),--(D11-{61,91,121})*{0.01,0.01,0}/360*C11)

OR

=SUMPRODUCT(--(D12>{60,90,120}),--(D12-{60,90,120})*{0.01,0.01,0.015}/360*C12)
 
You must log in or register to reply here.
Back
Top