Phasing using a formula with different frequency and start and end date

[jadezhang1504]

Hi,

I would really like to get some help on this model that I am building.

Imagine the following data

Customer	Amount	Start Date	End Date	Frequency	Jan	Feb	Mar	Apr	May	Jun	July	Aug	Sep	Oct	Nov	Dec	Jan 18	Feb 18
John Pree	$2000	05/02/17	05/02/18	4		500				500				500				500
Mary Lee	$3600	01/03/17	01/03/18	6			600		600		600		600		600		600
Joe Gree	$1200	15/02/17	15/02/18	12		100	100	100	100	100	100	100	100	100	100	100	100
Lisa	$600	01/01/17	31/03/17	6	200	200	200

<tbody>
</tbody>

I have a formula which I found on the internet for a 12 monthly frequency. Depending on the start and end date I type in, the contract will phase evenly over 12 months base on the number of days in a month. But I don't know how to do one for a different frequency. For example, a quarterly contract, which means the sales is phased quarterly, or a 2monthly (6 frequency) contract, so the sales are occurring every second month.

Can anyone help with this?

Thanks

Jade

 

[barry houdini]

Hello Jade, Welcome to MrExcel,

For the first entry, with a frequency of 4 wouldn't that mean the payments were every 3 months, i.e. in February, May, August and November? Also the dates for the last entry don't seem right.

Having said that, assuming your table starts at A1 then with actual dates in F1 across (1st of each month) you could use this formula in F2 copied across and down

=IF($C2>EOMONTH(F$1,0),0,IF(MOD(IFERROR(DATEDIF($C2,EOMONTH(F$1,0),"m"),0.5),12/$E2)=0,MIN($B2-SUM($E2:E2)+$E2,$B2/(DATEDIF($C2,$D2,"m")/12*$E2)),0))

That will give you zeroes where there is no payment, if you want you can format to show zeroes as blanks