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
1	Start	Rent	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
2	Apr-17	3000				750			750			750			750			750			750			750
3	Jan-17	2500	625			625			625			625			625			625			625			625
4	May-17	3200					800			800			800			800			800			800			800

<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>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C2 =IF(($C$1:C$1)<$A2,"",IF(MOD(COLUMNS($C2:C2)-MATCH($A2,$C$1:$Z$1,0),3)=0,$B2/4,"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[nico2pacnwa]

Thank you AliGW that was very helpful.

 

[AliGW]

You're welcome!