Saltar al contenido

Consejo de Excel: Determinar la duración restante de un préstamo usando NPER

Anteriormente he explicado cómo se pueden utilizar las funciones de la hoja de cálculo en Excel para determinar el pago de un préstamo, así como cómo calcular el interés total en una sola celda de la hoja de cálculo. Esta vez voy a usar la función NPER en Excel para mostrarte cómo puedes determinar cuánto tiempo te tomará pagar la cuenta de la tarjeta de crédito en la que estás haciendo los pagos mensuales.

Puedes refrescar las funciones PMT y CUMIPMT, pero cualquier préstamo típico tiene cuatro valores clave:

Consejo de Excel: Determinar la duración restante de un préstamo usando NPERConsejo de Excel: Determinar la duración restante de un préstamo usando NPER
  • Tasa de interés
  • Plazo del préstamo
  • Cantidad prestada
  • Pago mensual

Como se discutió en mi artículo anterior, si tienes la tasa de interés, el plazo y la cantidad del préstamo, puedes usar la función PMT para resolver el pago. A veces, como en el caso del saldo de una tarjeta de crédito, sabrás el tipo de interés, el pago y la cantidad del préstamo, pero no el plazo. En este caso, la función NPER de Excel (abreviatura de número de períodos) le permite calcular el cuarto valor.

El NPER tiene 3 argumentos obligatorios y 2 opcionales:

tasa – La tasa de interés del préstamo expresada como una tasa mensual.

pmt – El pago mensual, que siempre debe mostrarse como una cantidad negativa.

pv – El saldo actual del préstamo

fv – Este argumento opcional permite especificar un valor futuro si se debe una cantidad global al final del préstamo. Omitir este argumento implica que el préstamo debe ser pagado hasta 0.

tipo – Este argumento opcional permite especificar si los pagos se realizan al principio de cada período, o puede omitirse el argumento para indicar que los pagos se realizan al final de cada período. También puede especificar 0 en esta posición para indicar explícitamente que los pagos se realizan al final de cada período.

Como se muestra en la figura 1, se tardará 36 meses en devolver 20.000 dólares con un pago mensual de 586,04 dólares a un tipo de interés del 3,5%. Siempre asegúrese de que el pago se muestre como un número negativo, de lo contrario el NPER puede mostrar un plazo de pago un poco más largo. Omití los 2 argumentos opcionales, así que en este caso, la función PMT asume que el préstamo se paga a 0 y los pagos se hacen al final de cada período.

Figura 1: Utiliza la función NPER de Excel para calcular el plazo de pago de un préstamo.

Para los préstamos a largo plazo, el NPER puede devolver un gran número de meses, como el 94, que puede ser difícil de convertir en meses en su cabeza. Vamos a ampliar nuestros cálculos para que la salida sea más fácil de usar.

Primero, como se muestra en la figura 2, añadiremos la función de redondeo a nuestra fórmula de NPER. Los períodos de préstamo normalmente implican alguna fracción del mes final, que para nuestros propósitos queremos tratar como un mes completo. La función REDONDA redondea un número hacia arriba, a diferencia de la función REDONDA comúnmente utilizada que puede redondear los números hacia arriba o hacia abajo.

ROUNDUP tiene dos argumentos:

número – En este caso el resultado de NPER será nuestro número

num_digits – en este caso usaremos el cero, ya que deseamos redondear al siguiente mes completo. Si quisiera redondear un número para decir el millar más cercano, usaría -3 en su lugar.

Figura 2: Utiliza la función RONDA con la fórmula NPER para convertir a meses.

A continuación, tenemos que convertir el resultado de ese retorno de ROUNDUP/NPER en un número de años y meses. Para hacerlo, podemos usar la función TRUNC. Esta función convierte un número en un entero eliminando la parte decimal o fraccionaria. También se puede usar REDONDA y especificar el cero como el número de dígitos para lograr el mismo efecto.

Para calcular el número de años enteros en el préstamo, podemos usar esta fórmula:

=TRUNC(B4/12)

O esto funcionaría también:

=ROUNDDOWN(B4/12,0)

En cualquier caso, tomamos el número de períodos devueltos por el NPER, lo dividimos por 12, y luego truncamos los decimales.

Use esta fórmula para calcular el número de meses que quedan después de los años enteros:

=ROUNDUP(B4-TRUNC(B4/12)*12,0)

Podemos entonces encadenar todo esto en un formato ordenado, como se muestra en la Figura 3:

=”meses, o “&TRUNC(B4/12)& ” Años”,&ROUNDUP(B4-TRUNC(B4/12)*12,0)&” Meses”

En esto estamos usando el ampersand para concatenar, o unir, texto y cálculos en una frase comprensible. Prefiero usar el ampersand para unir el texto, pero si eres un fan de la función CONCATENAR de Excel, la fórmula tomaría esta forma:

=CONCATENADO(“meses, o “,TRUNC(B4/12)”, Años”,,RONDA(B4-TRUNC(B4/12)*12,0)”, Meses”)

Figura 3: Usar el signo de ampersand para concatenar texto y cálculos en una frase lógica.