How MRR is calculated from prorated invoices

This article describes how ChartMogul calculates MRR from prorated invoices sent to the API. Along with the tutorial on adding prorated invoices, this tutorial should help you format invoices correctly so that ChartMogul reports metrics as you would like.

ChartMogul's formula for calculating MRR from prorated invoices

ChartMogul calculates MRR from each subscription line item that is sent to the API. This forms the basis of the MRR history of a subscription.

The formula for calculating MRR from a subscription line item looks like this:

MRR = (amount_in_cents − tax_amount_in_cents) × subscription_duration_ratio × prorate_factor

The above is rounded to the closest integer value, in cents.

The components of the formula are as follows,

amount_in_cents — The final amount charged towards a subscription, for the specified quantity and service duration, after discounts and taxes have been applied. For the MRR calculation, the value specified in the invoice is used as is.
tax_amount_in_cents — The tax that has been applied to this subscription, in cents. For the MRR calculation, the value specified in the invoice is used as is.
subscription_duration_ratio — This factor normalizes the plan interval to a month. For example, if the customer pays $90 for a 3 month plan, then the subscription_duration_ratio is 1/3 so that MRR will be $30.
prorate_factor — The factor by which the prorated amount charged to the customer needs to be multiplied for calculating MRR.

How is the `prorate_factor` calculated?

The prorate_factor is calculated as the estimated full service duration of the subscription plan, over the prorated service duration for which the customer has been charged.

prorate_factor = estimated_full_service_period/prorated_service_period

The estimated_full_service_period and the prorated_service_period are calculated as follows,

The estimated_full_service_period is the full original duration of the plan that the customer has bought, in seconds. For example, if a customer bought an annual plan, then it refers to the number of seconds in that year. It is calculated depending on the context of the prorated invoice.

ChartMogul expects that in most cases, the previous line item for this subscription will have the same plan and will not be prorated. Since prorations are usually adjustments to invoiced service periods, prorated line items often have the same service_period_end timestamp as an existing line item. If present, this previous service duration will be considered as the estimated_full_service_period and will be calculated as:

(service_period_end − service_period_start) of the previous line item, in seconds.
If the previous line item does not have the characteristics described above, then the plan information from the prorated line item is used to calculate the estimated_full_service_period. Keeping the service_period_end the same as the prorated line item, an estimated_service_period_start timestamp is calculated imagining that there were no proration.

estimated_service_period_start = service_period_end − plan_interval

This timestamp is then used to calculate the estimated_full_service_period as,

(service_period_end − estimated_service_period_start) of the prorated line item, in seconds.

Why not just use the plan interval as the estimated full service period? If the customer buys a monthly plan but pays a prorated amount of $20 for just 10 days, shouldn't the MRR simply be 20x3 = $60? It depends. This might work for a 30-day month, but it won't work for a 31-day month, or a service duration that spans two different calendar months.

Since the number of days in a month varies, the amount paid per unit of time varies depending on the service period being invoiced. Proration behavior also differs across billing systems that generate these invoices. ChartMogul therefore uses the specified service period timestamps as much as possible in order to keep this contextual information from billing systems intact.

The prorated_service_period is the service duration for which the customer has been charged a prorated amount. It is expressed in seconds, and is calculated from the service duration specified in the prorated line item. (service_period_end − service_period_start) of the prorated line item, in seconds.

Using the above logic to determine the estimated_full_service_period and the prorated_service_period, the prorate_factor is calculated and applied to the MRR formula.

If the subscription line item is not prorated at all, then the prorate_factor is 1.

Finally, if the plan in a prorated line item is the same as the previous line item for the subscription, then ChartMogul assumes that the prorated charge is due to a change in the subscription's quantity. It therefore adds the MRR of the prorated item to the current MRR of the subscription.

Having seen the formula and logic in detail, let us now look at some examples to see how the formula is applied under different cases.

Example calculation: Prorated charge for an increase in subscription quantity

This example is for a prorated line item that describes an increase in subscription quantity. An example of such a subscription event would be if a customer buys extra seats on their subscription half-way through their service period.

For the purposes of the calculation, let us use the same example used in the tutorial for adding a prorated charge for an additional subscription.

Let's imagine that:

Your customer Adam Smith bought a monthly subscription to the Bronze Plan on March 1st, 2016 and paid $50.
Adam then upgraded by subscribing to another seat on the Bronze Plan on March 16th.
You charged him a prorated amount of $25 for the additional subscription for the remaining service period until the end of March 31st.

The prorated line item that you would send is shown below.

{
  "type": "subscription",
  "subscription_external_id": "sub_0001", // External ID of Adam's subscription
  "plan_uuid": "pl_eed05d54-75b4-431b-adb2-eb6b9e543206", // UUID of Bronze Plan
  "prorated": true,
  "service_period_start": "2016-03-16 12:00:00",
  "service_period_end": "2016-04-01 00:00:00",
  "amount_in_cents": 2500,
  "quantity": 1
}

Let us now apply the formula for calculating the MRR of this prorated line item,

MRR = (amount_in_cents − tax_amount_in_cents) × subscription_duration_ratio × prorate_factor

amount_in_cents = 2500 tax_amount_in_cents = 0 subscription_duration_ratio = 1 (since the Bronze Plan is a monthly plan) prorate_factor = estimated_full_service_period/prorated_service_period

In this case, the previous line item for this subscription would be the charge for the Bronze Plan at $50, for the month of March. The service duration would have run from 1st March 2016 (00:00) to 1st April 2016 (00:00).

estimated_full_service_period = (service_period_end − service_period_start) of the previous line item, in seconds = (01-04-2016 00:00:00 − 01-03-2016 00:00:00) = 2678400 seconds

prorated_service_period = (service_period_end − service_period_start) of the prorated line item, in seconds = (01-04-2016 00:00:00 − 16-03-2016 12:00:00) = 1339200 seconds

Therefore, prorate_factor = 2678400 / 1339200 = 2

MRR for the prorated line item = (2500 − 0) × 1 × 2 = $50

Since this line item describes the addition of more seats to the same subscription plan, ChartMogul adds the previous MRR of the subscription with the additional MRR from this prorated line item, to arrive at the new MRR of the subscription.

New MRR of the subscription = $50 + $50 = $100

Similarly, ChartMogul also changes the subscription quantity by the value specified under quantity for the line item. In this case, the quantity of the line item is specified as 1, meaning one additional seat was added. The final quantity of the subscription thus becomes 2 after the prorated line item is processed.

Example calculation: Prorated charge for a decrease in subscription quantity

This example is for a prorated line item that describes a decrease in subscription quantity. An example of such a subscription event would be if a customer removes seats from their subscription half-way through their service period.

For the purposes of the calculation, let us use the same example used in this tutorial.

Let's imagine that:

Your customer Adam Smith bought an annual subscription to the Gold Plan on January 1st, 2015 and paid $200 for 20 seats. This results in an MRR of $16.67.
Adam decided to reduce the number of seats midway through his annual subscription to 10, on July 1st.
You gave Adam a prorated amount of $50 for the unused time on the Gold Plan for 10 seats, as credit.

To add this prorated credit and register the downgrade in ChartMogul, the prorated line item that you would send is shown below.

{
  "type": "subscription",
  "subscription_external_id": "sub_0001", // External ID of Adam's subscription
  "plan_uuid": "pl_ab225d54-7ab4-421b-cdb2-eb6b9e553462", // UUID of Gold Plan
  "service_period_start": "2015-07-01 00:00:00",
  "service_period_end": "2016-01-01 00:00:00",
  "amount_in_cents": -5000,
  "quantity": -10,
  "prorated": true
}

Credit for reduced subscription quantity. ChartMogul recognizes credit line items as those with negative amount_in_cents. All credit line items impact cash flow metrics. Furthermore, ChartMogul recognizes credit for a reduction in subscription quantity using the negative quantity value. Only credit for a reduction in subscription quantity impacts MRR.

Let us now apply the formula for calculating the MRR of this prorated line item,

MRR = (amount_in_cents − tax_amount_in_cents) × subscription_duration_ratio × prorate_factor

amount_in_cents = -5000 tax_amount_in_cents = 0 subscription_duration_ratio = 1/12 (since the Gold Plan is an annual plan) prorate_factor = estimated_full_service_period/prorated_service_period

In this case, the previous line item for this subscription would be the charge for the Gold Plan at $200 for 20 seats, for the entire year of 2015. The service duration would have run from 1st January 2015 (00:00) to 1st January 2016 (00:00).

estimated_full_service_period = (service_period_end − service_period_start) of the previous line item, in seconds = (01-01-2016 00:00:00 − 01-01-2015 00:00:00) = 31536000 seconds

prorated_service_period = (service_period_end − service_period_start) of the prorated line item, in seconds = (01-01-2016 00:00:00 − 01-07-2015 00:00:00) = 15897600 seconds

Therefore, prorate_factor = 31536000 / 15897600 = 1.9836956

MRR for the prorated line item = (-5000 − 0) × 1/12 × 1.9836956 = $ -8.27

Since this line item describes the reduction of seats from the same subscription plan, ChartMogul reduces the previous MRR of the subscription by the MRR from this prorated line item, to arrive at the new MRR of the subscription.

New MRR of the subscription = $16.67 − $8.27 = $8.4

Similarly, ChartMogul also changes the subscription quantity by the value specified under quantity for the line item. In this case, the quantity of the line item is specified as -10, meaning ten seats were removed. The final quantity of the subscription thus becomes 10 after the prorated line item is processed.

Differences in cents from expected MRR. In the above case, Adam reduced his seats by exactly half, for exactly half the plan duration. Shouldn't the MRR be exactly half at $8.33? This is one of the cases where proration isn't as simplistic.

Even though Adam was billed for exactly half the calendar year, the second half of the year is longer than the first half when measured in seconds. This means that the credit given was marginally lower than expected. Therefore, the resulting MRR is marginally higher at $8.4.

Example calculation: Prorated charge for a subscription plan change

This example is for a prorated line item that describes a change in subscription plan. An example of such a subscription event would be if a customer upgrades from a less expensive monthly plan, to one that's more expensive, half-way through their service period.

For the purposes of the calculation, let us use the same example used in this tutorial.

Let's imagine that:

Your customer Adam Smith bought a monthly subscription to the Bronze Plan on March 1st, 2016 and paid $50.
Adam then changed his plan to the Copper Plan, which costs $60 a month, on March 16th.
You charge Adam a prorated amount of $30 for the Copper Plan until end of March, and credit him a prorated amount of $25 for the unused time on the Bronze Plan.
He pays the difference of $5 for the remaining service period until the end of March 31st.

To add this prorated charge and credit, the prorated line items that you would send are shown below.

{
  "type": "subscription",
  "subscription_external_id": "sub_0001", // External ID of Adam's subscription
  "plan_uuid":"pl_b2cc1314-9e4b-4437-8056-c4b79294b8bb", // UUID of the new Copper Plan
  "prorated": true,
  "service_period_start": "2016-03-16 12:00:00",
  "service_period_end": "2016-04-01 00:00:00",
  "amount_in_cents": 3000,
  "quantity": 1
},
{
  "type": "subscription",
  "subscription_external_id": "sub_0001", // External ID of Adam's subscription
  "plan_uuid":"pl_eed05d54-75b4-431b-adb2-eb6b9e543206", // UUID of the old Bronze Plan
  "prorated": true,
  "service_period_start": "2016-03-16 12:00:00",
  "service_period_end": "2016-04-01 00:00:00",
  "amount_in_cents": -2500,
  "quantity": 1
}

Let us now apply the formula for calculating the MRR of the prorated charge,

MRR = (amount_in_cents − tax_amount_in_cents) × subscription_duration_ratio × prorate_factor

amount_in_cents = 3000 tax_amount_in_cents = 0 subscription_duration_ratio = 1 (since the Copper Plan is a monthly plan) prorate_factor = estimated_full_service_period/prorated_service_period

In this case, the previous line item for this subscription would be for a different plan (the Bronze plan). Therefore, ChartMogul will use the Copper Plan's billing interval to determine the estimated_full_service_period.

estimated_service_period_start = service_period_end − plan_interval = 01-04-2016 00:00:00 − 1.month = 01-03-2016 00:00:00 estimated_full_service_period = (service_period_end - estimated_service_period_start) of the prorated line item, in seconds = (01-04-2016 00:00:00 - 01-03-2016 00:00:00) = 2678400 seconds

prorated_service_period = (service_period_end − service_period_start) of the prorated line item, in seconds = (01-04-2016 00:00:00 − 16-03-2016 12:00:00) = 1339200 seconds

Therefore, prorate_factor = 2678400 / 1339200 = 2

MRR for the prorated charge line item = (3000 − 0) × 1 × 2 = $60

Since this prorated charge is for a plan change the new MRR of the subscription will simply be the MRR from the new Copper plan.

New MRR of the subscription = MRR from Copper plan = $60

Credit for unused time on a plan. ChartMogul recognizes credit line items as those with negative amount_in_cents. Furthermore, it recognizes credit for unused time on a plan using the positive quantity value. Such credit does not impact MRR, but impacts cash flow.

More proration cases. If you have a special case of prorated billing for which you are unsure how to structure the invoices, please write to us at [email protected] and we will help you.

ChartMogul's formula for calculating MRR from prorated invoices

How is the prorate_factor calculated?

Example calculation: Prorated charge for an increase in subscription quantity

Example calculation: Prorated charge for a decrease in subscription quantity

Example calculation: Prorated charge for a subscription plan change

How is the `prorate_factor` calculated?