flexible update of remaining amount and end date based on current month

**k1dr0ck** · 05-05-2015, 01:27 AM

Hi,

I have a simple worksheet that calculates the outstanding amount(D1) and the end date(B7) from the amounts on Row 4 based on the current month(E1), starting month(B6) and monthly amount(B5).

my problem is when i change monthly amount(B5), for example, to 1,500 and add an amount to Row 4 ,for example, on C4 20,000 it shows an incorrect amount on D1 and incorrect end date on B7 based on the current month.

How do I make the formula, or a VBA code if possible, be flexible to make the correct calculations to update D1 and E7 when i make changes on Row 4 and B5?

I've attached my sample file.

**samba_ravi** · 05-05-2015, 01:48 AM

Try
B7=DATE(YEAR(B6),MONTH(B6)+INT(B4/B5)-1,1)
D1=MAX(0,B4-DATEDIF(B6,E1,"m")*B5)

**k1dr0ck** · 05-05-2015, 03:09 AM

initially it has deducted 1,000 per month from Jan to Apr, so D1 is 16,000, then on May if i add 20,000 on C4 and make it 1,500 on B5, D1 is only 14,000 it should now be 36,000 and B7 is Jan-16 it should now be Apr-17 --- (1,500 per month May-15 to Apr-17)

another example problem on May if i add 20,000 on C4 and make it 2,500 on B5, D1 should now be 36,000 and B7 should now be Jul-16 --- (2,500 per month May-15 to Jun-16 but 1,000 only for Jul-16) how do i show that it should only be 1,000 for the last month?

i think a vba code is best suited to handle the problem but i dont know how to manipulate dates using vba code