financial tracker formula (1 Viewer)

conception_native_0123

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
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

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
I just realize that my last post made no sense. Sorry everyone. I will upload document in little bit.
 

conception_native_0123

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
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.
 

Attachments

  • finance.zip
    8.5 KB · Views: 302

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:33
Joined
May 7, 2009
Messages
19,169
I was thinking that you should know the formula.
if you don't, ask anyone in your organization that knows.
 

conception_native_0123

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
that doesnt help arnelgp. :( yes i should know but im not finance expert. i do not know excel. thank you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:33
Joined
May 7, 2009
Messages
19,169
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

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:33
Joined
May 7, 2009
Messages
19,169
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

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
yes that correct. so here would be code possibly:

Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:33
Joined
May 7, 2009
Messages
19,169
based on your formula on post#9.
 

Attachments

  • finance.zip
    8.5 KB · Views: 319

conception_native_0123

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
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

Well-known member
Local time
Today, 02:33
Joined
Mar 13, 2021
Messages
1,826
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
Code:
=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

Code:
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.
 

Attachments

  • finance_confirmation_arnelgp.zip
    8.6 KB · Views: 162

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:33
Joined
May 7, 2009
Messages
19,169
seems fine against the logic you showed.
 

Users who are viewing this thread

Top Bottom