Xcel newb needing help calculating simple interest on a judgment

[mault]

I have used court calculators in the past to calculate the amount of interest (simple) owed, to-date, on a judgment but have been tasked with something I am unaware how to solve. My best best was to figure out an Xcel formula in hopes of simplifying my life.

Judgment was awarded on December 22, 2018, the current amount owed as of today is $32,920.49, with an annual interest rate of 4%. The daily interest on the judgment is $3.61. What is tricky is that the debtor wants to make payments at the end of each month. Interest will continue to accrue until the judgment is paid.

My question: is there a formula that can deduct the payments (sometimes $1,000, could be more) from the principal amount of the judgment, and have the daily interest applied to the calculation?

Cells: A1 Judgment Date; B1 Judgment Amount; C1 Annual Interest Rate; D1 Daily Interest Rate; E1 Payments Made; F1 Amount Due

Thank you for ANY advice on this matter!

 

[kweaver]

Do you need to know when (each) payment was made?

 

[mault]

Do you need to know when (each) payment was made?

Ahh - I would probably need to add a column to account for the date of payment as well - thanks.

 

[kweaver]

Does this look correct?

D2: =B2*C2/365
G2: = F2-A2
G3: =F3-F2
H2: =G2*$D$2 (fill down)
I2: =B2+H2-E2
I3: =I2+H3-E3 (fill down)

 

[mault]

Does this look correct?

D2: =B2*C2/365
G2: = F2-A2
G3: =F3-F2
H2: =G2*$D$2 (fill down)
I2: =B2+H2-E2
I3: =I2+H3-E3 (fill down)

This is excellent work - thank you! Payments have not started yet and the first payment should be June 30th of this year. Since the judgment is as of today's date, should I change the judgment amount to account for the date I actually receive payment? and then go from there? Otherwise, I am unclear how to initially calculate the payments. Thank you

 

[kweaver]

You would enter the data in A2, B2, C2, and E column and F column. The calculations are then made in D2 and the G, H, and I columns.

 

[mault]

You would enter the data in A2, B2, C2, and E column and F column. The calculations are then made in D2 and the G, H, and I columns.

THANK YOU!! This problem is officially solved!

 

[mault]

You have been extremely helpful and I've tried to modify the formula you gave me, but need to tweak it a bit.

The judgment amount is $32,920.49 and the total amount of accrued but unpaid interest to-date is $681.86. So the current amount of $33,602.35 is due with the first payment being made June 30, with monthly installments. The payment will first be applied to the accrued interest and then to the judgment amount - so the formula needs to reflect that as well. So the daily interest will also decrease as payments are made. Is that something you can help with as well? THANK YOU

 

[kweaver]

I need a bit of clarification first. From 12/22/2018 to the current day, there are 159 days lapsed. 159 days times 3.61/day is $573.63. How did you arrive at $681.86?
From 12/20/2018 to 6/30/2019, that's 190 days, times $3.61 is $685.47.

 

[mault]

I need a bit of clarification first. From 12/22/2018 to the current day, there are 159 days lapsed. 159 days times 3.61/day is $573.63. How did you arrive at $681.86?
From 12/20/2018 to 6/30/2019, that's 190 days, times $3.61 is $685.47.

Sorry for the confusion. The judgment was entered November 21, 2018 and docketed December 22 - So I actually have to go back to the date of the judgment entry (11/21/18). With simple interest included, the amount owed (as of yesterday) is $33,602.35. The original judgment amount is $32,920.49. As of today, 190 days have passed.

After each monthly payment is received (minimum $1k, sometimes more), since it will be applied first to the accrued interest and then to the judgment amount, then the daily interest will also change. Thank you.