Excel 2007 : If Statement for Recurring Interest and Late Fees

**Cdoyle** · 02-05-2011, 10:29 PM

I am trying write a formula that will automatically change the value
of the "Late fee" column to show the total owed on the 1st of every Month after the due date. For example:
Col “A” Due Date 1 May 2011
Col “B” Date Paid
Col “C” Paid Amount
Col “D” Late Fee Owed

1 May 2011-31 May 2011 if the person has not paid the fee of $194.00 on 1 Jun 2011 they will ($194.00 [amount owed] * 5%[interest]) + $20.00 [late fee] = 223.96. If they still have not paid by 1 Aug 2011, they will now owe( 223.96*5%)+$20.00=$255.16 and so on until they pay.

**zbor** · 02-06-2011, 04:25 AM

Try this... But I think you failed...

194+5% +20 is 223.70
and August is third month, not second...

Check example...

What I get is:

Due date	Date Paid	Paid amount	Late Fee Owed
1.5.2011	10.5.2011	$194,00	$194,00
1.5.2011	9.6.2011	$194,00	$223,70
1.5.2011	9.7.2011	$194,00	$254,89
1.5.2011	8.8.2011	$194,00	$287,63
1.5.2011	7.9.2011	$194,00	$322,01

**Cdoyle** · 02-06-2011, 09:07 AM

Thank you so much for taking the time to figure this out for me. The formula you wrote is great!!! Sorry about my type-O's, meant to say July vice Aug..... If possible I do have one more question. What if you leave the date paid blank col blank, how can you get the formula to calculate the late fees?