Simulador de préstamos personales en Excel

Simulador de préstamos personales en Excel

9353

Aquí os traemos una pequeña herramienta que hemos hecho, es bastante sencilla y podrá ayudarnos a realizar el cálculo de la cuota de un préstamo a través de excel. Podremos usarlo como un simulador de préstamos personales o como una calculadora hipotecaria sencilla.

Aquí podéis descargar otro simulador al que le hemos añadido la opción de añadir un período de carencia. Simulador de préstamos personales con carencia

Lo único que tendremos que hacer para calcular la cuota que tendremos que pagar en cada período será introducir los datos correspondientes en cada apartado. Una vez hecho esto habremos calculado el cuadro de amortización de nuestro préstamo en excel.

El modelo calculará diversos datos, la cuota a pagar cada mes para devolver el préstamo,los intereses que pagamos en cada período, la parte del capital que amortizamos cada mes, el capital pendiente que nos queda por amortizar y el capital amortizado a lo largo de los períodos anteriores. Aquí os ofrecemos una captura de pantalla:

calculo prestamo excel o simulador hipoteca excel

Cambiando periodicidad de pagos y sistemas de amortización en nuestra calculadora de préstamos

Esta pequeña calculadora de préstamos o hipotecas, nos permitirá elegir entre diversos aspectos:

El período de los pagos: seleccionaremos la periodicidad de los pagos, pueden ser; mensuales, bimensuales, trimestrales, cuatrimestrales, semestrales y anuales. Elegiremos del desplegable la opción que queramos.

El sistema de amortización: aunque en la mayoría de préstamos se utiliza el sistema de amortización francés, podemos elegir entre el americano y el de cuotas de amortización constantes. Tendremos que seleccionar el método utilizado para el cálculo del cuadro de amortización en la lista.

Esta calculadora hipotecaria en excel nos resultará útil si queremos tener una idea de cuanto nos costará la hipoteca en cada período. Además nos permite ver como varían las cuantías totales de intereses y cuotas, si cambiamos el sistema de amortización o la periodicidad de los pagos.

  • nathanael

    Como lo descargo????

    • sinjob

      Hola, me gustaría saber como puedo modificar el dato de tipo de interés. en mi caso sería del 2,2%
      muchas gracias

      • J. Garcia

        Donde pone Tipo de interés, sale un 5% (tal como se ve en la imagen), pulsa encima e introduce el 2,2% y ya estaría. Un saludo

  • D. Celdran

    Hay dos enlaces en el inicio de la entrada que verás con letra azul. Con tal de clicar encima del enlace se te descarga automáticamente.

  • dani

    Tendrían algún simulador en el caso de un prestamo con cuota final ?

    • J. Garcia

      Hola, ¿a qué te refieres con cuota final?

      • dani

        Hola, en mi caso es un prestamo hipotecario denominado cuota final con el BBVA. Deja para el final la amortización de un % del prestamo (un 30%), por lo que al principio pagas intereses de todo pero vas amortizando solo del 70% restante, por lo que al principio las cuotas son más baratas

        • J. Garcia

          Esa opción no está contemplada en este simulador

  • Cristian Molina Cordero

    Porque lo tienen protegido con contraseñas¿?¿?
    no se puede editar

    • J. Garcia

      Está protegido con contraseña para que no se efectúen cambios por error que provoque un mal funcionamiento. Qué es lo que deseas editar?

  • Lucila Figueroa Alcorta

    Hola, es el cuarto simulador con sistema de amortización francés que pruebo en internet, llenando los mismos datos, todos me dan totales diferentes. Incluso pruebo el simulador de prestamos del Banco Ciudad, copio los mismos datos en este excel, y el calculo de las cuotas da diferente.

    • J. Garcia

      Hola Lucía.
      ¿Me puedes poner los enlaces de los otros simuladores? He intentado entrar en el del Banco Ciudad y no me ha dejado. Pero generalmente los simuladores de las entidades bancarias calculan mal el tipo de interés. Por simplificar ellos cogen el tipo de interés nominal y lo dividen entre 12. Eso sería así en préstamos con tipos simples. Con tipos compuestos el tipo de interés se calcula de otra manera. Realmente la cifra que se obtiene es muy parecida, pero a largo plazo puede suponer una cuantía considerable. La diferencia en la cuota debería ser muy pequeña, si es muy grande, algo estará mal o habrá algún supuesto que no se ha contemplado (como puedan ser comisiones, etc.). Un saludo

  • Diego

    Muchas gracias por compartir la hoja.

  • Raúl Cruz Rodríguez

    La verdad están muy buenos estos simuladores de crédito, en mi caso yo necesito de simuladores en forma semanal y quincenal, además de insertar una columna para el impuesto al valor agregado (IVA) del 16% entre las columnas de intereses y cuota amortización calculado a la columna de los intereses, obviamente deberá sumar; intereses+IVA+cuota amortización=cuota mensual, se lo voy agradecer infinitamente, además me podría explicar ¿de dónde toman el dato de Tipo de interés efectivo?, muchísimas gracias por su valioso apoyo. Saludos.

    • J. Garcia

      El interés efectivo se calcula a partir del nominal. Si es compuesto se calcula de una forma y si es simple de otra, en este caso se toma el compuesto. Las entidades bancarias para simplificar simplemente dividen el tipo nominal entre 12 para calcular el efectivo (cosa que siendo riguroso no se podría hacer), el resultado es muy parecido, pero a largo plazo es favorable a la entidad. Los cambios que dices puedes hacerlos tú mismo, introduces las contraseña “prestamosambito” (sin las comillas) y puedes modificar la plantilla fácilmente. Un saludo

  • Roberto Chacón

    Estimado J. García, yo comparto la misma opinión de Lucila Figueroa y ya probé en dos simuladores de crédito, uno que yo he he hecho en excell y el otro en la página del Banco de España: http://app.bde.es/gnt/clientebanca/simulador_hipotecario.jsp#topForm1
    El Ejemplo que te pongo es un crédito de $ 74,375.00 a 5 años con una tasa nominal del 10%, me da en los dos simuladores $ 1,580.25 y al bajar el tuyo da $ 1,564,52.
    El simulador tuyo es excelente; pero tiene algún error, yo visualicé el error hace tiempo en el otro simulador con carencia, porque tiene el mismo error. Podrías verificar? Gracias y un saludo

    • J. Garcia

      Bueno. Como dije en algún comentario anterior es algo técnico. Los simuladores de los bancos y en cualquier banco, los intereses efectivos los calculan dividiendo el interés nominal entre 12. Esto es un error que cometen todos los bancos y la gran mayoría de simuladores que puedas encontrar. Lo hacen así ya que simplifican el cálculo. La fórmula matemática para calcular un tipo de interés efectivo cuando hay capitalización compuesta es la que sale en mi simulador y es la siguiente. Int Efectivo = ((1+Int.Nominal)^(1/n)) – 1. La diferencia entre ese cálculo y el que hacen los bancos es mínimo, pero siempre sale a favor del banco. Si consultas cualquier libro de finanzas elementales verás que se calcula de la manera en la que sale en el simulador. Espero haberte aclarado la duda. Como te digo, el error no es mío, está perfectamente calculado. En lo referente a que sale mejor para el banco es porque dividiendo simplemente por 12 el interés nominal, el interés efectivo es mayor que con la fórmula que deberían usar. La variación como te he dicho es pequeña pero en un préstamo de una cuantía elevada es bastante, imagínate esto en todos los préstamos que pueda haber concedido una entidad financiera… Un saludo

      • Roberto Chacón

        Estimado J. García, tengo una observación, yo utilicé la fórmula siguiente, que incluso lo hice de forma manual para calcular la cuota de los $ 74,375 al 10% a 60 meses o sea 5 años y sin cobro inicial de comisión:
        74,375 [ (0.00833(1+0.00833)^60) / ((1+0.00833)^60-1)] y por la utilización de menos decimales me dio como respuesta $ 1,580.10. El cálculo de los intereses lo hacemos como interés simple y la tasa efectiva se va aumentado según el plazo, aditivos incluidos en la cuota mensual del préstamo como seguros, comisiones por manejo, etc. y por la comisión inicial por desembolso. Gracias por compartir tus comentarios y tu experiencia. Si estoy haciendo algo equivocado, te agradezco tus observaciones.

        • J. Garcia

          Si en mi simulador introduce los datos de su préstamo. Y en la casilla de tasa de interés efectivo la cambia y pone el interés nominal dividido por 12. La cuota mensual le saldrá 1,580.25. Ahora bien como le dije anteriormente ese cálculo del tipo de interés es erróneo. Si usa leyes financieras compuestas, el cálculo está mal, ese préstamo estaría mal calculado. Que se haga así siempre (simplemente dividiendo entre 12) es una cosa y que esté correctamente calculado es otra. Que se haya generalizado esa manera de cálculo es simplemente porque beneficia a quien presta el dinero, lo de reducir la complejidad del cálculo no me lo creo porque en una hoja de cálculo no se tarda ni 1 segundo en hacerlo. Se puede decir que tu cálculo es un cálculo aproximado, pero no es el correcto. Un saludo

          • Roberto Chacón

            Hola, perdona de nuevo mis dudas; en mi país El Salvador, no es permitido calcular en base a interés compuesto los intereses de los créditos, sino que se hace en base a la fórmula de interés simple I=Pin y se utiliza oficialmente para las instituciones financieras la fórmula siguiente para calcular el interés efectivo en donde i (Interés Nominal) debe de ser representada en forma decimal y dividida por el período ya sea mensual, trimestral, etc y a la vez elevado al mismo período para que tenga relación siendo representado por k. He visto que tú mencionas que no debemos dividir la Tasa Nominal entre 12 y esa sería mi primera pregunta y si puedes extender el por qué y la otra es que por qué en la fórmula ((1+Int.Nominal)^(1/n)) – 1, siempre elevas a 1 y lo divides entre el período, siempre es así? porque he visto otros ejemplos de dicha fórmula en donde por ejemplo si buscas calcular una tasa mensual haces los siguiente:
            (1+0.24/12)^360/30, qué básicamente vuelve a lo mismo porque 360/30 es 12.

          • http://ambito-financiero.com J. Garcia

            Las fórmulas financieras se estipulan en función del plazo. En principio, si es corto plazo se utilizarían las fórmulas simples y si es largo plazo, las compuestas.

            Si usas leyes de capitalización compuesta para hacer tus cálculos, estarías cometiendo un error si divides entre 12 el interés. Eso sería válido para las leyes simples, pero para la compuesta es esa que comentas de ((1+int.nom)^(1/n)) – 1.

            Si bien es cierto que el resultado es prácticamente idéntico, no es correcto matemáticamente. De hecho, muchas entidades calculan así el interés (curiosamente este cálculo les beneficia).

            La fórmula esa siempre es así (en capitalización compuesta). Por ejemplo, tienes el interés nominal anual y quieres calcular el efectivo mensual. Pues n sería: n=12. Si fuera semestral n=2, trimestral n=4, y así con cada período.

            Sobre lo de que en tu país se calcula de manera diferente, no me tiene mucho sentido, dividir el interés entre 12 (en caso de ser mensual) y luego elevarlo a 12.

            Un saludo ;)

          • Roberto Chacón

            La fórmula para calcular el interés efectivo que utilizamos en nuestro país es (1+i/k)^k – 1, en donde i=interés nominal, k=el número de períodos, si por ejemplo queremos saber la tasa efectiva del 36% de interés nominal, hacemos lo siguiente ((1+0.36/12)^12) – 1 = 0.42576089 y multiplicado por 100 = 42.58%, he leído libros en donde el valor k debe ser igual, si lo divides entre 4, debes elevarlo a 4 y regularmente esta fórmula es indicativo en varias publicaciones. Podrías recomendarme algún libro o publicación en internet en donde hablen de la fórmula ((1+int.nom)^(1/n)) – 1 para que pueda tener aún más claro este punto que quisiera poder esclarecer completamente. Gracias por tu tiempo

          • http://ambito-financiero.com J. Garcia

            El nominal y el efectivo, cuando se trata de 1 año deben ser iguales. Si empleas las formas que te comenté, lo verás.Pasas el nominal anual, a interés mensual efectivo y ese lo pasas a anual efectivo y tendrías otra vez el 36%.

            La fórmula que aplicáis la desconozco, nunca la había visto. Pero por ejemplo, si tienes el nominal anual, para conocer el tipo efectivo mensual, por ejemplo, con leyes financieras simples se calcularía así (1+i/k)-1 o directamente i/k.

            Libros hay muchos, cualquiera que hable de leyes financieras te explicarán lo que te digo, y en cualquier web que busques también te servirá. Un libro que me gusta bastante es uno que se llama Gestión patrimonial y banca privada. Manual del asesor financiero.

  • flor

    holaaaaaaaa necesito la contrasena para desproteger el excel por favor

    • J. Garcia

      La contraseña es “prestamosambito” (sin las comillas). Un saludo

  • flor

    muchas gracias!!!!!!!!!!! solo tengo que entrar los datos de mi prestamo y ya esta ok lo que voy a pagar cada mes??? no tengo que calcular nada a 12 meses???

    • J. Garcia

      Si, en la plantillas introduces los datos que quieras y te calcula cuanto pagarías para devolver un préstamo. Un saludo

  • Hugo ML

    Estoy probando y me parece sencillo de manejar, en mis caso tratare de manipularlo ya que presto dinero y quiero hacer los pagos mensuales con intereses mensuales.

  • M. Medina

    He realizado 2 préstamos bancarios i tu simulador me ha servido de maravilla. Gracias

    • http://ambito-financiero.com J. Garcia

      De nada! Me alegro que te haya servido ;)

  • Matias

    Quisiera preguntarte si el interes que uno coloca es anual o el total en el lapso de los años. Por ejemplo 50 mil al 36% en 2 años

    • http://ambito-financiero.com J. Garcia

      Es el interés anual ;)

  • gabriel solarte

    Como puedo hacer un sistema de este tipo que tenga en cuenta pagos a capital mayores a la cuota determinada

    • http://ambito-financiero.com J. Garcia

      Podrías crear una columna auxiliar para meter el capital adicional