How to Model ARR and MRR in Excel

MRR and ARR measure the recurring revenue a subscription business can count on each month and each year. The clean way to model them is a roll-forward: start with last month's MRR, add new and expansion revenue, subtract contraction and churn, and land on this month's ending MRR. That single equation drives the whole subscription model and makes every movement traceable.

What an ARR and MRR roll-forward does

Monthly recurring revenue (MRR) is the normalized monthly subscription revenue at a point in time. Annual recurring revenue (ARR) is simply MRR * 12. The roll-forward decomposes the change in MRR into its drivers so you can see why revenue grew or shrank, not just that it did.

It fits the model as the revenue engine. Ending MRR feeds the income statement, the components feed retention and churn metrics, and the new revenue line links back to sales and marketing spend in a unit economics build.

Build it step by step

Lay out months across the columns and the five roll-forward lines down the rows: beginning, new, expansion, contraction, churn, and ending. Each month's ending feeds the next month's beginning.

Drive new MRR from a sales assumption, and drive expansion, contraction, and churn from rates applied to the beginning balance.

1. Set beginning MRR equal to the prior month ending: =B12 pointing at last month.
2. Add new MRR from new bookings, and expansion MRR as =beginning * expansion_rate.
3. Subtract contraction as =-beginning * contraction_rate and churn as =-beginning * churn_rate.
4. Ending MRR is =beginning + new + expansion - contraction - churn.
5. Compute ARR on a separate line as =ending_MRR * 12.

=100000+15000+3000-2000-5000 lands ending MRR at 111,000.

The formulas and the linked metrics

The core identity is ending = beginning + new + expansion - contraction - churn. Keep contraction and churn as negative numbers if you sum the row, or as positive numbers you subtract, but be consistent so the total ties.

Net revenue retention is =(beginning + expansion - contraction - churn) / beginning, which deliberately excludes new MRR because retention measures the existing base only. Gross revenue retention drops expansion: =(beginning - contraction - churn) / beginning. ARR is always =MRR * 12, so an ending MRR of 111,000 implies ARR of =111000*12, or 1,332,000.

• Ending MRR: =beginning + new + expansion - contraction - churn.
• ARR: =ending_MRR * 12.
• Net revenue retention: =(beginning + expansion - contraction - churn) / beginning.
• Gross revenue retention: =(beginning - contraction - churn) / beginning.

Pitfalls and what reviewers check

A frequent mistake is mixing one-time revenue into MRR. Setup fees, professional services, and usage overages are not recurring and inflate ARR if included. Keep them on a separate non-recurring line.

Reviewers trace the ending MRR back through its components to confirm the roll-forward identity holds every month, check that net retention excludes new MRR, and verify ARR is exactly twelve times MRR rather than a separately maintained number that can drift. A broken link between one month's ending and the next month's beginning is the most common error and is easy to catch by tracing precedents.