Change quarters with date criteria

[nico2pacnwa]

Hi good people,

I am creating a monthly financial model from Jan-17 to Dec-19. I need help with the rent calculation. In my input sheet I have two inputs for rent 1) Start Date 2) Amount (annual figure) which is paid quarterly. What ever my start date is I want my rent to start on that date and previous months remain zero; there after I want my rent to appear after every 3 months.

Example: If my rent Start Date = "Apr-17" and Amount = "3000"; then my rent will appear as "750" (Quarterly Payments) in Apr-17, Jul-17, Oct-17 and Jan-18.

Hope you could help me with this.

Thanks in advance.

 

[AliGW]

How about this in C2 copied across and down?

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
Start	Rent

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Jan-17[/TD]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]May-17[/TD]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]3000[/TD]

[TD="align: right"]750[/TD]

[TD="align: right"]750[/TD]

[TD="align: right"]750[/TD]

[TD="align: right"]750[/TD]

[TD="align: right"]750[/TD]

[TD="align: right"]750[/TD]

[TD="align: right"]750[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]625[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]625[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]May-17[/TD]
[TD="align: right"]3200[/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]800[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(($C$1:C$1)<$A2,"",IF(MOD(COLUMNS($C2:C2)-MATCH($A2,$C$1:$Z$1,0),3)=0,$B2/4,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[nico2pacnwa]

Thank you AliGW that was very helpful.

 

[AliGW]

You're welcome!