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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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:
Upvote 0
don't quote post if it is not really needed. use Reply instead of Reply With Quote.
besides, I know what I wrote.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top