Calculating Deferred Revenue for a Web Hosting Business

If you’re in the market to buy or sell a web hosting business, it’s important to understand the concept of ‘deferred revenue’ and how it impacts your valuation. Deferred revenue is a liability reflecting money that’s already been collected for services that are to be rendered in the future. Whereas normally revenue contributes positively to a hosting business’ valuation, deferred revenue decreases the valuation by virtue of being a liability. Hosting company valuations are largely determined based on the recurring revenue (i.e. the Annual Recurring Revenue, or ARR). As a general rule of thumb, a typical valuation of hosting businesses floats around 1x ARR, although it does vary. But revenue that’s already received for work to be done for periods in the future is considered deferred and is subtracted from the valuation. In practice however, most buyers define deferred revenue for the purposes of adjustments to an offer as revenue that is received for periods that exceed 1 year in the future. Let’s say you sell a hosting plan with a two year billing cycle; basically the first year contributes to the ARR calculation and increases the valuation, and the second year is deferred revenue and needs to be subtracted from the valuation.

Today’s blog goes through the steps to calculate deferred revenue.

Step 1: Calculate ‘Daily Revenue’ Column

First you must export an order history report from your billing system, where each and every order is given as a row along with a billing cycle duration column. There will be a mixture of billing cycles including monthly, annual, semi-annually, and more. We instead want an apples-to-apples comparison and do so by calculating the daily revenue.

1. Select the entire ‘billingcycle’ column.
2. Click the ‘Insert’ ribbon up top, then click ‘Pivot Table’.
3. Drag the ‘billingcycle’ field under ‘Rows’.

Now you should have something that looks like the PivotTable below, listing all of the billing cycles being utilized.

The next step is to determine a multiplier for each billing cycle that turns it into daily revenue.

1. Select all billingcycles listed in the Pivot Table
2. Copy/paste the billingcycles into a new sheet into Column A. Call the new sheet something like “Daily Revenue Multiplier”.
3. Besides, in Column B, we determine the multiplier that turns each billing cycle it into daily revenue:
   • For Annually, enter the formula “=1/365”
   • For Biennially, enter the formula “=1/730”
   • For Free, Free Account, or One Time, enter a zero
   • For Monthly, enter the formula “=12/365”
   • For Quarterly, enter the formula “=4/365”
   • For Semi-Annually, enter the formula “=2/365”
   • For Triennially, enter the formula “=1/1095”
   • For any other billing cycles that may show up, just follow a similar pattern and calculate the fraction that’d make it a daily figure.
4. You can now delete the sheet that housed the Pivot Table. It’s not needed any more.
5. Go back to the order history sheet, and label a blank column “Daily Revenue”.

Now we’re going to use a vlookup formula to match the appropriate multiplier with the appropriate billing cycle for each row. We’re going to do this by creating a formula in the new ‘Daily Revenue’ column. An example formula is as follows:

=I2*VLOOKUP(J2,’Daily Revenue Multiplier’!A:B,2,FALSE)

• I2 is the revenue collected for the first order, a.k.a. the ‘amount’
• J2 is the ‘billingcycle’ for the first order
• ‘Daily Revenue Multiplier’!A:B identifies the table with the daily revenue multipliers we created
• “2” refers to the 2^nd column, i.e. the fractions we created to covert to daily revenue
• “FALSE” allows only for exact matches

Once the formula is created, drag it all the way down, so that daily revenue is calculated for every row in the order history sheet.

If this part has been confusing, I’d highly suggest doing some reading on how vlookup formulas work. A misstep here will throw off all of our calculations because everything is based on having an accurate daily revenue calculation.

Step 2: Calculate ‘Days Beyond a Year’ Column

Now that we know the daily revenue amounts, we need to determine how many days of deferred revenue there are for each order. To do so, we calculate the number of days remaining for each order beyond a year from today (assuming the data was exported today).

Excel expresses unformatted dates as a five digit number. Using these, we subtract a-year-from-now from the last date of the billing cycle. Let’s assume that the last day of an order’s billing cycle is Dec. 31, 2022 and a-year-from-now is Jan. 6, 2021. The five digit code for the former is 44926 and the latter is 44202. The difference, 724, is the number of days of deferred revenue. We now need to perform this same calculation on every order using a formula.

1. Find the five digit number that represents a-year-from-now by typing the date into Excel in plain English, highlighting it, and changing the number format to ‘General’. In our example this is 44202.
2. Ensure that the number format of the dates in the ‘nextduedate’ column are proper (or whatever other name the column that contains the orders’ end dates is called). Do this by first highlighting the entire ‘nextduedate’ column and changing the number format to ‘Number’. Now click the dropdown arrow in the header and you should hopefully see a bunch of five digit numbers in the 40,000’s. Quickly scroll through and look for anything that’s different. Investigate any outliers to ensure they make sense. For instance, if the ‘nextduedate’ is a 0, that could make sense for free accounts, but you want to double check. Also keep an eye out for any dates that are still expressed as words instead of the five digit number; in these instances you need to get a little creative with formulas to get the date into a format that Excel recognizes and that you can do calculations with.
3. Create a new column called ‘Days Beyond a Year’.
4. Subtract a) from b) using a formula like this: =K2-44202, where K2 is the ‘nextduedate’ for the first order. Drag this formula all the way down.
5. Convert negatives to a 0. We don’t want to include negative numbers in this column, because that means there’s no deferred revenue. Filter for all the negative cells in the row header’s dropdown and manually change them to 0. Or you can change the formula slightly to this: =IF((K2-1)-44202<0,0,(K2-1)-44202), which will ensure any negative number becomes a 0.
6. Convert #VALUE! Errors to a 0. We don’t want to include any cell with errors in this column either. Again use the header’s dropdown, scroll through to look for #VALUE! errors, and filter for them if necessary. Review them rows thoroughly to ensure they make sense in context of the order details. For instance, a free account may return a #VALUE! error due to the lack of a ‘nextduedate’. If there aren’t any issues needing addressing, manually change all the cells with #VALUE! errors to 0’s. Or you can change the formula again to this: =IFERROR(IF((K2-1)-44202<0,0,(K2-1)-44202),0), which will convert any #VALUE! error cell into a 0.

Step 3: Calculate ‘Deferred Revenue’ Column

Now that we’ve calculated the daily revenue for each row, and the number of days where deferred revenue applies, we can multiply the two together to calculate ‘Deferred Revenue’. Simply create a new column entitled ‘Deferred Revenue’, use a formula like =P2*Q2 to do the calculation, and drag it all the way down.

Step 4: Tally the ‘Deferred Revenue’ total

There are a number of ways to determine the total ‘Deferred Revenue’. You can simply highlight the entire column and note the total. Or you can use a formula like =SUM(R:R). Or you can highlight the entire order history table, click the ‘Insert’ ribbon, create a PivotTable, and drag ‘Deferred Revenue’ under row labels. And we’re done!

I hope you found this article about how to calculate Deferred Revenue for a web hosting company helpful. If you have any questions, please reach out to us at [email protected]. If you are interested in selling your business please click here for a free evaluation Free Evaluation

You may also enjoy reading: Maximize Future Value: Key Components to Consider When Building Your IT Services Business