I am working with an Excel spreadsheet which covers a twelve month
period for a condo association's maintenance fee report.

My problem is composing an Excel formula which would ‘age’ only the
applicable late fee (which is $50.00 or 34.96503496503497% of the
monthly fee of $143.00).  The Excel formulation also must
automatically update from the initial month and year which the payment
was due to the present.

Here is the format of my spreadsheet:

Column A:     Date
Column B:     Type of Fee
Column C:     Description
Column D:     Amount (due)
Column E:     Payment (received)
Column F:     Late Fee
Column G:     Balance

Each ‘row’ represents a month (i.e., January 1992, February 1992,
etc.).

---

Request for Question Clarification by hammer-ga on 31 Dec 2002 12:40 PST

As you may be able to tell from the comments, we are unclear as to
what you need. In order to write formulas, we need to know exactly
what calculations you need performed under what conditions. For
example:

If today's date is more than 30 days after Date, and Amount is greater
than $0.00, then Late Fee should equal $50.00.  Interest should be
accumulated on the Late Fee per month at the rate of 1.5% compounded
monthly.

Please consider exactly what you want your spreadsheet to do and post
a clarification so we can write an accurate formula.

- Hammer

---

Clarification of Question by torrealta-ga on 08 Jan 2003 10:30 PST

The following should help clarify my inquiry:

If today's date is more than 30 days after Date, and Amount is greater
than $0.00, then Late Fee should equal $50.00.  Interest should
continue to
accumulate on the Late Fee for each additional month that the
Maintenance Fee is overdue at the rate of 34.96503496503497% of the
"Amount" compounded monthly (i.e., if the Maintenance Fee is three
months overdue the Late Fee would be $150.00 ($50.00 x 3 months); if
the Maintenance Fee is ten years overdue the Late Fee would be
$6,000.00 ($50.00 x 120 months)).

Thank you for your responses to date.

---

Clarification of Question by torrealta-ga on 08 Jan 2003 10:35 PST

Additional clarification:

The Excel formula should also automatically update each time the
spreadsheet is open to reflect the current date/late fee.

---

Request for Question Clarification by hammer-ga on 08 Jan 2003 11:02 PST

34.96503496503497% is a very peculiar number. Should we calculate
using this percentage, or can we simply use $50.00?

- Hammer

Assuming that the data starts on the second row of your spreadsheet,
with the Column Headers being in the first row, here is the formula
for the Late Fee column:

=IF(D2 > 0, INT((NOW() - A2) / 30) * (D2 * 0.349650349650349), 0)

The IF statement has three parts:
1. Condition to check, in this case, AmountDue > 0
2. What to do if the condition is True
3. What to do if the condition is False
These formulas will calculate if AmountDue > 0, otherwise, they simply
return 0.


This formula finds the number of days difference between today (NOW())
and the date in your Date column. It divides by 30 and takes the whole
number portion of the result to get the number of full 30 day periods
overdue. It then multiplies the number of 30 day periods by the
AmountDue times 34.96503496503497%. This method assumes that the
percentage is to be used, therefore, an amount on which a partial
payment has been made will generate a late fee of less than $50.00.

If you always want to use $50.00, regardless of the actual amount due,
use this one:

=IF(D2 > 0, INT((NOW() - A2) / 30) * 50, 0)

Search strategy: None. Used Excel.
Additional Resources: Browse through the Function list. This can be
reached by clicking the Paste Function button on you toolbar. It looks
like an f with an x subscript. This will show you a list of functions
and what they do. Read the Help on those that interest you.

Good luck with your Excel project!

- Hammer

Hmm, I believe some clarification might help me understand. First,
would it be correct to say that your report covers cumulative
maintenance fees your organization receives each month from all
tenants/members, or are you developing a report sheet to record
individual tenant/member payments?
Next, could you elaborate about "aging" late payments? I am guessing,
but, do you want your spreadsheet to automatically add a $50 late
charge if a maintenance fee is not received by a certain date? If so,
this might be accomplished with a conditional formula (i.e., very
roughly, =if((date>30),y+50,"")  ), which would yield either 50 or
zero in the cell, depending on the condition. One would then add the
results of that cell to the sum for each of the rows.

Otherwords, do you want a flat $50.00 late charge added to late
payments or the 34% added to late payments?