Allocating revenue cash blows on a straight line basis over a fixed period

[sddatta]

Hi, I have been given a task at work to allocate the revenue inflow from sales between a given start date and end date. The key known variables are:

Start date
End date
Frequency of billing
Total number of bills
Amount per bill
Total amount of cash flow during the period

What would be the best way to allocate it across the period? I've tried a formula (see the attached spreadsheet) which works for certain start dates but doesn't for the others. The main issues I was having is when the cash flow begins on 1 July or any other date of a given month other than the 1st, the formula does not give me any result.

=IF(AND(MONTH>=CASHFLOWSTARTDATE,STARTDATE<=$CASHFLOWENDDATE),(MOD(DATEDIF($CASHFLOWSTARTDATE,MONTH,"m")+FREQUENCY,FREQUENCY)=0)*BILLINGAMOUNT,0)

I know the end date is causing a problem, see cells T5 and T6. This is because of the Date in the month column (T4) being greater than the cashflow date.

Can someone please help me correct my logic in the If statement? It would save a lot of time rather than doing bits and pieces manually.

 

[sandy666]

nothing attached

 

[sddatta]

nothing attached

How do I attach files? It only gives me the option for a link.

 

[sandy666]

read Board FAQ / Guidelines
or
post a link to the shared excel file with representative example of source and expected result using OneDrive, GoogleDrive, DropBox or any similar

 

[sddatta]

read Board FAQ / Guidelines
or
post a link to the shared excel file with representative example of source and expected result using OneDrive, GoogleDrive, DropBox or any similar

Here's a link to the file.

https://docs.google.com/spreadsheets/d/1EOWtoay11edmtdU6i2rgHWBFX9ze0VD1KTz8Z_fqW5Q/edit?usp=sharing

 

[sandy666]

don't quote post if it is not really needed. use Reply instead of Reply With Quote.
besides, I know what I wrote.

 

[sddatta]

Yes apologies for that. Just noticed the Reply to thread button. Just new to the forum.

 

[sandy666]

no problem

 

[sddatta]

Any help would be appreciated....