Cómo crear un plan de amortización en Excel

Un plan de amortización muestra el interés aplicado a un préstamo con interés fijo y cómo el monto principal se reduce a través de los pagos. También muestra un plan detallado de todos los pagos para que puedas observar cuánto se destina al principal y cuánto se destina a los cargos por intereses. Este How.com.vn te enseñará a crear tu propio plan de amortización en Microsoft Excel.

Método 1

Método 1 de 2:

Crear un plan de amortización de forma manual

Descargar el PDF

2
Crea etiquetas en la columna A. Crea etiquetas para los datos en la primera columna para mantener todo organizado. Esto es lo que debes ingresar en cada celda:
- A1: Monto del préstamo
- A2: Tasa de interés
- A3: Meses
- A4: Pagos
3
Ingresa la información relacionada con el préstamo en la columna B. Completa las celdas B1 a B3 con la información del préstamo. Deja en blanco la celda B4 (la celda al lado de la etiqueta de pagos).
- El valor "meses" debe ser el número total de meses del término del préstamo. Por ejemplo, si el préstamo es por dos años, ingresa 24.
- El valor "tasa de interés" debe figurar en forma de porcentaje (por ejemplo, 8,2 %).
4
Calcula tu pago en la celda B4. Para ello, haz clic en la celda B4 y escribe la siguiente fórmula en la barra de fórmila (fx) en la parte superior de la hoja y luego presiona Entrar o Retorno: =ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2).
- El símbolo de dólar en la fórmula es una referencia absoluta para asegurarte de que siempre utilice esas celdas específicas, por más que esté copiada en otra parte de la hoja de cálculo.
- La tasa de interés del préstamo se debe dividir entre 12, dado que es una tasa anual calculada de forma mensual.
- Por ejemplo, si el préstamo es por $150 000 a un interés del 6 % por 30 años (360 meses), el pago del préstamo se calculará por $899,33.
5
Crea encabezados para las columnas en la fila 7. Tendrás que agregar información adicional en la hoja, lo que requiere una segunda área de gráfico. Ingresa las siguientes etiquetas en las celdas:
- A7: Período
- B7: Saldo inicial
- C7: Pago
- D7: Principal
- E7: Interés
- F7: Principal acumulado
- G7: Interés acumulado
- H7: Saldo final
6
Completa la columna del período. Esta columna debe contener las fechas de los pagos. Esto es lo que debes hacer:
- Escribe el mes y el año del primer pago del préstamo en la celda A8. Es posible que tengas que darle formato a la columna para mostrar el mes y el año de forma correcta.
- Haz clic en la celda para seleccionarla.
- Arrastra desde el centro de la celda seleccionada hacia abajo para cubrir todas las celdas hasta A367. Si esto no hace que todas las celdas reflejen las fechas mensuales de pagos correctas, haz clic en el pequeño ícono con un rayo en la esquina inferior derecha de la celda inferior y asegúrate de que la opción Último mes esté seleccionada.
7
Completa las entradas en las celdas B8 a H8.
- El saldo inicial del préstamo debe ir en la celda B8.
- En la celda C8, escribe =$B$4 y presiona la tecla Entrar o Retorno.
- En la celda E8, crea una fórmula para calcular monto del interés del préstamo en el saldo inicial para dicho período. La fórmula se verá como =ROUND($B8*($B$2/12), 2). El símbolo del dólar crea una referencia relativa. La fórmula buscará la celda apropiada en la columna B.
- En la celda D8, resta el monto del interés del préstamo en las celdas E8 del pago total en la celda C8. Usa referencias relativas para que la celda se copie correctamente. La fórmula se verá como =$C8-$E8.
- En la celda H8, crea una fórmula para restar la parte del principal del pago del saldo inicial para dicho período. La fórmula se verá como =$B8-$D8.
8
Sigue el plan al crear las entradas de B9 a H9.
- La celda B9 debe incluir una referencia relativa para el saldo final del período previo. Escribe =$H8 en la celda B9 y presiona la tecla Entrar o Retorno.
- Copia las celdas C8, D8 y E8, y pégalas en C9, D9 y E9 (respectivamente).
- Copia la celda H8 y pégala en la celda H9. Es en este momento en que la referencia relativa resulta útil.
- En la celda F9, crea una fórmula para tabular el principal acumulado pagado. La fórmula se verá como =$D9+$F8.
- Ingresa la fórmula del interés acumulado en la celda G9 de esta forma: =$E9+$G8.
Cuando pases el cursor del ratón en la parte inferior derecha de la parte resaltada, el cursor se convertirá en una cruz.
10
Arrastra la cruz hasta la fila 367. De esta forma, llenarás todas las celdas hasta la fila 367 con el plan de amortización.
- Si luce raro, haz clic en el ícono similar a una hoja de cálculo que se encuentra en la esquina inferior derecha de la celda final y selecciona Copiar celdas.
Anuncio

Método 2

Método 2 de 2:

Usar una plantilla de Excel

Descargar el PDF

Visita https://templates.office.com/en-us/loan-amortization-schedule-tm03986974. Esta es una plantilla de amortización gratuita y descargable que facilita el cálculo del interés total y los pagos totales. Incluso cuenta con la posibilidad de agregar pagos adicionales.^{[1]XFuente de investigación}
Esto permite guardar la plantilla en tu computadora en formato de plantilla de Excel (XLTX).
3
Haz doble clic en el archivo descargado. Su nombre es tf03986974.xltx, y generalmente podrás encontrarlo en la carpeta de descargas. De esta forma, se abrirá la plantilla en Microsoft Excel.
- Los datos en la plantilla solo sirven como ejemplo, por lo que podrás agregar tu información sin problemas.
- Si fuese requerido, haz clic en Habilitar edición para realizar cambios en el libro de trabajo.
4
Escribe el monto del préstamo en la celda "Monto del préstamo". Se encuentra en la sección "INGRESAR VALORES", cerca de la esquina superior izquierda de la hoja. Para escribir, simplemente haz clic en el valor existente ($5000) y escribe tu monto.
- Al presionar la tecla Retorno o Entrar (o hacer clic en otra celda), los montos en el resto de la hoja se recalcularán. Esto ocurrirá cada vez que cambies un valor en esta sección.
Ingresa esta información en la celda "Tasa de interés anual".
Esta información se debe ingresar en la celda "Período del préstamo en años".
Por ejemplo, si haces un pago mensual, escribe 12 en la celda "Cantidad de pagos por año".
Esta información se debe incluir en la celda "Fecha de inicio del préstamo".
Si pagas un monto superior al mínimo adeudado en el período de cada pago del préstamo, ingresa el monto adicional en esta celda. De lo contrario, cambia el valor por defecto a 0 (cero).
El valor por defecto del "NOMBRE DEL PRESTAMISTA" es "Woodgrove Bank". Cámbialo para incluir el nombre del banco para tu referencia.
11
Guarda la hoja de cálculo como un archivo nuevo de Excel. Para ello, sigue estos pasos:
- Haz clic en el menú Archivo en la parte superior izquierda y selecciona Guardar como.
- Selecciona un lugar en la computadora o la nube donde quieres guardar tu plan.
- Ingresa el nombre del archivo. Si el archivo no está establecido como "Libro de Excel (*.xlsx)", selecciona dicha opción del menú desplegable (debajo del nombre del archivo).
- Haz clic en Guardar.
Anuncio

Consejos

Si no obtienes un saldo final de $0, asegúrate de haber usado referencias absolutas y relativas como se ha indicado y verifica que las celdas se hayan copiado correctamente.
Ahora, puedes revisar cualquier período durante el pago del préstamo para ver qué parte del pago se destina al principal y al interés, y cuánto has pagado del principal y el interés a la fecha.

Anuncio

Advertencias

Esto solo funcionará para los préstamos para viviendas calculados mensualmente. Si se trata de un préstamo automotriz o uno capitalizado diariamente, solo obtendrás estimaciones aproximadas para el interés pagado.

Anuncio