How to Create an Excel Revenue Formula for Termination Dates and Fee Changes
Question details
The user needs a formula in Excel or Google Sheets to calculate monthly recurring revenue, accounting for specific start dates, termination dates, and scheduled fee changes.
- Product
- Excel / Google Sheets
- Device & OS
- not provided
- Scenario
- Tracking monthly recurring revenue across columns where contracts may terminate mid-year or have a scheduled fee increase/decrease.
- Observed behavior
- The formula needs to dynamically output the correct monthly fee under columns representing different months, returning zero if the contract is terminated or hasn't started, and updating the value if the fee change date has passed.
Ensure all your dates (start, termination, fee change, and month headers) are formatted as actual date values in your spreadsheet, rather than text strings, so the logical comparisons work correctly.
Using a Nested IF Formula for Single Fee Changes
Use a combination of IF, OR, and AND functions to evaluate the month header against the contract's date milestones.
This formula checks two main conditions for each month: first, whether the month is outside the active contract period (before the start date or on/after the termination date), and second, whether the month is on or after a scheduled fee change date.
Place your month headers (e.g., Jan-2024, Feb-2024) in row 2 starting from column B.
In columns AA through AE of row 3, input the Start Date (AA), Termination Date (AB), Base Fee (AC), Fee-Change Date (AD), and New Fee (AE). Leave termination or fee-change dates blank if they do not apply to the specific contract.
Click cell B3 (the first month of the first contract) and enter the following formula: =IF(OR(B$2<$AA3,AND($AB3<>"",B$2>=$AB3)),0,IF(AND($AD3<>"",B$2>=$AD3),$AE3,$AC3))
Press Enter, select cell B3, and drag the fill handle across your month columns and down through your contract rows to populate the entire revenue schedule.
Track Your Monthly Revenue with WPS Spreadsheet
WPS Office offers a powerful, free Spreadsheet tool that fully supports advanced logical formulas, date formatting, and financial modeling, making it easy to manage complex revenue schedules.
- 1. Open your workbook: Launch WPS Spreadsheet and open your revenue tracking document.
- 2. Format cells as dates: Ensure your date cells are formatted correctly by selecting them, right-clicking, and choosing 'Format Cells' > 'Number' > 'Date'.
- 3. Insert the nested formula: Paste the nested IF formula into the intersection cell of your first month and first contract row.
- 4. Fill the data range: Drag the green fill handle at the bottom right of the cell to apply the logic to all subsequent months and contracts.
Frequently Asked Questions
Why is my formula returning an error instead of the base fee?
This usually happens if your date headers or contract dates are stored as text rather than valid date formats. Select your cells, right-click to open 'Format Cells', and ensure your spreadsheet software recognizes them as dates.
How do I handle multiple fee changes for a single contract?
For multiple fee changes, a nested IF statement becomes too complex to manage. Instead, you should create a separate sorted fee schedule table and use an approximate match VLOOKUP or XLOOKUP function to retrieve the correct fee for a given month.
What if a contract doesn't have a termination date yet?
The provided formula automatically accounts for this scenario. The logic checks if the termination date cell is empty. If it is blank, the formula ignores the termination logic and continues calculating the active fee.
Can I use this formula in Google Sheets as well as Excel?
Yes, the logical functions IF, AND, and OR, along with absolute and relative cell references (like $AA3 and B$2), work exactly the same in Google Sheets, Microsoft Excel, and WPS Spreadsheet.
