I've calculated deferred revenue and monthly revenue calculations for subscription revenue in the attached spreadsheet. The calculation is driven based on the days to the next invoice date. It works well aside from one problem. An invoice can be sent on any day of the month and it is only valid for that month (or 12 months if a year invoice). The issues comes from the change in the number of days in a month. Based on my current calculations February will have less revenue than March because of 28 versus 31 days - that shouldn't happen and should be even. I've tried to figure out a solution for this using Days360 function so that each month is treated evenly but I've not managed to figure it out.
Any thoughts that might work would be appreciated?
Bookmarks