Automatic penalty on loan

[ernest5]

I lend 1000 on Jan 1 '17
what I want is that if 1 year later there is an opening balance meaning that the loan is not paid off
it should automatically charge a penalty of 1% of the loan and the same should be on the next year if there is still an outstanding balance, I have some ideas on how to do it like the formulas: today,mod,index,match,autosum, etc but i could not figure it out


I would appreciate it if someone could help me on this one


Thanks

 

[Dave Patton]

Excel 2010
	A	B	C
1	1-Jan-17	1000
2	2-Jan-18	500	5
2c

Cell Formulas
Range	Formula
C2	=AND(A2>EDATE(A1,12),B2>0)*B2*0.01