Building A SaaS Pricing Model In Excel

Introduction

SaaS founders need to test pricing strategies before launching or changing prices. An Excel based pricing model lets you simulate different scenarios, understand customer value, and forecast revenue impact.

Why a SaaS pricing model matters

Pricing decisions directly impact revenue, customer acquisition, and lifetime value. A model helps you:

Test price points before market launch
Understand how pricing affects conversion rates
Forecast revenue under different scenarios
Plan for tiered pricing and packaging changes

Core components of a SaaS pricing model

Customer segments. Different user types with varying willingness to pay.
Pricing tiers. Free, basic, pro, enterprise packages.
Conversion rates. How many visitors become trial users, then paying customers.
Churn rates. Monthly or annual customer loss.
Customer acquisition cost. Marketing spend per new customer.
Lifetime value. Total revenue from a customer over their lifetime.

Step by step walkthrough using a real case

Company background

A B2B SaaS company offers project management software. They have three pricing tiers:

Basic: $29/month per user
Professional: $79/month per user
Enterprise: Custom pricing starting at $199/month

They want to test a price increase and add a new mid tier package.

Step 1. Define the current state

Start with current metrics:

Monthly website visitors: 10,000
Trial sign up rate: 5% (500 trials/month)
Trial to paid conversion: 15% (75 new customers/month)
Customer distribution: 60% Basic, 30% Professional, 10% Enterprise
Average revenue per user (ARPU): $52.40
Monthly churn: 3%
Customer acquisition cost (CAC): $350

Step 2. Build the baseline forecast

Create a 36 month forecast with:

New customers per month (growing at 5% monthly)
Churn applied to existing customers
Revenue calculation: Customers × ARPU
CAC calculation: New customers × $350

Baseline shows:

Month 12: 850 customers, $44,540 monthly revenue
Month 24: 1,550 customers, $81,220 monthly revenue
Month 36: 2,400 customers, $125,760 monthly revenue

Step 3. Design the new pricing structure

Proposed changes:

Basic: Increase from $29 to $39 (34% increase)
Professional: Keep at $79
Add new "Team" tier at $59/month
Enterprise: Increase minimum to $249 (25% increase)

Expected impact on conversion:

Basic tier conversion drops from 60% to 50% of customers
New Team tier captures 20% of customers
Professional stays at 30%
Enterprise stays at 10%

New ARPU calculation: (50% × $39) + (20% × $59) + (30% × $79) + (10% × $249) = $19.50 + $11.80 + $23.70 + $24.90 = $79.90

Step 4. Model the conversion rate impact

Price increases typically reduce conversion. Test scenarios:

Optimistic: Trial to paid conversion drops from 15% to 14%
Realistic: Drops to 13%
Pessimistic: Drops to 12%

For each scenario, calculate:

New customers per month
Customer count over time
Revenue trajectory

Step 5. Calculate customer lifetime value (LTV)

Formula: ARPU ÷ Churn Rate

Baseline LTV: $52.40 ÷ 3% = $1,747 New pricing LTV: $79.90 ÷ 3% = $2,663

LTV:CAC ratio improves:

Baseline: $1,747 ÷ $350 = 5.0
New pricing: $2,663 ÷ $350 = 7.6

Step 6. Run sensitivity analysis

Test what happens if:

Churn increases to 4% due to price sensitivity
Conversion drops more than expected
Customer mix shifts differently
CAC increases with price changes

Create a data table in Excel to show all combinations.

Step 7. Build the dashboard

Create a one page summary with:

Key metrics before and after
Revenue comparison chart
Customer count projection
LTV:CAC ratio trend
Break even analysis

How to interpret results

Focus on the trade offs:

Higher prices increase revenue per customer but may reduce conversion
Better LTV:CAC ratio justifies higher CAC if needed
Customer count growth may slow initially but revenue grows faster

Look for the inflection point where higher prices generate more total revenue despite fewer customers.

Actions the SaaS company can take based on the model

Implement the price increase with clear communication of added value
Monitor conversion rates weekly for the first three months
Adjust marketing messaging to highlight value vs price
Consider grandfathering existing customers at old prices
Plan a phased rollout by customer segment
Set up A/B testing for different price points

Conclusion

A well built SaaS pricing model gives you confidence in pricing decisions. It shows the financial impact before you make changes, reducing risk and maximising revenue potential.

FAQs

How often should I update my pricing model

Update it quarterly or when you consider pricing changes. Also update when you get new data on conversion rates or churn.

What if I don't have historical data

Start with industry benchmarks, then refine as you collect your own data. Run small tests to gather conversion rate data.

How do I account for annual vs monthly billing

Model both separately, then combine. Annual billing typically has lower churn but may have different conversion rates.

What Excel functions are most useful for pricing models

Use: XLOOKUP for tier mapping, PMT for lifetime value, FORECAST for growth projections, and Data Tables for sensitivity analysis.

How do I model competitor pricing impacts

Add a scenario where you lose market share if competitors don't follow your price increases. Test different response rates.

Can this model handle usage based pricing

Yes, add columns for usage metrics and tier thresholds. Use IF statements to calculate revenue based on usage levels.