How to Calculate Contract Values with Annual Percentage Increases in WPS Office Spreadsheets
Question details
The user needs to calculate contract values for each column date based on a start date, end date, initial amount, and annual percentage increase, using either filled formulas or dynamic spill formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a contract value schedule across multiple date columns, where each covered year applies a set annual percentage increase.
- Observed behavior
- The formula should return blank outside the contract term and apply cumulative annual increases only for the years within the start and end dates.
Confirm that the source data contains valid dates in the start date, end date, and column header cells, and that the initial amount and annual increase percentage are numeric.
Use a fillable formula for all WPS Office Spreadsheets versions
Best when you need a broadly compatible formula that you can copy across the contract date row in WPS Office Spreadsheets.
This approach uses DATEDIF to count completed years between the contract start date and each column date, then applies the annual increase as a compound multiplier. The IF and OR wrapper returns blank for dates before the start date or after the end date.
Open the workbook in WPS Office Spreadsheets that contains start dates in column K, end dates in column L, annual increase rates in column O, and initial contract values in column P.
Select cell AC2 and enter: =IF(OR(AC$1<$K2,AC$1>$L2),"",$P2*(1+$O2)^DATEDIF($K2,AC$1,"y"))
Drag the fill handle from AC2 to the right through the last date column to apply the contract value calculation to the remaining periods.
Use a dynamic spilled formula in newer WPS Office versions
Use this when your version of WPS Office Spreadsheets supports dynamic array formulas and you want a single formula to populate the entire date range automatically.
Calculate Contract Value Increases with WPS Office Spreadsheets
Use WPS Office Spreadsheets to build a contract value schedule with the same Excel formula logic. Enter the annual percentage increase formula in WPS Office, then use fill or dynamic spill to update all contract period values automatically.
- 1. Download and install WPS Office: Install WPS Office on your computer and launch WPS Office Spreadsheets.
- 2. Open the Excel contract schedule: Open the .xlsx workbook that contains the contract dates, initial amounts, start dates, end dates, and annual increase rates.
- 3. Enter the formula in the first cell: Click the first contract period cell and enter the fillable formula shown in the recommended solution.
- 4. Fill or spill across the date columns: Use the fill handle across columns, or in a dynamic-array-enabled version enter the spill formula once.
- 5. Check the calculated values: Confirm that blank cells appear outside the contract start and end dates and that values increase annually by the expected percentage.
Frequently Asked Questions
Which formula should I use if my WPS Office version does not support dynamic arrays?
Use the traditional fillable formula in AC2: =IF(OR(AC$1<$K2,AC$1>$L2),"",$P2*(1+$O2)^DATEDIF($K2,AC$1,"y")), then drag the fill handle across the remaining date columns.
Why does the formula return blank for some date columns?
The IF and OR wrapper intentionally returns an empty string when the column date is before the contract start date or after the contract end date, so values appear only within the contract term.
Can I use a percentage value such as 5% directly in column O?
Yes. If cell O2 is formatted as a percentage, 1+$O2 works correctly. If you use a decimal value such as 0.05, the calculation produces the same annual multiplier.
Does WPS Office support opening and saving the same .xlsx contract workbook?
Yes. WPS Office Spreadsheets supports the .xlsx format, so you can open, edit, and save the workbook while keeping compatibility with Microsoft Excel.
What does DATEDIF do in this contract value formula?
DATEDIF calculates the number of complete years between the contract start date and the column header date, which controls the exponent for the annual percentage increase.
