Manual de funciones financieras en excel

En este tema hablaremos de diversas funciones que podemos aplicar en la vida diaria con ayuda de excel.

Las Funciones Financieras en Excel este trabajo se elabora  para que practiquemos las diferentes funciones financieras que contiene Excel ya que con anterioridad se ha maneja otras funciones, que son las básicas, pero hay personas que no saben usar bien lo que es el Excel y piensan que solo con hacer tablas, gráficos etc., es todo lo que contiene Excel, pero aquí vemos que no sirve para ya que tiene infinidad de comandos para trabajar en ellos, y para esto la finalidad será que podremos interactuar con diferente tipos de funciones, en las cuales no pensaremos que si no sabemos en qué aplicarlas no las podremos practicar, porque en Excel en el comando donde vienen todas las funciones financieras existe un icono que dice ayuda y ahí nos muestra la definición de la función, cuales son los datos que necesitaremos y vienen un pequeño ejemplo de cómo lo haremos, y es ahí donde nos damos una idea en donde aplicaremos las funciones financieras de Excel, y todo es más fácil ya que en Excel se aplica la formula se seleccionan los datos y se realiza el problema.

Ahora veremos 5 funciones aplicadas en diferentes casos

Función RRI

Descripción: Devuelve una tasa de interés equivalente para el crecimiento de una inversión.

Sintaxis:

La sintaxis de la función RRI tiene los siguientes argumentos:

1.    Nper    Obligatorio. Nper es el número de períodos de la inversión.

2.    Va    Obligatorio. Va es el valor actual de la inversión.

3.    Vf    Obligatorio. Vf es el valor futuro de la inversión.

RRI devuelve los valores Nper (número de períodos), va (valor actual) y vf (valor futuro) determinados de la tasa de interés, calculados con la ecuación siguiente:

Ejemplo:

El Sr. Ricardo Sánchez quiere hacer una inversión de $2'000,000.00 a 48 meses en la institución bancaria Bancomer pero ¿Quiere saber cuánto será la tasa de interés?

Tabla No. 1

Tabla No. 2

Función cupon.días

Descripción: Devuelve el número de días del período (entre dos cupones) donde se encuentra la fecha de liquidación.

Sintaxis:

La sintaxis de la función CUPON.DIAS tiene los siguientes argumentos (argumento: valor que proporciona información a una acción, un evento, un método, una propiedad, una función o un procedimiento.):

·         Liquidación: Obligatorio. La fecha de liquidación del valor bursátil. La fecha de liquidación del valor bursátil es la fecha posterior a la fecha de emisión en la que el comprador adquiere el valor bursátil.

·         Vencto: Obligatorio. La fecha de vencimiento del valor bursátil. La fecha de vencimiento es aquella en la que expira el valor bursátil.

·         Frecuencia: Obligatorio. El número de pagos de cupón al año. Para pagos anuales, frecuencia = 1; para pagos semestrales, frecuencia = 2; para pagos trimestrales, frecuencia = 4.

·         Base: Opcional. Determina en qué tipo de base deben contarse los días.

Ejemplo:

La Srta. Marisol Bernat realizo un préstamo de un crédito, al banco de HSBC, donde su fecha de liquidación es el 20/01/17, con fecha de vencimiento al día 10/07/17 en donde sus pagos serán trimestrales ya que es a 2 años y la base será de 1. Pero ella quiere saber ¿cuántos días del periodo se le devolverán al final del crédito?

Tabla No.1

                                                                                 

Tabla No. 2

Función tasa nominal.

Descripción: Devuelve la tasa de interés nominal anual si se conocen la tasa efectiva y el número de períodos de interés compuesto por año.

Sintaxis:

La sintaxis de la función TASA.NOMINAL tiene los siguientes argumentos (argumento: valor que proporciona información a una acción, un evento, un método, una propiedad, una función o un procedimiento.):

Ø  Tasa_efectiva    Obligatorio. La tasa de interés efectiva.

Ø  Núm_per_año    Obligatorio. El número de períodos de interés compuesto por año.

Ejemplo:

Un maestro de la UJAT realizo un préstamo en la institución bancaria de HSBC, y él quiere saber ¿Cuánto de interés nominal anual se le devolverá?

Tabla No. 1

Tabla No.2

Función pago.int.entre.

Descripción: Devuelve la cantidad de interés pagado de un préstamo entre los argumentos per_inicial y per_final.

Sintaxis:

La sintaxis de la función PAGO.INT.ENTRE tiene los siguientes argumentos (argumento: valor que proporciona información a una acción, un evento, un método, una propiedad, una función o un procedimiento.):

ü  Tasa    Obligatorio. La tasa de interés.

ü  Núm_per    Obligatorio. El número total de períodos de pago.

ü  Vp    Obligatorio. El valor actual.

ü  Per_inicial    Obligatorio. El primer período del cálculo. Los períodos de pago se numeran comenzando por 1.

ü  Per_final    Obligatorio. El último período del cálculo.

ü  Tipo    Obligatorio. El momento del pago. 

Ejemplo: Una Sra. pide un préstamo de $24,000.00 a la institución de Banco Azteca  con una tasa de interés del 3% en un periodo de 12 meses y lo que se le devolverá de interés entre el periodo inicial 1 y el periodo final 1 y su tipo es 0. Dicho Sra. ¿Quiere saber cuanto de interés se le devolverá en los dichos periodos?

Tabla No. 1

Tabla No.2

Función Int.efectivo.

Descripción: Devuelve la tasa de interés anual efectiva, si se conocen la tasa de interés anual nominal y el número de períodos compuestos por año.

Sintaxis: La sintaxis de la función INT.EFECTIVO tiene los siguientes argumentos (argumento: valor que proporciona información a una acción, un evento, un método, una propiedad, una función o un procedimiento.):

§  Tasa_nominal    Obligatorio. Es la tasa de interés nominal.

§  Núm_per_año    Obligatorio. Es el número de períodos compuestos por año.

Ejemplo:

En base al maestro de la UJAT que realizo un préstamo en la institución bancaria de HSBC, él ya sabe cuál fue su tasa nominal ahora quiere saber ¿Cuánto será la tasa de interés anual efectiva que le devolverán?

Tabla No. 1

Tabla No. 2

A lo largo de la presente investigación se logró demostrarse cómo utilizar y que tan importantes son las funciones financieras, en la vida ya que todos en algún momento hemos solicitado un préstamo, un crédito etc. Pero nunca nos hemos puesto a pensar, como calcular la cantidad de intereses que nos pueden devolver al finalizar los plazos. O el número de periodos que el banco nos puede devolver al finalizar.

Lo que podemos concluir aquí en esta investigación que se realizó, pues que es muy importante que sepamos el manejo de Excel y beneficios nos proporciona, y pues podemos ayudarnos en Excel para cuando tengamos un préstamo queramos saber qué es lo que nos van a devolver, y afirmar si el banco está cumpliendo con lo que yo tengo , por eso tenemos que tener en cuenta estos métodos.

(HSBC, s.f.)

(excel)

Solver como una herramienta y funciones financieras en Excel

En este tema hablaremos de ejemplos reales de amortizaciones.
Habran dos formas de realizar las amortizaciones:

• La forma manual que la podemos hacer en cualquier lado. Ya sea en un cuaderno etc.:Ñ

Lo que tenemos que tener para hacer es tener los datos que nos proporciona el Banco como lo es:

• El monto del préstamo
• El numero de periodos
• y por ultimo la tasa de interés%

 Para sacar el pago mensual en excel existe un icono que tiene una fx se oprime y hay un recuadro para buscar formulas y en ese recuadro se pone la palabra PAGO se le da enter y queda otro recuadro donde hay una serie de campos que hay que llenar. Como se muestra en la imagen de abajo.

y ya de ahí solo se va llenando la tabla:

• Fecha: en que fecha les da el banco para ir dando las mensualidades
• Periodo: Cuantos periodos son
• Pago mensual: El que se saca con la formula de fx
• Saldo: Es el monto del préstamo
• Interés: Se hace con el monto del préstamo * la tasa de interés y eso da el interés 
• Capital amortizado: es la resta de del pago mensual con el interés viene siendo el capital amortizado.
• Saldo: se va descontando de el monto total - el capital amortizado.

Es muy fácil hacerlo.

Y la segunda es utilizando las formulas de Excel

Es casi lo mismo nada mas que este método es mas fácil ya que solo meten formula. bueno las únicas celdas que quedan igual son:

1. Fecha
2. Periodo
3. Pago mensual

En donde cambia es las otras tres:

• Interés: se oprime la función fx y pondrá en el recuadro la palabra PAGO.INT se vera a sí como en la foto:

• Capital amortizado: se oprime igual la función fx y se pone en el recuadro la palabra PAGO.PRIN y aparecerá un recuadro como el que se vera en la imagen de abajo.

• Saldo: solo se resta el saldo - capital amortizado 

Y aquí les dejare otro ejemplo que es lo mismo con los dos métodos:

A continuación veremos otra parte importante de los prestamos....

• El primer tema del que hablaremos y tendremos ejemplos reales:

VALOR ACTUAL: es un procedimiento que permite calcular el valor presente de un determinado número de flujos de caja futuros, originados por una inversión. La metodología consiste en descontar al momento actual (es decir, actualizar mediante una tasa) todos los Flujos de caja (en inglés cash-flow) futuros o en determinar la equivalencia en el tiempo 0 de los flujos de efectivo futuros que genera un proyecto y comparar esta equivalencia con el desembolso inicial. Dicha tasa de actualización (k) o de descuento (d) es el resultado del producto entre el coste medio ponderado de capital (CMPC) y la tasa de inflación del periodo. Cuando dicha equivalencia es mayor que el desembolso inicial, entonces, es recomendable que el proyecto sea aceptado.

Una de las funciones financieras, calcula el valor actual de un préstamo o una inversión a partir de una tasa de interés constante. Puede usar VA con pagos periódicos y constantes (como una hipoteca u otro préstamo), o con un valor futuro que sea su objetivo de inversión.

 VALOR FUTURO: 

Es la cantidad de dinero que alcanzará una inversión en alguna fecha futura al ganar intereses a alguna tasa compuesta.

una de las funciones financieras, calcula el valor futuro de una inversión a partir de una tasa de interés constante. Puede usar VF con pagos periódicos constantes o con un único de suma fija

• PAGO:

Una de las funciones financieras, calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante

• TASA DE INTERÉS:

Es la cantidad que se abona en una unidad de tiempo por cada unidad de capital invertido. También puede decirse que es el interés de una unidad de moneda en una unidad de tiempo o el rendimiento de la unidad de capital en la unidad de tiempo

                                                    

                                                                                                                               (Wicksell, 17)

EJERCICIO: 

Se realizo un ejercicio en base a las definiciones. Este ejercicio  fue en base a el precio de titulación automática de la Universidad IEU se tomo de referencia tres bancos para ver que tasa de rendimiento dan al ahorrar o invertir en ellos. Y de las tres instituciones tendremos que elegir una la que mas nos convenga.

Como aquí vemos banco Azteca nos ofrece una tasa de rendimiento de 4.20%. Los $26,000.00 de Valor Futuro son $24,000.00 es de la titulación y $2000.00 de gastos aparte. los 12 es el periodo que es a un año. y mi valor presente que buscábamos es que tenemos que depositar al banco $15869.41 para que dentro de 12 meses tengamos $26,000.00.

Esta es otra institución que es BANAMEX nos ofrece una tasa de rendimiento del 5%. Los $26,000.00 de Valor Futuro son $24,000.00 es de la titulación y $2000.00 de gastos aparte. los 12 es el periodo que es a un año. y mi valor presente que buscábamos es que tenemos que depositar al banco $14,477.77 para que dentro de 12 meses tengamos $26,000.00.

Y por ultimo tenemos Bancomer nos ofrece una tasa de rendimiento del 7%. Los $26,000.00 de Valor Futuro son $24,000.00 es de la titulación y $2000.00 de gastos aparte. los 12 es el periodo que es a un año. y mi valor presente que buscábamos es que tenemos que depositar al banco $11,544.31 para que dentro de 12 meses tengamos $26,000.00.

Entonces como vemos en la institución bancaria en donde debemos depositar menos es Bancomer es por eso que yo eligí Bancomer, y después de esto agarraremos para obtener el VF, Pago y Tasa.

Este método lo hacemos para ver cuanto de rendimiento nos dara al termino de los 12 meses, como se puede ver en la imagen si nos da lo que esperamos, si depositamos los $11544.31.

En este método veremos cuanto debemos ahorrar mensualmente, y nos una cantidad de $1453.45 mensualmente.

Y por último con  este método terminamos, lo hacemos para corroborar si esta bien lo que hicimos en los anteriores o sea nos da el interés.

 

Excel Solver

Solver es una herramienta de análisis que tienes en el programa Excel, aplicado sobre todo en el mundo empresarial, permite calcular el valor de una celda que depende de diversos factores o variables donde a la vez existen una serie de restricciones que han de cumplirse.

Solver de Excel realiza son los cálculos para la resolución de problemas de programación lineal, en donde a partir de una función lineal a optimizar (encontrar el máximo o mínimo) y cuyas variables están sujetas a unas restricciones expresadas como inecuaciones lineales, el fin es obtener valores óptimos bien sean máximos o mínimos.

                                                                                                                                 (Cantoral, s.f.)

Ahora aplicaremos Excel Solver para ajustar el presupuesto inicial que tendrá un restaurante de Tortas & hamburguesas. En esta imagen se ve lo que seria el presupuesto original.

Se quiere hacer la inversion de $21,983.00 para comprar todo lo necesario, pero solo hemos conseguido un prestamos de $20,000.00 entonces utilizaremos solver.

En establecer objetivo se ponen los $21,983.00.

Para: Le ponemos Valor de $20,000.00 porque solo nos prestaron eso.

En combinación de celdas de variables: se elige la celdas de cantidad.

y por ultimo se utiliza las restricciones

Se le da en agregar 

Aparecerá un recuadro como este y en donde dice Referencia de celda se pone la restricción que quiere en mi caso puse en donde dice palas y le puse que fuera <= y en restricción se pone cuantas quieres nada mas yo puse solo 2 y me la restingar a solo 1.

Y es así como queda con los $20,000.00 justos del préstamo.

Amortizaciòn de prestamos en excel

Para entender el tema de amortización de préstamos primero abordaremos unos conceptos referentes a esto como lo son:

Prestamos: Se entiende por préstamo bancario, a la operación por la cual una entidad financiera pone a disposición del cliente una cantidad de dinero determinada que se estipula a través de un contrato,  en el que se adquiere la obligación de devolver ese dinero en un tiempo establecido.

Crédito: El crédito es una operación financiera en la que se pone a nuestra disposición una cantidad de dinero hasta un límite especificado y durante un período de tiempo determinado.

Crédito

En un crédito nosotros mismos administramos ese dinero mediante la disposición o retirada del dinero y el ingreso o devolución del mismo, atendiendo a nuestras necesidades en cada momento. De esta manera podemos cancelar una parte o la totalidad de la deuda cuando creamos conveniente, con la consiguiente deducción en el pago de intereses.

La diferencia que hay entre Préstamo y Crédito:

• En el préstamo la entidad financiera pone a disposición del cliente una cantidad fija y el cliente adquiere la obligación de devolver esa cantidad más unas comisiones e intereses pactados en el plazo acordado.
• En el crédito la entidad financiera pone a disposición del cliente, en una cuenta de crédito, el dinero que este necesite hasta una cantidad de dinero máxima.
• El préstamo suele ser una operación a medio o largo plazo y la amortización normalmente se realiza mediante cuotas regulares, mensuales, trimestrales o semestrales. De este modo, el cliente tiene la oportunidad de organizarse mejor a la hora de planear los pagos y sus finanzas personales.
• Generalmente los préstamos son personales y se conceden a particulares para un uso privado, por lo tanto, generalmente se requieren garantías personales (avales) o garantías reales (prendas o hipotecas).
• En el préstamo la cantidad concedida normalmente se ingresa en la cuenta del cliente y este deberá pagar intereses desde el primer día, calculándose los intereses sobre la cantidad que se ha concedido.

La amortización es un término económico y contable, referido al proceso de distribución de mota en el tiempo de un valor duradero. Adicionalmente se utiliza como sinónimo depreciación en cualquiera de sus métodos.

Se emplea referido a dos ámbitos diferentes casi opuestos: la amortización de un activo y la amortización de un pasivo. En ambos casos se trata de un valor, con una duración que se extiende a varios periodos o ejercicios, para cada uno de los cuales se calculan una amortización, de modo que se reparte ese valor entre todos los periodos en los que permanece.

Amortizar es el proceso financiero mediante el cual se extingue, gradualmente, una deuda por medio de pagos periódicos, que pueden ser iguales o diferentes.

En las amortizaciones de una deuda, cada pago o cuota que se entrega sirve para pagar los intereses y reducir el importe de la deuda.

La amortización financiera es el reintegro de un capital propio o ajeno, habitualmente distribuyendo pagos en el tiempo. Suele ser el producto de una prestación única, que genera una contraprestación múltiple con vencimiento posterior. Es común que el pago de estas obligaciones se haga a través de desembolsos escalonados en el tiempo, aunque también se puede acordar un solo pago al final del período.

En la amortización de préstamos la cuota es la cantidad a pagar en la periodicidad pactada. Según lo que hayamos acordado, puede ser mensual, trimestral, semestral, etc. Lo que paguemos va a depender del importe del préstamo, el tipo de interés y el plazo que hayamos pactado.

La cuota incluye la parte del dinero prestado que se devuelve o amortiza, más los intereses correspondientes.

Existen 3 tipos de amortización de préstamos

1. Amortización de préstamos: Cuota constante o método francés

También llamada ‘método francés’. Es la forma más frecuente de amortización de préstamos, y consiste en que vamos a pagar la misma cuota siempre, lo que varía son los intereses y el capital amortizado. Los intereses que se cobran en las cuotas se calculan sobre el importe pendiente de amortizar, es decir, lo que pagamos de intereses se va a ir reduciendo en una cuantía proporcional a la amortización del capital, ya que el capital pendiente de amortización será menor. En la primera fase, por tanto, se pagan principalmente intereses. Pero a medida que se van pagando cuotas, la parte de las mismas que corresponde a capital amortizado aumenta, disminuyendo así los intereses.

Amortización de préstamos: Cuota creciente

La cuota va aumentando con el paso del tiempo. Tiene la ventaja de que se paga menos al principio pero, lógicamente, la carga aumenta en el futuro.

3  Amortización de préstamos: Cuota decreciente

En este tipo de amortización de préstamos ocurre lo contrario. Se amortiza siempre la misma cantidad de capital de forma que los intereses se van reduciendo progresivamente y el total a pagar va descendiendo. Es decir, el importe de las cuotas desciende con el paso del tiempo.

A continuación veremos un ejemplo de un préstamo para un negocio es un caso real en el cual autorizan un monto de $50,000.00 con una tasa de interés del 8.76 anual para pagar en 12 meses (1 año)

(Luis, 2016)

(Banamex, s.f.)

(abelica, s.f.)