Thread: Spreadsheet for calculating interest and loan payment on judgement with no ending date

1. Spreadsheet for calculating interest and loan payment on judgement with no ending date

I won a settlement against a contractor in small claims court and agreed to allow him to pay me monthly until the settlement amount is paid off, with interest. The interest is calculated daily. The debtor pays whatever money he has whenever he can. So there isn't a due date or payment schedule per se. What I'm looking for is something that calculates the interest from the date of judgment to the date of payment. Then add that to the balance owing less the amount paid. Any help would be GREATLY appreciated!

2. I am not sure how you want to set it up and what the values are. But perhaps this can help

The Balance at any moment of time with Daily interest compounding is:
= Previous Balance * (1+AnnualPercentageRate/365.25)^(Days with no payment) - Amount Paid

so You can set up the date of payments and calculated the balance going down, based on the difference between days, the interest and the running balance.

Steve

3. Here is an example workbook. I separated out the interest earned so the running total is
Previous - Payment + Interest

Steve

4. Judgment Calculator

Hey Steve, thanks for the insight, but I'm stuck with the same kind of problem. I'm trying to use your spreadsheet, but the problem is that the judgment started out at 14,211.55 on 3/28/2008 at 12% interest. The debtor made a payment of 1,125 on 3/24/2010 so the accrued interest at that time was \$3,343.07 (far less than his payment). Under an agreement with debtor, his payments are credited first to outstanding interest, then principal. Am I correct in assuming that when he made his next payment on 11/22/2011 (598 days later) he had accrued an additional 2,792.12 in interest, PLUS the previous interest balance of 2,218.07 (previous balance after applying his 1,125 payment), making his new INTEREST balance of 4,510.19 and PRINCIPAL remaining at 14,211.55? Is there any way to modify your sheet so that payments go first to interest and IF the payment is less than the outstanding INTEREST the interest accumulates? This has been driving me crazy all day and I really would like to have a bullet proof spreadsheet for use in the future. Thanks for any help you can provide.

5. mgrantom,

Building on Steve's fine work I think this is what you are after?
mgrantom.JPG
Please note that I made up the second payment since you didn't specify and I made it so it was more than the outstanding interest to verify the calculations. You'll also note that the interest calculates differently than your numbers, this may be due to the fact that it appears as if you are not using compound interest but rather simple interest and not adding in the outstanding interest balance to the principle in calculating interest before the 2nd payment...please elaborate).

mgrantom Interest example.xls

HTH

6. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

mgrantom (2014-02-06),mllf (2014-12-02)

7. I am not sure where you are getting your numbers from. Even the elapsed days I calculate differently (you mention 598 I aget 608), so there is some differences. I suspect that RG is right and you want simple, not compound interest.

In the compount interest after a period of time, you not only owe 12% on the principal put you owe 12% on the accumulated unpaid interest. In simple interest the interest grows just based on the original principal, the unpaid interest you are essentially loaning them interest-free (and this is not a trivial matter). The \$1,125 being paid about every 2 years does not come close to the annual interest of over \$1,700 (over \$3400 in 2 years). The debt will never be paid off at this rate.

Here is a modification with 2 calcs side-by-side. Cols E/F are the compounded interested (daily) with that balance. Cols G/H are assuming an interest of 12% every year and then taking the amount based on the difference in days assuming 265.25 days/year.

You can delete the 2 you don't need. You can also delete col d as it is not used, it is only for reference.

Steve

8. The Following User Says Thank You to sdckapr For This Useful Post:

mgrantom (2014-02-06)

9. Thanks

THANK YOU, THANK YOU THANK YOU!! YOU GUYS ARE AWESOME!!

I noticed the difference in days too. I played around with this so much that I'm afraid I used a variety of methods to calculate the number of days. I believe your example just subtracts the end date from the beginning date, which I didn't even know you could do. I originally used the function 360days() so obviously it is using 360 days. Yes, you are correct, under Texas law the interest is simple not compounded. I am attaching a complete list of payments. Are you saying that your revised sheet IS using compound interest? If so, how do I change it? Did I say Thanks!!?

10. You are welcome.

My revised sheet has both. The Cols E/F has the numbers for Compound. The Cols G/H have the calcs for simple. You can delete cols E/F and it will only have simple interest.

I used 365.25 days/year. If you want change the values in col G. [you could also modify the annual interest to daily interest and put the calc in C3 [=C2*C1/365.25 or =C2*C1/360] and then eliminate the value in the calcs in Col G.

Here is a modified example with the explicit option to change the days/year for calculations. I also modified the simple interest, to allow for the principal to decrease as well instead of always assuming \$1700/year interest forever. Check it out with some future payments to see if it works as desired.

Steve

11. The Following User Says Thank You to sdckapr For This Useful Post:

mgrantom (2014-02-06)

12. mgrantom,

Simple Interest would be calculated as:
Daily Interest Amount = \$E5*(\$C\$2/365.25)
Number of Days = (\$B6-\$B5)
Interest since last payment: ROUND((E5*(\$C\$2/365.25))*(\$B6-\$B5),2))
Full formula with Steve's logic for empty cells:
=IF(OR(\$B6=0,\$C6=0),"",ROUND((\$E5*(\$C\$2/365.25))*(\$B6-\$B5),2))<-- Formula for D6 then copy down.

You didn't answer the question about charging interest on the UNPAID interest - Yes or No?
If YES then formula would be:
=IF(OR(\$B6=0,\$C6=0),"",ROUND(((\$E5+\$G5*(\$C\$2/365.25))*(\$B6-\$B5),2))<-- Formula for D6 then copy down.

HTH

13. The Following User Says Thank You to RetiredGeek For This Useful Post:

mgrantom (2014-02-06)

14. My impression was the interest was only charged on the principal which is what "simple interest". "Compound interest" is when you pay interest on unpaid interest. What distinguishes the different types of compounding is when the compounding is done, annually, monthly, daily, per payment, etc.

Steve

15. The Following User Says Thank You to sdckapr For This Useful Post:

mgrantom (2014-02-06)

16. Steve,

Normally I would agree with you but since the payee isn't even paying all the accrued interest due, going back on my dated education as an accountant, the unpaid interest should be added to the principal since that amounts to another loan. I may be out of date here but that's the way I understood it.

17. The Following User Says Thank You to RetiredGeek For This Useful Post:

mgrantom (2014-02-06)

18. I agree and I would view this as compounded interest with the period being when payments are done. But hey, I was never an accountant, nor do I claim to be. I am a chemist and I am just looking at a "growth" and whether it is linear (simple) or exponential (compounded) and the type of the compounding if approriate.

I was confused when with the phrase "The interest is calculated daily" since that suggests more compounding than banks do (which is typically calculated monthly). That statement is inconsistent with the current spreadsheet which calculates it by period. But even with the interest only being calculated at the time of payment with no compounding within that period, you raise a valid question: what should the new interest be based on:

The original principal [This seems "wrong" when the balance drops below the principal]
The max of the previous balance and the original principal (which is what my latest used)
The previous balance [which is your suggestion and I endorse this as being the "fairest", but does compound the interest since you are paying interest on interest]

We will have to see how mgrantom wants to calculate it.
Steve

19. The Following User Says Thank You to sdckapr For This Useful Post:

mgrantom (2014-02-06)

20. Once again, thanks for all the help. I'm assuming that simple interest would mean that I would not calculate interest on the unpaid interest. Not sure if this is technically correct, but it gives the debtor the benefit of the doubt, so that is how I'm going to do it this time. Next time, I will specify the exact method in any post judgment agreement. I'm an attorney by profession and really don't care for trying to figure out this type of accounting problem, but you guys really came through for me. I learned some new concepts in Excel and I'm very appreciative. Thanks!!