Tabla De Amortizacion En Excel 1ic3x

Monto del crédito: Tasa de interés (anual): Número de pagos (mensuales): Pago (mensual):

$150,000.00 12% 24 $7,061.02

# Pago 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

Pago Interés $1,500.00 $1,444.39 $1,388.22 $1,331.50 $1,274.20 $1,216.33 $1,157.89 $1,098.85 $1,039.23 $979.01 $918.19 $856.77 $794.72 $732.06 $668.77 $604.85 $540.29 $475.08 $409.22 $342.70 $275.52 $207.66 $139.13 $69.91

Pago Capital $5,561.02 $5,616.63 $5,672.80 $5,729.53 $5,786.82 $5,844.69 $5,903.14 $5,962.17 $6,021.79 $6,082.01 $6,142.83 $6,204.25 $6,266.30 $6,328.96 $6,392.25 $6,456.17 $6,520.73 $6,585.94 $6,651.80 $6,718.32 $6,785.50 $6,853.36 $6,921.89 $6,991.11

Saldo $144,438.98 $138,822.35 $133,149.55 $127,420.03 $121,633.20 $115,788.52 $109,885.38 $103,923.21 $97,901.42 $91,819.42 $85,676.59 $79,472.34 $73,206.04 $66,877.08 $60,484.83 $54,028.66 $47,507.92 $40,921.98 $34,270.18 $27,551.86 $20,766.36 $13,913.00 $6,991.11 $0.00

Monto del crédito: Tasa de interés (anual): Número de pagos (mensuales): Pago (mensual):

$150,000.00 12% 12 $13,327.32

Crear tabla

# Pago 1 2 3 4 5 6 7 8 9 10 11 12

Pago Interés Pago Capital $1,500.00 $11,827.32 $1,381.73 $11,945.59 $1,262.27 $12,065.05 $1,141.62 $12,185.70 $1,019.76 $12,307.55 $896.69 $12,430.63 $772.38 $12,554.94 $646.83 $12,680.49 $520.03 $12,807.29 $391.95 $12,935.36 $262.60 $13,064.72 $131.95 $13,195.36

Saldo $138,172.68 $126,227.09 $114,162.04 $101,976.34 $89,668.79 $77,238.16 $64,683.22 $52,002.74 $39,195.45 $26,260.08 $13,195.36 $0.00

Tabla de amortización en Exce

Amortizar significa extinguir gradualmente una deuda o un préstamo a través de pagos periód

Es muy probable que alguna vez hayas visto una tabla de amortización, especialmente si te

El asesor no hace los cálculos manualmente en el instante sino que utiliza un sistema compu

Variables para el cálculo

Para poder crear la tabla de amortización en Excel debemos tener al menos la siguiente in

Monto del crédito: Es indispensable conocer el monto del préstamo. Esta es la cantidad net Tasa de interés: No solo debemos cubrir el monto total del crédito sino también la tasa de in Número de pagos: Es necesario establecer el número de pagos que deseamos realizar para

Como regla general, entre mayor sea el número de pagos a realizar, menor será el monto de

Cálculo del monto de pago

Una vez que tenemos las variables previamente mencionadas podremos calcular el monto de cada uno de los p

Suponiendo que vamos a solicitar un crédito por un monto de $150,000 y que tenemos una ta =PAGO(1%,24,-150000)

La institución financiera nos proporcionó el dato de 12% de interés anual, pero para la funció

Para nuestro ejemplo ha quedado un pago de $7,061.02 que tendremos que hacer durante 2

Creación de la tabla de amortización La tabla de amortización en Excel será el desglose de cada uno de los pagos mensuales para conocer el monto

Utilizando nuestro ejemplo de préstamo, calcularemos el interés a pagar en el primer período =PAGOINT(1%,1,24,-150000)

Compara esta fórmula con la función PAGO de la sección anterior y verás que la única difere

Observa que la fórmula de la celda E2 hace referencia a las variables de la columna B y las he colocado como re

Por el contrario, para obtener el monto que se abona mes a mes a nuestra deuda, debemos utilizar la función P =PAGOPRIN(1%,1,24,-150000)

De esta manera calcularemos el monto de nuestro pago mensual que estará destinado al pag

Si revisas con detenimiento verás que la suma del pago de interés y pago a capital para todo

Para finalizar nuestra tabla de amortización podemos agregar algunas columnas adicionales,

El saldo es el monto del crédito menos la suma de todos los pagos a capital realizados hasta

Como tal vez ya lo imaginas, si queremos cambiar nuestra tabla de amortización para tener 3

Macro para tabla de amortización en Excel Lo único que necesita hacer nuestra macro es leer los valores de la columna B e insertar las 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Private Sub CommandButton1_Click() 'Limpiar el contenido de las celdas antes de iniciar Range("D2:G1200").ClearContents 'Obtener el número de pagos de la celda B3 num_pagos = Range("B3").Value

'Para cada fila de pago insertar las fórmulas correspondientes For i = 1 To num_pagos Cells(i + 1, 4).Value = i Cells(i + 1, 5).Formula = "=IPMT($B$2/12,D" & i + 1 & ",$B$3,-$B$1) Cells(i + 1, 6).Formula = "=PPMT($B$2/12,D" & i + 1 & ",$B$3,-$B$1) Cells(i + 1, 7).Formula = "=$B$1-SUM($F$2:F" & i + 1 & ")" Next i End Sub

Las líneas 12, 13 y 14 son las encargadas de insertar las fórmulas que harán los cálculos y e

Con esto hemos terminado el desarrollo de una tabla de amortización en Excel que será funcional para conoce

ón en Excel

stamo a través de pagos periódicos. El objetivo de una tabla de amortización es especificar el detalle de cada uno

ortización, especialmente si te has acercado a una institución bancaria para solicitar un crédito de auto o un crédito

o que utiliza un sistema computacional desarrollado para ese fin. Nosotros también podemos automatizar este tipo

s tener al menos la siguiente información:

éstamo. Esta es la cantidad neta otorgada por la institución financiera al aprobarnos un crédito. édito sino también la tasa de interés cobrada por la institución financiera ya que es la manera como obtienen ganan gos que deseamos realizar para cubrir nuestra deuda. Es una práctica muy común establecer una cantidad de pago

alizar, menor será el monto de cada uno de los pagos mensuales, pero el interés a pagar será mucho mayor. Si est

alcular el monto de cada uno de los pagos mensuales utilizando la función PAGO de Excel. Esta función tiene tres argumentos obligatorios

$150,000 y que tenemos una tasa de interés anual del 12% y queremos realizar 24 pagos mensuales. La fórmula q

erés anual, pero para la función PAGO necesita utilizar la tasa de interés para cada período, que en este caso es m

endremos que hacer durante 24 meses para saldar nuestra deuda.

s mensuales para conocer el monto exacto destinado tanto al pago de intereses como al pago del capital de nuestra deuda. El cálculo de

és a pagar en el primer período utilizando una fórmula como la siguiente:

erior y verás que la única diferencia es que el segundo argumento indica el período que deseamos calcular, que en

columna B y las he colocado como referencias absolutas porque deseo que dichas referencias permanezcan fijas al momento de copiar la

a deuda, debemos utilizar la función PAGOPRIN de Excel. La sintaxis de esta función será prácticamente idéntica a la de la función PAGOIN

ual que estará destinado al pago de capital de nuestra deuda. De igual manera, el segundo argumento de la funció

erés y pago a capital para todos los períodos nos da el total obtenido con la función PAGO. De esta manera podem

algunas columnas adicionales, por ejemplo el saldo en cada uno de los períodos:

agos a capital realizados hasta el momento. El saldo se va reduciendo con cada pago aunque no es una reducción

la de amortización para tener 36 pagos mensuales será necesario agregar manualmente los nuevos registros y cop

de la columna B e insertar las fórmulas correspondientes en cada fila de acuerdo al número de pagos a realizar. Pa

s correspondientes

,D" & i + 1 & ",$B$3,-$B$1)" ,D" & i + 1 & ",$B$3,-$B$1)" 2:F" & i + 1 & ")"

ulas que harán los cálculos y en VBA debemos utilizar el nombre de las funciones en inglés o de lo contrario obtend

Excel que será funcional para conocer el detalle de los pagos necesarios para liquidar una deuda. Puedes descargar el libro de trabajo el cu

pecificar el detalle de cada uno de los pagos hasta la liquidación total del préstamo.

r un crédito de auto o un crédito hipotecario. Generalmente el asesor del banco te preguntará el monto y la duración

podemos automatizar este tipo de tareas al crear una tabla de amortización en Excel y de esa manera conocer fá

un crédito. manera como obtienen ganancias por la prestación de dicho servicio. Generalmente encontraremos especificada la tablecer una cantidad de pagos mensuales (en bloques anuales): 12, 24, 36, 48, etc.

agar será mucho mayor. Si esta aseveración no te queda muy clara, seguramente lo estará una vez que hayamos c

ón tiene tres argumentos obligatorios y que son precisamente nuestras variables: Tasa de interés para cada período, número total de pago

pagos mensuales. La fórmula que debemos utilizar para calcular el pago mensual será similar a la siguiente:

período, que en este caso es mensual, así que debo hacer la división entre 12 para obtener el resultado de 1% de in

pital de nuestra deuda. El cálculo de pago de intereses lo haremos con la función PAGOINT de Excel. Esta función utilizará los mismos argu

ue deseamos calcular, que en este caso es el primer período. Para obtener el interés a pagar en cada uno de los 24

nezcan fijas al momento de copiar la fórmula hacia abajo. El segundo argumento de la función PAGOINT hace referencia a la columna D q

te idéntica a la de la función PAGOINT. Considera la siguiente fórmula que nos ayuda a obtener el pago a capital para el primer período:

egundo argumento de la función indica el número de período para el cual estamos haciendo el cálculo. Observa el r

PAGO. De esta manera podemos deducir que estas tres funciones son complementarias: La suma del resultado de

o aunque no es una reducción constante ya que al inicio pagamos más interés que al final pero en el último pago lle

ente los nuevos registros y copiar las fórmulas hacia abajo. Es por eso que una mejor solución para crear una tabla

número de pagos a realizar. Para ello agregaré un botón de comando ActiveX en la hoja y colocaré el siguiente cód

n inglés o de lo contrario obtendremos un error #¿NOMBRE? en nuestra hoja de Excel. Al momento de pulsar el bot

des descargar el libro de trabajo el cual contiene dos hojas, en la primera encontrarás la solución que tiene solo las fórmulas y en la segun

eguntará el monto y la duración deseada del crédito y de inmediato te mostrará una tabla con el desglose de los pag

el y de esa manera conocer fácil y rápidamente la cantidad de pagos a realizar y así como los montos exactos dest

e encontraremos especificada la tasa de interés de forma anual.

estará una vez que hayamos creado nuestra tabla de amortización en Excel y podamos analizar diversos escen

a cada período, número total de pagos, y monto del crédito.

á similar a la siguiente:

obtener el resultado de 1% de interés mensual. El segundo argumento de la función es el número de mensualidades

Esta función utilizará los mismos argumentos que la función PAGO pero agregará un cuarto argumento para indicar el número de período

s a pagar en cada uno de los 24 pagos podemos implementar una tabla como la siguiente:

INT hace referencia a la columna D que es precisamente donde se encuentra el número de pago correspondiente.

go a capital para el primer período:

aciendo el cálculo. Observa el resultado al incluir esta fórmula en nuestra tabla utilizando las variables previamente d

rias: La suma del resultado de las funciones PAGOINT y PAGOPRIN siempre será igual al resultado de la función P

l final pero en el último pago llegamos a liquidar el total del monto del crédito.

r solución para crear una tabla de amortización en Excel es utilizar una macro para generar automáticamente la ta

hoja y colocaré el siguiente código en el evento Click del botón:

el. Al momento de pulsar el botón se insertarán las fórmulas en las celdas correspondientes.

tiene solo las fórmulas y en la segunda hoja la que contiene la macro.

abla con el desglose de los pagos a realizar.

como los montos exactos destinados al pago de intereses y al pago de capital.

damos analizar diversos escenarios para un crédito.

es el número de mensualidades en las que pagaremos el rédito y finalmente el monto del crédito. Observa el cálculo

to para indicar el número de período para el cual deseamos calcular el monto del interés a pagar.

espondiente.

ndo las variables previamente definidas:

gual al resultado de la función PAGO.

generar automáticamente la tabla.

o del crédito. Observa el cálculo del pago y la fórmula implementada al leer los valores de los argumentos de las cel

s de los argumentos de las celdas en la columna B: