1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# Formula for Qtrly Payments

Discussion in 'Ask an Excel Question' started by slstewart249, May 15, 2018.

1. ### slstewart249New Member

Messages:
23
I am trying to write a formula to show quarterly payments for the next 5 years.

The Start Date is Feb-19
Qtrly Payement is \$25,000

I've tried various forms of IF(AND but its not working.

HELP!
FILE IS ATTACHED.

File size:
10.9 KB
Views:
5
2. ### HuiExcel NinjaStaff Member

Messages:
11,690
D8: =IF(MOD(D5,3)=MOD(MONTH(\$A\$7),3),\$A\$8,0)
copy accross
3. ### AlanSidmanActive Member

Messages:
421
Wouldn't =\$A\$8 be what you need. If this is not what you are looking for, then explain in more detail what you are looking for as it is not currently clear.
4. ### slstewart249New Member

Messages:
23
Thanks - One point I should have been more clear about is the payment table starts with 2018.

How do I revise the above formula so result is 0 for all months in 2018, and then shows \$25,000 starting in Feb 19? With the current formula it shows paymetn for 2018 too. Update file is attached.

File size:
11.2 KB
Views:
3
5. ### slstewart249New Member

Messages:
23
Also, I have multiple contracts with different payment start dates. The MOD formula only seems to work if the payment date starts in the 2nd month (2 i.e Feb).
6. ### bosco_yipExcel Ninja

Messages:
2,105
Try,

In D8, copied across :

=IF(0+TEXT(\$A\$7,"mm/yyy")<=0+TEXT(DATE(D4,D5,1),"mm/yyy"),IF(MOD(D5,3)=MOD(MONTH(\$A\$7),3),\$A\$8,0),0)

Regards
Bosco

#### Attached Files:

• ###### Qtrly Payment(2).xlsx
File size:
11.7 KB
Views:
2
Thomas Kuriakose likes this.
7. ### slstewart249New Member

Messages:
23
Thank you! One more feature to the formula.

If I drag the formula below across the rows, it shows payment each quarter every year. I have a multi-year table.

How would I write it so payment occurs only in 2019?
Thanks

=IF(0+TEXT(\$A\$7,"mm/yyy")<=0+TEXT(DATE(D4,D5,1),"mm/yyy"),IF(MOD(D5,3)=MOD(MONTH(\$A\$7),3),\$A\$8,0),0)

File size:
12.1 KB
Views:
1
8. ### bosco_yipExcel Ninja

Messages:
2,105
Payment occurs only in 2019,

In D8, revised formula (add highlighted red portion condition) copied across

=IF((0+TEXT(\$A\$7,"mm/yyy")<=0+TEXT(DATE(D4,D5,1),"mm/yyy"))*(0+YEAR(\$A\$7)=D4),IF(MOD(D5,3)=MOD(MONTH(\$A\$7),3),\$A\$8,0),0)

Regards
Bosco

#### Attached Files:

• ###### Qtrly Payment(3).xlsx
File size:
12.3 KB
Views:
6
Thomas Kuriakose likes this.