martes, 24 de noviembre de 2015

Promedio condicional

Puede descargar el archivo de Excel siguiente.
Disponemos en Excel de las funciones siguientes.
  • SUMAR.SI
  • SUMAR.SI.CONJUNTO
  • CONTAR.SI
  • CONTAR.SI.CONJUNTO
Pero no disponemos de la función PROMEDIO.SI

Vamos a ver un caso donde realizamos un promedio condicional donde varía el rango que deseamos promediar y además eliminamos los valores que no son numéricos. Lo vamos a resolver por tres métodos.



Deseamos calcular el promedio anual del Euribor a un año. La información la obtenemos del Banco de España.

En el punto 1.7 disponemos de un histórico de Series Temporales, concretamente en el siguiente enlace podremos descargar el archivo csv.

Método 1

Consiste en crear cada rango de forma manual. Para ayudarnos creamos la columna E.


Método 2

Sin usar fórmulas matriciales podemos obtener el promedio usando SUMAR.SI y CONTAR.SI.CONJUNTO.

Veamos la celda I6.
=SUMAR.SI(Años;G6;Porcentaje)/CONTAR.SI.CONJUNTO(Años;G6;Porcentaje;">0")/100

Método 3

En este caso usamos una función matricial. Recuerde que este tipo de funciones se validan no pulsando ENTER, sino pulsando simultáneamente tres teclas: CONTRO+SHIFT+ENTER.

La celda J6 contiene la siguiente expresión.
=SUMA((--(Años=G6))*SI.ERROR(VALOR(Porcentaje);0))/SUMA((Años=G6)*ESNUMERO(Porcentaje))/100

Para saber más ...

En el siguiente post se resuelve un caso similar pero que se caracteriza porque los rangos son todos del mismo tamaño. Lo interesante del caso que hemos visto en esta ocasión es que los rangos no son todos iguales, ya que existen años con 365 días y años con 366 días. Además teníamos el handicap de contar con días en los que no existía Euribor y se marcaban con un guión bajo (_).

viernes, 20 de noviembre de 2015

Préstamo Geométrico Fraccionado a tipo variable con AA al final de año

Puede descargar el archivo de Excel:
Veamos un caso práctico de un préstamo geométrico fraccionado a tipo variable con amortización anticipada al final de año.
Sea un préstamo con las siguientes características.
  • Principal 1.000.000 €
  • Duración 10 años
  • Pagos mensuales pospagables que son constantes dentro de cada año y variables al cambiar de año por dos motivos. Uno de ellos se debe a que se trata de un préstamo geométrico que incrementa los pagos un 4% anual acumulado. El otro motivo por el que varía la mensualidad al cambiar de año radica en que se trata de un préstamo a tipo de interés variable con revisión anual, Euribor + Diferencial. El diferencial es del 0,50% y el Euribor del primer año es del 2% y para años posteriores supondremos que se incrementa 0,30% cada año.
  • El banco únicamente nos deja realizar pagos adicionales en concepto de amortización anticipada (AA) al final de cada año, y no nos cobra ninguna comisión por efectuar estos pagos adicionales. Se han realizado dos amortizaciones anticipadas, la primera de 75.000 € al final del segundo año y la segunda de 100.000 € al final del cuarto año.
Se pide calcular el importe de la última mensualidad.


Los datos se indican en las celdas de color rosa. En un préstamo a tipo variable no conocemos el tipo de interés futuro cuando firmamos el préstamo. Únicamente conocemos el tipo de interés que se aplicará el primer año. Al ser la revisión anual tendremos que esperar a que transcurra el primer año para conocer el tipo que se aplicará el segundo año, y así sucesivamente. Para poder calcular este tipo de préstamos a tipo variable se hace el supuesto de que el tipo de interés que se conoce será el que se aplique durante toda la vida del préstamo, aunque sabemos que al transcurrir cada año se revisará el tipo y éste posiblemente cambiará. Al realizar el enunciado del problema tenemos que dar el tipo de interés de los 10 años, y es lo que hacemos estableciendo el supuesto de que a lo largo de este tiempo ha variado incrementándose 0,30% cada año. Hemos de ser conscientes de que la información de como variará el tipo de interés a lo largo de la vida del préstamo no la conocemos a priori.


Primero calculamos el cuadro de amortización anual, esto es, considerando que los pagos fueran anuales, trabajando con 10 años al tipo efectivo anual i correspondiente a cada año. En la columna R calculamos la anualidad que se tendría que pagar a final de año pero en este importe no hemos añadido aún la Amortización Anticipada (AA) que se paga al final del año, esto se hace en la columna S. Para calcular la mensualidad utilizamos la función PAGO considerando que la anualidad se paga al final de cada año, por lo que dentro de la función PAGO se usa el argumento VF. La celda Y10 es:
  • =PAGO(O10;12;;-R10)
En la columna Z ofrecemos un modo alternativo para calcular la anualidad sin usar la función VAgeo. La celda Z10 es:
  • =(V9/VNA(P10;Q10:$Q$19))*Q10


Después de haber calculado el cuadro de amortización anterior podemos crear el cuadro de 120 meses, pero ya no sería necesario puesto que en el cuadro anterior ya tenemos calculada la última mensualidad.


Sin VAgeo y como Hoja de Cálculo de Google

Puede obtener el fichero del siguiente enlace.
En la hoja de cálculo de Google las funciones se escriben en inglés.
  • VNA = NPV
  • PAGO = PMT
  • BUSCARV = VLOOKUP




Para saber más ...

Este ejemplo práctico tiene una limitación. La AA no se puede realizar a mitad de año, únicamente está previsto realizarla al final de alguno de los 10 años. Si el prestatario efectuará una entrega de capital adicional en concepto de Amortización Anticipada (AA), por ejemplo al final del mes 15, y el banco lo permitiera, este sistema de cálculo no sería válido y tendríamos que programar una macro que lo solucionara. Eso se puede ver en el siguiente post.

domingo, 8 de noviembre de 2015

Función SUMAR.SI

Puede descargar el archivo sumarsi.xlsx

La función SUMAR.SI nos permite hacer una suma condicional. Esto es, no suma todo el rango indicado sino únicamente las celdas del rango que cumplen el criterio que deseamos imponer.

La sintaxis de la formula es la siguiente.

=SUMAR.SI(rango, criterio, [rango_suma])

Veamos sus argumentos.

  • rango: es el rango que se analiza en el argumento criterio
  • criterio: es una igualdad o desigualdad que deben cumplir las celdas del rango especificado anteriormente
  • [rango suma]: es un argumento optativo que en ocasiones se ha de utilizar y en otras no. Este argumento no se usa cuando el rango que deseamos sumar es el que se ha especificado en primer lugar. Si el rango especificado en primer lugar, que es el que cumple o no el criterio, no es el que deseamos sumar sino otro, se debe especificar en este tercer argumento ese otro rango que deseamos sumar.
Veamos los ejemplos contenidos en el archivo descargable indicados al inicio de este post.

Hoja1 Caso 1

Primero veamos el caso donde no se ha de utilizar el tercer argumento [Rango de suma] ya que deseamos sumar justo el rango que se usa al analizar el criterio.

Disponemos de una tabla con 20 ciudades donde figura el número de habitantes que tienen y el número de empresas de cierto sector radicadas en esa ciudad.


Deseamos conocer cuantos habitantes hay entre todas las ciudades siempre que estas tengan más de 700.000 habitantes. Para ello utilizamos la función siguiente que se encuentra en la celda H7.

=SUMAR.SI(Habitantes;">=700000")

Observe que hemos nombrado rangos que son los siguientes.

  • Empresas   =Hoja1!$D$5:$D$24
  • Habitantes  =Hoja1!$C$5:$C$24
  • Población   =Hoja1!$B$5:$B$24


Nota

Todos los datos de Habitantes y Empresas se generan con funciones aleatorias. De esta forma cada vez que se presiona la tecla F9 de recálculo manual, o bien, cada vez que se introduce un valor en una celda y se pulsa ENTER, lo que sucede es que se calcula toda la hoja, y por tanto los valores aleatorios cambian.
En este caso para los Habitantes hemos usado la función siguiente.

=ALEATORIO.ENTRE(50;1000)*1000

Hoja1 Caso 2

Ahora deseamos conocer el número de empresas que se encuentran radicadas en ciudades que tengan más de 700.000 habitantes.

Para ello utilizamos la formula siguiente que se encuentra en la celda H11.

=SUMAR.SI(Habitantes;">=700000";Empresas)

Observe que ahora ha sido necesario introducir el tercer argumento ya que no deseamos sumar habitantes sino empresas. Los habitantes se analizan en el criterio pero lo que deseamos es sumar número de empresas.


Nota

Para generar las Empresas hemos utilizado la inversa de la Normal.

=REDONDEAR(INV.NORM(PROMEDIO(ALEATORIO();ALEATORIO();ALEATORIO();ALEATORIO();ALEATORIO();ALEATORIO());C5/3000;C5/10000);0)

Hemos usado como probabilidad el promedio de seis valores aleatorios uniformes cero uno U[0,1] con la función ALEATORIO(). El motivo es que así obtenemos un valor aleatorio entre 0 y 1 pero que ya no es uniforme y se encuentra más centrado en la media que es 0,5, pero aún tiene suficiente dispersión. Si en lugar de seis valores aleatorios hubiéramos usado un gran número de ellos veríamos como se cumple el teorema central del límite, donde para infinitos sumandos el valor obtenido tiende a una normal.

Hoja2 Caso 1

Disponemos en la columna B de una serie de datos fijos. Son números que van del 100 al 1.000, en saltos de 100. Creamos un nombre de rango que se llama Datos que es rango B5:B14.

El el Caso 1 deseamos conocer la suma de los valores del rango Datos que sean <= 300. Esto lo calculamos en la celda F6 con la siguiente función.

=SUMAR.SI(Datos;"<=300")

En el primer argumento indicamos el rango Datos. Si no hubiéramos nombrado el rango también podríamos haber puesto simplemente B5:B14.

En el segundo argumento de la función indicamos el criterio, que ha de ir entre comillas. Como en este primer caso el valor de 300 es fijo, va dentro de las comillas: "<=300".



Hoja2 Caso 2

El el segundo caso no deseamos que el valor 300 sea fijo, por lo que creamos la celda amarilla para que el usuario pueda poner el valor que desee. En el ejemplo de la imagen anterior hemos puesto en la celda amarilla el valor 400, así pues, deseamos calcular la suma de los datos que sean menor o igual a 400. Pero ese valor de la celda amarilla puede variar por lo que debemos escribir el criterio usando el concatenador & y así poder unir "<=" con la celda amarilla que es E10.

La fórmula queda así:

=SUMAR.SI(Datos;"<="&E10)

Si prueba a usar como criterio la expresión "<=E10" verá que no funciona. Es imprescindible usar el concatenador & que nos permite unir cadenas alfanuméricas.

Formato condicional

El rango Datos marca con un color rosa las celdas que cumplan que son menores o iguales que el valore marcado en la celda E10 (amarilla).

Pruebe a cambiar el valor de la celda amarilla y verá como cambia el color de las celdas del rango Datos.

Para conseguir este formato condicional hemos usado la siguiente expresión aplicada a la celda B5.

=B5<=$E$10


Y luego hemos aplicado este formato a todo el rango Datos. Esto lo hemos conseguido con la brocha que nos permite copiar y pegar, formato.




martes, 3 de noviembre de 2015

Herramientas estadísticas

En Excel podemos instalar ciertos complementos que incrementan la funcionalidad o nos permiten disponer de nuevas herramientas. El complemento denominada Herramientas para análisis nos permite disponer de nuevas herramientas estadísticas.

Instalar el complemento Herramientas para análisis

En Excel 2010 y en Excel 2013 tenemos que ir a Archivo, Opciones, Complementos.


Abajo vemos un botón denominado Ir. Al pulsar sobre él obtenemos los diferentes complementos que se pueden instalar. Seleccionamos Herramientas para análisis. No confundir con Herramientas para análisis - VBA que se utiliza como un complemento para la programación en Visual Basic para Aplicaciones.


Después de aceptar veremos en el menú Datos, a la derecha aparecen Solver (si se activó como complemento) y Análisis de datos.

Herramientas de Análisis de Datos

Al pulsar sobre Análisis de datos, aparece una ventana con la posibilidad de elegir entre múltiples herramientas estadísticas.
  • Análisis de varianza de un factor
  • Análisis de varianza de dos factores con varias muestras por grupo
  • Análisis de varianza de dos factores con una sola muestra por grupo
  • Coeficiente de correlación
  • Covarianza
  • Estadística descriptiva
  • Suavización exponencial
  • Prueba f: dos muestras para varianzas
  • Análisis de Fourier
  • Histograma

  • Media móvil
  • Generación de números aleatorios
  • Jerarquía y percentil
  • Regresión
  • Muestreo
  • Prueba t
  • Prueba t: medias de dos muestras emparejadas
  • Prueba t: dos muestras suponiendo varianzas iguales
  • Prueba t: dos muestras suponiendo varianzas desiguales
  • Prueba z
  • Funciones de VBA de Herramientas para análisis




En el siguiente enlace dispone de una página de Microsoft que comenta estas herramientas.

Generación de Mixturas

Puede descargar el archivo Mixtura.xlsm

Una mixtura según el diccionario es una mezcla o incorporación de varias cosas. En nuestro contexto nos referimos al concepto utilizado en estadística al mezclar varias distribuciones de probabilidad. Por ejemplo, en el campo actuarial es frecuente disponer de dos distribuciones de probabilidad y al mezclarlas deseamos conocer la distribución resultante, que ya no será pura.

En el archivo que hemos mencionado al inicio, y que puede ser descargado, lo que hacemos es realizar la mixtura entre dos distribuciones Poisson de diferente media. Se pude apreciar en los gráficos el resultado de la mezcla.


El resultado de la mezcla supone un mayor porcentaje de una u otra de las distribuciones de origen. Este porcentaje se maneja en la hoja de cálculo variando una barra horizontal.


La fórmula empleada es la siguiente.

P(X=x)=p×P10(X=x)+(1-p)×P25(X=x)

donde p es el porcentaje aplicado a la Poisson de media 10 y (1-p) que es el complementario, es el porcentaje aplicado a la Poisson de media 25.

La imagen anterior corresponde a una captura de pantalla donde se ha tomado p=22%, por tanto el complementario (1-p)=78%.



lunes, 2 de noviembre de 2015

Auditoria de fórmulas

En el menú o ficha Formulas existe un grupo denominada Auditoria de fórmulas. Veamos sus opciones y algunos otros puntos de interés al trabajar con fórmulas.


Rastrear precedentes

Si nos situamos en una fórmula que se alimenta de datos previos, al pulsar sobre la opción Rastrear precedentes aparentaran unas flechas azules que nos muestran los datos que utiliza esta fórmula. Si se vuelve a presionar se mostrarán nuevas flechas que indican de qué otras celdas se alimentan las anteriores y así sucesivamente.


Rastrear dependientes

Si nos situamos en una celda que se utilice como datos en otra y otras posteriores, al pulsar sobre Rastrar dependientes se mostrarán una serie de flechas azules que llegan hasta todos las celdas que utilizan como datos la celda en la que estamos situados. Si seguimos pulsando sobre Resaltar dependientes aparecerán nuevas flechas azules que marcan el siguiente nivel y así sucesivamente.

Quitar flechas

Podemos eliminar todas las flechas azules que hemos generado.

Mostrar fórmulas

Esta opción nos permite ver las fórmulas en lugar del resultado de su cálculo. Si en la celda no existe una fórmula sino un dato, al pulsar sobre la opción Mostrar fórmulas lo que veremos es el datos despojado de su formato. Por ejemplo, las fechas se verán sin el formato fecha, se verán como el número de serie que utiliza Excel para contar las fechas. En el caso de los porcentajes al desaparecer su valore se verán en tanto por uno que es como trabaja Excel.

Para volver a la situación anterior simplemente se ha de volver a pulsar sobre Mostrar fórmulas y lo volveremos a visualizar como anteriormente se encontraba.



Comprobación de errores

Podemos rastrear los errores y obtener información sobre ellos. Uno de los errores más típicos es el que se produce al crear una Referencia circular. De este aspecto trataremos un poco más abajo.

Evaluar fórmula

Es una opción que nos permite evaluar las diferentes parte de una fórmula. Es muy útil cuando tenemos fórmulas de gran extensión y complejidad. En versiones anteriores esta versión no existía y lo hacíamos pulsando la tecla de función sobre el tramo de fórmula que deseábamos evaluar. Esto se comentan un poco más abajo en el apartado denominado F9.

Referencia circular

Es un error típico en Excel que se produce cuando se crea una fórmula en una celda que utiliza como argumento alguna otra celda que directamente o a través de otras llega a estar vinculada con la celda que pretendemos calcular. En ese caso se crea una cadena de vínculos que forman un círculo cerrado. Esto produce un error del cual Excel no avisa, indicando la celda implicada en el error, para que lo solucionemos, ya que en caso contrario los cálculo de ese conjunto de celdas y todas las relacionadas con ellas no son estables, puesto que cada vez que se realice un recálculo pueden llegar a variar.

Para obtener más información sobre ello y ver un ejemplo puede consultar el post Referencia circular.

F9

Cuando editamos una fórmula podemos hacerlo bien pulsando sobre ella con el ratón, haciendo doble click o bien, pulsando la tecla de función F2. Una vez que estamos en modo edición, podemos seleccionar con el ratón parte de una fórmula y si en ese momento pulsamos F9, la zona seleccionada se evaluará, y nos dará un valor numérico. Esta operación se puede seguir repitiendo sobre otras zonas de la fórmula y nos dará los valores numéricos que va tomando esa expresión. Si ahora pulsamos ENTER, hablemos perdido la fórmula original y quedarán validados los valores numéricos que estemos viendo. Para evitar perder la fórmula original debemos pulsar la tecla ESC, y así evitamos que esos valores numéricos queden permanentemente.

Consejo con los paréntesis

Es frecuente que al introducir fórmulas con muchos paréntesis resulte que al validar la fórmula nos de un error debido a que hemos olvidado cerrar algún paréntesis. Un truco que podemos emplear es que justo después de abrir un paréntesis, le cerramos y luego escribimos lo que debe llevar en su interior. De esta forma al abrir y cerrar cada paréntesis nos aseguramos de que van en parejas.