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.

A		B	C	D	E
1 Principle		$ 8,000.00	$ 8,000.00	$ 9,500.00	$ 4,200.00
2 Current		$ 7,635.27	$ 5,340.03	$ 9,245.00	$ 4,184.63
3 Available		$ 364.73	$ 2,659.97	$ 255.00	$ 15.37
4 Payment		$ 1,000.00	$ 5,340.03	$ -	$ -
5 Date Due		29	14	11	28
6 Interest		1.90%	2.33%	2.08%	1.73%
7 Interest in $		$ 144.69	$ 124.60	$ 192.60	$ 72.36
8 Date Interest Added		29	19	20	2
9 Current + Interest		$ 6,635.27	$ -	$ 9,245.00	$ 4,184.63

<tbody>
</tbody>

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.