La función PMT en Excel 2013

La función PAGO de Excel 2013 en el menú desplegable del botón Financiero en la pestaña Fórmulas de la Cinta calcula el pago periódico de una anualidad, asumiendo un flujo de pagos iguales y una tasa de interés constante. La función PMT utiliza la siguiente sintaxis:

= PMT (tasa, nper, pv, [fv], [tipo])

Al igual que con las otras funciones financieras comunes, la tasa es la tasa de interés por período, nper es el número de períodos, pv es el valor presente o la cantidad que valen los pagos futuros en el presente, fv es el valor futuro o saldo de efectivo que desea después se realiza el último pago (Excel asume un valor futuro de cero cuando omite este argumento opcional como lo haría al calcular los pagos del préstamo), y el tipo es el valor 0 para los pagos realizados al final del período o el valor 1 para los pagos realizados al comienzo del período.

(Si omite el argumento de tipo opcional , Excel asume que el pago se realiza al final del período).

La función PMT se utiliza a menudo para calcular el pago de préstamos hipotecarios que tienen una tasa de interés fija. La figura muestra una hoja de trabajo de muestra que contiene una tabla que usa la función PMT para calcular los pagos de préstamos para un rango de tasas de interés (de 2.75 por ciento a 4.00 por ciento) y principales ($ 150,000 a $ 159,000).

La tabla usa el principal inicial que ingresa en la celda B2, lo copia en la celda A7 y luego lo aumenta en $ 1,000 en el rango A8: A16. La tabla usa la tasa de interés inicial que ingresa en la celda B3, se copia en la celda B6 y luego aumenta esta tasa inicial en 1/4 de un porcentaje en el rango C6: G6. El plazo en años en la celda B4 es un factor constante que se usa en toda la tabla de pagos del préstamo.

Para tener una idea de lo fácil que es construir este tipo de tabla de pago de préstamos con la función PMT, siga estos pasos para crearla en una nueva hoja de trabajo:

Ingrese los títulos Pagos de préstamos en la celda A1, Principal en la celda A2, Tasa de interés en la celda A3 y Plazo (en años) en la celda A4.

Ingrese $ 150,000 en la celda B2, ingrese 2.75% en la celda B3 e ingrese 30 en la celda B4.

Estos son los valores iniciales con los que crea la tabla de pagos de préstamos.

Coloque el puntero de la celda en B6 y luego cree la fórmula = B3.

Al crear una fórmula de vinculación que presenta el valor de la tasa de interés inicial en B3 con la fórmula, se asegura de que el valor de la tasa de interés en B6 refleje inmediatamente cualquier cambio que realice en la celda B3.

Coloque el puntero de la celda en la celda C6 y luego cree la fórmula = B6 + .25%.

Al agregar 1/4 de un porcentaje a la tasa de interés al valor en B6 con la fórmula = B6 + .25% en C6 en lugar de crear una serie con el controlador Autocompletar, se asegura de que el valor de la tasa de interés en la celda C6 siempre será 1/4 de un porcentaje mayor que cualquier valor de tasa de interés ingresado en la celda B6.

Arrastre el controlador de relleno en la celda C6 para extender la selección hacia la derecha hasta la celda G6 y luego suelte el botón del mouse.

Coloque el puntero de la celda en la celda A7 y luego cree la fórmula = B2.

Nuevamente, al usar la fórmula = B2 para llevar el principal inicial a la celda A7, se asegura de que la celda A7 siempre tenga el mismo valor que la celda B2.

Coloque el puntero de celda en A8 activo y luego cree la fórmula = A7 + 1000.

Aquí también, usa la fórmula = A7 + 1000 en lugar de crear una serie con la función Autocompletar para que el valor principal en A8 siempre sea $ 1,000 mayor que cualquier valor colocado en la celda A7.

Arrastre el controlador de relleno en la celda A8 hacia abajo hasta que extienda la selección a la celda A16 y luego suelte el botón del mouse.

En la celda B7, haga clic en el botón Insertar función en la barra de fórmulas, seleccione Financiero en la lista desplegable O Seleccionar una categoría y luego haga doble clic en la función PAGO en el cuadro de lista Seleccionar una función.

El cuadro de diálogo Argumentos de función que se abre le permite especificar los argumentos rate, nper y pv . Asegúrese de mover el cuadro de diálogo Argumentos de función hacia la derecha para que ninguna parte oscurezca los datos en las columnas A y B de su hoja de trabajo antes de continuar con los siguientes pasos para completar los argumentos.

Haga clic en la celda B6 para insertar B6 en el cuadro de texto Tasa y luego presione F4 dos veces para convertir la referencia relativa B6 en la referencia mixta B $ 6 (relativa a la columna, a la fila absoluta) antes de escribir / 12.

Convierte la referencia de celda relativa B6 en la referencia mixta B $ 6 para que Excel no ajuste el número de fila cuando copia la fórmula PMT en cada fila de la tabla, pero sí ajusta la letra de la columna cuando copia la fórmula en sus columnas. .

Debido a que la tasa de interés inicial ingresada en B3 (y luego trasladada a la celda B6) es una tasa de interés anual , pero desea conocer el pago mensual del préstamo, debe convertir la tasa anual a una tasa mensual dividiendo el valor en la celda B6 por 12.

Haga clic en el cuadro de texto Nper, haga clic en la celda B4 para insertar esta referencia de celda en este cuadro de texto y luego presione F4 una vez para convertir la referencia relativa B4 en la referencia absoluta $ B $ 4 antes de escribir * 12.

Debe convertir la referencia de celda relativa B4 en la referencia absoluta $ B $ 4 para que Excel no ajuste ni el número de fila ni la letra de la columna cuando copie la fórmula PMT en las filas y en las columnas de la tabla.

Debido a que el término en B3 (que luego se adelanta a la celda B6) es un período anual , pero desea conocer el pago mensual del préstamo, debe convertir los períodos anuales en períodos mensuales multiplicando el valor en la celda B4 por 12.

Haga clic en el cuadro de texto Pv, haga clic en A7 para insertar esta referencia de celda en este cuadro de texto y luego presione F4 tres veces para convertir la referencia relativa A7 en la referencia mixta $ A7 (columna absoluta, fila relativa).

Debe convertir la referencia de celda relativa A7 en la referencia mixta $ A7 para que Excel no ajuste la letra de la columna cuando copie la fórmula PMT en cada columna de la tabla, pero ajustará el número de fila cuando copie la fórmula hacia abajo a través de sus filas.

Haga clic en Aceptar para insertar la fórmula = PAGO (B $ 6/12, $ B $ 4 * 12, $ A7) en la celda B7.

Ahora está listo para copiar esta fórmula PMT original hacia abajo y luego para completar toda la tabla de Pagos de préstamos.

Arrastre el controlador de relleno en la celda B7 hacia abajo hasta que extienda el rango de relleno a la celda B16 y luego suelte el botón del mouse.

Una vez que haya copiado la fórmula PMT original en la celda B16, estará listo para copiarla a la derecha en G16.

Arrastre el controlador de relleno hacia la derecha hasta que extienda el rango de relleno B7: B16 a la celda G16 y luego suelte el botón del mouse.

Después de copiar la fórmula original con el controlador de relleno, asegúrese de ensanchar las columnas B a G lo suficiente para mostrar sus resultados. (Puede hacer esto en un paso arrastrando los encabezados de estas columnas y luego haciendo doble clic en el borde derecho de la columna G.)

Una vez que haya creado una tabla de préstamos como esta, puede cambiar el capital inicial o la tasa de interés, así como el plazo para ver cuáles serían los pagos en varios otros escenarios. También puede activar el recálculo manual para poder controlar cuándo se vuelve a calcular la tabla de pagos de préstamos.