Calculating interest on a specific day with irregular payments

[Sleepingsouls]

Hi Guys,

I've had a few work sheets that calculate interest and time on regular payments but what I am looking for is something a bit more complex and I can't seem to work it out. I feel like I'm missing something really simple that I should know.

[TABLE="width: 531"]
<tbody>[TR]
[TD]A[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1 Principle[/TD]
[TD][/TD]
[TD] $ 8,000.00[/TD]
[TD] $ 8,000.00[/TD]
[TD] $ 9,500.00[/TD]
[TD] $ 4,200.00[/TD]
[/TR]
[TR]
[TD]2 Current[/TD]
[TD][/TD]
[TD] $ 7,635.27[/TD]
[TD] $ 5,340.03[/TD]
[TD] $ 9,245.00[/TD]
[TD] $ 4,184.63[/TD]
[/TR]
[TR]
[TD]3 Available[/TD]
[TD="align: right"][/TD]
[TD] $ 364.73[/TD]
[TD] $ 2,659.97[/TD]
[TD] $ 255.00[/TD]
[TD] $ 15.37[/TD]
[/TR]
[TR]
[TD]4 Payment[/TD]
[TD][/TD]
[TD] $ 1,000.00[/TD]
[TD] $ 5,340.03[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]5 Date Due[/TD]
[TD="align: right"][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]6 Interest[/TD]
[TD][/TD]
[TD="align: right"]1.90%[/TD]
[TD="align: right"]2.33%[/TD]
[TD="align: right"]2.08%[/TD]
[TD="align: right"]1.73%[/TD]
[/TR]
[TR]
[TD]7 Interest in $[/TD]
[TD][/TD]
[TD] $ 144.69[/TD]
[TD] $ 124.60[/TD]
[TD] $ 192.60[/TD]
[TD] $ 72.36[/TD]
[/TR]
[TR]
[TD]8 Date Interest Added[/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]9 Current + Interest[/TD]
[TD][/TD]
[TD] $ 6,635.27[/TD]
[TD] $ -[/TD]
[TD] $ 9,245.00[/TD]
[TD] $ 4,184.63[/TD]
[/TR]
</tbody>[/TABLE]

Essentially what I want is the current balance to automatically update to current + interest on a certain date.
For example in B8 the date interest is added is =day(29) and on that day I would like B6*B2 (which is B7) to be added to B9. I have that part worked out (with the forumla =IF(DAY($N$1)=29,B4+B9,B4-B6) ) so essentially if it's not that day I want it to minus the Payment made (B4) from the current (B2). The problem is when the date moves past the 29th it reverts back to B2 - B4.

This part is just a bonus if possible - I would love for the Current + interest or B9 to automatically update current (2) before the due date (B5) so that when I put how much the company has paid I don't have to update B2 and then B4.

I'm not sure if that all makes sense...

Thanks in advance.

 

[MaxSmart]

For the first part of your problem I think all you need to do is to change your formula to test whether the current day is Greater than OR Equal to 29 - not just Equal to, i.e.

IF(DAY($N$1)>=29,B4+B9,B4-B6) )

 

[Sleepingsouls]

Oh my gosh... hahaha I told you I felt like it was something simple!! Thank you so much... Now I just need to figure out the last part.