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

sddatta

New Member
Joined
Aug 21, 2019
Messages
5
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
don't quote post if it is not really needed. use Reply instead of Reply With Quote.
besides, I know what I wrote.
 

sddatta

New Member
Joined
Aug 21, 2019
Messages
5

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
1,123,225
Messages
5,600,406
Members
414,383
Latest member
kevinlarey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top