Xcel newb needing help calculating simple interest on a judgment

mault

New Member
Joined
May 29, 2019
Messages
9
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

Well-known Member
Joined
May 8, 2018
Messages
1,606
Office Version
  1. 365
  2. 2010
Sorry to be dense, but from 11/21/2018 to yesterday, there are 221 days, times 3.61 = $797.31.
If a payment of $1000 was made on 6/30/2019, then 1000-797.31 is added to the original amount which would be 32920.49 + 202.69 = 33123.18 ?

What am I missing?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mault

New Member
Joined
May 29, 2019
Messages
9
Sorry to be dense, but from 11/21/2018 to yesterday, there are 221 days, times 3.61 = $797.31.
If a payment of $1000 was made on 6/30/2019, then 1000-797.31 is added to the original amount which would be 32920.49 + 202.69 = 33123.18 ?

What am I missing?

From my calculation, from yesterday to the judgment date (11/21/2018) there are 189 days in between those days (or 190 as of today). When payments start in June, the daily interest gets paid first, which as of today, on the $32,920.49 judgment, is $685.47. So if the initial payment is made at $1,000, only $314.53 gets applied to the judgment amount and the rest is applied to the daily interest. The formula has to account for that as well. Thank you.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,606
Office Version
  1. 365
  2. 2010
OK. I think I get it. Phew! LOL.

So, row 1 has headers and initially it looks like this:

judgment1.jpg


D2: =B2*C2/365
G2: =TODAY()-A2 using today; otherwise, it's =TODAY()-A2-1
H2: =G2*$D$2
I2: =B2+(E2-H2)

Additional payments would be made staring in row 3.

That means that

G3: =IF(ISBLANK(F3),"",F3-F2) which can then be filled down for other payments.

So, does this ending balance now look correct?

judgment2.jpg
 

mault

New Member
Joined
May 29, 2019
Messages
9
OK. I think I get it. Phew! LOL.

So, row 1 has headers and initially it looks like this:

judgment1.jpg


D2: =B2*C2/365
G2: =TODAY()-A2 using today; otherwise, it's =TODAY()-A2-1
H2: =G2*$D$2
I2: =B2+(E2-H2)

Additional payments would be made staring in row 3.

That means that

G3: =IF(ISBLANK(F3),"",F3-F2) which can then be filled down for other payments.

So, does this ending balance now look correct?

judgment2.jpg

The ending balance is correct, but when I input data into the second row, my balance only reflects the payment. Maybe it is the cells I am filling down the formula with..are they the same as last time? I included the fill in G3 as well as H2 and I3. Since the Daily Interest will not always be $3.61, does that formula need to be filled down as well in D2? THANK YOU!
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,606
Office Version
  1. 365
  2. 2010
Not the same...look at my notes from a reply or two ago. You don't have to change the daily interest. It's calculated so just leave alone as the cell is used for the calculations.
You can see that cell being used as $D$2 which is an Absolute cell reference to always be used (in the H column).
 
Last edited:

mault

New Member
Joined
May 29, 2019
Messages
9
The balance is still only applying the payment. So row three reflects the same amounts as you example, but the balance is subtracting the days interest and shows a balance of $891.77 (instead of $32,343.25). So I am missing something here...so close.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,965
Messages
5,627,899
Members
416,281
Latest member
Olawunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top