Calculation Issues

Calculate ACV from TCV by Contract Duration in Excel

Kushani NimanthikaKushani Nimanthika Oct 9, 2026 871 views

Question details

The user needs an Excel formula to calculate Annual Contract Value (ACV) based on Total Contract Value (TCV), project type, and contract duration in weeks.

How to Calculate ACV from TCV by Contract Duration in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Performing financial modeling or contract analysis where ACV must be derived accurately from TCV under specific duration rules.
Observed behavior
The desired output must return the exact TCV for zero-week 'Services Renewal' projects, and for all other cases, divide the TCV by the number of one-year periods (rounding up the weeks).
Before you start

Ensure your dataset is organized into clear columns for Total Contract Value (TCV), Contract Duration (in weeks), and Project Type before applying the logical formula.

Solution 1Recommended

Use an IF and ROUNDUP Formula to Calculate ACV

Combine IF, AND, and ROUNDUP functions to accurately determine the Annual Contract Value based on specific contract conditions.

This method uses the IF function to verify the specific 'Services Renewal' condition with a zero-week duration. If met, it bypasses division and returns the full TCV. Otherwise, it calculates the number of years by dividing the weekly duration by 52, rounds it up to the nearest whole year using ROUNDUP, and divides the TCV by that result.

1
Select the target cell

Click on the cell where you want the ACV calculation to appear (for example, cell D2).

2
Enter the logical formula

Input the formula: =IF(AND(C2="Services Renewal",B2=0),A2,A2/ROUNDUP(B2/52,0)). This assumes cell A2 contains the TCV, B2 contains the Duration in Weeks, and C2 contains the Project Type.

3
Apply the calculation

Press Enter to calculate the ACV for the first record. The formula will automatically handle standard division for multi-year durations.

4
Fill the formula down the column

Click and hold 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 dataset.

Use an IF and ROUNDUP Formula to Calculate ACV
Formula Logic: This formula gracefully handles multi-year contracts by rounding up to the next full year, seamlessly dividing the total value across the active contract periods (e.g., dividing by 2 for up to 104 weeks).
Advanced Financial Calculations in WPS Office

Calculate Complex Financial Metrics with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced logical and mathematical functions like IF, AND, and ROUNDUP, making it the perfect tool for processing financial data, contract values, and business analytics quickly and accurately.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open your contract analysis document.
  2. 2. Input the formula: Click on the target ACV cell and enter the combined IF, AND, and ROUNDUP formula just as you would in Excel.
  3. 3. Drag to fill: Use the fill handle to copy the calculation across your entire data range instantly.
Seamless format compatibility with Microsoft Excel (.xlsx) files.Fully supports advanced financial, logical, and mathematical formulas.Free to use with a lightweight, highly intuitive user interface.
microsoft office alternative - wps office

Frequently Asked Questions

What does the ROUNDUP function do in this ACV formula?

The ROUNDUP function ensures that any fractional year (for example, 60 weeks, which equals about 1.15 years) is rounded up to the next whole integer (2 years). This guarantees the TCV is correctly divided across the full active contract periods.

How do I adjust the formula if my duration is in months instead of weeks?

If your contract duration is recorded in months, change the division factor in the ROUNDUP function from 52 (weeks in a year) to 12 (months in a year). The updated mathematical logic would be ROUNDUP(B2/12,0).

Why is the AND function necessary for the zero-week condition?

The AND function ensures that both conditions—the project type being exactly 'Services Renewal' and the duration being exactly 0 weeks—are met simultaneously before the formula assigns the full TCV as the ACV without attempting division.