Cálculo de interés con Excel - versión mejorada

En la nota anterior sobre el tema de cálculo de intereses mostramos cómo construir una fórmula para esta tarea. El modelo que mostramos en la nota tiene un serio inconveniente: sólo sirve si las fechas del comienzo y fin del período caen en el mismo año. Por ejemplo, si la fecha de vencimiento del pago cae el 15/02/2008 y nuestro deudor pagó efectivamente la cuenta el 22/08/2009, la tabla de la nota anterior nos dará un resultado erróneo



Dado que varios lectores me han pedido que les envíe el archivo, y supongo que querrán usarlo para calcular intereses de morosos, mostraremos en esta nota cómo construir un real modelo de cálculo intereses.

Empezamos por diseñar nuestro modelo. Queremos que:

1 - Ingresado el monto adeudado, la fecha de vencimiento y la fecha de pago real, el modelo calcule los intereses por mora.

2- El modelo debe mostrar el detalle del cálculo por mes (estamos suponiendo que las tasas de interés cambian solo por meses).

3- El detalle del cálculo mostrar sólo los meses relevantes al cálculo. Es decir, sólo los meses del período del cálculo.

4 - Las tasas de interés mensuales deben ser definidas en forma dinámica, es decir, no serán ingresadas por el usuario sino definidas automáticamente por el modelo de acuerdo al período relevante (mes/año).

Nuestro primer paso será crear el "esqueleto" de nuestro modelo



Partiendo de la base que el número máximo de meses que puede haber en un cálculo sea 60 (5 años), el rango de la tabla del detalle será A10:A69 (sin incluir los encabezamientos)

Nuestro segundo paso será crear las fórmulas necesarias en las celdas B4, B5 y en la tabla del detalle del cálculo.

La fórmula de B4 es sencilla: =SUMA(intereses), donde "intereses" es el nombre del rango D10:D69.

La fórmula de B5 es obviamente =B3+B4.

Ahora nos ocuparemos de la tabla de cálculo de intereses. En A10 ponemos

=FECHA(AÑO(B1),MES(B1),1)

Esta fórmula determina cuál es el primer día del mes de la fecha de vencimiento. Usamos formato personalizado para que se vea en pantalla como Mes-Año. A partir de esta celda calculamos los 59 meses subsiguientes, con la fórmula

=EDATE(A10,1)

Para usar la función EDATE (o FECHA:MES, según las definiciones del sistema) tiene que estar instalado el complemento Analysis ToolPak. FECHA.MES calcula el número de serie que representa la fecha que indica el número de meses anteriores o posteriores a la fecha especificada (argumento fecha_inicial). Copiamos esta fórmula en todo el rango A11:A69.

Para calcular la cantidad de días en cada mes de acuerdo a las fechas de inicio y fin del período de cálculo de intereses no podemos usar la fórmula que desarrollamos en la nota anterior. En su lugar usaremos esta otra fórmula desarrollada a partir de una sugerida por un compañero de trabajo de uno de mis lectores (y como no sé su nombre no puedo darle el crédito)

=SI((MIN($B$2,EOMONTH(A10,0))-MAX(EOMONTH(A10,-1)+1,$B$1))+1>0,MIN($B$2,EOMONTH(A10,0))-MAX(EOMONTH(A10,-1)+1,$B$1)+1,0)

Esta fórmula usa las funciones MIN y MAX para determinar si las fechas de vencimiento ($B$1) y la de pago ($B$2) caen dentro del mes de la fila. En caso afirmativo calcula cuantos días caen dentro del mes considerado, En caso negativo el resultado es 0.
Para evitar el uso de columnas auxiliares usamos la función FIN.MES (EOMONTH) para determinar el primer y el último día del mes de la fila en la tabla en forma dinámica.

Ahora tenemos que ocuparnos de la columna C en la tabla del detalle. Primero creamos una hoja adiciones en la cual ponemos una lista de las tasa de interés por mes y año



Esta tabla nos sirve como argumento en la fórmula que ponemos en las celdas de la columna C de la tabla

=BUSCARV(A10,tabla_de_interes,2,0)

La fórmula en la columna D de la tabla es obvia: =$B$3*(C10/365*B10)

Antes de seguir adelante probamos nuestras fórmulas:



Vemos con satisfacción que las fórmulas funcionan correctamente. El primer mes en la tabla del detalle (A10) es diciembre de 2007, la cantidad de días de cada mes ha sido calculada correctamente y la tasa de interés corresponde a los valores de la tabla de tasas.

Sólo nos queda por resolver cómo ocultar las filas no relevantes al cálculo. Esto lo hacemos con facilidad usando Formato Condicional. De hecho, queremos ocultar una fila en la tabla si en la cantidad de días en la fila es 0. Seleccionamos todo el rango de la tabla (A10:D69) y aplicamos formato condicional con esta fórmula



con estas definiciones para fuente



y estas para los bordes



Ahora nuestro modelo cumple todos los requisitos.




Technorati Tags: MS Excel

Manejo de escenarios con Excel

Uno de los usos frecuentes de Excel es crear modelos de predicción de resultados o presupuestos según van cambiando ciertas variables. En ingles existe el término "what if" para describir este tipo de análisis. En el proceso de escribir esta nota he buscado un equivalente en castellano a esta expresión. La ayuda en línea de Excel traduce el término a "y si", que por algún motivo me parece menos acertado que su equivalente literal en inglés. Tal vez sería mas expresivo usar "qué pasaría si", pero supongo que a los traductores les debe haber parecido excesivamente largo.
Mi inclinación personal es usar la expresión "análisis de escenarios" y precisamente Escenarios es el nombre de una las herramientas que Excel pone a disposición de los usuarios.

Según mi experiencia personal, esta herramienta es poco conocida y aún menos usada a pesar que puede ahorrarnos tiempo y errores.

De acuerdo a la ayuda de Excel "Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir automáticamente en la hoja de cálculo"



La funcionalidad se encuentra en el menú Herramientas, junto con Buscar Objetivo y Solver. Esto no es casualidad ya que estas herramientas también permiten realizar análisis de sensibilidad y de escenarios. Bajo el menú Datos se encuentra otra herramienta para realizar análisis de sensibilidad, las Tablas.

Antes de usar la herramienta Escenarios necesitamos construir un modelo cuyos resultados sean sensibles al cambio en una serie de variables. Esta herramienta acepta hasta 32 variables.

Veamos un uso posible de Escenarios con un ejemplo. Supongamos una empresa cuyos ingresos son en euros (todas sus ventas son en Europa), los materiales son adquiridos en dólares y el resto de los ingresos y gastos son en pesos. El cuadro de pérdidas y ganancias de semejante empresa será afectado por los cambios en las cotizaciones de las monedas, aún si no hay cambios en la actividad real de la empresa.

Queremos investigar cuáles serán los resultados del año para este empresa de acuerdo a distintos escenarios para las tasa de cambio. El modelo que construimos será éste:



En las celdas B2 y B3 tenemos las tasas de cambio actuales del euro y el dólar. Estas celdas serán las variables en nuestro modelo.
En el cuadro de pérdidas y ganancias proyectado tenemos los montos en la moneda original en la columna C y los montos convertidos a pesos en la columna D.

Con las tasas de cambio actuales, predecimos un porcentaje de beneficio bruto del 42%. ¿Que pasaría si esta tasa cambiaran? Supongamos tres posibilidades (escenarios) posibles:

1 - euro = 4.50; dólar = 3.30
2 - euro = 4.10; dólar = 3.10
3 - euro = 4.10; dólar = 3.30

Para crear los escenarios con la herramientas abrimos el menú Herramientas-Escenarios



Apretamos el botón Crear e ingresamos los datos. Al primer escenario lo llamaremos Devaluación, al segundo Revaluación y al tercero Mixto.



Al apretar el botón Aceptar se abre este formulario donde ingresamos los valores de las variables correspondientes al escenario



Apretamos aceptar y en el formulario que se abre volvemos a apretar Agregar para insertar el próximo escenario (Devaluación). Volvemos a realizar el proceso para agregar Mixto.



Cuando terminamos de ingresar los datos de todos los escenarios podemos apretar el botón Mostrar. Esto hará que los resultados en el modelo cambien de acuerdo al escenario elegido. Por ejemplo, si elegimos el escenario Revaluación y apretamos Mostrar, veremos en la hoja que el porcentaje de la ganancia bruto cae al 39%. Así podemos elegir cada vez un escenario distinto y ver el resultado directamente en la hoja.
Hay que tener en cuenta que al cerrar el formulario, las celdas variables conservan el valor del último escenario elegido.
Pero la real utilidad de usar Escenarios se evidencia al apretar el botón Resumen. Se abre este formulario



En la ventanilla Celdas de Resultado ponemos las celdas que queremos que aparezcan en el resumen. Si queremos que aparezca todo el total de ingresos, el total de gastos y el beneficio bruto, elegimos las celdas D10, D16 y D17



Al apretar Aceptar Excel crea una nueva hoja donde podemos ver un cuadro que compara los tres escenarios con la situación actual



Uno de los inconvenientes es que Excel usa la dirección de las celdas como nombre de la fila, lo que hace que la tabla sea prácticamente ilegible, es especial si se trata de un modelo con muchas líneas.
La técnica para superar este inconveniente es poner nombres a las celdas que estamos interesados en mostrar


Beneficio_bruto = Hoja1!$D$17
Dólar = Hoja1!$B$3
Euro = Hoja1!$B$2
Total_de_Gastos = Hoja1!$D$16
Total_de_Ingresos = Hoja1!$D$10

Volvemos a generar el resumen y obtenemos



Escenarios permite consolidar escenarios de distintas hojas, a condición que los modelos sean idénticos. Para combinar escenarios hay que apretar el botón Combinar que abre un formulario donde ingresamos los libros y las hojas que queremos combinar.
Como habrán notado, la hoja Resumen usa Agrupación y Esquema para ocultar ciertas filas. Si mostramos estas filas podemos ver el nombre del creador del escenario y la fecha en que fue creado



Dado que Excel crea una hoja cada vez que activamos la opción Resumen, podemos crear varias hojas que guarden distintos escenarios.
También podemos crear una tabla dinámica del escenario usando la opción Informe de tabla dinámica en el formulario de Resumen.

Technorati Tags: MS Excel

Calcular intereses por mes y días en Excel (o como construir una mega-fórmula)

Digamos que queremos calcular intereses por la demora en el pago de una cuenta o factura. Supongamos también que la tasa de interés cambia cada mes. Dados estos datos, lo que buscamos es desglosar los días transcurridos entre la fecha de vencimiento y la del pago efectivo por mes.

Lo que buscamos es crear una especie de calculadora en una hoja de Excel tal que si ingresamos el monto adeudado y las fechas, nos haga el cálculo del interés a pagar. Nuestra hoja se vería así (el archivo se puede descargar aquí)




Nuestro enfoque será resolver el modelo con fórmulas. Empecemos por señalar que tanto los datos en las celdas B2 y B3 como en el rango A6:A17 son fechas y no texto. Es decir, estamos trabajando con fechas que son números, como ya hemos explicado en el pasado.
Por ejemplo, si seleccionamos la celda A6, donde vemos la palabra "enero", veremos en la barra de fórmulas que en realidad es la fecha 01/01/2008



Para calcular los días por mes de la fecha de iniciación hasta la fecha de pago usamos una "mega-fórmula". Baste con ver la fórmula que usamos para calcular, dinámicamente, los días del mes de febrero que caen entre las fechas de principio y fin del intervalo para entender por qué la llamamos "mega"-fórmula

=SI(MES(A6)=MES($B$2),MES(A6)<=MES($B$3)),DIA(FIN.MES(A6,0)),0)-SI(MES($B$2)=MES(A6),$B$2-A6,0),SI(SI(MES($B$3)=MES(A6),$B$3-A6,0)<>0,SI(MES($B$3)=MES(A6),$B$3-A6,0)+1,0))

Para usar la función EOMONTH (o FIN.MES, de acuerdo a las definiciones del sistema) hay que tener instalado el complemento Analysis Toolpak.

La técnica que propongo para construir esta mega-fórmula consiste en crear las fórmulas parciales en columnas auxiliares y luego unirlas en una única fórmula.



La primer columna auxiliar es C donde calculamos la cantidad de días que caen dentro del mes de la fecha inicial con esta fórmula

=SI(MES($B$2)=MES(A6),$B$2-A6,0)

La función MES nos permite ver si el mes de la línea es el mes de la primer fecha. En caso afirmativo calculamos la diferencia de días entre la fecha de inicio (B2) y el primer día del mes (A6). En caso de coincidir el mes el resultado es 0.

En la columna D hacemos lo mismo pero en relación a la fecha de pago efectivo (B3), con la fórmula

=SI(MES($B$3)=MES(A6),$B$3-A6,0)

En la columna E calculamos los días de los meses que caen entre ambas fechas con la fórmula

=SI(Y(MES(A6)>=MES($B$2),MES(A6)<=MES($B$3)),DIA(EOMONTH(A6,0)),0)

La función EOMONTH (FIN.MES) nos permite calcular con exactitud los días de cada mes, también en años bisiestos.

En la columna F calculamos el total de días de cada mes con una resta sencilla

=E6-C6


A esta altura del partido tenemos calculados los todos los días por mes, excepto para el último mes del período. Para esto u Samos la columna auxiliar G con esta fórmula

=SI(D6<>0,D6+1,0)

Ahora podemos combinar todas estas fórmulas intermedias en una única mega-fórmula. Primero nos proponemos la fórmula en palabras, lo que nos ayuda a crear la lógica. Nos fijamos en las columnas auxiliares y decimos:

1 - si el mes de la fila no es el de la fecha de pago, calculamos los días restando la columna C de E (E-C);
2 - si el mes es el de la fecha de pago tomamos el resultado de la columna D y le sumamos 1.

En la columna H empezamos a crear la mega-fórmula

=SI(MES(A6)<MES($B$3),F6,G6)



Necesitamos que toda la fórmula esté expresada en términos de celdas en la columna A y B para lo cual empezaremos reemplazando F6 y G6 por sus equivalentes, las fórmulas que hemos puesto en las columnas auxiliares. Al reemplazar F6 y G6 nos queda

=SI(MES(A6)<MES($B$3),E6-C6,SI(D6<>0,D6+1,0))



Ahora tenemos que ocuparnos de E6, C6 y D6, que al reemplazarlas por las fórmulas de las columnas auxiliares nos dan la mega-fórmula que estamos buscando.

Es de notar que esta fórmula supone que las fechas de principio y final caen en el mismo año.

Para ver cómo construir un modelo real para el cálculo de interese por mora, consulten esta nota.

Quien esté interesado en recibir el archivo con el ejemplo y las fórmulas, debe dirigir el pedido a mi mail.



Technorati Tags: MS Excel