Análisis financiero de datos de Excel

Puede realizar análisis financieros con Excel de forma sencilla. Excel le proporciona varias funciones financieras como PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, etc. que le permiten llegar rápidamente a los resultados del análisis financiero.

En este capítulo, aprenderá dónde y cómo puede usar estas funciones para su análisis.

¿Qué es la anualidad?

Una anualidad es una serie de pagos constantes en efectivo realizados durante un período continuo. Por ejemplo, ahorros para la jubilación, pagos de seguros, préstamos hipotecarios, hipotecas, etc. En funciones de anualidades:

  • Un número positivo representa el efectivo recibido.
  • Un número negativo representa efectivo pagado.

Valor presente de una serie de pagos futuros

El valor presente es la cantidad total que vale ahora una serie de pagos futuros. Puede calcular el valor actual usando las funciones de Excel -

  • PV- Calcula el valor presente de una inversión utilizando una tasa de interés y una serie de pagos futuros (valores negativos) e ingresos (valores positivos). Al menos uno de los flujos de efectivo debe ser positivo y al menos uno debe ser negativo.

  • NPV - Calcula el valor actual neto de una inversión utilizando una tasa de descuento y una serie de pagos futuros periódicos (valores negativos) e ingresos (valores positivos).

  • XNPV - Calcula el valor actual neto para un programa de flujos de efectivo que no es necesariamente periódico.

Note that -

  • Los flujos de efectivo PV deben ser constantes, mientras que los flujos de efectivo NPV pueden ser variables.

  • Los flujos de efectivo PV pueden estar al principio o al final del período, mientras que los flujos de efectivo NPV deben estar al final del período.

  • Los flujos de efectivo NPV deben ser periódicos, mientras que los flujos de efectivo XNPV no necesitan ser periódicos.

En esta sección, comprenderá cómo trabajar con PV. Aprenderá sobre el VPN en una sección posterior.

Ejemplo

Suponga que está comprando un refrigerador. El vendedor le dice que el precio del refrigerador es 32000, pero tiene la opción de pagar la cantidad en 8 años con una tasa de interés del 13% anual y pagos anuales de 6000. También tiene la opción de realizar los pagos ya sea al principio o al final de cada año.

Desea saber cuál de estas opciones es beneficiosa para usted.

Puede utilizar la función PV de Excel -

PV (rate, nper, pmt, [fv ], [type])

Para calcular el valor presente con pagos al final de cada año, omita el tipo o especifique 0 para el tipo.

Para calcular el valor presente con pagos al final de cada año, especifique 1 para el tipo.

Obtendrá los siguientes resultados:

Por lo tanto,

  • Si realiza el pago ahora, debe pagar 32.000 de valor actual.
  • Si opta por pagos anuales con pago al final del año, debe pagar 28, 793 del valor actual.
  • Si opta por pagos anuales con pago al final del año, debe pagar 32.536 de valor presente.

Puede ver claramente que la opción 2 es beneficiosa para usted.

¿Qué es EMI?

Investopedia define una cuota mensual equiparada (EMI) como "un monto de pago fijo realizado por un prestatario a un prestamista en una fecha específica cada mes calendario. Las cuotas mensuales equiparadas se utilizan para pagar tanto el interés como el capital cada mes, un número determinado de años, el préstamo se liquida en su totalidad ".

EMI en un préstamo

En Excel, puede calcular el EMI de un préstamo con la función PMT.

Supongamos que desea obtener un préstamo hipotecario de 5000000 con una tasa de interés anual del 11,5% y el plazo del préstamo es de 25 años. Puede encontrar su EMI de la siguiente manera:

  • Calcule la tasa de interés por mes (Tasa de interés por año / 12)
  • Calcular el número de pagos mensuales (número de años * 12)
  • Utilice la función PMT para calcular EMI

Como observas,

  • El valor presente (PV) es el monto del préstamo.
  • El valor futuro (FV) es 0, ya que al final del plazo el monto del préstamo debe ser 0.
  • El tipo es 1, ya que los EMI se pagan al comienzo de cada mes.

Obtendrá los siguientes resultados:

Pago mensual de principal e intereses de un préstamo

EMI incluye tanto los intereses como el pago parcial del principal. A medida que aumenta el tiempo, estos dos componentes de EMI variarán, reduciendo el equilibrio.

Llegar

  • La parte de intereses de sus pagos mensuales, puede utilizar la función IPMT de Excel.

  • El pago de la parte principal de sus pagos mensuales, puede utilizar la función Excel PPMT.

Por ejemplo, si ha tomado un préstamo de 1.000.000 a un plazo de 8 meses a una tasa del 16% anual. Puede obtener valores para el EMI, los montos de interés decrecientes, el pago creciente de montos de capital y el saldo decreciente del préstamo durante los 8 meses. Al final de los 8 meses, el saldo del préstamo será 0.

Siga el procedimiento que se indica a continuación.

Step 1 - Calcule la EMI de la siguiente manera.

Esto da como resultado una EMI de Rs. 13261.59.

Step 2 - A continuación, calcule los intereses y las partes principales del EMI para los 8 meses como se muestra a continuación.

Obtendrá los siguientes resultados.

Intereses y capital pagado entre dos períodos

Puede calcular los intereses y el principal pagados entre dos períodos, inclusive.

  • Calcule el interés acumulado pagado entre el 2º y el 3º mes utilizando la función CUMIPMT.

  • Verifique el resultado sumando los valores de interés del 2º y 3º mes.

  • Calcular el capital acumulado pagado entre 2 ª y 3 ª meses usando la función PAGO.PRINC.ENTRE.

  • Verifique el resultado sumando los valores principales del 2º y 3º mes.

Obtendrá los siguientes resultados.

Puede ver que sus cálculos coinciden con los resultados de su verificación.

Calcular la tasa de interés

Suponga que toma un préstamo de 100.000 y desea devolverlo en 15 meses con un pago mensual máximo de 12.000. Es posible que desee saber la tasa de interés a la que tiene que pagar.

Encuentre la tasa de interés con la función TASA de Excel -

Obtendrá el resultado como un 8%.

Cálculo del plazo del préstamo

Suponga que toma un préstamo de 100.000 a una tasa de interés del 10%. Quieres un pago mensual máximo de 15.000. Es posible que desee saber cuánto tiempo le llevará liquidar el préstamo.

Encuentre el número de pagos con la función Excel NPER

Obtendrá el resultado a los 12 meses.

Decisiones sobre inversiones

Cuando quieras realizar una inversión, comparas las diferentes opciones y eliges la que te rinda mejor. El valor actual neto es útil para comparar los flujos de efectivo durante un período de tiempo y decidir cuál es mejor. Los flujos de efectivo pueden ocurrir a intervalos regulares, periódicos o irregulares.

Primero, consideramos el caso de regular, periodical cash flows.

El valor actual neto de una secuencia de flujos de efectivo recibidos en diferentes momentos en el tiempo en n años a partir de ahora (n puede ser una fracción) es 1/(1 + r)n, donde r es la tasa de interés anual.

Considere las siguientes dos inversiones durante un período de 3 años.

A valor nominal, la Inversión 1 se ve mejor que la Inversión 2. Sin embargo, puede decidir qué inversión es mejor solo cuando conoce el verdadero valor de la inversión a partir de hoy. Puede utilizar la función NPV para calcular los rendimientos.

Los flujos de efectivo pueden ocurrir

  • Al final de cada año.
  • Al comienzo de cada año.
  • A mediados de cada año.

La función NPV supone que los flujos de efectivo se encuentran al final del año. Si los flujos de efectivo ocurren en diferentes momentos, entonces debe tener en cuenta ese factor en particular junto con el cálculo con VPN.

Suponga que los flujos de efectivo ocurren al final del año. Entonces ya puede utilizar la función NPV.

Obtendrá los siguientes resultados:

Como puede observar, el VPN de la Inversión 2 es mayor que el de la Inversión 1. Por lo tanto, la Inversión 2 es una mejor opción. Obtuvo este resultado porque los flujos de salida de efectivo para la Inversión 2 son en períodos posteriores en comparación con los de la Inversión 1.

Flujos de caja al comienzo del año

Suponga que los flujos de efectivo ocurren al comienzo de cada año. En tal caso, no debe incluir el primer flujo de efectivo en el cálculo del VPN, ya que representa el valor actual. Debe agregar el primer flujo de efectivo al VAN obtenido del resto de los flujos de efectivo para obtener el valor actual neto.

Obtendrá los siguientes resultados:

Flujos de caja a mitad de año

Suponga que los flujos de efectivo ocurren a mediados de cada año. En tal caso, necesita multiplicar el VPN obtenido de los flujos de efectivo por $ \ sqrt {1 + r} $ para obtener el valor actual neto.

Obtendrá los siguientes resultados:

Flujos de efectivo a intervalos irregulares

Si desea calcular el valor actual neto con flujos de efectivo irregulares, es decir, flujos de efectivo que ocurren en momentos aleatorios, el cálculo es un poco complejo.

Sin embargo, en Excel, puede hacer fácilmente dicho cálculo con la función XNPV.

  • Ordena tus datos con las fechas y los flujos de caja.

Note- La primera fecha de sus datos debe ser la más antigua de todas las fechas. Las otras fechas pueden aparecer en cualquier orden.

  • Utilice la función XNPV para calcular el valor actual neto.

Obtendrá los siguientes resultados:

Supongamos que la fecha de hoy es de 15 º de marzo de 2015. Como se observa, todas las fechas de los flujos de caja son de fechas posteriores. Si desea encontrar el valor actual neto al día de hoy, inclúyalo en los datos en la parte superior y especifique 0 para el flujo de caja.

Obtendrá los siguientes resultados:

Tasa interna de rendimiento (TIR)

La Tasa Interna de Retorno (TIR) ​​de una inversión es la tasa de interés a la que el VAN es 0. Es el valor de la tasa por la cual los valores presentes de los flujos de efectivo positivos compensan exactamente los negativos. Cuando la tasa de descuento es la TIR, la inversión es perfectamente indiferente, es decir, el inversor no gana ni pierde dinero.

Considere los siguientes flujos de efectivo, diferentes tasas de interés y los valores VAN correspondientes.

Como puede observarse entre los valores de la tasa de interés del 10% y el 11%, el signo del VPN cambia. Cuando ajusta la tasa de interés al 10,53%, el VAN es casi 0. Por lo tanto, la TIR es el 10,53%.

Determinación de la TIR de los flujos de efectivo de un proyecto

Puede calcular la TIR de los flujos de caja con la función de Excel TIR.

La TIR es del 10,53% como había visto en el apartado anterior.

Para los flujos de efectivo dados, la TIR puede:

  • existir y único
  • existen y múltiples
  • no existe

TIR única

Si la TIR existe y es única, se puede utilizar para elegir la mejor inversión entre varias posibilidades.

  • Si el primer flujo de caja es negativo, significa que el inversor tiene el dinero y quiere invertir. Entonces, cuanto mayor sea la TIR, mejor, ya que representa la tasa de interés que recibe el inversor.

  • Si el primer flujo de caja es positivo, significa que el inversor necesita dinero y está buscando un préstamo, cuanto menor sea la TIR, mejor, ya que representa la tasa de interés que paga el inversor.

Para saber si una TIR es única o no, varíe el valor estimado y calcule la TIR. Si la TIR permanece constante, entonces es única.

Como observa, la TIR tiene un valor único para los diferentes valores de estimación.

Múltiples TIR

En ciertos casos, es posible que tenga varias TIR. Considere los siguientes flujos de efectivo. Calcule la TIR con diferentes valores de estimación.

Obtendrá los siguientes resultados:

Puede observar que hay dos TIR: -9,59% y 216,09%. Puede verificar estas dos TIR calculando el VPN.

Tanto para -9,59% como para 216,09%, el VPN es 0.

Sin TIR

En ciertos casos, es posible que no tenga TIR. Considere los siguientes flujos de efectivo. Calcule la TIR con diferentes valores de estimación.

Obtendrá el resultado como #NUM para todos los valores de conjetura.

El resultado # NUM significa que no hay TIR para los flujos de efectivo considerados.

Patrones de flujo de efectivo y TIR

Si solo hay un cambio de signo en los flujos de efectivo, como de negativo a positivo o de positivo a negativo, se garantiza una TIR única. Por ejemplo, en inversiones de capital, el primer flujo de caja será negativo, mientras que el resto de los flujos de caja será positivo. En tales casos, existe una TIR única.

Si hay más de un cambio de signo en los flujos de efectivo, es posible que la TIR no exista. Incluso si existe, puede que no sea único.

Decisiones basadas en TIR

Muchos analistas prefieren usar la TIR y es una medida de rentabilidad popular porque, como porcentaje, es fácil de entender y fácil de comparar con el rendimiento requerido. Sin embargo, existen ciertos problemas al tomar decisiones con TIR. Si clasifica con TIR y toma decisiones basadas en estos rangos, puede terminar con decisiones equivocadas.

Ya ha visto que el VPN le permitirá tomar decisiones financieras. Sin embargo, la TIR y el VPN no siempre llevarán a la misma decisión cuando los proyectos se excluyen mutuamente.

Mutually exclusive projectsson aquellas para las que la selección de un proyecto excluye la aceptación de otro. Cuando los proyectos que se comparan son mutuamente excluyentes, puede surgir un conflicto de clasificación entre el VPN y la TIR. Si tiene que elegir entre el proyecto A y el proyecto B, el VPN puede sugerir la aceptación del proyecto A, mientras que la TIR puede sugerir el proyecto B.

Este tipo de conflicto entre el VPN y la TIR puede surgir debido a una de las siguientes razones:

  • Los proyectos son de tamaños muy diferentes, o
  • El momento de los flujos de efectivo es diferente.

Proyectos de gran diferencia de tamaño

Si desea tomar una decisión por TIR, el proyecto A produce un rendimiento de 100 y el proyecto B un rendimiento de 50. Por tanto, la inversión en el proyecto A parece rentable. Sin embargo, esta es una decisión incorrecta debido a la diferencia en la escala de los proyectos.

Considere -

  • Tienes 1000 para invertir.

  • Si invierte 1000 enteros en el proyecto A, obtiene un rendimiento de 100.

  • Si invierte 100 en el proyecto B, todavía tendrá 900 en su mano que puede invertir en otro proyecto, digamos el proyecto C. Suponga que obtiene un rendimiento del 20% en el proyecto C, luego el rendimiento total del proyecto B y el proyecto C es 230, que está muy por delante en rentabilidad.

Por lo tanto, el VPN es una mejor forma de tomar decisiones en tales casos.

Proyectos con diferentes tiempos de flujo de caja

Nuevamente, si considera la TIR para decidir, el proyecto B sería la elección. Sin embargo, el proyecto A tiene un VPN más alto y es una opción ideal.

TIR de los flujos de efectivo espaciados irregularmente (XIRR)

A veces, sus flujos de efectivo pueden estar espaciados irregularmente. En tal caso, no puede utilizar la TIR, ya que la TIR requiere intervalos de tiempo igualmente espaciados. En su lugar, puede utilizar XIRR, que tiene en cuenta las fechas de los flujos de efectivo junto con los flujos de efectivo.

La Tasa Interna de Retorno resultante es del 26,42%.

TIR modificada (MIRR)

Considere un caso en el que su tasa de financiación es diferente de su tasa de reinversión. Si calcula la Tasa Interna de Retorno con TIR, asume la misma tasa tanto para financiamiento como para reinversión. Además, también puede obtener múltiples TIR.

Por ejemplo, considere los flujos de efectivo que se indican a continuación:

Como observa, el VPN es 0 más de una vez, lo que da como resultado múltiples TIR. Además, no se tiene en cuenta la tasa de reinversión. En tales casos, puede utilizar la TIR modificada (MIRR).

Obtendrá un resultado del 7% como se muestra a continuación:

Note - A diferencia de la TIR, la MIRR siempre será única.