EXCEL

FINANCIERO

UNIDAD I
1.- Funciones Financieras.
2.- Función Pago.
3.- Métodos de Amortización.
4.- Actualización y Capitalización.
5.- Periodicidad y Tasas.
Curso: Excel Financiero

FUNCIONES FINANCIERAS EN EXCEL

FUNCION PAGO
Esta función financiera permite controlar dos factores financieros:

1. FACTOR DE RECUPERACION DE CAPITAL

Permite conver�r un capital inicial en una serie de pagos constantes y uniformes.

2. FACTOR DE DEPOSITO DE FONDO DE AMORTIZACIONES

Permite conver�r un valor en el futuro en una serie de depósitos contantes y uniformes.

FORMATO : =PAGO(TASA , NPER, VA, VF, TIPO)

TASA.- Tasa de interés periódica.

NPER.- Es la can�dad de periodos con los que se realizará la operación financiera.

VA.- Es el capital inicial o valor actual, el valor del dinero a inicio de operaciones.

VF.- Es el capital final o valor futuro, es el valor del dinero al finalizar operaciones-

2
Curso: Excel Financiero

TIPO.- Soporta los valores 0 ó 1, será 0 cuando se realice los pagos a fin de periodo y 1 cuando el pago se
realice a inicio de operaciones (Vencido o Adelantado).

Ejercicio 1 – Factor de Recuperación de Capital

Ejercicio 2 – Factor de Depósito de Fondo de Amor�zaciones

3
Curso: Excel Financiero

METODO DE AMORTIZACION FRANCES

También conocido como el método de las cuotas constantes y uniformes, que es el método
comercial, ósea el que u�lizan las en�dades financieras en el mercado nacional, para
construirlo u�lizaremos las siguientes funciones:
1. Pagoprin(Amor�zaciones)
2. Pagoint(Intereses)

FUNCION PAGOPRIN
Esta función permite calcular el capital pagado en una determinada cuota, a esto se le conoce
como la amor�zación del capital, recuerde que en este método las amor�zaciones varían de
una cuota a la otra.

FORMATO : =PAGOPRIN(TASA, PERIODO, NPER, VA, VF, TIPO )

TASA.- Tasa de interés periódica.

PERIODO.- Es el periodo para el que se desea calcular la amor�zación, recuerde que esta varía de una cuota a
otra.

NPER.- Es la can�dad de periodos con los que se realizará la operación financiera.

VA.- Es el capital inicial o valor actual, el valor del dinero a inicio de operaciones.

VF.- Es el capital final o valor futuro, es el valor del dinero al finalizar operaciones-

TIPO.- Soporta los valores 0 ó 1, será 0 cuando se realice los pagos a fin de periodo y 1 cuando el pago se realice
a inicio de operaciones (Vencido o Adelantado).

Ejercicio 3

Basado en el ejercicio 1, calcular el capital devuelto (Amor�zación) en las cuotas 1 , 5 , 9 y 12.

4
Curso: Excel Financiero

FUNCION PAGOINT
Esta función permite calcular el interés pagado en una determinada cuota, recuerde que en
este método los intereses varían de una cuota a la otra, esta función es complementaria a la
anterior.

FORMATO : =PAGOINT(TASA, PERIODO, NPER, VA, VF, TIPO )

TASA.- Tasa de interés periódica.

PERIODO.- Es el periodo para el que se desea calcular el interés, recuerde que esta varía de una cuota a otra.

NPER.- Es la can�dad de periodos con los que se realizará la operación financiera.

VA.- Es el capital inicial o valor actual, el valor del dinero a inicio de operaciones.

VF.- Es el capital final o valor futuro, es el valor del dinero al finalizar operaciones-

TIPO.- Soporta los valores 0 ó 1, será 0 cuando se realice los pagos a fin de periodo y 1 cuando el pago se
realice a inicio de operaciones (Vencido o Adelantado).

5
Curso: Excel Financiero

Ejercicio 4

Basado en el ejercicio 1, calcular el interes pagado en las cuotas 1 , 5 , 9 y 12.

U�lizando las formulas aprendidas, ahora puedes construir el cuadro de amor�zaciones bajo el
método francés, recuerda los pagos son iguales, mientras las amor�zaciones y los interés varían.

6
Curso: Excel Financiero

METODO DE AMORTIZACION ALEMAN

Este método es conocido como el método de las cuotas decrecientes, y se desarrolla en Excel
con operaciones matemá�cas básicas, como lo veremos en el siguiente ejercicio.

1. La amortización se ob�ene de dividir el capital inicial entre la can�dad de cuotas.

Amor�zación = 10000/12 = 833.33

2. El interés se calcula aplicando la tasa de interés periódica a cada saldo deudor.

Interés 1 = 1.5%*10000

Interés 2 = 1.5% * 9166.67

Interés 3 = 1.5% * 8333.33

3. El Pago Mensual es la suma de amor�zación con el interés en cada cuota.

4. El saldo se ob�ene de descontar la amor�zación a cada saldo pendiente de pago.

7
Curso: Excel Financiero

METODO DE AMORTIZACION AMERICANO

En este método no se devuelve el capital en cada cuota, ósea no se amor�za el capital, solo
se paga el interés generado en cada cuota, solo se devuelve el capital al finalizar la operación
financiera, también se construye con funciones matemá�cas de Excel, como lo veremos a
con�nuación:

1. En este caso no hay amor�zación hasta la úl�ma cuota en la que se devuelve el capital
prestado.
2. El interés se ob�ene de aplicar la tasa periódica al saldo deudor.
3. El pago mensual se ob�ene de sumar la amor�zación con el interés.

8
Curso: Excel Financiero

FUNCION VF
Esta función financiera permite controlar 2 factores financieros:

1. FACTOR SIMPLE DE CAPITALIZACION

Permite conver�r un capital inicial en capital final, ósea llevar un valor desde inicio de
operaciones hacia el futuro al finalizar el ejercicio financiero.

2. FACTOR DE CAPITALIZACION DE UNA SERIE

Permite conver�r una serie de depósitos constantes y uniformes en capital final.

FORMATO : =VF(TASA , NPER, PAGO, VA, TIPO)

TASA.- Tasa de interés periódica.

NPER.- Es la can�dad de periodos con los que se realizará la operación financiera.

PAGO.- Es el depósito periódico que se realiza en una cuenta y que se desea capitalizar.

VA.- Es el capital inicial o valor actual, es el valor del dinero al iniciar operaciones.

9
Curso: Excel Financiero

TIPO.- Soporta los valores 0 ó 1, será 0 cuando se realice los pagos a fin de periodo y 1 cuando el pago se
realice a inicio de operaciones (Vencido o Adelantado).

Ejercicio 7 – Factor Simple de Capitalización

Ejercicio 8 – Factor de Capitalización de una Serie

10
Curso: Excel Financiero

FUNCION VA
Esta función financiera permite controlar 2 factores financieros:

1. FACTOR SIMPLE DE ACTUALIZACION

Permite conver�r un capital final en capital inicial, ósea llevar un valor desde el fin de
operaciones hacia el inicio de operaciones, re�rándole los intereses cargados.

2. FACTOR DE ACTUALIZACION DE UNA SERIE

Permite conver�r una serie de pagos constantes y uniformes en capital inicial, re�rando
los intereses cargados.

FORMATO : =VA(TASA , NPER, PAGO, VF, TIPO)

TASA.- Tasa de interés periódica.

NPER.- Es la can�dad de periodos con los que se realizará la operación financiera.

11
Curso: Excel Financiero

PAGO.- Es el pago periódico que se realiza y que se desea actualizar.

VF.- Es el capital final o valor en el futuro, es el valor del dinero al finalizar operaciones.

TIPO.- Soporta los valores 0 ó 1, será 0 cuando se realice los pagos a fin de periodo y 1 cuando el pago se
realice a inicio de operaciones (Vencido o Adelantado).

Ejercicio 7 – Factor Simple de Actualización

Ejercicio 8 – Factor de Actualización de una Serie

12
Curso: Excel Financiero

FUNCION NPER
Esta función financiera nos permi�rá calcular la can�dad de cuotas con las que se debe
desarrollar una operación financiera.

FORMATO : =NPER(TASA , PAGO, VA, VF, TIPO)

Ejercicio 9

13
Curso: Excel Financiero

FUNCION TASA
Esta función financiera nos permi�rá calcular la tasa de interés aplicada a una operación
financiera.

FORMATO : =TASA(NPER , PAGO, VA, VF, TIPO)

Ejercicio 10

14
EXCEL
FINANCIERO

UNIDAD II
1.- Buscar Objetivo.
2.- Flujo de Caja.
3.- Escenarios.
4.- Análisis de Sensibilidad.
Curso: Excel Financiero

BUSCAR OBJETIVO

USAR BUSCAR OBJETIVO PARA ENCONTRAR EL RESULTADO DESEADO MEDIANTE EL

AJUSTE DE UN VALOR DE ENTRADA
Si conoce el resultado que desea de una fórmula, pero no está seguro de qué entrada valor la
fórmula debe obtener dicho resultado, use la caracterís�ca Buscar obje�vo. Por ejemplo,
suponga que necesita algún préstamo. Saber la can�dad de dinero que desee, ¿cuánto
�empo desea terminará de pagar el préstamo y cuánto se puede permi�r cada mes. Puede
usar Buscar obje�vo para determinar qué �po de interés que se debe proteger para cumplir
con los obje�vos del préstamo.

NOTA: Buscarobje�vo sólo funciona con un valor de entrada. Si desea aceptar más de una
entrada de valor; Por ejemplo, el importe del préstamo y el importe del pago mensual de un
préstamo, use el complemento Solver.

EJEMPLO

Dado que va a calcular la tasa de interés del préstamo necesaria para cumplir con los
obje�vos, use la función PAGO. La función PAGO calcula un importe de pago mensual. En este
ejemplo, el importe del pago mensual es el obje�vo que busca.

Preparar la hoja de cálculo

1. Abra una nueva hoja de cálculo en blanco.

2. En primer lugar, agregue algunos de los rótulos en la primera columna para facilitar la
lectura de la hoja de cálculo.

a) En la celda A1, escriba Importe del préstamo.

b) En la celda A2, escriba período en meses.

c) En la celda A3, escriba la tasa de interés.

d) En la celda A4, escriba pago.

3. A con�nuación, agregue los valores que sabe.

a) En la celda B1, escriba 100000. Esta es la can�dad que desea tomar prestado.

b) En la celda B2, escriba 180. Este es el número de meses que desea pagar el préstamo.

NOTA: Aunque se sabe el importe del pago que desee, no escriba un valor, porque el
importe del pago es el resultado de la fórmula. En su lugar, agregue la fórmula en la
hoja de cálculo y especifique el valor de pago en un paso posterior, al usar Buscar
obje�vo.

4. A con�nuación, agregue la fórmula para el que �ene un obje�vo. Por ejemplo, use la
función PAGO :

16
Curso: Excel Financiero

5. En la celda B4, escriba =PAGO(B3/12,B2,B1,,0). Esta fórmula calcula el importe del pago. En
este ejemplo, desea pagar cada mes de 900 $. No escriba dicha can�dad aquí, ya que desea
usar Buscar obje�vo para determinar la tasa de interés y Buscar obje�vo requiere que
comience con una fórmula.

La fórmula hace referencia a las celdas B1 y B2, que con�enen valores que especificó en los
pasos anteriores. La fórmula también hace referencia a la celda B3, que es donde especificará
que buscar obje�vo poner la tasa de interés. La fórmula divide el valor de B3 por 12 porque
no se especifica un pago mensual, y la función PAGO presupone una tasa de interés anual.

Porque no hay ningún valor en la celda B3, Excel presupone una tasa de interés de 0% y, con
los valores de ejemplo, devuelve un pago de 555.56 $. Puede omi�r ese valor por ahora.

USAR BUSCAR OBJETIVO PARA DETERMINAR EL TIPO DE INTERÉS

1. En la pestaña Datos, en el grupo Herramientas de datos, haga clic en Análisis de
hipótesis y, a con�nuación, en Buscar obje�vo.
2. En el cuadro definir la celda, escriba la referencia de la celda que con�ene el fórmula que
desea resolver. En el ejemplo, esta referencia es la celda B4.

3. En el cuadro valor, escriba el resultado de la fórmula que desee. En el ejemplo, esta es sería
-900. Tenga en cuenta que este número es nega�vo, porque representa un pago.

4. En el cuadro cambiando la celda, escriba la referencia de la celda que con�ene el valor que
desea ajustar. En el ejemplo, esta referencia es la celda B3.

NOTA: La fórmula en la celda especificada en el cuadro definir la celda debe hacer referencia
a la celda que buscar obje�vo cambia.

5. Haga clic en Aceptar.

Buscar obje�vo se ejecuta y produce un resultado, como se muestra en la siguiente

ilustración.

6. Por úl�mo, dar formato a la celda obje�vo (B3) para que muestre el resultado como un
porcentaje.

a) En la pestaña Inicio, en el grupo Número, haga clic en la opción Porcentaje.

b) Haga clic en Aumentar decimales o en Disminuir decimales para establecer la

can�dad de posiciones decimales.

17
Curso: Excel Financiero

FLUJO DE CAJA EN EXCEL

El flujo de caja, también conocido como cash flow, registra el dinero que entra y sale de un
negocio en un período determinado. Este informe financiero toma en cuenta los ingresos por
ventas y los egresos por el pago de cuentas de manera que podamos conocer la liquidez de
la empresa.

Cuando llega el momento de comprar mercancía para nuestro negocio, o de hacer el pago de
algún servicio, no es posible hacerlo con la promesa de que pronto cobraremos algunas
deudas sino que es necesario tener dinero para realizar el pago. El flujo de caja nos ayudará
a estar seguros de que al momento de “abrir la billetera” del negocio encontraremos dinero
para pagar.

Si un negocio no �ene dinero suficiente para soportar la operación de la empresa, se dice que
ya no es solvente y en caso de que no se resuelva pronto el problema de la solvencia
económica se conver�rá en un candidato para la bancarrota. Por esa razón es importante
tener siempre el dinero suficiente para asegurar el pago a los proveedores, empleados, y
cubrir los gastos de operación de la empresa.

PARA QUÉ SIRVE EL FLUJO DE CAJA

El hecho de saber cuánto dinero podemos u�lizar en un período específico de �empo nos
permite tomar decisiones como las siguientes:

Decidir la can�dad de mercancía o materia prima que podemos comprar al contado

o si es necesario solicitar un crédito.

18
Curso: Excel Financiero

Determinar si se debe cobrar a los clientes en efec�vo o si es posible otorgar crédito.

Conocer la fac�bilidad de pagar deudas adquiridas o si será necesario pedir un

refinanciamiento o tramitar un préstamo.

Si se �ene un excedente de dinero se podría decidir inver�rlo en el crecimiento del

negocio, como puede ser la compra de nueva maquinaria.

CÓMO ELABORAR UN FLUJO DE CAJA

Para poder elaborar un flujo de caja es necesario tener los libros contables de la empresa ya
que necesitamos información sobre los ingresos y egresos. El informe financiero que
crearemos tendrá la siguiente estructura:

1. Saldo inicial. Es necesario par�r de un saldo inicial de caja.

2. Ingresos. Incluye el ingreso obtenido por ventas, o los cobros realizados a créditos
previamente otorgados, ingresos por venta de ac�vos fijos u otras ac�vidades
dis�ntas al giro de la empresa.

3. Egresos. Las salidas de efec�vo que tendrá la empresa por compras al contado, gastos
administra�vos y de ventas, pago de impuestos, pago de nómina.

4. Flujo de caja económico. Dinero en efec�vo sin considerar los préstamos.

5. Financiamiento. Cuotas a pagar por préstamos adquiridos.

6. Flujo de caja financiero. El resultado de descontar el efecto del financiamiento al flujo
de caja económico.

19
Curso: Excel Financiero

ANÁLISIS DE SENSIBILIDAD EN EXCEL

El análisis de sensibilidad es un método que nos permite visualizar de manera inmediata las
ventajas y desventajas económicas de un proyecto. Este método es muy u�lizado para
iden�ficar el proyecto que nos dará los mejores rendimientos.

EJEMPLO DE UN ANÁLISIS DE SENSIBILIDAD

Supongamos que mi vecino me ha propuesto crear un nuevo negocio que

implica una inversión inicial de 100,000 dólares. Después de hacer el análisis
profundo del proyecto hemos es�mado que tendremos ventas por 85,000
dólares en el primer año.
Parece ser un negocio redituable, pero debemos considerar también los
gastos fijos que serán de 35,000 dólares y los gastos variables que serán de
25,000 dólares. Con estos gastos tendremos una ganancia de 20,000
dólares.

Como sabemos, todo proyecto �ene variables que no podemos controlar y que
inevitablemente irán cambiando con el �empo. En nuestro ejemplo, serán las ventas y los
gastos variables los que pueden variar y por lo tanto modificar nuestra ganancia.

En la imagen superior puedes observar que la fórmula para calcular las ganancias depende de
los valores de las ventas y de los gastos fijos y variables. Si podemos construir varios
escenarios donde modifiquemos los montos que son variables (ventas y gastos variables)
entonces podremos saber fácilmente la manera en que serán afectadas las ganancias en cada
uno de los casos.

CREAR EL ANÁLISIS DE SENSIBILIDAD EN EXCEL

Antes de crear el análisis de sensibilidad en Excel debo agregar los datos para los cuales se
crearán los diferentes escenarios en Excel. Como puedes observar, en la fila 7 he colocado
diferentes montos de venta y en la columna B (por debajo de los cálculos previos) he colocado
diferentes montos de gastos variables.

20
Curso: Excel Financiero

Para poder crear el análisis de sensibilidad en Excel debemos seleccionar el

rango de datos B7:F11 y pulsar el botón Análisis Y si que se encuentran en la ficha Datos
y posteriormente seleccionar el comando Tabla de datos.

En la opciónCelda de entrada (fila) colocaré la referencia a la celda que con�ene el

monto original de ventas (B4) y como Celda de entrada (columna) especificaré la
celda que con�ene los gastos variables (B6). Al pulsar el botón Aceptar se realizarán los
cálculos para obtener las ganancias para cada una de las combinaciones de valores indicados.

21
Curso: Excel Financiero

Nota:Por motivos ilustrativos he dado formato de moneda a las celdas

y he colocado en rojo los números negativos pero Excel no hará eso
automáticamente por nosotros.
Con esta tabla de datos podemos analizar y llegar a conclusiones muy interesantes sobre
nuestra información. Podemos observar que si las ventas fueran en realidad por 65,000
dólares y los gastos variables subieran a 35,000 dólares entonces comenzaríamos a tener
pérdidas en el negocio.

Por otro lado puedes observar que el punto de equilibrio financiero se da cuando las ventas
son de 75,000 dólares y los gastos variables son de 40,000 dólares dejándonos sin pérdidas ni
ganancias. En fin, podemos pasar �empo considerable analizando la información para poder
decidir si vale la pena inver�r nuestro dinero en este negocio.

Lo que Excel ha realizado tras bambalinas es que para cada posible valor de ventas y de gastos
variables que hayamos especificado ha calculado la fórmula de ganancias que está en la celda
B7. Esto es un gran beneficio para nosotros porque en lugar de ir remplazando los valores de
las celdas B4 y B6 para ver el comportamiento en las ganancias Excel realizará todos los
cálculos en un solo paso y los colocará en la tabla de datos.

El análisis se tornará interesante si para cada proyecto de inversión que tengas realizas
un análisis de sensibilidad en Excel para compararlos entre ellos y decidir por
la alterna�va que represente el menor riesgo posible y que tenga la mayor rentabilidad.

22