martes, 26 de abril de 2016

Cálculo del PayBack o del Periodo de Recuperación en Excel.

Aprenderemos hoy a obtener el PayBack o Periodo de recuperación de una inversión empleando nuestras hoja de cálculo.

Comenzaremos diciendo que desde luego no es el mejor indicador para medir la rentabilidad de una inversión, existen el VAN o la TIR como métodos más válidos, pero es sin duda un método sencillo y ágil, de fácil interpretación... y como mayor inconveniente, el PayBack no considera el paso del tiempo (y el efecto sobre los flujos de caja).
Lee un poco más en la Wikipedia.


Para explicar cómo realizar este cálculo partiremos del siguiente ejemplo:
Tenemos un desembolso inicial de 20.000 euros y unos flujos de caja (diferencia entre Cobros y Pagos) para los siguientes cinco años de:
5.000,00 € (-) 9.500,00 € (-) 5.000,00 € (-) 5.000,00 € (-) 10.000,00 €

En el rango B9:G9 calculamos el Flujo Acumulado para cada año (como la suma de los diferentes flujos acumulados hasta la fecha).

Cálculo del PayBack o del Periodo de Recuperación en Excel.


Por último vemos la fórmula necesaria para el cálculo:
𝑃ay𝐵ack =[Ú𝑙𝑡𝑖𝑚𝑜 𝑝𝑒𝑟𝑖𝑜𝑑𝑜 𝑐𝑜𝑛 𝐹𝑙𝑢𝑗𝑜𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜]+([𝑉𝑎𝑙𝑜𝑟 𝑎𝑏𝑠𝑜𝑙𝑢𝑡𝑜 𝑑𝑒𝑙 ú𝑙𝑡𝑖𝑚𝑜 𝐹𝑙𝑢𝑗𝑜 𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜]/[𝑉𝑎𝑙𝑜𝑟 𝑑𝑒𝑙 𝐹𝑙𝑢𝑗𝑜 𝑑𝑒 𝐶𝑎𝑗𝑎 𝑑𝑒𝑙 𝑠𝑖𝑔𝑢𝑖𝑒𝑛𝑡𝑒 𝑝𝑒𝑟𝑖𝑜𝑑𝑜])




Para el cálculo del 'Ú𝑙𝑡𝑖𝑚𝑜 𝑝𝑒𝑟𝑖𝑜𝑑𝑜 𝑐𝑜𝑛 𝐹𝑙𝑢𝑗𝑜𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜' empleamos la función CONTAR.SI. En la celda D14:
=CONTAR.SI(C9:G9;"<0")
También, más fiable:
=COINCIDIR(VERDADERO;INDICE($C$9:$G$9>0;1;0);0)-1
ejecutada matricialmente!!! (presionando Ctrl+Mayusc+Enter).

Lo que nos indica es el último año con Flujo Acumulado negativo, es decir, el anterior a la Recuperación.
Es más fiable, y recomendable, la segunda opción (empleando COINCIDIR...), ya que localizamos el primer ejercicio en que se genera un Flujo positivo.. mientras que la opción de CONTAR.SI únicamente sería valida cuando no hay fluctuaciones posteriores, y volviéramos a un Flujo Acumulado Negativo posterior.


El siguiente parámetro de nuestra fórmula '𝑉𝑎𝑙𝑜𝑟 𝑎𝑏𝑠𝑜𝑙𝑢𝑡𝑜 𝑑𝑒𝑙 ú𝑙𝑡𝑖𝑚𝑜 𝐹𝑙𝑢𝑗𝑜 𝐴𝑐𝑢𝑚𝑢𝑙𝑎𝑑𝑜 𝑛𝑒𝑔𝑎𝑡𝑖𝑣𝑜' lo calculamos en D15 con la fórmula INDICE siguiente:
=ABS(INDICE(C9:G9;1;D14))
que nos devuelve en VALOR ABSOLUTO (función ABS) el flujo Acumulado del año anterior a generarse la Recuperación, o dicho de otro modo, del último Flujo Acumulado Negativo.


El último parámetro '𝑉𝑎𝑙𝑜𝑟 𝑑𝑒𝑙 𝐹𝑙𝑢𝑗𝑜 𝑑𝑒 𝐶𝑎𝑗𝑎 𝑑𝑒𝑙 𝑠𝑖𝑔𝑢𝑖𝑒𝑛𝑡𝑒 𝑝𝑒𝑟𝑖𝑜𝑑𝑜' montamos la fórmula en D16:
=INDICE(C7:G7;1;D14+1)

para recuperar el flujo anual (NO los acumulados!!) correspondiente al año de la Recuperación de la Inversión.


Estamos listos para el cálculo final. Para nuestro ejemplo:
PayBack= 3 + (500 / 5000) = 3,10 años
El Plazo de recuperación de la inversión, según los flujos de tesorería definidos, es de 3,10 años.

23 comentarios:

  1. Gracias Ismael por tan buena explicación. Son pocos los post que se enfocan a comunicar tan bien este tipo de conceptos. un saludo

    ResponderEliminar
  2. ¿Y qué pasa si tengo flujos positivos desde un principio?

    ResponderEliminar
    Respuestas
    1. Hola Michelle,
      un placer saludarte igualmente.
      Este cálculo sirve para obtener el plazo de recuperación de una inversión.. si solo tienes flujos positivos NO hay recuperación
      ;-)

      Saludos

      Eliminar
  3. Muchas Gracias Ismael Romero por tu explicación, muy clara y entendible, Saludos y bendiciones

    ResponderEliminar
  4. Lo Ame! Gracias! que Dios te bendiga por compartir tu conocimiento

    ResponderEliminar
  5. Excelente aporte , muy útil.
    Saludos.

    ResponderEliminar
  6. Lo hace tan fácil que realmente nos damos cuenta que no hay monstruo de 7 cabezas. Gracias y felicitaciones.

    ResponderEliminar
    Respuestas
    1. ;-)
      Muchas gracias... no hay monstruos, confirmado... solo retos a superar
      Un saludo

      Eliminar
  7. Hola, muchas gracias por el ejemplo. Sin embargo, me queda una duda. ¿Por qué en la función coincidir terminas con un -1?

    ResponderEliminar
    Respuestas
    1. Hola,
      restar uno lo que nos indica es el último año con Flujo Acumulado negativo, es decir, el anterior a la Recuperación.

      Saludos

      Eliminar
    2. Muchas gracias por tu pronta respuesta, saludos.

      Eliminar
  8. ¿Como se montaría la formula INICE si la tabla la tenemos en el otro sentido? Es decir con los periodos uno debajo del otro (en filas) en lugar de columnas.

    ResponderEliminar
    Respuestas
    1. Hola, qué tal estás?
      un placer saludarte.

      solo tendrías que intercambiar los argumentos de INDICE de filas x columnas

      Saludos cordiales

      Eliminar
  9. Hola. Quisiera saber que modificación le debería hacer a la formula para que me muestre el resultado en años y meses E??? Es para una tarea.

    ResponderEliminar
    Respuestas
    1. Hola,
      todo depende de la periodicidad empleada...
      si está distribuido como el ejemplo, la parte entera son años completos y la parte decimal la multiplicas por 12....
      Por ejemplo 3,10 años equivale a 3 años y (0,1x12) 1,2 meses...

      Saludos

      Eliminar
  10. No debería ser el contar si <=0 ? osea de esta manera +contar.si(C9:G9;"<=0") , en caso de que se recupere totalmente en el último flujo?

    ResponderEliminar
    Respuestas
    1. Hola Juan Pablo,
      es una decisión personal, si quieres incluirlo o no...

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.