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
Buenas Jorge,no se si esta es la pagina indicada para mi pregunta, sino es asi me gustaria que me dijese en que pagina debo hacerla.
ResponderBorrarLa cuestion es que tengo una hoja de calculo bastante avanzada y me encuentro con el problema que las distintas formulas me dan hasta el 6º numero decimal y yo quisiera que solo me diese uno, le intento anidar al "SI" la funcion de "REDONDEAR" y me sale una formula circular. ¿Como podria hacerlo en la misma celda) sin tener que duplicarla. He leido sus comentarios y no encuentro forma de resolverlo.
Gracias
David
ResponderBorrarno sé como estás usando REDONDEAR para que genere un cálculo circular. Puedes mandarme el archivo para que vea donde está el problema.
Me he complicado solo con esto... pero lo que quiero saber:
ResponderBorrarTengo una base de datos... en el cual se actualiza constantemente, no es de empresa ni nada, lo que yo quiero hacer es buscar el 2do numero maximo de la tabla, ya que con la funcion buscar doy el dato "nombre" del objeto, hasta este momento solo he logrado el maximo y el minimo, pero son como 16 datos.
como quiero que solo ingresen datos y nada mas... esa parte la queria dejar mas "automatica"
grax de antemano
Mentiría si dijera que he entendido la consulta, pero mi sugerencia es que uses JERARQUIA para establecer el rango de cada valor y luego con alguna función de búsqueda ponerlo en la celda necesaria.
ResponderBorrarHola Jorge,
ResponderBorrarEl otro día una amiga me preguntó cómo podía obtener el nº de días de cada mes entre 2 fechas. Me acordé que tenías alguna nota sobre este punto y aquí encontré la fórmula que tienes en la columna B (Días) aportada por un lector.
Viéndola me di cuenta que se podía simplificar bastante. Realmente no haría falta incluir la función SI. Sería esto:
=MIN($B$2;FIN.MES(A10;0))-MAX(FIN.MES(A10;-1)+1;$B$1)+1
o incluso más simple:
=MIN($B$2;FIN.MES(A10;0))-MAX(A10;$B$1)+1
El otro cambio sería que en el formato condicional para ocultar habría que poner:
=$B10<=0 en lugar de =$B10=0 ya que ahora la fórmula da resultado negativo en lugar de 0 cuando está fuera del período buscado.
Un abrazo,
Sergio
como hago con formula de excel para calcular solo el interes entre: capital y cuota
ResponderBorrarExcel tiene funciones para calcular el pago (cuota fija), el interés de cada período y el principal de cada período: PAGO(), PAGOINT() y PAGOPRIN().
ResponderBorrarPara calcular el interés de un período dado usas, obviamente PAGOINT().
Buenos Dias, mi pregunta y no se si puedas resolvermela o orientarme a una web donde pueda darle solucion, es la siguiente: ¿tengo 1 BD en excel con valores de prestamos, pero necesito calcularle el interes dia a dia y sumarlos dado que varian los porcentajes en cada mes y no se como hacerlo en un listado de estos mismos? Eje prestamo $10000 interes dia 1-30 0.01% 1er mes, dia 1-30 0.02% 2do mes, dia 1-30 0.03 3er mes, no se si me hice entender pero te agredeceria me orientaras para darle solucion. Gracias.
ResponderBorrarBueno, fijate en la técnica que muestro en esta nota.
ResponderBorrarmuy buen artículo
ResponderBorrar