Hello to all Excelers,
I need help in coming up with a proper formula to determine payment.
Payment is due every month on the same day starting from a given date. If payment is made on the same day every month, it's simple. But payment can be made on any day so I need a formula to determine how much is due including any missed payments from previous months.
today's date: B1 = today()
start date: B3 = any date
last payment date: B5 = any date after start date
monthly payment: B7 = 10
payment due today: B9 = formula to determine payment
If payment was made every month formula would be:
=IF(DAY(B1)>=DAY(B3),B7,0)
To include missed payments I tried this formula:
=IF(DATEDIF(B5,B1,"m")>=1,DATEDIF(B5,B1,"m")*B7,IF(DAY(B1)>=DAY(B3),B7,0))
but it doesn't work for all situations.
If anyone can help it would be much appreciated. I've tried so many if statements it's driving me nuts and now I need professional assistance. Thank you.
I need help in coming up with a proper formula to determine payment.
Payment is due every month on the same day starting from a given date. If payment is made on the same day every month, it's simple. But payment can be made on any day so I need a formula to determine how much is due including any missed payments from previous months.
today's date: B1 = today()
start date: B3 = any date
last payment date: B5 = any date after start date
monthly payment: B7 = 10
payment due today: B9 = formula to determine payment
If payment was made every month formula would be:
=IF(DAY(B1)>=DAY(B3),B7,0)
To include missed payments I tried this formula:
=IF(DATEDIF(B5,B1,"m")>=1,DATEDIF(B5,B1,"m")*B7,IF(DAY(B1)>=DAY(B3),B7,0))
but it doesn't work for all situations.
If anyone can help it would be much appreciated. I've tried so many if statements it's driving me nuts and now I need professional assistance. Thank you.
