Formula Errors

How to Calculate ACV from TCV and Contract Duration in Excel

Tauseeq MagsiTauseeq Magsi Oct 12, 2026 869 views

Question details

Calculate the annual contract value (ACV) from a total contract value (TCV) based on the contract duration provided in months.

How to Calculate ACV from TCV and Contract Duration in Excel
Product
Excel
Device & OS
not provided
Scenario
A user needs to annualize contract values for financial modeling or sales reporting, adjusting the denominator based on whether the contract length exceeds 12 months.
Observed behavior
Contracts under 12 months should reflect the full TCV as the ACV, while longer contracts (e.g., 24 or 36 months) must be divided by the respective number of years.
Before you start

Ensure your Total Contract Value (TCV) data is formatted as numbers or currency, and verify that the contract duration column explicitly records time in months, not weeks or days.

Solution 1Recommended

Use the ROUNDUP Formula to Calculate ACV

Apply a combined division and ROUNDUP formula to automatically calculate the correct ACV based on monthly contract durations.

This formula works by determining the number of years the contract covers. By dividing the months by 12 and rounding up, any duration up to 12 months is treated as 1 year, 13 to 24 months as 2 years, and so on. The TCV is then divided by this year count.

1
Select the target cell

Click on the cell where you want the Annual Contract Value (ACV) to be displayed, such as cell C2.

2
Enter the formula

Assuming your Total Contract Value is in cell A2 and the duration in months is in cell B2, type the following formula: =A2/ROUNDUP(B2/12,0)

3
Apply to remaining rows

Press Enter to calculate the ACV for the first row. Then, click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply the formula to the rest of your data.

Use the ROUNDUP Formula to Calculate ACV
Formatting tip: After applying the formula, you can select the ACV column and click the Accounting Number Format ($) button in the Home tab to display the results as currency.

Calculate Financial Formulas Effortlessly with WPS Spreadsheet

WPS Spreadsheet is a powerful, lightweight, and free alternative for all your financial calculations. You can easily manage sales data, calculate TCV and ACV, and utilize advanced formulas with complete compatibility.

  1. 1. Open your data in WPS: Launch WPS Spreadsheet and open your financial or sales dataset containing the TCV and duration columns.
  2. 2. Select the output cell: Click the empty cell where the ACV calculation should appear.
  3. 3. Input the formula: Type =A2/ROUNDUP(B2/12,0) into the formula bar and press Enter to generate the value.
  4. 4. Fill the column: Double-click the fill handle on the bottom-right of the active cell to instantly calculate the ACV for all remaining contracts.
100% compatible with Microsoft Excel formats (.xlsx, .xls)Supports all standard financial, logical, and mathematical formulasBuilt-in data analysis and visualization tools for sales trackingLightweight installation with a familiar, user-friendly interface
microsoft office alternative - wps office

Frequently Asked Questions

What if my contract duration is stored in weeks instead of months?

If your duration is in weeks, adjust the denominator in the ROUNDUP function from 12 to 52. The new formula would be =A2/ROUNDUP(B2/52,0).

Why does my formula return a #DIV/0! error?

This error appears when the contract duration cell (B2) is completely blank or contains a zero. Ensure all cells in the duration column contain a valid number of months.

Can I use the standard ROUND function instead of ROUNDUP?

Using ROUNDUP is required for this specific calculation. A standard ROUND function might round a 15-month contract down to 1 year, which would incorrectly calculate the Annual Contract Value. ROUNDUP ensures any month going into a new year correctly divides the TCV by the next whole year.