Deferred Revenue Schedule

Hi - can someone help me in creating a deferred revenue schedule. (date format in this is DMY)

I have the following

- Invoice date
- Start date (date the revenue can begin to be recognized)
- End date (date the revenue recognition ends)

Ideally I want the formula in columns F-Q to recognize the start and end date of the periods and release the amount into the right period based on the daily rate x the number of days in that month.

Some invoices will be billed on random days in the month, so I only want to recognize the revenue as per the number of days that are applicable (so row 7 on the attached is an example)

Thanks in advance
Dave

Hi

The following formula appears to work across all examples.

=ROUND(IF(G$2<=$C14,IF(G$2<=$B14,0,(((G$2-$B14)+1)*$E14)-SUM($E14:F14)),(($C14-$B14)*$E14)-SUM($F14:F14)),2)

Cheers

Boom!!

Thanks a million, gotta love this site, I would have literally sat there for 2 days trying to figure this out!!!

Couple of things...

1) I downloaded the version 2 of the file. Why is it when I change the date of one of the rows already in the spreadsheet, the formulas start to error out?
2) Would it be possible for month 1 (partial month be by days) subsequent months be by month, and then last month be by days to finish off the reminder?
for example $1200 contract for one year, bills 2/1/2018 but contract actually begins 1/20/2018. So Jan would have 11 days of revenue, Feb thru Dec would have even revenue of 1/12th each month, then Jan 2019 would have remained of contract?

Thanks

Hi all - I've a follow on query from my original and only noticed this as we implemented this file in the real world.

The original calculation was on the basis that the period dates (start & end) were after the invoice date, we have another example where an invoice is issued say in July but the service start and end date is prior to this - so the formula would need to reference the invoice date and release all of the revenue in the invoice month.

Any chance someone could have a look at this file again?

Revised attachment

Hi Team,

I am here trying to build a formula driven deferred revenue for SaaS collection.

Revenue should be deferred based on service start date (column # C) for the next no of months based on billing frequency (column # E). For example: If my service start is 30th April 2018, whole one month revenue will be deferred in the month of April 2018.

Request you're help in finding the formula. Thank you in advance