question

Ziyi Wu
CLTV-Solution.xlsx

CLTV

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