22 de julio de 2012

0 Calculadora para Conversión de Tasas de Interes

Una de las principales cuestiones en el aprendizaje de conceptos financieros es la conversión de tasas de interés, y como siempre excel nos puede ayudar a asimilar los conceptos y mejorar la exactitud de nuestros cálculos.

Hoy les dejo una sencilla calculadora que les permitirá calcular la Tasa Efectiva Anual a partir de una Tasa Nominal con una periodicidad y modalidad determinadas.

Para convertir la tasa efectiva anual (EA) en tasa nominal vencida o viceversa es necesario tener en cuenta el siguiente gráfico, que se diseñó para entender la lógica de la funcionalidad de la calculadora financiera:




Para convertir la tasa efectiva anual (EA) en tasa nominal anticipada o viceversa es necesario tener en cuenta el siguiente gráfico:


Espero que les sea muy util; cualquier inquietud pueden comentar en el blog o si prefieren enviar un email a blog.excel.financiero@gmail.com.


 Descargar Archivo ejemplo: Calculadora Tasa EA

21 de julio de 2012

0 Función Precio


La función precio devuelve el precio por $100 de valor nominal de un valor bursátil (bono) que paga una tasa de interés periódica . Esta es la función mas utilizada para valorar bonos regulares.

La sintaxis es:


PRECIO (liquidación, vencimiento, tasa, rdto, amortización, frecuencia, base)

 El argumento liquidación hace referencia a la fecha de liquidación del bono, vencimiento es la fecha de vencimiento del valor bursátil, tasa es la tasa de interés nominal anual del bono, rdto es el rendimiento o "yield" , amortización es el rendimiento por cada $100 de valor nominal, frecuencia es el numero de pagos de cupón por año y base determina en que tipo de base deben ser contados los días. La base es opcional, si se omite, Excel asumirá por defecto la notación 30/360 días.

0 Macro para eliminar hipervinculos

En ocasiones se nos presenta la necesidad de eliminar los hipervinculos de una hoja de Excel lo cual no debería tener mayor complejidad si son uno o dos...pero que pasaría si fueran muchos mas? Seguramente tendríamos q eliminarlos uno a uno lo cual nos quita tiempo y eficiencia en nuestro trabajo. 

Para solucionar esta situación le presento un sencillo código de VBA que nos va a permitir eliminar todos los hipervinculos de una hoja de Excel con tan solo un click! Es importante aclarar que los hipervinculos eliminados con esta macro no podrán ser restablecidos con la opción "deshacer" (ctrl + z), adicionalmente solo se eliminaran los vínculos de la hoja activa en el momento de ejecución de la macro.

 

El código es el siguiente:

 

Sub eliMinar_hiPervinculos

Cells.Hyperlinks.Delete

End Sub

 

Espero les sea de utilidad!

7 de marzo de 2012

1 Valor Futuro de una Suma Presente

El valor futuro representa un solo flujo de dinero que se entrega al final del último periodo  de inversión.  Se puede calcular a partir de una suma presente, una serie de cuotas uniformes o una serie de cuotas no uniformes.  Se puede trabajar con tasas de interés iguales para todos los periodos o con tasas de interés diferentes en cada uno de estos.

En esta entrada vamos a ver como se calcula el valor futuro a partir de una suma presente; la fórmula matemática es la siguiente:

VF = VA (1+i)n

Supongamos que un Empresario ha invertido $1.000.000.000 a una tasa del 2% mensual….cuanto recibirá al cabo de finalizar el mes 12?

Veamos el planteamiento gráfico del problema:


Ahora vamos a resolver el problema, usando la función predeterminada de Microsoft Excel® VF.
En primer lugar vamos a incluir los datos del problema en las celdas de Excel®:


Después debemos llamar al asistente de funciones, para la versión Excel® 2010 vamos a la pestaña Fórmulas > Insertar Función:


Una vez hacemos click en "Insertar Función" el programa nos despliega el menú de funciones, allí escogemos las funciones financieras y buscamos la función VF, previamente nos hemos situado en la celda B6 donde vamos a calcular el valor futuro:


Cuando seleccionamos la función VF, aparecerá un cuadro de dialogo el cual describe de manera sencilla la sintaxis de la función, es decir los argumentos requeridos para el calculo del valor futuro.


En el extremo superior izquierdo del cuadro de dialogo aparece el nombre de la función que se va a calcular, en este  caso VF.  En los siguientes cuadros se preguntan todos los posibles argumentos de la función, para efectos del presente ejercicio vamos  diligenciar el argumento "Tasa", "Nper" y "Va"; notese que cada vez que nos situamos en alguno de los argumentos aparece en la parte inferior derecha la explicación de cada uno.  

Asi pues, para el argumento tasa vinculamos la celda B5, Nper la celda B4 y Va la celda B3...y obtenemos el siguiente resultado:


Como se puede observar, el resultado es negativo porque  Excel® supone que si al comienzo el flujo de caja es positivo (como se muestra en la representación gráfica del problema), se ha otorgado un préstamo y por ende el VF sera el pago de dicha obligación.  Por el contrario, si al comienzo se ha hecho una inversión, al final del flujo se dará el retorno positivo:



24 de febrero de 2012

0 Minigráficos en Excel 2010

En una entrada anterior les presente un "add-in" para excel llamado "Sparklines" el cual permitía incrustar gráficos miniatura dentro de las celdas de la hoja de calculo.

Pues bueno... con la versión 2010 de Excel ya no hay que descargar ningún complemento pues la función viene incorporada en el menú de la pestaña "Insertar" ubicada en la cinta de opciones.

Los minigraficos permiten confeccionar gráficos muy pequeños y ubicarlos en una celda, de forma que, con un simple vistazo podemos analizar la evolución o tendencia de los datos existentes en una tabla de datos o en un determinado rango.  



30 de octubre de 2011

6 Análisis de Sensibilidad: Tablas de Datos


Ejemplo: Tablas de datos


Las tablas de datos son una herramienta de Excel que nos permite calcular el valor que tomará una variable de salida de un modelo, ante valores asignados a una o dos variables de entrada.  En este orden de ideas podemos hablar de análisis de sensibilidad con una variable independiente o con dos variables independientes y una dependiente.

En esta ocasión les presento un sencillo ejemplo, el cual ilustra de manera fácil el proceso para efectuar un análisis de sensibilidad con la herramienta tabla de datos.

Vamos a suponer que tenemos los siguientes datos de una obligación financiera:



y queremos conocer el valor de la cuota mensual a pagar (la cual será nuestra variable de salida) para lo cual podemos utilizar la formula "PAGO()" de excel, que posee los siguientes argumentos:


Una vez hayamos formulado nuestra variable de salida, es decir el monto de la cuota mensual a pagar obtendremos el siguiente resultado:


Ya tenemos formulada nuestra variable de salida, ahora queremos saber como cambia dicha variable (cuota) ante cambios en un parámetro como por ejemplo la tasa de interés.  Es en una situación como esta donde la herramienta tabla de datos nos facilita el trabajo, veamos como hacerlo:

En primer lugar identificamos las variables de entrada y salida: en este caso las variables de entrada corresponden al valor del crédito, la tasa de interés y el plazo, y nuestra variable de salida es la cuota.

Digitamos verticalmente los valores que tomará la variable, en este caso definimos un delta o variación de 0,5% desde 1% hasta llegar al 5%.


Nos ubicamos en la celda A13 y digitamos "1%", luego bajamos una celda (A14) y formulamos el cambio en la tasa de interés, así: =B13+$G$3 y copiamos hasta llegar a la celda A21.  

En la celda B12 digitamos la siguiente formula =B7,  con lo cual referenciamos nuestra variable de salida.


Con lo anterior ya tenemos nuestra matriz lista para sensibilizar, con lo cual procederemos a seleccionar todo el rango (Desde A12 hasta B21) y llevamos a cabo el siguiente procedimiento: Pestaña Datos > Análisis Y Si > Tabla de Datos


Aparecerá entonces la siguiente ventana:


Diligenciamos únicamente el espacio de "Celda de Entrada (Columna):" (se utiliza cuando los valores que se asignan a la variable se han escrito a lo largo de una columna)  seleccionando la celda donde se encuentra nuestro parámetro de tasa de interés, es decir B4; hacemos click en aceptar y listo! tendremos una matriz de sensibilidad que nos indica el valor de la cuota mensual a pagar ante variaciones en la tasa de interés.


Ahora bien, que pasaría si no solo quisiéramos saber cual es el valor de la cuota ante cambios en la tasa de interés, sino también ante cambios en el plazo del crédito? tendríamos que hacer 2 tablas de datos? la respuesta a la pregunta es NO!  podemos hacer una sensibilidad en una sola tabla en la cual involucremos 2 variables independientes y 1 dependiente y podamos evaluar el impacto de dos parámetros en la variable de salida.

El procedimiento técnicamente es el mismo, con la diferencia que vamos a estructurar una matriz en la cual horizontalmente se encuentren los cambios en el parámetro de tasa de interés y verticalmente los cambios en el plazo (Variación = 6 meses), tal como se muestra a continuación:


Notese que en la celda A26 se ha referenciado nuestra variable de salida: Cuota, con el fin de poder sensibilizar el impacto de las variables independientes.  Bien, una vez tengamos la matriz y la referencia a la variable de salida, seleccionamos el rango A26 x G33 y nos dirigimos a Datos > Análisis Y Si > Tabla de datos, en esta ocasión vamos a diligenciar los datos solicitados tanto en la fila como en la columna, asi:


En la celda de entrada Fila, referenciamos la celda parámetro de la tasa de interés, y en la celda de entrada columna hacemos lo propio pero con el parámetro de plazo.  Hacemos click en aceptar y tendremos nuestra tabla de datos con 2 variables independientes y 1 dependiente!



Espero les guste y recuerden que cualquier comentario, sugerencia o inquietud la pueden manifestar comentando en el blog o en nuestra pagina en facebook: ExcelFinanciero/FacebookPage 












24 de septiembre de 2011

2 Mostrar y Ocultar Hojas mediante Macros



Siguiendo con el tema de la entrada anterior (Hojas Bien Ocultas), esta vez veremos como MOSTRAR todas las hojas ocultas de un modelo financiero mediante la construcción y ejecución de una macro y a su vez como volverlas a OCULTAR.

Supongamos que nos entregan un modelo financiero el cual consta de 12 hojas, sin embargo 7 de ellas contienen cálculos que el usuario final no debe manipular,y para esto el estructurador del modelo las ha bloqueado y ocultado.  Como mostrarlas???  

El procedimiento es el siguiente:

Vamos al editor de Visual Basic (Alt + F11), insertamos un Modulo y escribimos el siguiente código:


Sub MostrarHojasOcultas ()

Application.ScreenUpdating = False
 
    For Each N In Sheets
        N.Visible = True
    Next N

Application.ScreenUpdating = True
 
End Sub 



Una vez tengamos el código de la macro, guardamos y cerramos el editor de visual basic.

Para activar la macro vamos a la ficha programador > Macros > (Buscamos la macro) Ejecutar....y ya está! habremos mostrado todas las Hojas del modelo.

Ahora surge una nueva inquietud...como podemos ocultar de nuevo las hojas que hemos mostrado sin necesidad de hacerlo una por una?

Sencillo! vamos de nuevo al editor de Visual Basic (alt + F11) y digitamos el siguiente código (Recordemos que el comando "sheets" hace referencia a las hojas que componen el modelo, de esta manera se le agrega la característica ".Visible" la cual puede ser igual a "True" si se quiere mostrar la hoja o "False" si se quiere ocultar.):


Sub ocultar_hojas()


Sheets("Menú").Visible = True
Sheets("Menú").Select

Sheets("Resultados").Visible = True
Sheets("Resultados").Select

Sheets("Supuestos").Visible = True
Sheets("Supuestos").Select

Sheets("Balance_General").Visible = True
Sheets("Balance_General").Select

Sheets("P&G").Visible = True
Sheets("P&G").Select

Sheets("Proyección_Ingresos").Visible = False
Sheets("Proyección_Gastos").Visible = False
Sheets("Proyección_Costos").Visible = False
Sheets("Overhead").Visible = False
Sheets("Créditos").Visible = False
Sheets("Analisis").Visible = False
Sheets("Wacc").Visible = False
Sheets("Valoración").Visible = False

End Sub 



Si por alguna razón no queremos ocultar una de las hojas, lo único que debemos hacer es borrar la linea o cambiar = False   por  = True.


10 de septiembre de 2011

1 Hojas Bien Ocultas

Un modelo financiero esta compuesto por tantas hojas como su complejidad lo amerite, y varias o algunas de esas hojas contienen cálculos que preferiblemente deben ir ocultos ya sea por facilidad de maniobra del modelo por parte del usuario final o por temas de confidencialidad de la información.


Normalmente, las ocultamos siguiendo el procedimiento: Formato > Hoja > Ocultar; o click derecho sobre la hoja > ocultar.  Ocultar hojas de este modo no evita de ninguna manera que el usuario final tenga acceso a ellas e incluso modifique la formulación, con la posibilidad de descomponer completamente el modelo; y mucho menos sirve para ocultar información de carácter confidencial. Todo lo que se tiene que hacer es ir a Formato > Hoja > Mostrar... y seleccionar la hoja que se quiera ver.

Para estos casos, tenemos la posibilidad de ocultar una hoja de forma tan segura, que ni siquiera aparezca en el cuadro Mostrar hoja... esto utilizando el editor de Visual Basic para Aplicaciones (VBA).

El procedimiento es el siguiente:




Ingresamos al Editor de Visual Basic (Alt + F11) y localizamos nuestro modelo (libro) en el Explorador de proyectos (precedido con la palabra "VBAProject"), en este caso el libro que estoy trabajando se denomina "110909_Ejemplo_Ocultar_Hojas".

Las hojas del libro están al interior de la subcarpeta "Microsoft Excel Objetos", En caso de que no se vean las mostramos haciendo clic en el botón
+ de dicha subcarpeta, y Seleccionamos la hoja que queremos ocultar.

En la ventana Propiedades buscamos la propiedad Visible y establecemos su valor a  "2 - xlSheetVeryHidden" (si no se muestra la referida ventana, la mostramos con Ver- Ventana propiedades).  Con este valor en la propiedad Visible, la hoja estará oculta y no aparecerá en el cuadro Mostrar hoja...



Con este valor en la propiedad Visible, la hoja estará oculta y no aparecerá en el cuadro Mostrar hoja cuando el usuario intente buscar.

Ahora bien, existe la posibilidad de que el usuario tenga conocimientos un poco mas avanzados y reestablezca la propiedad Visible a "-1 - xlSheetVisible" (hoja visible) siguiendo los mismos pasos. 




Para evitar esto es necesario proteger el proyecto con una contraseña, entonces nos dirigimos a Herramientas > Propiedades de VBAProject..., una vez hacemos el procedimiento anterior nos aparece una ventana con dos fichas (General y Protección) donde seleccionamos la ficha Protección y marcamos la opción "Bloquear proyecto para visualización", acto seguido establecemos una contraseña confiable preferiblemente alfanumérica.


Con esto, si el usuario intenta mostrar el editor de Visual Basic, Excel le requerirá escribir la contraseña para abrirlo.

Finalmente cerramos el editor de Visual Basic y guardamos el archivo. De regreso en Excel, damos click derecho sobre cualquier hoja > Mostrar...:

y comprobamos que la hoja que recién ocultamos no aparece en este cuadro. Más aún, si solo hemos ocultado una hoja, ni siquiera estará habilitado el comando Mostrar...




29 de julio de 2011

0 Fixture Mundial Sub 20 Colombia 2011

Hoy comienza el Mundial Sub 20 en nuestro País, y para entrar en ambiente aqui les dejo un archivo con el Fixture del Mundial, con él podrán hacerle seguimiento a todos los partidos tanto de la fase de grupos como las fases finales (Octavos, Cuartos, Semifinales, 3 puesto y Final).

Disfruten el archivo y que VIVA COLOMBIA!!!

15 de marzo de 2011

0 SparkLines for Excel ®

SparkLines for Excel ® es un complemento totalmente gratuito que nos permite montar  gráficos miniatura dentro de las mismas celdas de Excel (líneas, barras, superficie, circular, áreas, etc.) . Son especialmente útiles para cuadros de mando, presupuestos, tableros de control y modelos de business intelligence en Excel.
Esta funcionalidad ya viene incorporada en Excel 2.010, pero con este complemento la pueden añadir a Excel 2.003 y a Excel 2.007.  Se instala como un nuevo menú en tu Excel.


DESCARGAR COMPLEMENTO: SPARKLINES FOR EXCEL®  
                                                                     
 

Excel Financiero Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates