financial tracker formula

[conception_native_0123]

i need help with formula please. i have sheet ready with all numbers in it but i trying to put in column to calculate how much payment is on interest accrual day. accrual day is different than payment due date. should i upload excel book so others can see? that might be easy way to show others. thank you.

 

[conception_native_0123]

I just realize that my last post made no sense. Sorry everyone. I will upload document in little bit.

 

[conception_native_0123]

my document is very simple. there is 2 records. column 'I" is what i don't understand. column "h" is something i calculate myself. i trying to track heavy interest charged on 2 cards. these 2 close to balance limit and i want make sure that no over limit fee is ever charged. so what i doing is keeping track of statement dates (interest accrual date) and check weather the amount interest that accrues will put balance over limits. if it does on date that interest accrues, i want to put in column 'I'' the date 2 days before column "g". that way the person i doing this for will know that she has to pay the interest before it accrues so she avoid over limit fee. is that good enough explaination? i do not know excel function very well. i really do not know how to write more than one together. but i did manage to do column h. thank you.

 

[arnelgp]

I was thinking that you should know the formula.
if you don't, ask anyone in your organization that knows.

 

[conception_native_0123]

that doesnt help arnelgp. yes i should know but im not finance expert. i do not know excel. thank you.

 

[arnelgp]

so what is your Calculation, disregard excel. you don't need to be a finance Expert if the calculation is
simple.

is it?

[Column I] = [Date Payment Due (column D)] + [Interest Accrual Day of Month (Column G)] - 2 Days

 

[conception_native_0123]

no it not that simple arnelgp. because if regular payment is made before interest accrual date on any given month, the minimum due related to interest accrual doesnt need happen. is that understandable? because regular payments taken into account interest accrual themselves. no need to pay on both days. no need to pay on interest accrual day if it happens after regular payment due date every month. hope that clear.

 

[arnelgp]

its clear. and you are only Interested on the [MIN DUE DATE TO AVOID OVER-LIMIT FEE (Column I)], is this correct?
if the computation is not that simple, then show us what is the computation and we will translated it to a formula.

 

[conception_native_0123]

yes that correct. so here would be code possibly:

if [DAY OF *column d*] > [column g] then
  [column "i"] = [(LONG DATE based on DAY of *column g* and MONTH of *column d*) - 2 days]
else
  [column "i"] = "NA"
end if

does that make sense hopefully? i guess it is more simple than i thought. thanks.

 

[arnelgp]

based on your formula on post#9.

 

[conception_native_0123]

thank you arne. i will look in a little bit and post again if i need more help. it will be later today. thank you.

 

[conception_native_0123]

ARNE,

i change what you make to do what i think i need. can you confirm for me if what i did is right? here my new forumla

=IF(G2<DAY(D2),IF(ABS(C2-B2)<ABS(F2),DATE(YEAR(D2),MONTH(D2),G2-2),"N/A"), "N/A")

i wanted it to do this, and seems like it works fine because i run tests to see

if INTEREST ACCRUAL DAY ON MONTH < DAY PAYMENT DUE then
  if AVAILABLE CREDIT < NEXT INTEREST ACCRUED then
    min due to avoid over-limit fee = DATE(YEAR(D3),MONTH(D3),G3-2) (what you did for me)
  else
    "N/A"
  end if
else
  "N/A"
end if

it seems i did it right. yes? i upload final sample. can you tell me? thank you.

 

[arnelgp]

seems fine against the logic you showed.

 

[conception_native_0123]

thank you. i will go with it.