Interés compuesto con Excel

Este es un mapa mental de las distintas funciones Excel para calcular el valor del dinero en el tiempo aplicando la ley de capitalización compuesta. 

En el centro de la imagen se representa la operación financiera mediante un diagrama de flujos de caja, en donde la flecha hacia abajo es la entrega del dinero y la flecha hacia arriba el cobro al vencimiento. Además, se indican las cuantías de los capitales y el tipo de interés.

En las ramas del mapa se expresan las distintas funciones Excel para calcular cada una de las variables, suponiendo conocidas las otras tres.

Las cuatro funciones de Excel para capitalización compuesta, son:

• Montante o valor futuro: VF(tasa,núm_per,pago,[va],[tipo])
• Capital inicial o valor actual: VA(tasa, nper, pago, [vf], [tipo])
• Tipo de interés o tasa: TASA(nper, pago, va, [vf], [tipo], [estimar])
• Número de periodos de capitalicación: NPER(tasa,pago,va,[vf],[tipo])

Donde, en mayúsculas, se indica el nombre de la función y entre paréntesis los argumentos a introducir en cada caso.

La imagen corresponde a uno de los capítulos del libro:

 📙Matemáticas financieras en mapas mentales 

Publicado en formato electrónico y en papel, en Amazon. En el libro se explica la teoría y varios ejemplos resueltos con álgebra, calculadora financiera y Excel. 

Planificación de compras

Realizar una lista con los productos que necesitamos y que formarán parte de nuestra cesta de la compra nos ayuda a encontrar la combinación de bienes preferida dentro de un presupuesto fijo y establecido para un periodo, fecha o situación determinada.

La aplicación Excel siguiente permite confeccionar la lista y calcula en forma de porcentaje la importancia de cada artículo en relación con el presupuesto total. Además, informa del dinero gastado y disponible en cada momento.

"El hombre debe ordenar sus acciones de tal forma que sea máxima la suma de su goce de la vida" (Gossen)

Aplicación Excel: Planificación de compras

Plan de viabilidad para emprendedores

Un plan de viabilidad es el estudio o simulación de la evolución económico-financiera futura de la empresa como herramienta para evaluar la conveniencia o no de llevar a cabo las inversiones previstas. 

Para considerar viable un proyecto es necesario que el negocio genere beneficios que cumplan unos objetivos determinados, y liquidez suficiente para su buen funcionamiento. De forma simultánea, el plan ha de calcular los riesgos operativos y financieros de la inversión. 

El empresario tratará de maximizar la rentabilidad y minimizar el riesgo, teniendo en cuenta que a mayor rentabilidad, mayor riesgo y viceversa.

La metodología empleada en esta aplicación Excel, es la confección de varios presupuestos de inversiones, financiación, cuentas de resultados y tesorería, y coordinarlos entre sí. Dichos presupuestos se pueden realizar fácilmente con las hojas detalladas a continuación:

Hojas de entrada de datos:

• Detalle de las inversiones, vida útil de los activos y cálculo de amortizaciones.
• Presupuesto de financiación, con tipos de interés, plazos y cálculo de intereses a pagar.
• Presupuesto de ingresos por ventas o servicios prestados, y costes de los aprovisionamientos.
• Gastos de personal, alquileres y generales 

Hojas de resultados:

• Cuenta analítica de resultados.
• Presupuesto de tesorería, calculado a partir de los resultados, al que se añaden los cobros y pagos por operaciones de explotación, inversión y financiación.
• Balance, que recoge el saldo de las cuentas al inicio de la actividad y al final de cada año transcurrido.
• El estudio se completa con análisis mediante ratios, del balance, el punto de equilibrio, las rentabilidades anuales, la rentabilidad del proyecto y el cálculo de riesgo operativo y financiero.

El análisis simultáneo de los distintos estados nos ayudará a evaluar la viabilidad del proyecto, y valorar si la combinación rentabilidad-riesgo es aceptable y cumple los objetivos deseados.

Descargar: Plan-de-viabilidad.xlsx

Amortización de préstamos mediante el sistema francés

El sistema de préstamo con amortización, método francés, es el más utilizado por las entidades financieras y en las ventas a plazos. Se caracteriza porque el pago se realiza mediante una cantidad constante periódica, compuesta de amortización e intereses, durante toda la vida del préstamo.

Sin embargo, en determinados casos, por ejemplo en los préstamos hipotecarios que se formalizan a largo plazo, la cuota puede modificarse si se ha contratado a tipo de interés variable para distintos periodos, y/o cuando se realicen amortizaciones parciales anticipadas. Estos supuestos implican el cálculo de una nueva cuota en función del nuevo tipo de interés del préstamo.

La cuota constante se calcula mediante la siguiente fórmula, obtenida por equivalencia financiera:

a=C* i * (1+i)^n / ((1+i)^n-1)

Donde:

• a=cuota de amortización constante a final de cada periodo (pago)
• C=capital del préstamo (va)
• i=tipo de interés efectivo al tanto por uno (nominal anual dividido por el número de pagos anuales)
• n=número de periodos (nper)

La hoja de cálculo Excel es una herramienta muy completa para el cálculo y simulación de préstamos porque tiene funciones propias para calcular todos los componentes y variables que intervienen en los mismos. En la aplicación siguiente se utilizan las siguientes funciones para calcular el cuadro de amortización:

• PAGO(tasa;nper;va;vf;tipo). Para calcular la cuota constante
• PAGOINT(tasa;período;nper;va;vf;tipo). Para calcular los intereses de un periodo determinado
• PAGOPRIN(tasa;período;nper;va;vf;tipo). Para calcular la amortización de periodo n
• TIR(valores;estimar). Para estimar la tasa interna de rentabilidad
• TAE= (1+TIR)^m-1 (Igual a la TIR anualizada, donde m=frecuencia de pagos en el año)

La aplicación al final de esta entrada permite incluir un periodo de carencia parcial, en el que se pagan solo intereses. 

Además del cuadro de amortización, realiza dos gráficos. Uno con la evolución de los componentes de la cuota del préstamo, intereses y amortización, y otro con el saldo pendiente al final de cada periodo y el importe amortizado. 

Se puede observar el comportamiento decreciente de los intereses pagados y del saldo, y creciente de la cantidad amortizada, dado que la cuota de pago periódica ha de ser constante.

La explicación teórica y ejemplos prácticos de cómo confeccionar el cuadro de préstamos sistema francés se realiza en el siguiente libro, publicado en Amazon.

Matemáticas financieras en mapas mentales

Descargar: Simulacion-prestamos-sistema-frances.xlsx

Se recomienda descargar la aplicación y ejecutarla en Excel. No se garantiza el funcionamiento correcto en otros paquetes ofimáticos.

Estimación de la curva de aprendizaje

Cuando las personas, individualmente o mediante alguna forma de sociedad o empresa, iniciamos una actividad, normalmente necesitamos un periodo de aprendizaje hasta que se ejecuta con la perfección deseada.
Esto se manifiesta por ejemplo en la producción de un bien, en el que el tiempo o los costes son menores a medida que se realizan más unidades o ciclos, y además la tasa de disminución del tiempo es cada vez menor. Se puede establecer, entonces, una relación entre el tiempo que se tarda en producir cada unidad y el número de unidades de producción consecutivas, que denominamos: curva de aprendizaje. 

Su estimación y conocimiento es muy útil para la planificación de la cantidad de trabajo necesario, programar la producción, prever plazos de entrega, estimación de costes y presupuestos, etc.
Para obtener la curva de aprendizaje primero debemos elegir una función que se ajuste bien a los datos, y esta suele ser una función potencial de la forma tn=kn^r, (donde tn=tiempo de la unidad n; k=tiempo  de la primera unidad; n=la unidad producida; y r=exponente negativo que le da forma decreciente a la función.)
Los coeficientes de esta función los podemos estimar por MCO (Mínimos Cuadrados Ordinarios) a partir de los datos observados en el proceso productivo, previa transformación de la función potencial en lineal  mediante logaritmos Log(tn)=Log(k)+rLog(n).
Otra forma de cálculo es mediante la estimación de una tasa o porcentaje de aprendizaje constante (p) cuando se duplica la producción. Esta tasa se deduce de la función anterior mediante p=k2n^r/kn^r=2^r. O viceversa, conocida la tasa de aprendizaje se puede obtener el exponente de la función tn  r=log(p)/log(2).
La aplicación siguiente, a partir de una serie de observaciones, calcula la curva de aprendizaje por MCO mediante funciones de regresión Excel y deduce la tasa o porcentaje de aprendizaje a partir de la función potencial. Además, se realiza la estimación a partir de un  gráfico de puntos al que se agrega la tendencia potencial, que debe coincidir con la anterior.

Aplicación Excel: Curva de aprendizaje

Diagrama de Pareto

Como resultado del análisis de los factores, elementos o causas que contribuyen a un mismo efecto o resultado, observamos que en múltiples ocasiones unos pocos factores son responsables de la mayor parte de dicho efecto. Esto se conoce como principio de Pareto, en honor al economista italiano, nacido en París, que lo ha explicado en 1906. 

También se le conoce como regla del 80/20 basándonos en que el 80 % del resultado lo genera el 20 % de las causas, o como una ley de potencia por el tipo de relación matemática entre las variables efecto y causa. 

La aplicación práctica del análisis de Pareto se realiza mediante una comparación cuantitativa de las causas, ordenándolas en una tabla, de mayor a menor, según su porcentaje de contribución a un efecto determinado. Una vez ordenadas obtenemos los porcentajes acumulados con objeto de conocer el grado de concentración de las causas más importantes. La representación gráfica de la tabla indicada es el diagrama de Pareto que aporta una imagen intuitiva del análisis.
La aplicación Excel siguiente, a partir de la introducción de las causas o elementos con su frecuencia correspondiente, calcula la tabla y diagrama de Pareto.
El diagrama de Pareto descubre los aspectos prioritarios que hay que tratar para conseguir un objetivo o resolver un problema determinado, diferenciando los pocos factores vitales de los muchos factores útiles, que contribuyen al resultado. Es, por tanto, una herramienta útil tanto en las ciencias naturales como en las sociales, siendo algunos ejemplos de su aplicación el control de calidad, distribuciones de la renta, ventas, costes, producción, inventarios, etc.

Bajar Libro Excel: diagrama_pareto.xls

Calendario perpetuo para registro de datos contables con Excel

Este es un calendario anual con Excel que tiene la ventaja de que se puede convertir en perpetuo, simplemente modificando el año.

La aplicación ajusta automáticamente los días del año elegido a los de la semana que corresponda.

Con objeto de buscarle utilidad, al lado de cada día creamos una celda para registrar datos contables. Esto nos servirá para llevar un control de numerosos datos  como pueden ser: ventas, gastos, kilómetros, cotizaciones de acciones, precios, etc.

Al final de cada mes calcula el total del mes, la media diaria y la desviación típica.

También se puede utilizar para llevar el mismo registro contable, pero para años distintos. Para ello simplemente cambiamos en distintas hojas el año que corresponda.

Recomiendo descargar la aplicación y ejecutar en Excel de escritorio.

Descargar libro Excel: Calendario-perpetuo-datos-contables.xlsx

Revisión de préstamos a tipo de interés variable

En los préstamos hipotecarios y personales a tipo de interés variable se realiza una modificación anual o semestral (según esté pactado en el contrato), del tipo interés, que implica una variación de la cuota. La modificación se realiza sumando a un índice de referencia determinado el diferencial que tienen pactado entre la entidad de crédito y el prestatario. La nueva cuota se calcula en función del tipo de interés revisado, el n.º de pagos pendientes y el saldo a la fecha de revisión.

Aunque es habitual que la entidad prestamista comunique al prestatario el nuevo tipo de interés y la cuota resultante, es conveniente realizar su cálculo a modo de comprobación y de previsión de pagos.

Esto se puede realizar con la siguiente aplicación Excel, que incluye dos hojas, una para la revisión anual y otra para la semestral (las más usuales en España).

Libro Excel: Revisión de préstamos a tipo de interés variable

En el libro siguiente se explica con mapas mentales la teoría y ejemplos prácticos de matemáticas financieras utilizando fórmulas, calculadora financiera y Excel.

Echa un vistazo:  Matemáticas financieras en mapas mentales