Revenue Recognition Date Start/End

Hi, I am building a revenue recognition model for licenses. The amount paid must be spread out evenly based on the amount and the term length of the license (e.g. $100 spread over 2 months is 50 for each month. I am having trouble getting the model to start on the start date and end on the end date. Would appreciate any help. Thank you.

Your first entry (row 6) has a start date of 18th Feb and an end date of 18th April, so does that mean you want 10 (or should it be 11 ?) day's worth under Feb, 31 day's worth under March and 18 day's worth under April?

Pete

Hi, Thanks for responding! Would you be able to explain it for both ways? Thanks for your help.

1) For row 6, 18 days in Feb, 31 days in March and 18 days in April? I understand that this method is more complex.
2) For row 6, Since the columns show Month end date, For Feb 18 through April 18 = 2 months. Therefore, the start would be in Feb and the end would be in april.

I'm just about to log out, so I don't have time to give you a solution at the moment. However, this thread from yesterday is very similar:

https://www.excelforum.com/excel-for...two-dates.html

The main difference is that the dates in the columns of that one are end-of-month values, whereas yours are the first of each month.

If you want to show it as equal monthly amounts, then row 6 should EITHER be shown in Feb and March OR March and April, NOT February to April.

Also, if worked out on a daily basis, it would be for the remaining days of February (i.e. 28th minus 18th), which is either 10 or 11 days, depending on whether you count the days as inclusive or not. It's not really any more complicated working in days rather than months.

Anyway, I'll pick this up tomorrow if no-one else has jumped in overnight.

Pete

You can put this formula in cell L6 of your file:

=IF(AND($G6<=EOMONTH(L$5,0),$H6>=L$5),(MIN(EOMONTH(L$5,0),$H6)-MAX(EOMONTH(L$5,-1),$G6))/($H6-$G6)*$I6,0)

Then you can copy this across and down as required. You can also put this formula in AE6:

=SUM(L6:AC6)

and copy down, to check that it sums to the value in column I.

The main formula works things out on a daily basis, so 30-day months have a different value than 31-day months, and the first and last payments are based on the number of days in those months.

Hope this helps.

Pete

Hey Pete, thanks for your help! It seems to work well. I had a few follow ups if you could be so kind.

1. Does it matter if I use start of the month instead of end of the month for your formula? (It seems to not matter) =IF(AND($G6<=EOMONTH(L$5,0),$H6>=L$5),(MIN(EOMONTH(L$5,0),$H6)-MAX(EOMONTH(L$5,-1),$G6))/($H6-$G6)*$I6,0)
2. For line 5 where I placed the dates. Is there a way to automatically show the correct last day of the month for respective year? For instance, instead of me manually typing in 1/31/18 or 4/30/18, there is a formula that calculated that?
3. How would I show equal monthly amounts? For instance, for line 6, If I start in Feb and end in March. Also, If I start in the following month March and end in April?

Thanks and I greatly appreciate any input!

In answer to your questions:

1. If it works for you then use that.

2. You can put this formula in M5:

=EOMONTH(L5,1)

Format L5 and M5 as a date in the style you prefer, then copy M5 across as far as you need to.

3. Before I went out earlier, I was working on an alternative that worked on months, but I couldn't get it right for row 14 where you have a start date of 1st April and an end date of 1st June (all the others were right). If I tweaked the formula to get that row correct, then many of the others were wrong, so I think I need a different approach. I'll take another look later on.

Hope this helps.

Pete

Thank you pete! I would appreciate any advice you can offer

This seems to have done it - put this formula in L6:

=IF(AND($G6<=L$5,(SUM($K6:K6)-$K6)<$I6),$I6/$J6,0)

then copy across and down as required. This assumes that you have end-of-month dates across row 5.

Hope this helps.

Pete

You seem to have got rid of your Months column in V3 - I was working on your V2, and I've attached my copy of that which I worked on.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

thanks pete!

You're very welcome.

Pete