How to Calculate ACV from TCV and Contract Duration in Excel
Question details
Calculate the annual contract value (ACV) from a total contract value (TCV) based on the contract duration provided in months.

- 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.
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.
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.
Click on the cell where you want the Annual Contract Value (ACV) to be displayed, such as cell C2.
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)
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.

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. Open your data in WPS: Launch WPS Spreadsheet and open your financial or sales dataset containing the TCV and duration columns.
- 2. Select the output cell: Click the empty cell where the ACV calculation should appear.
- 3. Input the formula: Type =A2/ROUNDUP(B2/12,0) into the formula bar and press Enter to generate the value.
- 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.

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.




