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.

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

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

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

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?

Hi Glenn, Sorry I had to think about this. I hope it makes sense. Thanks again! The logic should be as follows:

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.

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"))))),"")

So only changed the layout completely, then??

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.