+ Reply to Thread
Results 1 to 2 of 2

Please help! A Multi amount Simple interest spreadsheet calculator w/ random pay schedule

  1. #1
    Registered User
    Join Date
    07-10-2014
    Location
    Bend, Oregon
    MS-Off Ver
    office professional 2010
    Posts
    0

    Please help! A Multi amount Simple interest spreadsheet calculator w/ random pay schedule

    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
    Attached Files Attached Files

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145
    Hello amoroso,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1