01 / 10Dos Proyectos Pasaron una Regla que No Cumplían

Funciones FinancierasCONTAR.SICalidad de Datos

Periodo de recuperación en Excel con flujos desiguales

El Consejo Aprobó 390.000 £ en Proyectos Energéticos con una Regla de Recuperación a Tres Años y Ambos Tardaron Cuatro o Más, Porque la Hoja Dividía el Coste entre el Ahorro Anual Medio

8 nov 202611 min de lectura

UsaCONTAR.SIINDICE COINCIDIRVAPROMEDIO

Brindlecombe Dairy aprueba cualquier proyecto energético que se pague solo en tres años. En marzo el consejo aprobó con esa regla una bomba de calor y una instalación solar. Las dos tardaron cuatro años o más, y un compresor que la hoja rechazó se recuperaba en dos.

ProyectoCosteLa hoja decíaTardó en realidad
Bomba de calor150.000 £2,81 años4,13 años
Instalación solar240.000 £3,00 años4,00 años
Compresor45.000 £3,38 años2,00 años

Alcance. CONTAR.SI, INDICE, PROMEDIO, VA, SI, SUMA y BUSCAR funcionan igual en todas las versiones de Excel desde 2007, en Windows, Mac y la web.

La BUSCARX de la sección 6 necesita Excel 2021 o Microsoft 365, y tiene al lado una versión con BUSCAR.

La lista de proyectos está en A1:K6: coste en B y ahorros de los años 1 a 6 de C a H. Las secciones 3 a 7 colocan los flujos de un proyecto en una columna de una segunda hoja.

01Dos Proyectos Pasaron una Regla que No Cumplían

La hoja de inversiones de Brindlecombe recoge el coste de cada proyecto y el ahorro que el proveedor previó para cada uno de seis años. La columna de recuperación era una sola fórmula, copiada hacia abajo:

=B2/PROMEDIO(C2:H2)

Cinco Proyectos Energéticos y Dos Formas de Medir la Recuperación

Los cinco proyectos energéticos de Brindlecombe Dairy en A1:K6. La columna B es el coste y de la C a la H el ahorro previsto para cada uno de seis años. La columna I es la recuperación que daba la hoja, el coste entre el ahorro medio. La J es la recuperación según el flujo acumulado, y la K la compara con la regla de tres años.

ABCDEFGHIJK
1
Project
Cost
Year 1
Year 2
Year 3
Year 4
Year 5
Year 6
Cost ÷ average
Payback (years)
Within 3 years?
2
LED lighting
60000
24000
24000
24000
24000
24000
24000
2.5
2.5
TRUE
3
Heat pump
150000
20000
30000
40000
50000
80000
100000
2.81
4.13
FALSE
4
Solar array
240000
30000
50000
70000
90000
110000
130000
3
4
FALSE
5
Compressor
45000
25000
20000
15000
10000
5000
5000
3.38
2
TRUE
6
Insulation
36000
15000
15000
15000
15000
15000
15000
2.4
2.4
TRUE

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

Para la iluminación LED y el aislamiento da la respuesta correcta: 2,50 y 2,40 años. Para la bomba de calor dice 2,81 años, y para la instalación solar justo 3,00. Las dos superaron el listón de tres años y se aprobaron 390.000 £.

La columna J sigue el dinero año a año. A la bomba de calor aún le faltaban 10.000 £ al final del año 4, y la solar solo alcanzó el equilibrio el último día del año 4. Ninguna se acercaba a tres años.

Escenario: Escribe los cinco proyectos en A1:H6. Escribe =B2/PROMEDIO(C2:H2) en I2, cópiala hasta I6 y comprueba que I3 da 2,81. Después escribe =SUMA(C3:E3) y comprueba que los tres primeros años de la bomba de calor solo suman 90.000 £.

Pruébalo en la cuadrícula

02Coste ÷ Promedio Supone que Todos los Años Son Iguales

Dividir el coste entre el ahorro medio da la recuperación solo cuando todos los años ahorran lo mismo. La iluminación LED ahorra 24.000 £ cada año, así que el promedio es la cifra de cada año y 60.000 ÷ 24.000 es la verdad.

Los ahorros de la bomba de calor crecen a medida que se retiran las calderas viejas: 20.000 £ el año 1 y 100.000 £ el año 6.

El promedio de 53.333 £ sube por los años 5 y 6 y luego se reparte entre los años 1 a 3, como si ese dinero hubiera llegado antes.

AñoAhorro de la bombaPromedio usado
120.00053.333
230.00053.333
340.00053.333
450.00053.333
580.00053.333
6100.00053.333

El mismo atajo falla al revés con el compresor. Sus ahorros bajan en cuanto se arreglan las peores fugas de aire, de 25.000 £ a 5.000 £.

El promedio de 13.333 £ esconde dos primeros años que devuelven los 45.000 £ completos, así que una recuperación de dos años se presentó como 3,38 y se rechazó.

El promedio también depende de cuántos años incluyas. Añade a la bomba de calor un séptimo año de 100.000 £ y el atajo baja a 2,50 años, aunque los cuatro primeros años no han cambiado.

Escenario: Escribe =B5/PROMEDIO(C5:H5) para el compresor y comprueba que da 3,38. Después escribe =SUMA(C5:D5) y comprueba que sus dos primeros años ya suman 45.000 £, el coste completo.

Pruébalo en la cuadrícula

03Monta una Columna de Flujo Acumulado

La recuperación es el momento en que el total acumulado de caja deja de ser negativo. Así que monta primero ese total acumulado, una fila por año. En una segunda hoja, coloca la bomba de calor hacia abajo:

Año (A)Flujo (B)Acumulado (C)
0-150.000-150.000
120.000-130.000
230.000-100.000
340.000-60.000
450.000-10.000
580.00070.000
6100.000170.000

El coste entra en negativo en el año 0. El acumulado empieza con =B2 en C2, y cada fila de abajo suma su propio año al total de arriba:

=C2+B3

Cópiala hasta C8. El signo cambia entre el año 4 y el año 5, así que la bomba de calor se recupera en algún punto del año 5, lejos del año 3.

Escenario: En una hoja nueva, escribe los años 0 a 6 en A2:A8 y los flujos de la bomba de calor en B2:B8, con -150000 en B2. Escribe =B2 en C2 y =C2+B3 en C3, cópiala hasta C8 y comprueba que C6 da -10.000.

Pruébalo en la cuadrícula

04Cuenta los Años Completos con CONTAR.SI

Cada año que termina con el acumulado todavía en negativo es un año completo antes de la recuperación. CONTAR.SI los cuenta en una sola celda:

=CONTAR.SI(C3:C8;"<0")

Empieza el rango en C3, el año 1, para no contar la inversión del año 0. En la bomba de calor devuelve 4, porque los años 1 a 4 terminan en negativo. Ponlo en H2.

El cero no es negativo. El acumulado de la instalación solar es exactamente 0 al final del año 4, así que CONTAR.SI devuelve 3 y el paso siguiente suma el año 4 entero.

Esa es la respuesta correcta: el dinero ha vuelto el último día del año 4.

Escenario: Escribe =CONTAR.SI(C3:C8;"<0") en H2 y comprueba que da 4. Después cambia B6 a 60000 y verás que baja a 3, porque el año 4 termina ahora justo en 0.

Pruébalo en la cuadrícula

05Suma la Fracción de Año con INDICE

La última pieza es cuánto del año siguiente tarda la caja en ponerse al día. Divide lo que aún se debía entre el flujo de ese año:

=H2-INDICE(C2:C8;H2+1)/INDICE(B2:B8;H2+2)

INDICE(C2:C8;H2+1) es el último acumulado negativo, -10.000 al final del año 4, porque C2 es el año 0. INDICE(B2:B8;H2+2) es la caja del año 5, 80.000. Restar un negativo lo suma: 4 + 10.000 ÷ 80.000 es 4,125.

ProyectoAños completosPendienteAño siguienteRecuperación
Iluminación LED212.00024.0002,50
Bomba de calor410.00080.0004,13
Instalación solar390.00090.0004,00
Compresor120.00020.0002,00
Aislamiento26.00015.0002,40

La fracción supone que la caja llega de forma uniforme durante el año. Es la convención habitual, y basta para una regla de tres años. Dilo en el informe al consejo, para que nadie lea 4,13 como una fecha.

Si un proyecto no se recupera en los años de la lista, CONTAR.SI devuelve 6 e INDICE se sale de B8 con #¡REF!. Evítalo con SI:

=SI(H2=6;"Más de 6 años";H2-INDICE(C2:C8;H2+1)/INDICE(B2:B8;H2+2))

Escenario: Escribe =H2-INDICE(C2:C8;H2+1)/INDICE(B2:B8;H2+2) en H3 y comprueba que da 4,125. Dale formato con dos decimales y compáralo con el 2,81 que vio el consejo.

Pruébalo en la cuadrícula

06Cuando el Acumulado Vuelve a Ser Negativo

CONTAR.SI supone que, una vez positivo, el acumulado se queda así. Un coste grande más adelante rompe esa idea. La batería de Brindlecombe cuesta 100.000 £, ahorra 40.000 £ al año y necesita 100.000 £ de celdas nuevas en el año 4: un neto de -60.000 ese año.

AñoFlujoAcumulado
0-100.000-100.000
140.000-60.000
240.000-20.000
340.00020.000
4-60.000-40.000
540.0000
640.00040.000

CONTAR.SI encuentra tres años negativos, el 1, el 2 y el 4, y la fórmula de la sección 5 devuelve 3,33. Esa cifra no significa nada: el dinero volvió por primera vez a los 2,50 años y se recuperó del todo a los 5,00.

Usa el último año negativo en lugar del recuento. En Microsoft 365, BUSCARX busca de abajo arriba con un modo de búsqueda de -1:

=BUSCARX(VERDADERO;C3:C8<0;A3:A8;0;0;-1)

Devuelve 4. Ponlo en H2 en lugar del recuento y la fórmula de la sección 5 devuelve 5,00. En versiones anteriores, =BUSCAR(2;1/(C3:C8<0);A3:A8) devuelve el mismo último año.

Los dos números sirven de control. Si CONTAR.SI y el último año negativo no coinciden, el acumulado volvió a bajar de cero, y el informe al consejo debe dar los dos cruces.

Escenario: Escribe los flujos de la batería en B2:B8 y deja el acumulado en C2:C8. Comprueba que =CONTAR.SI(C3:C8;"<0") devuelve 3 y que =BUSCARX(VERDADERO;C3:C8<0;A3:A8;0;0;-1) devuelve 4.

Pruébalo en la cuadrícula

07Recuperación Descontada con VA

La recuperación simple trata 80.000 £ del año 5 como si valieran lo mismo que 80.000 £ hoy. La recuperación descontada reduce primero la caja de cada año, al coste de capital de la empresa, y después repite los mismos pasos.

Pon la tasa en H1, un 8 % en Brindlecombe, y descuenta cada año en la columna D:

=VA($H$1;A2;0;-B2)

VA devuelve lo que vale hoy una cantidad única del año A2. El año 0 se queda en -150.000, y los 80.000 del año 5 pasan a 54.446,66. Monta el acumulado en la columna E igual que en la sección 3.

AñoDescontadoAcumulado
0-150.000,00-150.000,00
118.518,52-131.481,48
225.720,16-105.761,32
331.753,29-74.008,03
436.751,49-37.256,53
554.446,6617.190,12

Aplica la fórmula de la sección 5 a las columnas D y E, con el recuento de años descontados negativos en H4:

=H4-INDICE(E2:E8;H4+1)/INDICE(D2:D8;H4+2)

La bomba de calor se recupera en 4,68 años descontados, frente a los 2,81 de la hoja que vio el consejo.

Mientras los ahorros sean positivos, descontar solo retrasa la recuperación, así que un proyecto que no pasa la recuperación simple tampoco pasa esta.

Escenario: Escribe 8 % en H1 y =VA($H$1;A2;0;-B2) en D2, copiada hasta D8. Monta el acumulado en E2:E8, escribe =CONTAR.SI(E3:E8;"<0") en H4 y comprueba que la fórmula de arriba da 4,68.

Pruébalo en la cuadrícula

08Cinco Trampas

  1. Coste entre ahorro medio. Solo acierta cuando todos los años son iguales. Los ahorros que crecen parecen más rápidos de lo que son, y los que bajan, más lentos.
  2. Contar el año 0. =CONTAR.SI(C2:C8;"<0") incluye la inversión y suma un año a cada recuperación. Empieza el rango en el año 1.
  3. Redondear a años enteros. Llamar "año 5" a la bomba de calor y "año 3" a la iluminación LED pone un 2,50 bajo la misma etiqueta que un 2,99.
  4. Un acumulado que vuelve a bajar de cero. CONTAR.SI mezcla años negativos tempranos y tardíos. Usa el último año negativo e informa de los dos cruces.
  5. Recuperar no es ganar. La regla ignora todo lo que pasa después del cruce. El compresor se recupera antes que nadie y ahorra menos a partir del año 2, así que ordena los proyectos por VAN.

09Cuatro Ejercicios

  1. Reprodúcelo. Escribe los cinco proyectos y confirma que coste ÷ promedio deja la bomba de calor por debajo de tres años.
  2. Corrígelo. Monta la columna acumulada de la bomba de calor y confirma que CONTAR.SI e INDICE dan 4,13.
  3. Rómpelo. Escribe la batería y confirma que CONTAR.SI da 3,33 mientras que el último año negativo da 5,00.
  4. Descuéntalo. Añade VA al 8 % y confirma que la recuperación descontada de la bomba de calor es de 4,68 años.

Qué Llevarse

Cada ahorro de la hoja de Brindlecombe era la cifra del propio proveedor. El error estaba en el promedio, que movió los ahorros tardíos a los primeros años, justo donde mira una regla de tres años.

La recuperación es una pregunta sobre el momento, así que respóndela con un total acumulado. Cuenta los años negativos, suma la fracción de año, comprueba que el acumulado no vuelve a bajar y descuéntalo antes de que nadie firme.

Comparte este artículo:
Volver al Blog

Practícalo

CONTAR.SI

Un ejercicio en una cuadrícula real

Abrir el ejercicio

Las funciones que usa este artículo

Sintaxis, ejemplos resueltos y los errores que esperar: una página de referencia para cada una.

Sigue leyendo

  1. MAX.SI.CONJUNTO y MIN.SI.CONJUNTO: por qué devuelven 0Leer el artículo
  2. Combinar consultas en Power Query: filas duplicadasLeer el artículo
  3. Contar valores únicos en Excel con UNICOS y CONTAR.SILeer el artículo
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy