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.

4 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