MOD Function - Fixed Value every 3rd Month

[slstewart249]

I am trying to write a MOD formula so that a Fixed Value appears every 3rd month AFTER an initial value.

For example, I expect an upfront Payment value or $400K in Aug 2018. I want a reoccurring payment value of $500K starting in Nov 2018.

How do I write the formula so the Reoccurring value doesn't start until after the initial value, and reoccures every 3 months.

Attached is file.

 

[Hui]

G9: =IF(AND(SUM(F$8:$F8)>0,MOD(G6,3)=2),$E$9,0)

copy across

 

[slstewart249]

Thanks, This is helpful. How do I adjust the the formula though if I need to change when the first payment is made? For example, I receive 1st Payment April 2019 and 2nd payment July 2019. With the current formula, its coming back with 2nd payment in May, not 3 months later.

 

[Hui]

G9: =IF(AND(SUM($G$8:G8)>0,OFFSET(G9,,-3)),$E$9,0)
Copy across
Then type a repayment in what ever month you want in Row 9
and it will do the rest