Deferred Revenue Model

Hey Folks,
I've uploaded my sheet and what I basically have is this, I have an invoice month, a start month (when we recognise the revenue from), an invoice amount and how many months I want to recognise the revenue over...

What I need is a formula that will calculate the monthly amount to recognise in each month, I have hundreds of transactions and different start dates and durations so I don't want to have to manually input the formula out to each month, so is there something that I can use to do this for me?

Thanks in advance!

Dave

Thanks for the reply, but I actually need the invoice amount to be divided by the Amortise number. So in the first example for Joe, I want to see 1,000 divided by 8 and then that amount (125) to appear each month for 8 months until the amount is recognised in full and then of course, I need it to start depending on the start month...

Hey Soren, thanks for this, it's almost there... have a look at the attached as I put the formula in and it does what I want it to do, but as you mentioned, I want the start month to be the first month, so in Joe's example, the deferred element should start from Jan, also, when I fill the rest of the fields in, it releases the revenue an extra month, so if you have a look at the totals in column V and take it away from the original invoice amount, it just shows that it's released an additional month...Deferred Revenue Example.xlsx

PERFECT!! Thanks Soren! Gotta love this site!

@ Dave350z

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Dear Soren,

Thanks a bucnh amazing formula really helped me out with tracking memberships!!!!

Dear Soren,

I am currently using the formula in my spreadsheet. However, I find that when the sheet is calculating across two years it seems to deduct a months subscription and place it in an incorrect month. For example, I have records starting from JAN 13 and range till DEC 15. If i was to start the membership in JAN 14 only 11 months are recorded on the year 2014 (JAN 14- Nov 14) the charge for DEC 14 is being reflected in JAN 13. Any help is much appreciated.

Regards,

Deferred Revenue - Health Club Membership.xlsx

Hi, Looking for a template i can report deferred gym membership revenues by updating with the current month