Calculating a Due date with Different frequency

[lallens]

I need a formula that can tell me the next due date if I have the first due date, the frequency the payment is being paid (Monthly, Weekly, Semi-monthly) and the number of payments that are setup.

Column D is the first Payment date, Column E is the frequency, Column F is the number of payments, Column G is were I would put the next due date.

So if I have a person paying $xx amount first payment is due 11/1/2018 and they are paying weekly for 10 payments. Column G should give me a date of 11/22/2018 since it is greater than today date and still within the 10 payments.

Thanks in advance
Allen

 

[lallens]

I just added an additional if statement IF(TODAY()>H2,"Ended",IF(TODAY()=H2,H2,IF....

 

[lallens]

I think I need a better formula for my Payment Plan Expires.. I have:

If(E2="","",IF(E2="M",F2*30+D2,IF(E2="W",F2*7+D2,IF(E2="S",F2*14+D2,D2))))

 

[Yongle]

Ok - will check that out.

 

[Yongle]

I think there may be a problem with the weekly.. I have a payment date of 11/2/2018 Weekly Frequency and 3 payments it is showing me the next payment of 11/30/2018 when it should be 11/16/2018 or Ended

You are correct - In the "W" bit of the formula, replace 7 with 6

INT((TODAY()+7
should be
INT((TODAY()+6

 

[Yongle]

Using Named Ranges

The active cell MUST be in row2 when you create these named ranges - references are relative to the active cell at the time of being created.

1. Create 3 named ranges with the following names and RefersTo formula

MONTHLY
=DAY(Payments!$D2)+EOMONTH(TODAY(),-(DAY(Payments!$D2)>=DAY(TODAY())))

WEEKLY
=Payments!$D2+INT((TODAY()+6-Payments!$D2)/7)*7

SEMI
=Monthly-(Monthly-TODAY()>15)*15

2. Formula in G2 copied down
=IF(TODAY()>H2,"Ended",IF(E2="W",Weekly,IF(E2="M",Monthly,IF(E2="S",Semi,"Freq?"))))

 

[Yongle]

I just added an additional if statement IF(TODAY()>H2,"Ended",IF(TODAY()=H2,H2,IF....

No - that would then apply to Monthly & Semi too - which would be wrong - the fix is in post#14

 

[Yongle]

I think I need a better formula for my Payment Plan Expires.. I have:

If(E2="","",IF(E2="M",F2*30+D2,IF(E2="W",F2*7+D2,IF(E2="S",F2*14+D2,D2))))

Try this
=IF(F2>0,IF(E2="W",7*F2+D2,IF(E2="M",EDATE(D2,F2-1),IF(E2="S",EDATE(D2,INT((F2-1)/2))+15*MOD(F2+1,2),""))),"")

Your payment terms determine whether 15 is the correct number of days to add for the 2nd,4th,6th ... payments

 

[lallens]

This works great.. Thank you very much

 

[Yongle]

Did you test using Named Ranges as per post#15 ?

 

[Yongle]

And I think the formula for "Payment Plan Expires" needs a minor adjustment
- test the original with only 1 weekly payment for an explanation

=IF(F2>0,IF(E2="W",7*(F2-1)+D2,IF(E2="M",EDATE(D2,F2-1),IF(E2="S",EDATE(D2,INT((F2-1)/2))+15*MOD(F2+1,2),""))),"")