Help! Excel challenged! Simple interest calculation

[Cool Fandango]

I don't know how to use excel at all. I've been watching many tutorials, but still cannot figure it out. I need a spreadsheet calculating post judgment simple interest. It's complicated (at least for me). Here it is:

Simple Interest
Original Amount: $150,000
Beginning Date: November 19, 2007 through today (October 21, 2022)
Annual Interest Rate: 7%
One payment has been made in the amount of $716.88 on April 20, 2020.

Thank you in advance for your help!!

 

[6StringJazzer]

Simple interest means your payment is first applied to accrued interest, then anything remaining is applied to principal. Interest is not charged on unpaid interest.

You have a very unusual situation here in that you went 13 years with no payments and your first payment is not enough to cover a large amount of accrued interest. It is hard to make sense of what you want to calculate without more information. Looks like a student loan.

The last row here shows the current loan principal amount, and accrued interest to date. You can update the date in column A to reflect the current date after today. The payment for that row is shown as $0 to calculate the current principal and accrued interest to date.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Payment Date	Payment	Accrued Interest Since Last Payment	Remaining Principal	Remaining Interest		Annual Rate	7.0%	Daily Rate	0.019165%
2	11/19/2007			$150,000.00
3	4/20/2020	$ 716.88	$ 130,398.36	$150,000.00	$129,681.48
4	10/22/2022	$ -	$ 26,303.90	$150,000.00	$155,985.38
Sheet13

Cell Formulas
Range	Formula
J1	J1	=H1*4/(365*4+1)
C3:C4	C3	=(A3-A2)*$J$1*D2
D3:D4	D3	=D2-(B3-MIN(B3,C3))
E3	E3	=MAX(0,C3-B3)
E4	E4	=MAX(0,C4-B4)+E3

 

[Cool Fandango]

Simple interest means your payment is first applied to accrued interest, then anything remaining is applied to principal. Interest is not charged on unpaid interest.

You have a very unusual situation here in that you went 13 years with no payments and your first payment is not enough to cover a large amount of accrued interest. It is hard to make sense of what you want to calculate without more information. Looks like a student loan.

The last row here shows the current loan principal amount, and accrued interest to date. You can update the date in column A to reflect the current date after today. The payment for that row is shown as $0 to calculate the current principal and accrued interest to date.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Payment Date	Payment	Accrued Interest Since Last Payment	Remaining Principal	Remaining Interest		Annual Rate	7.0%	Daily Rate	0.019165%
2	11/19/2007			$150,000.00
3	4/20/2020	$ 716.88	$ 130,398.36	$150,000.00	$129,681.48
4	10/22/2022	$ -	$ 26,303.90	$150,000.00	$155,985.38
Sheet13

Cell Formulas
Range	Formula
J1	J1	=H1*4/(365*4+1)
C3:C4	C3	=(A3-A2)*$J$1*D2
D3:D4	D3	=D2-(B3-MIN(B3,C3))
E3	E3	=MAX(0,C3-B3)
E4	E4	=MAX(0,C4-B4)+E3

Jeff,

Thank you so, so much for your help! You're awesome! This answers my question and helps me out so much! The debt I'm referring to is a judgment I have against a man who killed my dad. I'm in the process of renewing the judgment (needs to be renewed every 8 years), and I need to submit a spreadsheet with the judgment renewal showing the accounting since the judgment was first entered back in 2007. I struggled for days trying to figure out how to do it in Excel, but I was a complete failure. I'm so relieved that there are knowledgeable people out there like you that are willing to help. Thanks again!

 

[6StringJazzer]

I will caution that I very confident of the solution I provided for data up to today, but I have not tested it for various situations about how to keep it up to date in the future. If you expect to continue using this until the judgment is fully paid then I will take another look. I can also provide an actual Excel file if that helps.

I am so sorry about your dad.

 

[Cool Fandango]

I will caution that I very confident of the solution I provided for data up to today, but I have not tested it for various situations about how to keep it up to date in the future. If you expect to continue using this until the judgment is fully paid then I will take another look. I can also provide an actual Excel file if that helps.

I am so sorry about your dad.

Hi Jeff,

Thank you for the heads-up! An actual Excel file would be so helpful! I tried to re-create it and even with you providing all the formulas, I'm still having trouble! I'm a mess! Thanks again for all your help! You're a life saver!

 

[6StringJazzer]

Dropbox

www.dropbox.com

 

[Cool Fandango]

Dropbox

www.dropbox.com

Thank you, thank you! You are wonderful! I appreciate your help so much!