Help with a formula for a deferred revenue model.

[amartino44]

Hello. I have a deferred revenue model that I need help with. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales. Please help Spreadsheet attached.

[alvin-chung]

Hi, try this formula on R22 and you can copy across F22:AC33

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[ammartino44]

@alvin. This doesn't work. It returns a value of 108.33 in cell R22. The value should be $8.33. And in R23 it should be $9.17 from 2010 and $9.17 from 2011. I think what your formula is doing (at least in R22) is adding up all of the previous months values and the value from the current. I just need the current month value. Hope that makes sense.

[ammartino44]

@alvin. Actually, this formula doesn't work at all

[alvin-chung]

Could you fill in the expected result manually in 2011 and share the file here for illustration?