Excel para analistas financieros: habilidades y fórmulas clave
Prepara tu prueba técnica de finanzas: estructura de modelos, referencias mixtas, SUMIFS, XLOOKUP, VPN, TIR y control de errores en Excel.
Por Ayoub Farid · · 3 min de lectura
Los analistas financieros pasan gran parte de su día a día trabajando en Excel. Cualquier prueba de Excel para entrevista de trabajo en este campo suele incluir un ejercicio de modelado financiero con límite de tiempo, por lo que la velocidad y una estructura limpia son tan importantes como dominar las funciones de Excel.
La estructura es lo primero
- Datos de entrada (inputs), cálculos y resultados (outputs) en hojas separadas o en zonas claramente delimitadas dentro de tu hoja de cálculo.
- Supuestos siempre en celdas, nunca escritos directamente dentro de las fórmulas: tasa de crecimiento, tasa impositiva, tasa de descuento.
- Fórmulas consistentes a lo largo de cada fila para poder arrastrar la fórmula hacia la derecha sin errores.
- Código de colores: usar texto azul para las celdas de inputs o datos manuales es una convención estándar en el sector.
Fórmulas que usarás a diario
| Tarea | Fórmula |
|---|---|
| Crecimiento | =B2*(1+$C$1) |
| Variación interanual | =(C2-B2)/B2. Consulta cambio porcentual |
| CAGR (Tasa de crecimiento anual compuesto) | =(end/start)^(1/years)-1 |
| Resúmenes condicionales | SUMIFS (en español: SUMAR.SI.CONJUNTO) |
| Búsquedas avanzadas | XLOOKUP (BUSCARX), INDEX MATCH (INDICE/COINCIDIR) |
| Márgenes | =(B2-C2)/B2. Consulta margen de beneficio |
| Valor actual neto | =NPV(rate,C2:G2)+B2 (en español: VNA) |
| Tasa de retorno | =IRR(B2:G2) (en español: TIR) |
| Pago de préstamos | =PMT(rate/12,years*12,-amount) (en español: PAGO) |
Nota sobre las fórmulas: Las hojas de cálculo como Google Sheets o Cellio usan las fórmulas en inglés, mientras que la versión en español de Excel emplea los nombres localizados indicados entre paréntesis. Ten en cuenta que =NPV() asume que el primer flujo de caja ocurre al final del primer período; por eso, la inversión inicial (período 0) se suma por fuera de la función.
Las referencias mixtas son cruciales
En las proyecciones financieras, las fórmulas se suelen rellenar hacia la derecha (años) y arrastrar hacia abajo (partidas o conceptos). Por eso, dominar cada celda y rango con el signo $ adecuado marca la diferencia:
- Una tasa de crecimiento en la columna C que se aplica a todos los años:
$C2. - El encabezado de un año en la fila 1 que baja por todas las filas:
D$1.
Aprender Excel a fondo implica dominar esto; revisa nuestra guía sobre referencias mixtas y la diferencia con una referencia absoluta estándar.
Escenarios
- Una celda selectora de escenarios combinada con
CHOOSE(ELEGIR) oXLOOKUP(BUSCARX) para alternar entre supuestos base, optimista y pesimista. - Tablas de datos (Análisis de hipótesis / What-If Analysis) para realizar análisis de sensibilidad rápidos.
Comprobaciones de control
Asegúrate de que el balance general cuadre (la diferencia entre activo y pasivo más patrimonio neto debe ser cero), que la caja coincida con el flujo de efectivo y que no existan valores fijos escritos a mano dentro de las filas de fórmulas de Excel.
Preguntas frecuentes
¿Qué habilidades de Excel necesita un analista financiero?
Estructuración de modelos financieros, referencias absolutas y mixtas, SUMIFS, fórmulas de búsqueda (XLOOKUP o INDEX/MATCH), cálculo de crecimiento y márgenes, funciones de valoración como NPV e IRR, modelado de escenarios y diseño de comprobaciones de balance.
¿Cuáles son las funciones de Excel más utilizadas en finanzas?
NPV (VNA), XNPV (VNA.NO.PER), IRR (TIR), XIRR (TIR.NO.PER), PMT (PAGO), SUMIFS (SUMAR.SI.CONJUNTO), XLOOKUP (BUSCARX), INDEX/MATCH (INDICE/COINCIDIR), EOMONTH (FIN.MES) y ROUND (REDONDEAR) son esenciales.
¿Cómo preparo una prueba técnica de modelado financiero?
Practica la construcción rápida desde cero de un modelo de proyección simple a tres años. Separa claramente los supuestos de los cálculos y mantén fórmulas uniformes en cada fila.
¿Por qué el resultado de NPV en Excel difiere del de una calculadora financiera?
La función NPV de Excel descuenta el primer valor introducido como si perteneciera al final del período 1. Si tienes un desembolso inicial en el momento cero, debes sumarlo fuera de la función en lugar de incluirlo dentro del rango descontado.