Please let me explain this complicated Spreadsheet that I am attempting to build. I work for an agency that is awarded judgments through small claims court. The way the money award is set up is flexible but at its basic level a judge awards the following on the Date of Judgment:
• Date of Judgment: 6-1-14
• Principle Amount: $5000
• Principle Amount Interest Rate – Simple interest: 18%
• Cost & Fee Amount: $500
o Cost & Fee Amount Interest Rate – Simple Interest: 9%
• Pre Adjudication Interest Amount- Which is the amount of interest that accrued from date of last activity till Judgment date. This can either continue to accumulate interest or can be awarded at a flat amount. $100
The Interest is calculated daily. The debtor pays whatever money they have whenever they can. So there isn't a due date or payment schedule per se. typically as a payment is made it will be posted in the following way against the following amounts:
1. Principle interest accumulated amount. So
a. =((Principle Interest /365.25)*Principle Amount)*(Payment date-Date of Judgment)
2. Cost & Fee interest accumulated amount. So
a. =((Cost & Fee interest/365.25)*Cost & Fee Amount)*(Payment date-Date of Judgment)
3(a). If there is an Interest Rate attached to the PRE adjudication Interest amount. So
a. =((Pre Interest/365.25)*Pre Amount)*(Payment date-Date of Judgment)
If the payment satisfies the accumulated simple interest amounts than we can begin posting that payment against the following in order
3(b). Pre Adjudication Amount
4. Cost & Fee Amount
5. Principle Amount
On the surface this may seem like a simple set of equations and formulas, but it has proven to be quite complex. One wrench in the whole thing is that additional Cost & Fee’s may come up AFTER the date of judgment that will alter the Cost & Fee Interest accumulation and the Cost & Fee Balance. So somehow we need to be able to add additional cost and fee’s without a payment being made on that date (the answer lies within a negative payment being made that will be added to the cost and fee amount, this is just difficult to apply).
The goal of this is to be able to determine:
1. Post Adjudication Interest-So all interest accrued after the Judgment Date to a specific date.
2. Total Payoff Amount on any date after the judgment and/or after any payments has been made.
I have been working on this for a while and have a spreadsheet that is very close to being the answer, just need help working out a few kinks, or if somebody has a whole other approach I would love to look at it.
I am willing to go back and forth with you until we both solve this. I am looking forward to seeing your work and sharing mine.
Thanks in advance for time and thought processes
Nick
Bookmarks