Archivos por Etiqueta: Excel

El plan financiero, modelización en Excel y sus componentes

EL PLAN FINANCIERO, SUS COMPONENTES Y MODELIZACIÓN EN EXCEL

En el siguiente post se explicarán las partes fundamentales que debe tener un plan financiero, para posteriormente realizar una modelización en Excel del Plan Financiero, aplicado a un ejercicio práctico que será resuelto.

De forma estructural, las partes que todo plan financiero debe tener son las siguientes:

  1. Hipótesis, asunciones y limitaciones que tomamos en la modelización, y que pueden limitar, simplificar o acotar el alcance del plan financiero.
  2. Necesidades de financiación totales que tiene el proyecto.
  3. Mix de la financiación (deuda, accionistas, subvenciones, etc) y su coste promedio ponderado (WACC).
  4. Proyección de los principales estados financieros: Cuenta de resultados, balance de situación, estado de flujos de efectivo y variación del patrimonio neto. El número de años a proyectar dependerá de la tipología del proyecto, pero podemos asumir 3-4 años.
  5. Cálculo de los principales ratios financieros a nivel de: liquidez, actividad, solvencia, rentabilidad y valoración.
  6. Estimación de la rentabilidad económico-financiera del proyecto (Van, TIR, Payback) mediante el descuento de flujos de caja del accionista.
  7. Es una práctica común que además de proyectar el escenario que denominaremos como base o más probable, se proyecten escenarios optimistas y pesimistas (a nivel de la cuenta de resultados para ver en qué margen puede variar la cuenta de resultados a nivel de ventas y distintos niveles de beneficio (margen bruto, EBITDA, EBIT, EBT, Beneficio neto).

En el siguiente link se realiza una modelización en Excel del plan financiero en un caso práctico.

 

www.antonioalcocer.com

www.minds4change.org

Simulador de inversiones en Excel

SIMULADOR DE INVERSIONES EN EXCEL PARA PRODUCTOS FINANCIEROS Y FONDOS DE INVERSIÓN

A continuación os dejo un simulador en Excel para poder saber cuánto dinero tendré consolidado en un producto financiero (recomendable revisar el curso monográfico de matemáticas financieras y el curso de productos financieros e inversión como por ejemplo un fondo de inversión donde conozco:

  1. Cantidad inicial a invertir.
  2. Periodo de inversión.
  3. Rentabilidad nominal anual.
  4. Costes anuales del producto: 3%.

Este simulador también tiene en cuenta los costes anuales del producto, calculados como un porcentaje sobre los derechos consolidados en el fondo de inversión al finalizar el año. Como nota aclaratorio comentar que por lo general los productos financieros suelen tener liquidaciones diarias con aplicación de rentabilidad y comisiones diarias.

Así por ejemplo, si invertimos €10.000 a un plazo de 10 años con una rentabilidad nominal anual del 30%, unos costes anuales de gestión y depósito del fondo de inversión del 1,75%, los derechos consolidados netos (es decir, deducidos los costes anuales del producto) antes de pagar impuestos serán de €115.547. Sin embargo, nuestro poder adquisitivo, por el valor temporal del dinero, no será por valor de €115.547, ya que existe el efecto de la inflación, que si asumimos es el 3% anual, hace que nuestro poder adquisitivo de [€115.547, t=10 años] sea igual al de [€85.978, hoy t=0 años].

Esto quiere decir que compraremos los mismo dentro de 10 años con €115.547 que con €85.978 si el coste de la vida es del 3% anual.

Si además tenemos en cuenta la parte fiscal, los fondos de inversión pagan impuestos cuando se venden y general beneficios, siendo los impuestos a pagar del 21% sobre la plusvalía/beneficios generados. En este caso:

  • Costes del producto financiero: €8.861.
  • Beneficios: €115.547-€10.000-€8.861=€96.886.
  • Impuestos a pagar: 21%*€96.886=€20.346.
  • Dinero neto recibido por el inversor después de impuestos: €76.540.

Saludos cordiales,

Antonio Alcocer

www.antonioalcocer.com

www.minds4change.org 

Calculadora de préstamo bancario método amortización francés

CALCULADORA DE PRÉSTAMOS EN EXCEL SEGÚN EL MÉTODO DE AMORTIZACIÓN FRANCÉS

Hola a todos,

Aquí os adjunto un aplicación/macro para Excel que calcula las cuotas a pagar, gastos de constitución y TAE (Tasa Anual Equivalente) de un préstamo bancario de cualquier tipo que se amortiza por el método francés, es decir, las cuotas que se pagan son siempre constantes. Es importante resaltar por tanto, que en un préstamo francés la cuota está formada por el principal que se devuelve al banco (es decir, dinero que nos ha prestado el banco) e intereses (ganancia que obtiene el banco por prestarnos el dinero), y aunque ambas cantidades van cambiando a lo largo de la vida del préstamo, su suma (que es la cuota que se paga) es constante.

Para poder hacer funcionar la aplicación, tenéis que tener habilitadas las macros en Excel. Os váis al menú: Herramientas, Macro, Seguridad, eligiendo un nivel de seguridad medio. Luego abrís el archivo Excel y cuando os pregunte en una pantalla habilitáis las macros.

Las variables para calcular la cuota del préstamo son:

  1. Introduzca la cantidad a financiar en euros. Ej. 250.000. Es decir, estaríamos pidiendo un préstamo a la entidad bancaria por valor de €250.000.
  2. Número de años de duración del préstamo. Ej. 35.
  3. Introduzca el interés nominal anual del préstamo en %. Ej. 5. Por lo general los préstamos bancarios están referenciados a un tipo de interés variable que se llama EURÍBOR más un tipo fijo (llamado diferencial y que dependiendo de la entidad bancaria y la solvencia del cliente será mayor o menor). Este interés fluctúa diariamente, pero para cálculos hipotecarios se debería usar un valor promedio del 5%. Téngase en cuenta que el interés nominal anual no es el coste anual del préstamo o TAE. Por favor consulte el curso de matemáticas financieras para aclarar los conceptos.
  4. Con qué periodicidad se pagarán las cuotas: 1. Mensual, 2. Trimestral, 3. Semestral. 4. Anual. Para el ejemplo hemos elegido pago mensual de la cuotas, marcamos “1”.
  5. Introduzca los gastos de constitución y apertura. Son un % sobre el importe financiado. Ej. 0,5. Es decir, la entidad bancaria nos cobra una comisión del 0,5% sobre los €250.000 financiados (€1.250) por estudiar y formalizar el préstamo bancario.

Para esta simulación la cuota mensual a pagar sería de 1.261€ mensuales, con un coste total de la operación del 5,13% TAE (que como podemos ver no es del 5%, porque adicionalmente hay costes de formalización del préstamo). Adicionalmente la aplicación os muestra un gráfico con la cantidad de principal que se amortiza e intereses bancarios pagados a lo largo de la vida de préstamo.

Para entender cuál es la lógica programada en la macros, debemos comprender:

  1. Al resolver un préstamo, lo primero que debemos calcular es la cuota a pagar. En todo préstamo hay que resolver la ecuación financiera siguiente: la cantidad que nos presta el banco en t=0, en este caso €250.000 debe ser igual a la deuda que contraemos con en banco en t=0. Si la duración del préstamo es 35 años (35*12=420 cuotas mensuales constantes), al banco le debemos 420 cuotas mensuales, que todas ellas llevadas a t=0 deben tener un valor de €250.000. Por matemáticas financieras y valor temporal del dinero, moveremos flujos monetarios individuales colocados en distintos instantes de tiempo, con la fórmula del interés compuesto usando un tipo de interés nominal del 5%. El resultado de esta operación es la siguiente fórmula:
    • Capital financiado = Cuota constante?* [1-(1+i)^(-n*t)]/i
  2. Para nuestro caso concreto, como los pago son mensuales, el interés nominal a usar debe ser el nominal mensual (5%/12) y el tiempo en meses (12 meses * 35 años = 420 meses).
  3. Aplicando la fórmula obtenemos:
    • 250.000=Cuota*[1-(5%/12)^(-420)]/(5%/12)
  4. Despejando la ecuación, obtenemos una cuota mensual de €1.250.
  5. A continuación, debemos calcular los intereses pagados el primer mes: (5%/12)*250.000. Y la cantidad amortizada de principal el primer mes será: 1.250-(5%/12)*250.000. La deuda pendiente con el banco al finalizar el primer mes ya no será €250.000, sino 250.000-cantidad amortizada (que no es la cuota!) de 1.250-(5%/12).
  6. Este proceso lo repetimos de forma iterativa durante los 420 periodos.
  7. Para comprobar que hemos resuelto correctamente el préstamo, la cantidad de principal que debemos al banco en t=35 años=420 meses, debe ser €0.

Cualquier comentario es bienvenido!

Saludos y espero que os sea de utilidad.

Antonio Alcocer

www.antonioalcocer.com

www.minds4change.org

 

error: Content is protected !!