Amortización de préstamos modelo con Excel, OpenOffice o Google Sheets
Las hojas de cálculo están disponibles en varios proveedores populares, y las instrucciones en esta página funcionarán con cualquiera de los siguientes (entre otros):
- Microsoft Excel
- Hojas de cálculo de Google
- Oficina abierta
Plantillas de bricolaje contra calculadora
Describimos cómo crear su propia hoja de cálculo desde cero en esta página, y puede usar esas instrucciones con ligeras modificaciones en otros programas de software. Pero no hay necesidad de hacerlo todo usted mismo, a menos que realmente quiera hacerlo. Las plantillas le permiten ingresar algunos detalles sobre su préstamo y terminar con ellas, y las plantillas preconstruidas son fáciles de usar.
Para usar una plantilla de préstamo en Excel, abra la plantilla en su computadora (descargue la plantilla si es necesario):
- Haga clic en Archivo y seleccione Nuevo en el menú de la izquierda
- Verás una lista de plantillas disponibles
- Mire las plantillas (en Plantillas de muestra) o busque en Office.com
- Haga doble clic en la plantilla de "Amortización de préstamo"
Un método alternativo para obtener la plantilla de Amortización de Préstamo es:
- Haga clic derecho en la pestaña de hojas en la parte inferior ('Hoja1, Hoja2, etc.')
- Seleccione Insertar ...
- Elija Soluciones de hoja de cálculo en el menú emergente
- Haga doble clic en la opción 'Amortización de préstamo'
Una vez que esté en la plantilla, complete la información relacionada con su préstamo:
- Monto del préstamo: ingrese el monto que está pidiendo prestado.
- Tasa de interés anual: use la tasa de interés de su préstamo (puede usar APR o una tasa de interés establecida, si está disponible). No debería necesitar convertir a formato decimal, pero asegúrese de que la tasa se muestre correctamente.
- Número de pagos por año: ¿con qué frecuencia paga? Para pagos mensuales, ingrese 12.
- Fecha de inicio del préstamo: esto podría ser útil para la planificación, pero no es esencial para cálculos precisos.
- Pagos extras opcionales: si paga más, o si desea saber qué tan útil sería pagar más, use este campo. Preste atención a cuánto ahorra en intereses acumulados y cómo se paga el préstamo más rápidamente cuando paga un monto adicional.
Cómo construir un modelo de préstamo de Excel
Hacerlo usted mismo toma más tiempo, pero desarrollará conocimientos financieros y habilidades de hoja de cálculo que no puede obtener de una plantilla. Además, puedes personalizar al contenido de tu corazón. Dicho esto, después de haber hecho esto algunas veces, puede que le resulte más rápido usar una plantilla para comenzar el proceso , luego "desproteger" la plantilla y hacer sus modificaciones.
Digamos que quiere un modelo que le muestre cada pago anual o mensual. Comience en la fila superior de su hoja de cálculo y agregue las siguientes secciones (donde ingresará la información sobre su préstamo):
- Monto del préstamo
- Tasa de interés (aprenda a convertir porcentajes y decimales para estos cálculos)
- Número de períodos (o plazo del préstamo )
Proporcione a esas celdas un color de relleno verde, lo que le indica que puede cambiar esos valores al comparar préstamos y ejecutar situaciones hipotéticas.
Ahora estás listo para hacer tu tabla de datos. Haga una fila con los siguientes nombres de columna:
- Número de período
- Inicio del saldo del préstamo
- Pago
- Monto aplicado al interés
- Monto aplicado al principal
- Saldo remanente del préstamo
Si lo desea, también puede agregar filas adicionales (como el interés acumulado pagado, por ejemplo).
A continuación, necesitará una fila para cada pago como parte de su tabla de datos. En la columna de la izquierda de la hoja de cálculo (debajo de la columna "Período" descrita arriba), coloque un número en cada fila: la primera fila es "1", luego mueva hacia abajo una fila para "2", y así sucesivamente. Cada fila es un pago. Para un préstamo de 30 años, tendría pagos mensuales de 360, para números grandes como ese, es más fácil completar los primeros períodos y usar el "identificador de relleno" de Excel para completar todas las filas restantes.
Ahora, haga que Excel complete y calcule valores para usted.
Recuerde utilizar "$" cuando se refiera a cualquier número de fila en sus cálculos, excepto el Período; de lo contrario, Excel buscará en la fila incorrecta.
- Use la función PMT para calcular su pago mensual (usando información en su "área de entrada"): este pago generalmente no cambia durante la vigencia del préstamo.
- Use la función IPMT para mostrar el monto de cada pago que le interesa.
- Reste el monto del interés del pago total para calcular cuánto principal pagó en ese mes.
- Reste el capital que pagó del saldo de su préstamo para llegar al saldo de su nuevo préstamo.
- Repita para cada período (o mes).
Tenga en cuenta que después de la primera fila de su tabla de datos, se referirá a la fila anterior para obtener el saldo de su préstamo.
Para ver un ejemplo de cómo funcionan las matemáticas y cómo podría verse su tabla, desplácese hasta el final de nuestra página Cálculo de amortización .
Si su préstamo usa pagos mensuales, asegúrese de configurar cada período correctamente en las fórmulas. Por ejemplo, un préstamo a 30 años tiene 360 períodos totales (o pagos mensuales). Del mismo modo, si paga una tasa anual del 6 por ciento, debe hacer que la tasa de interés periódica sea del 0,5 por ciento (o el 6 por ciento dividido por 12 meses).
Si no quiere hacer todo el trabajo de trabajar en hojas de cálculo, hay una manera más fácil. Use una Calculadora de Amortización de Préstamo en línea. También es útil para verificar dos veces la salida de la hoja de cálculo.
Qué puedes hacer con tu hoja de cálculo
Una vez que haya modelado su préstamo, puede aprender mucho sobre su préstamo.
Tabla de amortización: su hoja de cálculo muestra una tabla de amortización , que puede usar para crear una variedad de gráficos de líneas. Vea cómo se cancela su préstamo a lo largo del tiempo, o cuánto adeudará en su préstamo en cualquier fecha en el futuro.
Principal e interés: la hoja de cálculo también muestra cómo cada pago se divide en principal e interés. Comprenderá cuánto cuesta pedir prestado, y cómo esos costos cambian con el tiempo. Su pago se mantiene igual, pero pagará cada vez menos intereses con cada pago mensual.
Pago mensual: su hoja de cálculo también realizará cálculos sencillos. Por ejemplo, deberá calcular el pago mensual. Cambiar el monto del préstamo (si considera comprar algo menos costoso, por ejemplo) afectará su pago mensual requerido.
Escenarios "What if": la ventaja de usar hojas de cálculo es que tiene la capacidad informática para realizar tantos cambios en el modelo como desee. Verifique qué sucedería si realiza pagos adicionales en su préstamo. Luego vea qué sucede si pide prestado menos (o más). Con una hoja de cálculo, puede actualizar las entradas y obtener respuestas instantáneas.