Loan Tracker with prior/missed payments

[aurelius33]

I am working on a simple interest loan tracker spreadsheet. The goal is to split a payment into principal and interest, apply the principal against the balance, and track if any payments have been missed. If a payment has been missed (change status to a 3 is the trigger to show a missed payment). The prior principal, prior interest, and prior balance will track the shortage/outstanding balance. I'm to the point I have it most of the way there. But I can't quite get the logic straight so a payment's interest is pulled out, the remainder is applied to reduce the prior balance (to zero when possible), and apply the rest to the principal. Overages would be handled like an additional principal payment but show a positive in the prior balance column.

LoanScrubber.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Pmt Num	Date	Status	Pmt	Additional	Correction	Principal	Interest	Prior Prin	Prior Int	Prior Total	Balance	Daily Int	L2 Date	L2 Pmt	L2 Add	L2 Prin	L2 Rate	L2 Int	L2 Balance	L2 Prior	Maint	Performance	Savings	TOTAL		Loan Amount:	$ 39,000.00
2	0	7/8/2024		$ 455.76					$ -	$ -		$ 39,000.00													$ -		Interest Rate:	16.50%
3	1	9/5/2024	1	$ 455.76	$ -		$ -	$ 1,040.18	$ -	$ 584.42	$ 584.42	$ 39,000.00	$ 17.63									$ 100.00			$ 555.76		Num of Payments:	100
4	2	9/12/2024		$ 555.76	$ 100.00		$ (152.07)	$ 123.41	$ -	$ -	$ 484.42	$ 39,152.07	$ 17.63												$ 555.76		Origination Date:	7/8/2024
5	3	9/19/2024		$ 455.76			$ (152.55)	$ 123.89	$ -	$ -	$ 484.42	$ 39,304.62	$ 17.70												$ 455.76		Pmt Frequency:	52
6	4	9/26/2024		$ 455.76			$ (153.03)	$ 124.37	$ -	$ -	$ 484.42	$ 39,457.65	$ 17.77												$ 455.76		Days in a year:	365
7	5	10/3/2024		$ 455.76			$ (153.52)	$ 124.86	$ -	$ -	$ 484.42	$ 39,611.17	$ 17.84												$ 455.76
Template

Cell Formulas
Range	Formula
B2	B2	=AB4
I2:I7	I2	=IF([@Status]=3,[@Principal],IF([@Pmt]-[@Interest]<=0,0,0))
J2:J7	J2	=IF([@Status]=3,[@Interest],IF([@Pmt]-[@Interest]<=0,[@Interest]-[@Pmt],0))
L2	L2	=$AB$1
G3:G7	G3	=IF([@Pmt]-[@Interest]<=0,0,IF(K2<=0,[@Pmt]-[@Interest],[@Pmt]-K2-[@Interest]))
H3:H7	H3	=ROUND(L2*$AB$2*([@Date]-B2)/$AB$6,2)
K3:K7	K3	=IF([@Status]=3,K2+[@[Prior Prin]]+[@[Prior Int]],L2-[@Balance]-[@Principal]+[@[Prior Prin]]+[@[Prior Int]]+K2-[@Additional]-[@Correction])
L3:L7	L3	=IF([@Status]=3,L2,L2-[@Principal])
M3:M7	M3	=L2*$AB$2/$AB$6
B4:B7	B4	=B3+7
D2	D2	=ROUND(($AB$2/$AB$5+$AB$2/$AB$5/((1+$AB$2/$AB$5)^$AB$3-1))*[@Balance],2)
D3:D7	D3	=IF($D$2+[@Additional]+[@Correction]<=L2,$D$2+[@Additional]+[@Correction],L2+[@Interest]+[@Additional]+[@Correction])
Y3:Y7	Y3	=[@Pmt]+[@[L2 Pmt]]+[@Maint]+[@Performance]+[@Savings]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D300	Expression	=C2=3	text	NO