Help with a formula for a deferred revenue model.

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.

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

Formula:

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

@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.

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