# License Revenue Recognition model with Renewals

1. ## License Revenue Recognition model with Renewals

Hi, I am trying to build a revenue recognition/renewal model as attached.

It supposed to spread the Total Price over the Start and End dates.
As an example, I highlighted row 15. Here, the Total Price is 1200. That first should be multiplied by the appropriate renewal rate of 80% and then be spread over the start and end date.
However, at the end of the end date, the assumption is that the license is again renewed. Therefore the total of 1200*.8 *.8 should be spread over the start and end months as the first term. This should persist through perpetuity as long as there is a renewal assumption.

Is there a clearer way to model this/equation that would work? Ive been spinning my head. Should we do if statements with 3 scenarios? This would be applied to a much larger data set. Thanks again for your help.

2. ## Re: License Revenue Recognition model with Renewals

I suspect that you have oversimplified your samples.

Every start date is the first of the month. Is it?

Every end date is the end of a month. Is it?

You stated that renewals would normally follow on year after year, but it is NOT explicitly a rule. Is it?

I have assumed for safety's sake that the answer to all of those Q's is NO. Therefore the formula is a bit longer.

=IFERROR(IF(M\$14<\$D15,""+N("Pre-start"),
IF(EOMONTH(\$D15,0)=M\$14,(M\$14-\$D15+1)*\$H15+N("Start month"),
IF(AND(LOOKUP(2,1/(\$I15:\$L15>0),\$I\$14:\$L\$14)+1=YEAR(M\$14),MONTH(M\$14)=MONTH(\$E15)),DAY(\$E15)*\$H15+N("Non-renewal end month"),
IF(M\$14>DATE(LOOKUP(2,1/(\$I15:\$L15>0),\$I\$14:\$L\$14)+1,MONTH(\$E15)+1,1),""+N("Post-end month"),
\$H15*M\$12+N("Normal month"))))),"")

copied across and down. The in-formula coments (in red) can be deleted, if you wish, but they're handy if you want to uderstand what each bit fo the formula is doing

3. ## Re: License Revenue Recognition model with Renewals

Hi Glenn, Thanks for your help!
Contracts for the most part are 1 year long. Start dates are usually at the beginning of the month and end dates end at the end of the month.
Initially I would prefer to have renewals in perpetuity. However, it would be nice to have it as an option on a per line basis.

For your model, for simplicity, if you change line 15 start and end date to 1/1/19-12/31/19, the first cycle should be 1200*80% and spread over the 12 months. The second cycle should be 1200*80%*80% spread over 12 months. third should be 1200*80%*80%*80% spread over 12 etc and in perpetuity as long as there is a renewal assumption.

Although your model renews, it doesen't seem to be be incorporating the 80% of 1200. I would appreciate any further guidance. thanks

4. ## Re: License Revenue Recognition model with Renewals

I didn't understand that... or see that... (not into financial modelling!!). I'll have another look.

5. ## Re: License Revenue Recognition model with Renewals

thank you sir! I appreciate your help. This is something more complex than my skills can handle

6. ## Re: License Revenue Recognition model with Renewals

Some qs.

Contact starts in june 2020. The year 2019 80% cell should be blank. Correct?

When does the first 80% apply? On Day one or after the first renewal?

7. ## Re: License Revenue Recognition model with Renewals

The 80% applys on day one. The percentages will all be there because of a vlookup that matches percentages to a product.

It might make more sense to think of it On an annual basis. Here is my goal. However my task is to apply this on a monthly basis.
1. If the contract term spans the full year 1/1/19-12/31/19, then 1200*80% spread over those 12 months. When it renews on 1/1/20, 1200*80%*80% spread over 1/1/20-12/31/20
2. If the contract term stops mid year 6/1/18-5/30/19, then 1200*80% for the period 1/1/19-5/30/19. The license then renews 6/1/19 and it should be 1200*80%*80% spread over those next 12 months. On 6/1/20, it should be 1200*80%*80%*80% etc.

8. ## Re: License Revenue Recognition model with Renewals

Clear (I think). Play with this and see where it breaks. The column headed "keep blank" can, of course, be hidden.

I changed the numbers highlighted in yellow (overtyped) to have sensible numbers that facilitate manual checking!!

=IFERROR(
(POWER(0.8,1+INT((SUMPRODUCT(--(LEN(\$M15:M15)>0)))/DATEDIF(\$D15,\$E15,"m")))+N("Multiplier"))*
IF(N\$14<\$D15,""+N("Pre-start"),
IF(EOMONTH(\$D15,0)=N\$14,(N\$14-\$D15+1)*\$H15+N("Start month"),
IF(AND(LOOKUP(2,1/(\$I15:\$L15>0),\$I\$14:\$L\$14)+1=YEAR(N\$14),MONTH(N\$14)=MONTH(\$E15)),DAY(\$E15)*\$H15+N("Non-renewal end month"),
IF(N\$14>DATE(LOOKUP(2,1/(\$I15:\$L15>0),\$I\$14:\$L\$14)+1,MONTH(\$E15)+1,1),""+N("Post-end month"),
\$H15*N\$12+N("Normal month"))))),"")

9. ## Re: License Revenue Recognition model with Renewals

Hi Glenn and everyone.

Thanks for your help. I played around with your model and it was slightly off in some cases. Today I went through an example with my colleague. Attached is a version
of how they like it done. The only problem is that in the first subscription term it is supposed to be X% or 80% of 1200 for line 15 spread over the first term, then
80% x 80% of 1200 for the second term, 80%x80%x80%x 1200 spread over the third term. We decided that we would only do 3 terms and in perpetuity, we would
multiply the last renewal rate x the previous year's month and so on.

Would you be able to help with my logic/adjust my logic where I would have the following for line 15? I am bad with if statements.

First term: rate 1 x 1200 spread over the term
Second term: rate 1 x rate 2 x 1200 spread over the term
Third term: rate 1 x rate 2 x rate 3 x 1200 spread over the term
Perpetuity: the previous year's month x the rate 3

10. ## Re: License Revenue Recognition model with Renewals

So only changed the layout completely, then??

11. ## Re: License Revenue Recognition model with Renewals

Hi Sorry,

THey thought it was a bit too complex. I appreciate your help regardless.

12. ## Re: License Revenue Recognition model with Renewals

I spent rather longer at this than I should have. It's all the more discouraging when it all gets thrown back in your face. Someone will, no doubt, help you finish this, but I'll not be that person.

There are currently 1 users browsing this thread. (0 members and 1 guests)