DISCOVER

# How to Use Excel to Calculate Deferred Revenue

Updated February 21, 2017

If you are in business, the chances are you've received income from a customer but haven't yet provided a service. The money for services or items that you have received but have not yet delivered on is deferred revenue. Deferred revenue could be a factor if you are a business that offers prepaid plans of any kind, including yearly contracts, discounts for pay-in-advance or any other service where you have been paid but have not yet delivered a service or item. Microsoft Excel can calculate deferred revenue for your business.

Open a blank worksheet in Microsoft Excel, Click the "Office" button and then click "New." Click "Blank Workbook" and then click "Create."

Click cell "A1" and then type "Earnings."

Click cell "B1" and then type "Prepaid."

Click cell "C1" and then type "Percentage of contract left."

Click cell "D1" and then type "Deferred income."

Click cell "A2" and then type the name of your first customer or income source.

Click cell "B2" and then type the amount the customer has paid you for the work. For example, type "\$2400."

Click cell "C2" and then type the percentage of the contract that is remaining. For example, if you have contracted with a customer to clean its building for 12 months and there are 11 months remaining, the percentage is 11/12 or 0.92.

Click cell "D2" and then type "=b2*c2." Press the "Enter" key to calculate the deferred revenue for the item.

#### Tip

If you have multiple customers and multiple rows of information, you don't have to retype the formula each time. Grab the fill handle, which looks like a little black square, at the bottom right hand corner of cell D2. Drag the fill handle down the column to fill as many rows as you need. Excel will automatically calculate the deferred revenue for each row.

#### Things You'll Need

• Excel 2007 or Excel 2010

#### References

Cite this Article