Need Help Badly

[comarcks]

Hi Everyone,

I hope you're all doing fine. I need your help to get the correct formula for the #ofPaymentsMade and the NextPaymentSchedule. Below is sample table. I hope some can help me on this.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Duration[/TD]
[TD]Payment Term[/TD]
[TD]# of Payments Made[/TD]
[TD]Next Payment Schedule[/TD]
[/TR]
[TR]
[TD]10/01/2012[/TD]
[TD]08/18/2016[/TD]
[TD]4 Years[/TD]
[TD]Every 6 months[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/27/2012[/TD]
[TD]05/05/2014[/TD]
[TD]2 Years[/TD]
[TD]Every 6 months[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if you have questions.

 

[MarcelBeug]

What is the logic? You mean as of today? Payments at the beginning or end of the term?

The first one 5 payments made (4/1/2013, 10/1/2013, 4/1/2014, 10/1/2014 and 4/1/2015) and next schedule 10/01/2015?
And the second: 4 payments made and no next schedule?

 

[oldbrewer]

4 years times 12 = 48 months divide by 6 months = 8 payments

what does next payment schedule mean

do you want to know, today, how many payments made and how many still to be paid ?

 

[comarcks]

Sorry, my bad. Yes I want to know how many payments has been made as of today's date. And what's the next payment schedule. Also, Thank you for bringing that up.Please add the remaining payments needed.

Thank you. Oldbrewer

 

[BGY23]

Starting at

A2 Start 01/10/2012
B2 End 18/08/2016
C2 Total payments =DATEDIF(A2,B2,"M")/6
D2 Payments Made =ROUNDDOWN(DATEDIF($A$2,TODAY(),"M")/6,0)
E2 Last paid =EDATE(A2,D2*6)
F2 Next payment due=EDATE(A2,(D2+1)*6)

All calculations use UK date format dd/mm/yy and are based upon todays date.

 

[comarcks]

Thank you BGY23. You're the best.

What about the second row? where it should have an output like "contract Finished" in the next payment due field.

 

[BGY23]

I had to change the formulas for if today() gretaer than the end date on the contract.


Try this.
C2 =ROUNDUP(DATEDIF(A4,B4,"M")/E4,0)
D2 =IF(TODAY()>B4,C4,ROUNDDOWN((DATEDIF(A4,TODAY(),"M")+E4)/E4,0))
F2 =IF(EDATE(A4,D4*E4)>B4,B4,EDATE(A4,D4*E4))
G2 =IF(TODAY()>B4,"Fully paid",MIN(EDATE(A4,(D4+1)*E4),B4))

 

[BGY23]

Copy it down to row 3