A Simple Way to Calculate ARR Metrics

Introduction

Annual Recurring Revenue (ARR) is a critical metric for SaaS companies, revealing both current health and future growth potential. By analyzing the ARR waterfall, which breaks down key drivers like New Sales, Churn, Upsell, and Downsell, you can quickly gauge the health of your business. 

While gathering these metrics can be challenging—especially when sales data is incomplete—I'll walk you through a process to efficiently consolidate and analyze data, providing timely ARR insights. This approach is particularly helpful for FP&A teams needing quick, actionable insights or those in early stages of building ARR processes.

Step 1: Start with Basic Bookings Data

This is the most basic format of bookings data you’ll likely encounter and will serve as your starting point for building out your ARR calculations. Most companies will have basic sales booking data provided by their CRM, typically containing contract information.

At a minimum, this data should include:

• Subscription ID

• Customer Name/ID

• Product Name/ID (if there is more than 1)

• Start Date

• Booking Date (if different from Start Date)

• Contract Term Length or End Date (one can be used to calculate the other)

• Net Total Contract Value (TCV), ensuring that all discounts and currency conversions are applied.

Important Notes on Data

Just as a side note for this example, we’re assuming that all bookings are for recurring subscriptions and not one-time charges, professional services, or any other non-recurring revenue types. If that weren’t the case, the following steps wouldn’t be applicable to the entire dataset.

Step 2: Calculate ARR from Booking Data

Basic ARR Calculation

To calculate the ARR, assuming all bookings are for recurring subscriptions:

1. Take the Net TCV.

2. Divide it by the number of months in the contract term.

3. Multiply by 12.

This ensures that all monthly or multi-year deals are converted into the proper annual format.

Recognize Deal Types and Changes

Next, we need to recognize the booking deal type and any changes to the ARR that will impact retention calculations. We then create a table showing the ARR on a monthly basis for each customer. While you can track ARR for a specific customer and product, we’ll keep it simple for this example.

Extract Customer IDs and Prepare Data

1. First, we need to extract the unique customer ID values from the bookings data.

2. Then, in the subsequent columns, insert the end-of-month date for the last month of the prior fiscal year.

3. Continue adding one month across until you reach the last month with actuals in the current fiscal year. Below is an example of the table you should have create:

SUMIF Formula for ARR Calculation

Create a SUMIF function to pull in the ARR from the booking data:

• Start Date should be less than or equal to the current month's last day.

• End Date should be greater than the current month's last day.

Copy this formula down and across the table until you reach the last customer and the last active month in the current fiscal year, Sales_Data signifies you must reference the data criteria forms the sales data table on the other tab.

=SUMIFS(Sales_Data[Net ARR],Sales_Data[Start Date],"<="&[Date],Sales_Data[End Date],">"&[Date],Sales_Data[CustomerID],[CustomerID])

Compare Monthly ARR Changes

1. In the subsequent columns, take the date of the first month of actuals you want to show (one column over from where you entered the last day of the prior fiscal year).

2. Create logic to compare the current month’s ARR to the previous month’s.

3. This logic will determine if the ARR changed due to a New Sale, Upsell (Expansion), Churn, Downsell, or if there was no change at all.

Copy the formula, making sure to reference the Current ARR aka [CurrARR] from the first month of the fiscal year when actuals begin, and the Prior ARR aka [PriorARR] from the last month of the previous fiscal year.

=IF(AND([PriorARR]=0,[CurrARR]>0),"New",IF(AND([CurrARR]=0,[PriorARR]>0),"Churn",IF(AND([PriorARR]>0,[CurrARR]>[PriorARR]),"Upsell",IF(AND([CurrARR]>0,[PriorARR]>[CurrARR]),"Downsell",IF([PriorARR]=[CurrARR],"None","Error")))))

Below is an example of what the table will look like:

Monthly ARR Comparison Formula

Copy this formula across all months until the most recent month in the current fiscal year. This will give you a month-by-month comparison to track the nature of any ARR changes. ARR_Table means to reference the Table on the previous table to pull in the data to the summary tab, where you will be referencing the [Chang].

=SUMIFS(ARR_Table[CurrMonth],ARR_Table[CurrChange],[Change])-SUMIFS(ARR_Table[PriorMonth],ARR_Table[CurrChange],[Change])

Add ARR Totals

One last thing I like to do is add the ARR totals for each month in the row above where the table starts. I find it helpful to see where we stand each month, and it makes building the ARR Waterfall summary easier, as we’ll see in the next section. 

Be sure your sum formula extends a few thousand rows beyond the last row in the ARR table. Always check the formula during your monthly process to ensure you're summing all the rows correctly.

Step 3: Building the ARR Waterfall Summary

On the summary tab, set up your ARR waterfall starting with the first month from the fiscal year of actuals you’re calculating. Then, add rows for:

• Beginning ARR

• New Sales

• Upsell

• Churn

• Downsell

Calculate Beginning Balance

For the beginning balance in the first month, use a SUMIF function to calculate the ARR total as of the month prior. For example, if your fiscal year starts in January, sum the ARR totals with Start and End Date logic for December of the prior year.

Ending ARR Calculation

To calculate the ending ARR, simply sum the beginning balance with the ARR change activity. Take the SUMIFS formula used in the beginning balance cell and change the month reference to the current month you're calculating.

Balance Check and Variance Review

If there’s a variance, this means the ARR totals in your bookings dataset aren't balancing, and you’ll need to review the ARR Total and Change table formulas. I typically start with the ARR change table since its formula can be complex. Make sure there are no error results in the change scenarios.

Carry Forward the ARR Balance

Next, set the beginning balance in the next column to equal the ending balance from the previous column. Carry the formulas over and ensure the checks sum to zero. If they do, the ARR waterfall will balance with the bookings data, and you can be confident in your calculations.

Step 4: Key ARR KPIs and Insights

Next, we want to add a few key calculations to provide insights into the business's top-line performance. Executives and investors typically prefer two distinct ways to view ARR KPIs:

• Monthly (current month vs. previous month)

• Annually (current month vs. same month one year ago)

Both methods are valid, so it might be helpful to calculate both if possible. If you're an early-stage company with only one or two years of sales data, I’d recommend the monthly view. If your company has more than two years of sales data, consider using the annual view, as it will provide a more compelling story to your board or potential investors.

Core 4 KPI Calculations

Let’s look at the core 4 KPI calculations:

• Growth: Current ARR balance vs. previous (either month or same month last year)

• Gross Retention: 1 + (Churn + Downsell) / Prior ARR

• Net Retention: 1 + (Upsell + Churn + Downsell) / Prior ARR

• New Bookings: (New + Upsell)

Conclusion

If you’ve followed along, the final result should look something like the example below. Now, you have a solid high-level view of the business’s revenue activities. The next step would be to work toward systematizing this with additional FP&A tools. However, it’s valuable to have a quick way to perform this calculation for fast insights when needed.

Feel free to reach out if you have any questions or if you're interested in taking a more systematized approach to these calculations for your organization.