| | Example of Customer Lifetime Value (CLV) Calculation |
| | | Year |
| | | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| | Initial Acquistion Cost | -250 |
| | Average Annual Revenue (of the retained customer) | | 500 | 550 | 600 | 650 | 700 | 750 | 800 | 850 | 900 | 950 |
| | Average Customer Cost | | 250 | 275 | 300 | 325 | 350 | 375 | 400 | 425 | 450 | 475 |
| | Average Profit Contribution | | 250 | 275 | 300 | 325 | 350 | 375 | 400 | 425 | 450 | 475 |
| | Customer Retained (at 75% assumed retention rate) | | 100% | 75% | 56% | 42% | 32% | 24% | 18% | 13% | 10% | 8% |
| | Average Profit Contribution of Retained Customers | | 250.0 | 206.3 | 168.8 | 137.1 | 110.7 | 89.0 | 71.2 | 56.7 | 45.1 | 35.7 |
| | 10% Discount Rate applied to each year | 1 | 1.10 | 1.21 | 1.33 | 1.46 | 1.61 | 1.77 | 1.95 | 2.14 | 2.36 | 2.59 |
| | Discounted Average Profit of Retained Customer | | 227.3 | 170.5 | 126.8 | 93.6 | 68.8 | 50.2 | 36.5 | 26.5 | 19.1 | 13.8 |
| | Running Total of Average Profit | -250.0 | -22.7 | 147.7 | 274.5 | 368.2 | 436.9 | 487.2 | 523.7 | 550.2 | 569.3 | 583.0 |
| | | CLV= | 583.0 |
Ziyi Wu
CLTV-Solution.xlsx
