Payback y Payback Descontado en una sola fórmula con BYROW

Henry necesitaba calcular el Payback y el Payback Descontado de una inversión, y tenía dos fórmulas separadas con LET + SCAN que funcionaban bien por separado. El reto: combinarlas en una sola fórmula que devolviera ambos resultados a la vez.

Su fórmula de Payback:

``
=LET(
FC; C5:H5;
FC_ACUMULADO; SCAN(0; FC; SUMA);
AÑO_COMPLETO; COINCIDIRX(0; FC_ACUMULADO; 1) - 1;
AÑO_INICIAL; INDICE(FC_ACUMULADO; AÑO_COMPLETO);
AÑO_FINAL; INDICE(FC_ACUMULADO; AÑO_COMPLETO + 1);
PAYBACK; AÑO_COMPLETO + AÑO_FINAL / (AÑO_INICIAL - AÑO_FINAL);
RESULTADO; CAMBIAR(VERDADERO;
INDICE(FC; 1) >= 0; 0;
ESNOD(PAYBACK); "NO PAYBACK";
PAYBACK);
RESULTADO)
`

Y su fórmula de Payback Descontado, que era prácticamente idéntica pero añadía el descuento de los flujos de caja con la tasa FDD:

`
=LET(
FC_ORIGINAL; C5:H5;
AÑOS; C4:H4;
FDD; J2;
FC; FC_ORIGINAL / (1 + FDD) ^ AÑOS;
FC_ACUMULADO; SCAN(0; FC; SUMA);
AÑO_COMPLETO; COINCIDIRX(0; FC_ACUMULADO; 1) - 1;
AÑO_INICIAL; INDICE(FC_ACUMULADO; AÑO_COMPLETO);
AÑO_FINAL; INDICE(FC_ACUMULADO; AÑO_COMPLETO + 1);
PAYBACK; AÑO_COMPLETO + AÑO_FINAL / (AÑO_INICIAL - AÑO_FINAL);
RESULTADO; CAMBIAR(VERDADERO;
INDICE(FC; 1) >= 0; 0;
ESNOD(PAYBACK); "NO PAYBACK";
PAYBACK);
RESULTADO)
`

La clave es que ambas fórmulas comparten toda la lógica de cálculo. La única diferencia es si se aplica descuento o no a los flujos de caja.

John propuso una solución elegante que devuelve ambos valores en una sola celda combinando APILARH y BYROW. La idea: construir una matriz de 2 filas (flujos originales y flujos descontados) y recorrerla con BYROW, aplicando la misma lógica de Payback a cada fila:

`
=APILARH(
{"Payback"; "Descontado"};
BYROW(
C5:H5 / (1 + J2 {0; 1}) ^ C4:H4;
LAMBDA(r;
LET(
s; SCAN(; r; SUMA);
SI(@r < 0;
BUSCARX(0; s; C4:H4 - s / r; "No Payback"; 1);
)
)
)
)
)
`

El truco está en {0; 1}: al multiplicar la tasa de descuento por 0 o por 1, la primera fila mantiene los flujos originales (Payback normal) y la segunda los descuenta (Payback Descontado). Todo en una sola fórmula.

Otro miembro de la comunidad aportó una versión alternativa más compacta para el Payback simple, usando SCAN y aritmética de arrays para localizar el punto de corte:

`
=LET(
_m; J158:T158;
_s; SCAN(0; _m; SUMA);
_c; SUMA((_s < 0) 1) + 1;
_ent; SUMA((_s < 0) * 1) - 1;
_fr; INDICE(_s;; _c) / INDICE(_m;; _c);
_ent + _fr)
``

Las reacciones de Henry lo dicen todo: "Dios mío, pedazo solución" y "funcionan a la perfección".

El problema: dos Paybacks en una sola fórmula

Henry necesitaba calcular el Payback (periodo de recuperación de una inversión) y el Payback Descontado (el mismo cálculo pero descontando los flujos de caja a una tasa). Tenía dos fórmulas separadas con LET + SCAN que funcionaban bien por su cuenta. El reto: combinarlas en una sola fórmula que devolviera ambos resultados a la vez.

Sus dos fórmulas eran casi idénticas. La lógica era la misma —acumular los flujos con SCAN, localizar el año en que el acumulado pasa a positivo y calcular la fracción del año— y la única diferencia era si los flujos se descontaban o no. Cuando dos cálculos comparten casi todo, es señal de que se pueden unificar.

La solución de John: APILARH + BYROW

John construye una matriz de dos filas (flujos originales y flujos descontados) y la recorre con BYROW, aplicando la misma lógica de Payback a cada una:

=APILARH(
    {"Payback"; "Descontado"};
    BYROW(
        C5:H5 / (1 + J2 * {0; 1}) ^ C4:H4;
        LAMBDA(r;
            LET(
                s; SCAN(; r; SUMA);
                SI(@r < 0;
                    BUSCARX(0; s; C4:H4 - s / r; "No Payback"; 1);
                )
            )
        )
    )
)

El truco está en {0; 1}: al multiplicar la tasa de descuento por 0 en la primera fila y por 1 en la segunda, la fila de arriba mantiene los flujos originales (Payback normal) y la de abajo los descuenta (Payback Descontado). Una única fórmula produce las dos métricas, y APILARH les pone su cabecera.

Una alternativa compacta para el Payback simple

Otro miembro aportó una versión más corta para el caso simple, usando SCAN y aritmética de arrays para localizar el punto de corte:

=LET(
    _m; J158:T158;
    _s; SCAN(0; _m; SUMA);
    _c; SUMA((_s < 0) * 1) + 1;
    _ent; SUMA((_s < 0) * 1) - 1;
    _fr; INDICE(_s;; _c) / INDICE(_m;; _c);
    _ent + _fr
)

Aquí SUMA((_s < 0) * 1) cuenta cuántos años el acumulado sigue en negativo (convirtiendo cada comparación en 1 o 0), y a partir de ahí calcula el año entero y la fracción que falta para recuperar la inversión.

Funciones clave

  • SCAN: genera el acumulado progresivo de los flujos de caja, base de cualquier cálculo de Payback.
  • BYROW: aplica la misma LAMBDA a cada fila de la matriz, permitiendo calcular el Payback normal y el descontado de una vez.
  • APILARH: junta cabeceras y resultados en un bloque limpio.
  • {0; 1}: la constante matricial que "enciende y apaga" el descuento para reutilizar la misma lógica en dos escenarios.
  • BUSCARX con búsqueda aproximada: localiza el punto donde el acumulado cruza el cero.

Conclusión

La reacción de Henry lo resume: "Dios mío, pedazo solución". La lección es aplicable mucho más allá de las finanzas: cuando dos fórmulas comparten casi toda su lógica y solo cambian en un parámetro, casi siempre puedes unificarlas metiendo ese parámetro en un array ({0; 1}) y recorriéndolo con BYROW. Menos duplicación, una sola celda que mantener. Este tipo de retos financieros con matrices dinámicas es habitual en la comunidad de Influexcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel