Todos los analistas financieros La guía Analyst Trifecta® La guía definitiva sobre cómo ser un analista financiero de clase mundial. ¿Quieres ser un analista financiero de clase mundial? ¿Está buscando seguir las mejores prácticas líderes en la industria y destacarse entre la multitud? Nuestro proceso, llamado The Analyst Trifecta®, consta de análisis, presentación y habilidades blandas, pasa más tiempo en Excel del que les gustaría admitir. Basándonos en años y años de experiencia, hemos recopilado las fórmulas de Excel más importantes y avanzadas que todo analista financiero de clase mundial debe conocer.
1. PARTIDA DE ÍNDICE
Fórmula: = INDICE (C3: E9, COINCIDIR (B13, C3: C9,0), COINCIDIR (B14, C3: E3,0))
Esta es una alternativa avanzada a las fórmulas BUSCARV o BUSCARH (que tienen varios inconvenientes y limitaciones). INDEX MATCH Fórmula de coincidencia de índices La combinación de las funciones INDEX y MATCH es una fórmula de búsqueda más poderosa que BUSCARV. Aprenda a usar INDEX MATCH en este tutorial de Excel. El índice devuelve el valor de una celda en una tabla según la columna y el número de fila y Coincidencia devuelve la posición de una celda en una fila o columna. Aprenda cómo en esta guía es una poderosa combinación de fórmulas de Excel que llevará su análisis financiero y modelos financieros al siguiente nivel.
INDICE devuelve el valor de una celda en una tabla según el número de columna y fila.
COINCIDIR devuelve la posición de una celda en una fila o columna.
Aquí hay un ejemplo de las fórmulas INDICE y COINCIDIR combinadas. En este ejemplo, buscamos y devolvemos la altura de una persona según su nombre. Dado que el nombre y la altura son variables en la fórmula, ¡podemos cambiar ambas!
Fórmula de coincidencia de índice La combinación de las funciones INDICE y COINCIDIR es una fórmula de búsqueda más poderosa que BUSCARV. Aprenda a usar INDEX MATCH en este tutorial de Excel. El índice devuelve el valor de una celda en una tabla según la columna y el número de fila y Coincidencia devuelve la posición de una celda en una fila o columna. Aprende cómo hacerlo en esta guía.
Para obtener una explicación paso a paso o cómo usar esta fórmula, consulte nuestra guía gratuita sobre cómo usar INDEX MATCH en Excel Index Match Formula La combinación de las funciones INDEX y MATCH es una fórmula de búsqueda más poderosa que VLOOKUP. Aprenda a usar INDEX MATCH en este tutorial de Excel. El índice devuelve el valor de una celda en una tabla según la columna y el número de fila y Coincidencia devuelve la posición de una celda en una fila o columna. Aprenda cómo hacerlo en esta guía.
2. SI se combina con Y / O
Fórmula: = SI (Y (C2> = C4, C2 <= C5), C6, C7)
Cualquiera que haya pasado mucho tiempo haciendo varios tipos de modelos financieros Tipos de modelos financieros Los tipos más comunes de modelos financieros incluyen: modelo de 3 estados, modelo DCF, modelo de fusiones y adquisiciones, modelo LBO, modelo presupuestario. Descubra los 10 tipos principales y sabe que las fórmulas IF anidadas pueden ser una pesadilla. La combinación de IF con la función AND u OR puede ser una excelente manera de mantener las fórmulas más fáciles de auditar y más fáciles de entender para otros usuarios. En el siguiente ejemplo, verá cómo usamos las funciones individuales en combinación para crear una fórmula más avanzada.
Para obtener un desglose detallado de cómo realizar esta función en Excel, consulte nuestra guía gratuita sobre cómo usar IF con Y / O Declaración IF entre dos números Descargue esta plantilla gratuita para una declaración IF entre dos números en Excel. En este tutorial, le mostramos paso a paso cómo calcular IF con una declaración AND. Aprenda a construir una declaración IF que probará si una celda contiene un valor entre dos números y luego generará el resultado que desea si se cumple esa condición. 
Declaración IF entre dos números Descargue esta plantilla gratuita para una declaración IF entre dos números en Excel. En este tutorial, le mostramos paso a paso cómo calcular IF con una declaración AND. Aprenda a construir una declaración IF que probará si una celda contiene un valor entre dos números y luego generará el resultado que desea si se cumple esa condición
3. OFFSET combinado con SUM o AVERAGE
Fórmula: = SUMA (B4: DESPLAZAMIENTO (B4,0, E2-1))
La función OFFSET Función OFFSET La función OFFSET se clasifica en las funciones de búsqueda y referencia de Excel. OFFSET devolverá un rango de celdas. Es decir, devolverá un número especificado de filas y columnas de un rango inicial que se especificó. por sí solo no es particularmente avanzado, pero cuando lo combinamos con otras funciones como SUM o AVERAGE podemos crear una fórmula bastante sofisticada. Suponga que desea crear una función dinámica que pueda sumar un número variable de celdas. Con la fórmula SUM normal, está limitado a un cálculo estático, pero al agregar OFFSET puede hacer que la referencia de celda se mueva.
Cómo funciona: para que esta fórmula funcione, sustituimos la celda de referencia final de la función SUMA con la función DESPLAZAMIENTO. Esto hace que la fórmula sea dinámica y la celda a la que se hace referencia como E2 es donde puede decirle a Excel cuántas celdas consecutivas desea sumar. ¡Ahora tenemos algunas fórmulas avanzadas de Excel!
A continuación se muestra una captura de pantalla de esta fórmula un poco más sofisticada en acción.
Como puede ver, la fórmula SUM comienza en la celda B4, pero termina con una variable, que es la fórmula OFFSET que comienza en B4 y continúa por el valor en E2 (“3”), menos uno. Esto mueve el final de la fórmula de suma sobre 2 celdas, sumando 3 años de datos (incluido el punto de partida). Como puede ver en la celda F7, la suma de las celdas B4: D4 es 15, que es lo que nos da la fórmula de compensación y suma.
Aprenda a crear esta fórmula paso a paso en nuestro curso avanzado de Excel.
4. ELIGE
Fórmula: = ELEGIR (elección, opción1, opción2, opción3)
La función ELEGIR Función ELEGIR La función ELEGIR se clasifica en Funciones de búsqueda y referencia de Excel. Devolverá un valor de una matriz correspondiente al número de índice proporcionado. La función devolverá la enésima entrada en una lista dada. Como analista financiero, la función ELEGIR es útil para seleccionar de un conjunto de datos dado. Por ejemplo, somos excelentes para el análisis de escenarios en modelos financieros. Le permite elegir entre un número específico de opciones y devolver la "opción" que ha seleccionado. Por ejemplo, imagine que tiene tres supuestos diferentes para el crecimiento de los ingresos el próximo año: 5%, 12% y 18%. Con la fórmula ELEGIR, puede devolver un 12% si le dice a Excel que desea la opción 2.
Obtenga más información sobre el análisis de escenarios en Excel. Análisis de escenarios El análisis de escenarios es una técnica que se utiliza para analizar decisiones especulando varios resultados posibles en inversiones financieras. En modelos financieros, esto.
Para ver una demostración en video, consulte nuestro Curso de fórmulas avanzadas de Excel.
5. XNPV y XIRR
Fórmula: = XNPV (tasa de descuento, flujos de efectivo, fechas)
Si es analista que trabaja en banca de inversión Trayectoria profesional en banca de inversión Guía profesional en banca de inversión: planifique su trayectoria profesional en el IB. Obtenga información sobre los salarios de la banca de inversión, cómo ser contratado y qué hacer después de una carrera en IB. La división de banca de inversión (IBD) ayuda a gobiernos, corporaciones e instituciones a recaudar capital y completar fusiones y adquisiciones (M&A). , investigación de capital, planificación y análisis financieros (FP&A FP&A Analyst Conviértase en un analista de FP&A en una corporación. Describimos el salario, las habilidades, la personalidad y la capacitación que necesita para los trabajos de FP&A y una carrera financiera exitosa. Los analistas, gerentes y directores de FP&A son responsable de proporcionar a los ejecutivos el análisis y la información que necesitan), o cualquier otra área de las finanzas corporativas que requiera descontar los flujos de efectivo,¡entonces estas fórmulas son un salvavidas!
En pocas palabras, XNPV y XIRR le permiten aplicar fechas específicas a cada flujo de caja individual que se está descontando. El problema con las fórmulas básicas de VAN y TIR de Excel es que asumen que los períodos de tiempo entre el flujo de caja son iguales. De manera rutinaria, como analista, tendrá situaciones en las que los flujos de efectivo no se sincronizan de manera uniforme, y esta fórmula es la forma en que lo arregla.
Para obtener un desglose más detallado, consulte nuestra guía gratuita de fórmulas IRR vs XIRR XIRR vs IRR Por qué usar XIRR vs IRR. XIRR asigna fechas específicas a cada flujo de caja individual, lo que lo hace más preciso que la TIR al crear un modelo financiero en Excel. así como nuestra guía XNPV.
6. SUMIF y COUNTIF
Fórmula: = CONTAR.SI (D5: D12, ”> = 21 ″)
Estas dos fórmulas avanzadas son excelentes usos de funciones condicionales. SUMIF agrega todas las celdas que cumplen con ciertos criterios y COUNTIF cuenta todas las celdas que cumplen con ciertos criterios. Por ejemplo, imagine que desea contar todas las celdas que son mayores o iguales a 21 (la edad legal para beber en los EE. UU.) Para averiguar cuántas botellas de champán necesita para un evento con un cliente. Puede usar COUNTIF como una solución avanzada, como se muestra en la captura de pantalla a continuación.
En nuestro curso avanzado de Excel, desglosamos estas fórmulas con más detalle.
7. PMT e IPMT
Fórmula: = PMT (tasa de interés, # de períodos, valor presente)
Si trabaja en banca comercial Perfil de carrera en banca comercial Una carrera en banca comercial consiste en brindar a los clientes productos crediticios como préstamos a plazo, líneas de crédito renovables, servicios sindicados, servicios de administración de efectivo y otros productos de renta fija. Como analista de crédito o administrador de cuentas, usted brinda asesoramiento financiero, bienes raíces, FP&A FP&A La planificación y análisis financiero (FP&A) es una función importante en una corporación. Los profesionales de FP&A apoyan la toma de decisiones ejecutivas para el CEO, el CFO y la Junta Directiva con planificación, análisis y modelado. Aprenda lo que implica el trabajo de un analista, gerente o director de FP&A: salario, requisitos, educación, habilidades o cualquier puesto de analista financiero que se ocupe de cronogramas de deuda, querrá comprender estas dos fórmulas detalladas.
La fórmula PMT le da el valor de pagos iguales durante la vigencia de un préstamo. Puede usarlo junto con IPMT (que le indica los pagos de intereses para el mismo tipo de préstamo), luego separe los pagos de capital e intereses.
A continuación se muestra un ejemplo de cómo utilizar la función PMT para obtener el pago hipotecario mensual de una hipoteca de $ 1 millón al 5% durante 30 años.
8. LEN y TRIM
Fórmulas: = LEN (texto) y = TRIM (texto)
Estas son fórmulas un poco menos comunes, pero ciertamente muy sofisticadas. Estas aplicaciones son excelentes para los analistas financieros. La guía Analyst Trifecta® La guía definitiva sobre cómo ser un analista financiero de clase mundial. ¿Quieres ser un analista financiero de clase mundial? ¿Está buscando seguir las mejores prácticas líderes en la industria y destacarse entre la multitud? Nuestro proceso, llamado The Analyst Trifecta®, consta de análisis, presentación y habilidades blandas que necesitan organizar y manipular grandes cantidades de datos. Desafortunadamente, los datos que obtenemos no siempre están perfectamente organizados y, a veces, puede haber problemas como espacios adicionales al principio o al final de las celdas
En el siguiente ejemplo, puede ver cómo la fórmula TRIM limpia los datos de Excel.
9. CONCATENAR
Fórmula: = A1 & "más texto"
Concatenar no es realmente una función en sí misma, es solo una forma innovadora de unir información de diferentes celdas y hacer que las hojas de trabajo sean más dinámicas. Esta es una herramienta muy poderosa para los analistas financieros que realizan modelos financieros (consulte nuestra guía gratuita de modelos financieros. aprende más).
En el siguiente ejemplo, puede ver cómo el texto "Nueva York" más "," se une con "NY" para crear "Nueva York, NY". Esto le permite crear encabezados y etiquetas dinámicos en hojas de trabajo. Ahora, en lugar de actualizar la celda B8 directamente, puede actualizar las celdas B2 y D2 de forma independiente. Con un gran conjunto de datos, esta es una habilidad valiosa para tener a su disposición.
10. Funciones CELL, LEFT, MID y RIGHT
Estas funciones avanzadas de Excel se pueden combinar para crear algunas fórmulas muy avanzadas y complejas para usar. La función CELDA puede devolver una variedad de información sobre el contenido de una celda (como su nombre, ubicación, fila, columna y más). La función IZQUIERDA puede devolver texto desde el principio de una celda (de izquierda a derecha), MID devuelve texto desde cualquier punto de inicio de la celda (de izquierda a derecha) y DERECHA devuelve texto desde el final de la celda (de derecha a izquierda).
A continuación se muestra una ilustración de estas tres fórmulas en acción.
Para ver cómo se pueden combinar de una manera poderosa con la función CELL, lo desglosamos paso a paso en nuestra clase de fórmulas avanzadas de Excel.
Más formación sobre fórmulas de Excel
Esperamos que estas 10 fórmulas avanzadas de Excel le hayan sido útiles. Deben recorrer un largo camino para mejorar sus habilidades de análisis financiero y modelado financiero. Habilidades de modelado financiero. Aprenda las 10 habilidades de modelado financiero más importantes y lo que se requiere para ser bueno en el modelado financiero en Excel. Habilidades más importantes: contabilidad.
A continuación, se muestran más recursos financieros para ayudarlo a convertirse en un usuario avanzado de Excel:
- Hoja de trucos de fórmulas de Excel Hoja de trucos de fórmulas de Excel La hoja de trucos de fórmulas de Excel de Finance le brindará todas las fórmulas más importantes para realizar análisis financieros y modelado en hojas de cálculo de Excel. Si desea convertirse en un maestro del análisis financiero de Excel y un experto en la construcción de modelos financieros, ha venido al lugar correcto.
- Accesos directos de teclado de Excel Accesos directos de Excel Accesos directos de Excel para PC Mac: lista de los accesos directos de MS Excel más importantes y comunes para usuarios de PC y Mac, finanzas, profesiones contables. Los atajos de teclado aceleran sus habilidades de modelado y ahorran tiempo. Aprenda a editar, formatear, navegar, cinta, pegar especial, manipulación de datos, edición de fórmulas y celdas, y otros atajos
- Curso intensivo gratuito de Excel
- Curso avanzado de Excel
- Excel para modelos financieros
