Tabla de amortización con Excel

Varias veces en el pasado he recibido consultas sobre cómo construir una tabla de amortización de préstamos con Excel.

Aclaremos que hay varios sistemas de amortización, y aquí mostraré como construir la tabla de amortización con cuota fija. A quien quiera ver otras posibilidades le recomiendo la página Excel Avanzadode Adolfo Aparicio. Allí podrán descargar un archivo Excel (Prestamos.xls) con muchas variantes de tablas de amortización.

En esta nota veremos como construir un cuadro de amortización de prestamos que sea flexible en cuanto al tipo de plazo (mensual, trimestral, anual, etc.) y la cantidad de plazos. Nuestro modelo se divide en dos partes: el cuadro de datos





y la tabla de amortización



El cuadro de datos contiene los parámetros necesarios para el cálculo: monto/importe del préstamo, tasa de interés anual, período del pago en meses (1: mensual, 3: trimestral, 12: anual) y número de períodos. Así por ejemplo, en un préstamo a tres años con pagos trimestrales el período de pago será 3 y el número de períodos 12.

Las fórmulas en la tabla de amortización son las siguientes:

Pago (columna B): =PAGO($C$4*($C$5/12);$C$6;-$C$3).
La expresión ($C$4*($C$5/12) nos permite expresar la tasa de interés anual en términos del plazo de pago del préstamo.

Cuota del interés (columna C): =PAGOINT($C$4*($C$5/12);A11;$C$6;-$C$3)

Cuota del capital (columna D): =PAGOPRIN($C$4*($C$5/12);A11;$C$6;-$C$3)

Saldo (columna E): =E10-D11, es decir, resta el pago a cuenta del capital del saldo anterior.

Capital amortizado: =D11+F10, el complementario de la la columna anterior.

La tabla contiene 120 filas con fórmulas, es decir sirve para préstamos de hasta 10 años. Para hacer invisibles las filas que están fuera del rango del préstamo, usamos Formato Condicional a lo largo de todas las columnas del cuadro de amortización:



En la fórmula del formato condicional, =$A12>$C$6, hay que prestar atención a la dirección semi-absoluta del primer miembro.

Technorati Tags: MS Excel

Tablas dinámicas en lugar de Autofiltro.

Uno de mis lectores me pregunta si se puede aplicar autofiltro simultáneamente a varias hojas. En sus propias palabras:

¿se puede aplicar autofiltro a varias hojas de una sola vez? Tengo 25 hojas y
quiero filtrar en todas desde una fecha determinada. ej: desde ej 01/06/2007.

La respuesta es no, no se puede. Pero podemos usar tablas dinámicas para lograr efectos parecidos a los de Autofiltro. Para esto usaremos tablas dinámicas con la opción "rangos de consolidación múltiples".

Supongamos un cuaderno Excel (que se puede descargar aquí) con listas de datos compuestas de fechas e importes, en tres hojas. Nuestro objetivo en consolidar los datos en una tabla y mostrar los datos de determinadas fechas.

Construimos la tabla dinámica con rangos de consolidación múltiples








Agregamos las listas de las distintas hojas



Y ponemos la tablas en una hoja aparte (Reporte)



Obtenemos



Ahora empezamos a mejorar el resultado. Eliminamos el elemento Columna arrastrándolo fuera de la tabla y movemos el elemento Página1 a la zona de campos de columna



Reemplazamos los rótulos de la tabla por otros más significativos



Para filtrar los datos según fechas abrimos el menú de ítems del campo



Y seleccionamos los elementos deseados



Este método es muy efectivo si queremos seleccionar sólo una fecha o un rango reducido. Si tenemos muchos elementos (fechas) en nuestras listas el método puede ser muy trabajoso.

Technorati Tags: MS Excel

Presupuesto de viaje con Excel

Desde el 7 al 29 de setiembre estaré de viaje con mis hijas. Pasaremos nuestras vacaciones en la Argentina donde visitaremos Puerto Madryn para hacer el avistaje de ballenas en la península de Valdés, Bariloche y San Martín de los Andes, las Cataratas del Iguazú y por supuesto Buenos Aires, mi ciudad natal.

Cuento todo esto por dos motivos. El primero es que durante esas tres semanas el blog estará inactivo. El segundo es para sugerir cómo se puede construir con Excel un modelo sencillo pero efectivo para programar el presupuesto de un viaje (o de cualquier otra actividad).
Los "ingredientes" de nuestro "plato" serán: tablas dinámicas, validación de datos y rangos dinámicos definidos con nombres y la función DESREF.
También usaremos un evento para validar fechas.

La tabla dinámica funciona como un generador de reportes que nos permitirá agrupar los gastos según los distintos conceptos que hayamos definido (tipo de gastos como vuelos, alojamiento, excursiones, etc.; o gastos por tramos).

Usaremos Validación de Datos para generar listas desplegables en los distintos campos de la lista que será la base de la tabla dinámica.

Aquí pueden descargar el archivo con el ejemplo del presupuesto de viaje.

Empezamos por crear la lista, las base de datos que servirá a la tabla dinámica (en la hoja BD en nuestro ejemplo). Aquí definimos que campos queremos que contenga la tabla. En nuestro ejemplo definimos (empezando por la columna A): Tipo de Gasto, Fecha, Tramo, Detalle, Moneda, Suma, Peso, Dólar, Euro.

Agregamos las últimas cuatro columnas bajo la suposición que tendremos gastos en distintas moneda. En la columna Moneda pondremos la denominación de la moneda en la cual realizamos el gasto (peso, dólar o euro); en las últimas tres convertimos cada suma a su equivalente con fórmulas que mostraremos luego.

En esta hoja hemos agregado también un evento que abre un calendario cuando queremos introducir una fecha en alguna celda de la columna B. Esta técnica la hemos mostrado en la nota sobre validación de fechas en Excel.

Ahora creamos una hoja que llamamos "Parámetros" donde tenemos los valores de tipo de gastos que servirá a la lista desplegable de la validación de datos. Además tenemos un cuadro de cambio cruzado de monedas para las conversiones.



En esta hoja definimos los nombres:

cambio =parametros!$C$2:$F$5
db_range =DESREF(BD!$A$1;0;0;CONTARA(BD!$A:$A);CONTARA(BD!$1:$1))
monedaH=parametros!$C$2:$F$2
monedaV=parametros!$C$2:$C$5
Tipo_de_Gasto=DESREF(parametros!$A$3;0;0;CONTARA(parametros!$A:$A)-1;1)



Antes de crear la hoja con el reporte, introducimos algunos datos en la hoja BD:





Ahora ya podemos crear nuestro reporte, usando una tabla dinámica que ponemos en la hoja Reporte. Por ejemplo si queremos ver el presupuesto por tipo de gasto:



o por tramo



de acuerdo a los campos que arrastremos a la zona de campos de fila.

Technorati Tags: MS Excel