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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Amount[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Frequency[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan 18[/TD]
[TD]Feb 18[/TD]
[/TR]
[TR]
[TD]John Pree[/TD]
[TD]$2000[/TD]
[TD]05/02/17[/TD]
[TD]05/02/18[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Mary Lee[/TD]
[TD]$3600[/TD]
[TD]01/03/17[/TD]
[TD]01/03/18[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe Gree[/TD]
[TD]$1200[/TD]
[TD]15/02/17[/TD]
[TD]15/02/18[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]$600[/TD]
[TD]01/01/17[/TD]
[TD]31/03/17[/TD]
[TD]6[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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