Trying to calculate the Deferred Revenue release formula

Hi,

Currently I am having trouble figuring how I can calculate the revenue recognition for the correct period.

I have spreadsheet which runs from Jan 2011
example.

cell A2 = Invoice billing period
cell B2 = Date when the revenue recognition begins
cell D2 = Billing amount
cell E2 = Billing period covered

My problem is that I have 2 different conditions for same type of billing e.g. Monthly new business should recognised in the same billing period however, Monthly renewals should recognised in the following period.

The formula I m currently using is only reads the month and not the year so excel returns the value in the same billing month for all years 2011, 2012 and 2013.

=IF(AND(OR($B2<=F$1,MONTH($B2)=MONTH(F$1),YEAR($B2)=YEAR(F$1)),COUNTIF($E2:E2,">0")<=$E2),$D2/$E2,0)

Can someone guide me to the right direction

Thanks.

Thanks for the reply. Your formula only works for one month i.e. March & April 2013. I need to calculate the revenue for all the periods for e.g. for annual revenue formula should automatically calculates the revenue for next 11 months as well.

Any thoughts/ suggestions....

I have tried to include value, but it does not seem to make any difference

Please Login or Register  to view this content.

Melvin,
The Op wants to spread over the income over the length of the contract. If you look at row 3, the annual figure is 8,334.84
this should be divided by 12 and equally spread over every month until the end of the period. This shows in H3 only, it should show in all years until the end.

This seems to work for the first year

Please Login or Register  to view this content.

Thanks everyone. EOMONTH formula worked perfectly fine.