CAPPEDVALUES
Description
Typically used for 401(k) deductions, ESPP deductions, or tax payments that have a
regular value per period, but drop to zero (0) when the payment reaches the cap.
Returns an array of values over a set of periods from an array of values that you
provide, over the same periods, limited by a provided cap over the whole duration.
The function returns the full values for all periods until it reaches the one where
the cap you specified is reached; for that period it returns a partial value, and if
there are any periods left, the function returns 0 for those.
Syntax
CAPPEDVALUES(
values
,
cap
, [
periods
])
- values: The values array. The array must be either 1 row or 1 column.
- cap: The cap value.
- periods: The number of periods, such as quarters or months, in the array. If you omit the periods argument, the function uses the number ofvaluesthat you specified to determine the number of periods.
Example
This example shows how to populate the 401(k) withholding values in rows 6 & 8,
based on the values in rows 2, 5, and 7. The function uses salaries and 401(k)
withholding amounts for Peter and Aileen, based on a 401(k) cap of $18,000 and a
401(k) contribution percentage of 17%.
The formula in row 6 (for Peter) is =CAPPEDVALUES(F2*C5:N5,$B$2). The
values
argument value provides the information that the
formula uses to determine the number of periods.
The formula in row 8 (for Aileen) is =CAPPEDVALUES(F2*C7,$B$2,12).
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | |||||||||||||||
2 | 401K cap | $18,000.00 | Contribution | 17% | |||||||||||
3 | |||||||||||||||
4 | Jan
| Feb
| Mar
| Apr
| May
| June
| July
| Aug
| Sept
| Oct
| Nov
| Dec
| Total
| ||
5 | Peter | Salary | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | 11,054.00 | $132,648 |
6 | Peter | 401K | 1879.18 | 1879.18 | 1879.18 | 1879.18 | 1879.18 | 1879.18 | 1879.18 | 1879.18 | 1879.18 | 1087.38 | 0 | 0 | $18,000 |
7 | Aileen | Salary | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $12,487.00 | $149,844 |
8 | Aileen | 401K | 2122.79 | 2122.79 | 2122.79 | 2122.79 | 2122.79 | 2122.79 | 2122.79 | 2122.79 | 1017.68 | 0 | 0 | 0 | $18,000 |