How to Use Excel to Calculate Deferred Revenue
By Stephanie Ellen
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.
- 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.
Stephanie Ellen teaches mathematics and statistics at the university and college level. She coauthored a statistics textbook published by Houghton-Mifflin. She has been writing professionally since 2008. Ellen holds a Bachelor of Science in health science from State University New York, a master's degree in math education from Jacksonville University and a Master of Arts in creative writing from National University.