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. We’ll be using spreadsheets exported from the popular web hosting billing system WHMCS as the source data.
First you must export the following files “tblhosting” ; “tbladdons” and “tbldomains”. When you export a ‘tblhosting’ spreadsheet from WHMCS, 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-annuall, and more. We instead want an apples-to-apples comparison and do so by calculating the daily revenue.
Now you should have something that looks like the PivotTable below, listing all of the billing cycles being utilized.
Ignore the ‘Count of billingcycles’. We don’t need that. We just need the list of all billing cycles being utilized. The next step is to determine a multiplier for each billing cycle that turns it into 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)
Once the formula is created, drag it all the way down, so that daily revenue is calculated for every row in ‘tblhosting’.
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.
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.
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.
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 ‘tblhosting’ 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 from WHMCS data helpful. If you have any questions, please reach out to us at email@example.com.
You may also enjoy reading: Maximize Future Value: Key Components to Consider When Building Your IT Services Business