Monthly Deferred Revenue

Hi all,
I'm trying to create a deferred revenue schedule.

I've got three dates which are relevant;
1. Invoice date -> this is the date the invoice was created
2. Start date -> this is the date the service starts from (this can be in the past, current, future related to the invoice date)
3. End date -> this is the date at which the service ends

The monthly amount is calculated on the number of months between the start date and end date.

I've not put any formula in aside from the monthly value calculation, but the values in the cells F-W are what I'm trying to get to.

1st Line - invoice & start date are the same, end date is 12 months => the invoice is split evenly over 12 months based on the monthly value
2nd Line - invoice date is before start date => invoice is split from the month of the start date over the term between start & end date based on the monthly value
3rd Line - invoice date is after start date & end date => the invoice fully recognised in the current period of July
4th Line - invoice date is after start date but before end date => the invoice is brought up to the current period and then the monthly value is recognised until the end date

Hopefully this isn't too complicated, but it would take me 2 days to figure this out!!!

Any help is appreciated!!!

Thanks
Dave

If I change the start & end date for the first line to 01/02/2018 to 31/01/2019 (it's still 12 months), but it pushes 2 month's of revenue into February and then the total spread out for the remainder is 100 a month until January 2019, which would release 1 month too much (if that makes sense?)

Always fun deferring revenue with so many rules

try this formula

Formula:

Please Login or Register  to view this content.

see attached file for the result per line

Spot on now, thanks!! If only our accounting system would just do it for us!!

Thanks both!

Hi there, I'm having a similar problem, although my excel document has a slightly different build. I'm trying to get my monthly deferred revenue amount to breakout to the life of the billing period for each line. Column E in my spreadsheet states the billing frequency which can be Quarterly, Monthly or Annual. My boss wants me to have multiple lines per Service agreement (in progress). I need column M to pull in only the amount of revenue that should be deferred (for instance, if the First invoice date is in August, but the billing period is from September through November, the entire Quarterly contract amount needs to sit in column M for the month of August). So I need column M to be populated based off of M2 & column F (when I change M2 to 8/31/21, I need it to pull the correct deferred revenue into the column. Then I need the Date rows to the right (T-infinity) to pull the column K amount into the correct columns by the months of the billing period. Is there anyone who can help me, I know it's complicated and I'm just not an Excel guru.