Do you know how much profit your customers produce over their lifetime? “Customer lifetime value”, or “CLV”, is a valuable metric that helps you target your most profitable customer segments and understand how much you can spend to acquire them.
How does the theory work in action? Let’s say you sell two products: widgets and gidgets, and that the cost to sell either product is equal.
Let’s say you use a CLV calculation and discover that a widget customer is worth $1,000 over a lifetime, but a gidget customer is only worth $500. Now you know that
- You’ll be far more profitable by selling widgets instead of gidgets
- You need to spend < $1,000 to sell a widget
So how do you calculate CLV for a customer segment? First, gather this data:
| Data | Value | # |
| Average # of orders a customer places before defecting | A | |
| Average time between orders, in equal increments | B | |
| Average revenue per order | C | |
| Gross profit margin (as a %) | D | |
| Discount rate (used to bring the future payments into today’s dollars. Could be the inflation rate, or the rate of return of another investment) | E |
To calculate your CLV, use the “Present Value” function in Microsoft Excel:
Here are the formulas for the data you’ll enter in each box:
| Line | Formula | Your entry |
| Rate | E/B | |
| Nper | A | |
| Pmt | -C*D | |
| FV | Enter 0 (for future value) | |
| Type | Enter 1 |
The result is your customer lifetime value – the present value of the total profit a customer delivers over a lifetime of purchasing from your company.
We explored this topic in more detail during a 20-minute webinar last week. We’ve also programmed these formulas into excel workbooks – download them here.
{ 6 comments… read them below or add one }
i want to calculate clv for core-customers of my compny and i need some help.for example what is the metric of average time between to orders ,day , month or year?&how can i calculate discount rate? if possible please help me .thanks
I would appreciate if you could send me the sheet
Would you please send me the Excel worksheet. Thanks.
would you please send me it to me.thank you vert much
Have a question What would be my the expected profit for 500 customers? Worked several ways any sugesstions?
Car % Sold Per Sale Profit Per Sale
VW Golf 25% $1,000
Saab 15% $2,000
Mercedes 35% $12,000
Jaguar 25% $1,200
BMW 10% $400
Sum 110%
would u plz send me the excel workbook to find out customer lifetime value Acquired from Google Search Advertising.
{ 2 trackbacks }