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!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,576
Office Version
  1. 365
  2. 2010
Do you need to know when (each) payment was made?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,576
Office Version
  1. 365
  2. 2010
Does this look correct?

Clip0001.jpg


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)
 
Last edited:

mault

New Member
Joined
May 29, 2019
Messages
9

ADVERTISEMENT

Does this look correct?

Clip0001.jpg


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

Well-known Member
Joined
May 8, 2018
Messages
1,576
Office Version
  1. 365
  2. 2010
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

New Member
Joined
May 29, 2019
Messages
9

ADVERTISEMENT

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

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

Well-known Member
Joined
May 8, 2018
Messages
1,576
Office Version
  1. 365
  2. 2010
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.
 
Last edited:

mault

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

Watch MrExcel Video

Forum statistics

Threads
1,127,560
Messages
5,625,506
Members
416,115
Latest member
Gonzo5711

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