Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate customer lifetime value (LTV) in Excel and Google Sheets
Excel & Google Sheets
=B2*C2*D2/(1+E2)-F2✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Average Purchase Value | Average Purchase Frequency (per year) | Average Customer Lifespan (years) | Discount Rate | Cost of Acquisition |
| 250 | 4 | 10 | 0.05 | 50 |
=B2*C2*D2/(1+E2)-F2→0.0392156863
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your average purchase value in cell B2.
- 2Input the average purchase frequency per year in cell C2.
- 3Enter the average customer lifespan in years in cell D2.
- 4Input the discount rate in cell E2.
- 5Enter the cost of acquiring a new customer in cell F2.
Tips
- Ensure all inputs are in consistent units (e.g., years for lifespan).
- Adjust the discount rate based on your business's financial situation.
- Be aware of locale differences in decimal and thousands separators.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the discount rate?
The discount rate reflects the time value of money and can be influenced by factors like interest rates.
Can I use monthly data instead of yearly?
Yes, adjust the frequency and lifespan to reflect monthly calculations.
Why subtract the acquisition cost?
Subtracting the acquisition cost gives the net profit from each customer over their lifetime.