LTV by Modeling Revenue Function with a Spreadsheet

Mobile_App_LTV_Calculator_copyThis is the 6th section in a series of 6 posts. Here is a summary post explaining all the different methods.

In section 5 we modeled the retention function with a spreadsheet. Here we will use similar statistical methods like regression and log functions to model out the revenue function.

This method is great if you want to build a full model that takes any number of data points and accounts for both revenue and retention.

Download Options

The spreadsheet will be more useful for you if you have your own copy so feel free to get one:

Calculator Details


  • Day 1-30 retention data (column B)
  • Day 1-30 ARPDAU data (column C)

NOTE: If you have datapoints on ARPU to date instead you can override column F instead of putting the inputs in columns B and C.


  • 180 day LTV


This model assumes that the revenue function is a logarithmic function – y=a*ln(x)+b

The input data (known x’s and known y’s) is used in order to calculate the ‘a’ and ‘b’ paramters with statistical regression. This is done in cells H5 and I5.

Once we have the function it’s easy to calculate 180d LTV – a*ln(180)+b.

The only modification is that we are comparing the 30d ARPU given by the function to the data we already know and adjust the 180d LTV result accordingly.


  • Very flexible and can be applied with any number of data points
  • Accounts for variable ARPDAU


  • Complicated (although slightly simpler compared to modeling retention function)
  • Calculates every segment on it’s own without using data from previous segments

Advanced hack – working with partial data 

You can easily use this spreadwheet with less than 30 days of data:

  • Put the user and arpdau data in columns B and C or simply put the ARPU to date in column F
  • As long as you have days in column A and the repective ARPU to date in column F the spreadsheet would work
  • If you are using less than 30 points of data simply adjust the formulas in cells H5 and I5 to reference the right ranges

More methods to calculate user value

If you have data from one segment you can use this data to model the LTV in other segments more easily. Here is the post

You can also try other spreadsheet method – modeling your retention fucntion


Feel free to share:
Previous articleCLV by Modeling Retention Function with a Spreadsheet
Next article71 Percent of Players Prefer Video Ads According to Unity
Raised in the Kibbutz and reborn in the city, Yaniv is a certified entre-parent-neur. When he’s not busy doing SEO, content marketing, administration, QA, fund raising, customer support… [stop to breathe], you can find Yaniv snowboarding down the slopes of France and hiking with his kids. Yaniv holds a B.Sc. in Computer Science and Management from Tel Aviv University. He is also an avid blogger and a speaker at industry events. Before SOOMLA, Yaniv co-founded EyeView


    • Hi, thanks for your question.
      ARPDAU is the amount of revenue of that day divided by the amount of active users in that day.
      Daily ARPU is the amount of revenue of that day divided by the amount of users in the original cohort (in this case 1,000). This column is presented as part of the calculation that leads to Commulative ARPU. The statistical regression is applied to the Commulative ARPU column.
      Hope this helps – let me know if you have additional questions.

Leave a Reply to Yaniv Nizan Cancel reply

Please enter your comment!
Please enter your name here