Fórmula del impuesto sobre la renta

En esta publicación, examinaremos un par de ideas para calcular el impuesto sobre la renta en Excel utilizando tablas de impuestos. Específicamente, usaremos BUSCARV con una columna auxiliar, eliminaremos la columna auxiliar con SUMPRODUCTO y luego usaremos la validación de datos y la función INDIRECTA para facilitar la elección de la tabla de impuestos deseada, como soltero o casado. presentación conjunta.

Objetivo

Antes de adelantarnos, dejemos claro nuestro objetivo. Queremos almacenar una tabla de impuestos en Excel. Queremos ingresar un ingreso imponible y hacer que Excel calcule el monto del impuesto, la tasa impositiva marginal y la tasa impositiva efectiva. Esta idea se ilustra en la captura de pantalla siguiente.

Como se trata de Excel, hay muchas formas de lograr el objetivo. En esta publicación, usaremos BUSCARV con una columna auxiliar, luego eliminaremos la columna auxiliar con SUMPRODUCTO y luego permitiremos al usuario seleccionar la tabla de impuestos correcta con INDIRECT. Exploramos cada enfoque.

BUSCARV

La función BUSCARV es una función de búsqueda tradicional que devuelve un valor relacionado de una tabla de búsqueda.

Dado que la función BUSCARV devuelve un valor único y no una suma agregada de muchos valores, necesitaremos modificar nuestra tabla de impuestos agregando una columna auxiliar. La columna auxiliar calculará el impuesto acumulativo para cada categoría impositiva, como se muestra a continuación.

La fórmula de la columna auxiliar acumulativa es sencilla: simplemente aplicamos la tasa marginal al ingreso del grupo. El archivo de muestra siguiente contiene la fórmula de referencia.

Si asumimos un ingreso imponible de $50,000, necesitamos escribir una fórmula que básicamente realice los siguientes cálculos:

=5081.25+((50000-36900)*.25)

Podemos usar BUSCARV para obtener todos los valores relacionados de la tabla de impuestos en función de la renta imponible. La sintaxis básica de la función BUSCARV es la siguiente:

BUSCARV(valor_búsqueda, matriz_tabla, núm_índice_columna, [búsqueda_rango])

Dónde:

• lookup_value es el valor que buscamos
• table_array es donde estamos buscando
• col_index_num es la columna que tiene el valor a devolver
• [range_lookup] será VERDADERO ya que estamos realizando una búsqueda de rango, no buscando un valor coincidente exacto

Aplicamos la BUSCARV a la calculadora de impuestos a continuación.

Sustituiremos cada valor de la tabla en la siguiente fórmula con una función BUSCARV:

=5081.25+((50000-36900)*.25)

Al hacerlo se produce la siguiente fórmula:

=BUSCARV(C5,B13:E19,4,VERDADERO)+(C5-BUSCARV(C5,B13:E19,1,VERDADERO))*BUSCARV(C5,B13:E19,3,VERDADERO)

Ahora podemos ingresar un ingreso imponible en C5 y nuestra fórmula aplica la matemática deseada.

Pero, ¿qué pasaría si no quisiéramos que nuestra hoja de trabajo contuviera la columna auxiliar del monto del impuesto acumulado? Podemos usar la función SUMAPRODUCTO en su lugar.

SUMAPRODUCCIÓN

La función SUMPRODUCT puede devolver la suma de varios productos. Es decir, es capaz de aplicar la tasa a cada tramo y devolver la suma. Esta capacidad elimina la necesidad de una columna auxiliar.

Esta función es más complicada de visualizar porque opera en varias celdas a la vez.

Para nuestra discusión, consideramos la calculadora actualizada en la captura de pantalla a continuación, que no contiene ninguna columna de ayuda.

Si el ingreso imponible fuera de $50 000, nos gustaría que Excel realizara los siguientes cálculos. Necesita multiplicar los $50,000 por 10% porque todos los $50,000 están sujetos a impuestos por al menos el 10%. A eso, necesitamos que Excel le sume el 5% de 40.925 (50.000-9.075), porque la tasa diferencial del siguiente tramo es del 5% (15%-10%). A eso hay que sumarle el 10% de 13.100 (50.000-36.900), porque la tasa diferencial de ese tramo es del 10% (25%-15%).

La idea de la tasa diferencial se ilustra en la siguiente captura de pantalla.

Usar la tasa diferencial en lugar de la tasa marginal simplifica nuestra función SUMAPRODUCTO.

Para visualizar los cálculos, podríamos configurar una nueva columna que muestre la cantidad que se aplica a cada tasa diferencial restando $50,000 de cada grupo de ingresos, como se muestra a continuación.

Para ver el impuesto extendido, agregamos otra columna que calcula el monto según la tasa diferencial, que se muestra a continuación.

Pero solo queremos incluir las filas de impuestos que sean números positivos. Podríamos agregar otra columna que convierta los valores negativos a cero y luego sumarlos, como se muestra a continuación.

Afortunadamente, $8,356 es el mismo valor devuelto por nuestra fórmula BUSCARV, así que creo que nuestros cálculos son buenos.

Con la idea básica en mente, reconsideraremos la siguiente hoja de trabajo.

Dado que nuestro objetivo es eliminar las columnas auxiliares, podemos pedirle a la función SUMAPRODUCTO que calcule la tasa diferencial, calcule la cantidad que se aplicará a cada tasa y excluya los valores negativos, todo en una sola función:

=SUMAPRODUCTO(D14:D20-D13:D19,C5-B14:B20,N(C5B14:B20))

La función SUMPRODUCTO devuelve la suma del producto de sus argumentos. Es decir, multiplica sus argumentos y luego calcula la suma. Repasemos los argumentos. El primer argumento, D14:D20-D13:D19, calcula la tasa diferencial. (Tenga en cuenta que D13 debe ser una celda vacía, no una cadena de texto, razón por la cual hicimos la fila 13 delgada). El segundo argumento, C5-B14:B20, calcula la cantidad que se aplica a cada tasa diferencial. El tercer argumento, N(C5B14:B20), excluye los valores negativos al convertir el resultado booleano de la comparación, por ejemplo, VERDADERO o FALSO, en 1 o 0. Cuando Excel multiplica la fila por 0, se excluye de la suma.

Ilustración

Configuraré algunas fórmulas nuevas en otra área de la hoja de trabajo para ilustrar los cálculos que realizan SUMPRODUCT. Tenga en cuenta que las referencias de celda siguientes son diferentes a las anteriores. SUMPRODUCT utiliza las columnas B y C. Para ilustrar la lógica, configure la columna D para representar el primer argumento (tasa diferencial), la columna E para representar el segundo argumento (cantidad) y la columna F para representar el tercer argumento (VERDADERO/ FALSO). lógica para incluir o excluir la fila). La columna G multiplica estas columnas y G33 proporciona la suma.

Si su objetivo es eliminar las fórmulas auxiliares, SUMPRODUCT puede resultarle útil. Sin embargo, mi enfoque preferido es utilizar BUSCARV e INDIRECTO como se demuestra a continuación.

BUSCARV e INDIRECTA

Queremos brindarle al usuario la posibilidad de seleccionar un estado civil para efectos de la declaración, como soltero o MFJ. Almacenaremos las tarifas para cada estado civil en una tabla de Excel separada (Insertar Tabla). Nombraremos cada tabla con el estado civil deseado. Usaremos la validación de datos para proporcionar un menú desplegable en la celda con la lista de opciones. Usaremos el enfoque BUSCARV demostrado anteriormente para recuperar valores relacionados y calcular la renta imponible. Usaremos la función INDIRECTA para indicarle a Excel qué tabla usar para la búsqueda. Uf… son muchas partes móviles. Dado que se exploraron anteriormente en este blog, en lugar de proporcionar cada uno de los pasos aquí, simplemente proporcionará referencias a las publicaciones de blog relacionadas en la lista de recursos adicionales a continuación.

Aquí está nuestro producto final:

Todos los enfoques discutidos anteriormente se incluyen en el archivo de muestra a continuación. No dude en consultar el archivo según sea necesario.

Si tiene algún enfoque alternativo, compártalo publicando un comentario a continuación… ¡gracias!

Recursos adicionales

• Archivo de muestra: Impuestos
• Publicaciones BUSCARV
• publicaciones INDIRECTAS
• Publicaciones de validación de datos
• Publicaciones de mesa
• Gracias a Don Steele por compartir su archivo Excel de tasa impositiva marginal: marginaltaxrate