Calculate a Tiered Rent Savings Fee in Excel
Question details
Create a formula that calculates a progressive fee from rent savings using two percentage tiers and a maximum total fee.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The rent savings amount is entered in cell A2, and the fee must be calculated from the savings amount.
- Observed behavior
- The fee should charge 6% on savings up to $100,000, 3% on savings from $100,001 through $233,333, and never exceed $10,000.
Enter the rent savings as a numeric value in A2, without text such as dollar signs typed into the cell. Format the result cell as Currency after adding the formula.
Use a progressive tiered fee formula with a $10,000 cap
Use this formula when rent savings are in A2 and each rate must apply only to its own savings tier.
The inner MIN functions restrict each tier to its allowed range, while MAX prevents a negative second-tier amount. The outer MIN ensures that the final fee cannot be greater than $10,000.
Click the cell where you want the calculated fee to appear, such as B2.
Type or paste =MIN(10000,MIN(A2,100000)*6%+MAX(0,MIN(A2,233333)-100000)*3%) and press Enter.
With the result cell selected, use Home > Number Format > Currency or Accounting to display the fee as a dollar amount.
Drag the fill handle from the bottom-right corner of the formula cell down the column to calculate fees for other rent savings values.
Calculate Rent Savings Fees with WPS Spreadsheets
WPS Spreadsheets supports the same MIN, MAX, percentage, and cell-reference formulas needed to calculate a capped, tiered rent savings fee. Enter the formula once, then fill it down to apply the calculation across your worksheet.
- 1. Open the savings worksheet: Open your workbook in WPS Spreadsheets and confirm that the rent savings values are in column A, starting with A2.
- 2. Choose the fee column: Click the first result cell, such as B2, beside the corresponding rent savings value.
- 3. Add the tiered formula: Enter =MIN(10000,MIN(A2,100000)*6%+MAX(0,MIN(A2,233333)-100000)*3%) and press Enter.
- 4. Apply currency formatting: Select the fee cells and choose Home > Currency Format to display the calculated values as dollar amounts.
- 5. Fill the formula down: Drag the fill handle from B2 down to calculate the capped fee for each savings amount.
Frequently Asked Questions
What does the MAX function do in this tiered fee formula?
MAX(0,MIN(A2,233333)-100000) prevents the second-tier calculation from becoming negative when the rent savings in A2 are $100,000 or less.
Why is MIN(A2,100000) used for the 6% rate?
MIN(A2,100000) limits the 6% calculation to the first $100,000 of rent savings, even when A2 contains a larger amount.
How can I calculate the fee when the rent savings value is in another cell?
Replace every instance of A2 in the formula with the cell that contains your rent savings amount, such as C5.
How do I make sure the fee never exceeds $10,000?
Keep the outer portion of the formula as MIN(10000,...). It compares the calculated fee with $10,000 and returns the lower amount.
Can I use this formula in WPS Spreadsheets?
Yes. WPS Spreadsheets supports the MIN, MAX, percentage, and cell-reference functions used in this Excel-compatible formula.
