• 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...

  • 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

slstewart249

New Member
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.
 

Attachments

  • Qtrly Payment Formula.xlsx
    10.9 KB · Views: 5
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.
 
D8: =IF(MOD(D5,3)=MOD(MONTH($A$7),3),$A$8,0)
copy accross

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.
 

Attachments

  • Qtrly Payment Formula.xlsx
    11.2 KB · Views: 3
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).
 
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
 

Attachments

  • Qtrly Payment(2).xlsx
    11.7 KB · Views: 2
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)
 

Attachments

  • Qtrly Payment Formula.xlsx
    12.1 KB · Views: 1
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)

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
 

Attachments

  • Qtrly Payment(3).xlsx
    12.3 KB · Views: 9
Back
Top