jueves, 27 de noviembre de 2008

Cuadro de Amortización Automático

Descargar el fichero autocuadro.xlsm

Vamos a automatizar la generación de un Cuadro de Amortización de un Préstamo. Al cambiar el principal o el tipo de interés las celdas del cuadro se actualizan, como es lógico al tratarse de una hoja de cálculo. Pero lo que no se actualiza de forma automática es el número de filas del cuadro al variar la duración del préstamo. Para este caso se ha de utilizar una macro que cambie el número de filas del cuadro al cambiar la duración de la operación financiera.


El ejemplo se desarrolla en varias hojas:
  1. anual: Préstamo francés con pago anual constante
  2. mensual: Préstamo francés con pago mensual constante
  3. carencia: Préstamo francés con meses de carencia
  4. italiano: Préstamo de cuota de amortización constante y carencia
La macro que recalcula el cuadro de amortización es la siguiente:


Código:

Sub mCuadro()
Call mLimpia
Call mPeriodos
Call mCopia
Range("A1").Select
End Sub
Sub mLimpia()
Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Range("A1").Select
End Sub
Sub mPeriodos()
Dim fin As Integer
fin = Range("G4").Value + 10
Range("B10:B11").Select
Selection.AutoFill Destination:=Range("B10:B" & fin)
Range("B10:B" & fin).Select
Range("A1").Select
End Sub
Sub mCopia()
Dim fin As Integer
fin = Range("G4").Value + 10
Range("C11:G11").Select
Selection.AutoFill Destination:=Range("C11:G" & fin)
End Sub

En las dos últimas hojas (carencia, italiano) la macro no se lanza pulsando con el ratón sobre el botón que se ha creado para lanzar la macro. En estos casos no existe botón, y la macro se lanza simplemente escribiendo el número de años del préstamo (celda D6). Inmediatamente despues de validar la celda la macro se ejecuta y se actualiza el cuadro de amortización.

La macro que permite esta ejecución automática no se programa en un Módulo sino que se ha de escribir en el apartado 'Microsoft Excel Objetos' y en la hoja de la que se trate. Es una macro que se lanza al producirse un evento. Concretamente el evento es un Worksheet_Change que permite lanzar un proceso al producirse un cambio en la hoja. Ese cambio en nuestro caso es un cambio en el Target que es la celda D6.



Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$6" Then
Call mCuadro
End If
End Sub


lunes, 24 de noviembre de 2008

Generación de números aleatorios únicos (sin repetición)

Descargar el fichero RNDunicos.xlsm


Generar números aleatorios en Excel es tarea sencilla. Con la función =ALEATORIO(), con la función =ALEATORIO.ENTRE, y en lenguaje de Visual Basic con la función RND, que es la abreviatura de random. Por el contrario, si lo que deseamos es generar una serie de números aleatorios sin repetición la tarea se complica un poco, ya que debemos ir comprobando por cada número nuevo, si se encuentra entre los anterioremente generados y en caso afirmativo desecharle y generar otro.



La siguiente macro nos permite realizar la tarea descrita.


Código:

Sub unicos1()
Dim i As Integer, j As Integer
Dim A() As Long
Dim esta As Boolean
Dim x As Long, y As Long, z As Long, num As Long
x = Application.InputBox(prompt:="Entre el número aleatorio inicial" _
        , Title:="Generador de Números Aleatorios", Default:=1, Type:=1)
y = Application.InputBox(prompt:="Entre el número aleatorio final" _
        , Title:="Generador de Números Aleatorios", Default:=1000, Type:=1)
z = Application.InputBox(prompt:="¿Cuantos números aleatorios desea generar?" _
        & " (<15000 _="" default:="100," exit="" if="" sub="" then="" title:="Generador de Números Aleatorios" type:="1)" z=""> 15000 Then z = 15000
If z > y - x + 1 Then
    MsgBox "!Ha especificado más números " _
    & "de los que son posibles en el rango!"
    Exit Sub
End If
ReDim A(z)
Randomize
A(1) = Int((y - x + 1) * Rnd + x)
For i = 2 To z
    Do
        num = Int((y - x + 1) * Rnd + x)
        esta = False
        For j = 1 To i - 1
           If num = A(j) Then esta = True: Exit For
        Next j
    Loop While esta
    A(i) = num
Next i
For i = 1 To z
    Cells(i, 2) = A(i)
Next i
End Sub

Existe otra variante de esta macro que utiliza el comando Find para buscar el nuevo número aleatorio generado entre los anteriores. Si le encuentra entre los ya generados en la columna B buscará un nuevo número aleatorio. Al comando Find es necesario añadirle la propiedad LookAt:=xlWhole para que localice exactamente el número buscado. Si esto no se hiciera así, sucede que busca por las cifras, y si esta buscando el número 1, piensa que se encuentra, por ejemplo, en la cifra 107, o en la 819, esto es, siempre que aparezca un 1.


Código:

Sub unicos2()
    Dim x As Long, y As Long, z As Long, num As Long
    Dim control As Boolean
    Dim i As Long
    Dim celda As Range
    x = Application.InputBox("Entre el número aleatorio inicial" _
        , "Generador de Números Aleatorios", 1, , , , , 1)
    y = Application.InputBox("Entre el número aleatorio final" _
        , "Generador de Números Aleatorios", 1000, , , , , 1)
    z = Application.InputBox("¿Cuantos números aleatorios desea generar?" _
        & " (<15000 100="" 1="" _="" aleatorios="" de="" enerador="" exit="" if="" meros="" n="" sub="" then="" z=""> 15000 Then z = 15000
    If z > y - x + 1 Then
        MsgBox "!Ha especificado más números " _
            & "de los que son posibles en el rango!"
        Exit Sub
    End If
    Randomize
    Cells(1, 2) = Int((y - x + 1) * Rnd + x)
    For i = 2 To z
        Do
            control = False
            Randomize
            num = Int((y - x + 1) * Rnd + x)
            Set celda = Range("b1", Range("b1").End(xlDown).Address). _
            Find(num, LookIn:=xlValues, LookAt:=xlWhole)
            If Not (celda Is Nothing) Then
                control = True
            End If
        Loop Until Not control
        Cells(i, 2) = num
    Next
End Sub

domingo, 2 de noviembre de 2008

Funciones matriciales en Excel

Puede descargar los siguientes archivos.


Las funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcionan a la hoja de cálculo una enorme potencia. Las funciones matriciales, en general, abarcan varias celdas, aunque en algunos casos únicamente se centran en una única celda. Las fórmulas matriciales se identifican ya que se encuentran entre corchetes {}. Veamos algunas aplicaciones. 

Hoja 'Formula'

Las fórmulas matriciales pueden abarcar varias celdas o una sola. En esta hoja vamos a ver la potencias de cálculo de este tipo de expresiones cuando arrojan el resultado en una sola celda.


Para introducir una fórmula matricial se han de seguir tres pasos:
  1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Si la fórmula matricial no abarca más de una celda simplemente no situaremos en esa celda.
  2. Escribir la fórmula propiamente dicha
  3. Para validar no se ha de pulsar INTRO, sino que lo que hemos de pulsar es la combinación de tres teclas: CONTROL+MAYUSCULAS+INTRO
Al ver una función matricial en Excel la verá ente llaves {}. Estas llaves no las debe introducir usted al escribir la fórmula, las introducirá Excel al validar la función pulsando CONTROL+SHIFT+INTRO.

Recuerde que la tecla de MAYUSCULAS también se la llama SHIFT.


viernes, 24 de octubre de 2008

Transferir los datos de un Rango a un Array

Descargar el fichero pasa_matriz.xlsm.

Deseamos transferir los datos que existen en el rango de una hoja a una matriz (ARRAY) mediante una macro. Vamos a verlo por varios métodos. El primer método es el clásico, cargando dato a dato en la matriz. Otro método nos permite cargar la matriz de una sola vez. También vamos a conocer cómo se determina la dimensión de una matriz.

Método Clásico

Podemos utilizar el método clásico que consiste en tomar los datos uno a uno del rango e ir alimentando la matriz. Esto se puede hacer con bucles FOR..NEXT.

Imagine que el rango es B4:D13 y esta lleno de datos (algunos numéricos y otros alfanuméricos). Queremos trasladar todos los datos a la matriz X de dimensión X(1 to 10, 1 to 3).

Utilicemos el método clásico consistente en pasar los datos del rango a la matriz de uno en uno. Esto se hace utilizando, en este caso, dos bucles FOR..NEXT anidados.
Sub pasardatos()
Dim MiMatriz(1 To 10, 1 To 3)
Dim i As Integer, j As Integer
For i = 1 To 10
  For j = 1 To 3
    MiMatriz(i, j) = Worksheets("Hoja1").Cells(3 + i, j + 1).Value
  Next j
Next i
End Sub
El otro método es pasar el rango de datos de una sola vez a la matriz.

Pruebe con esto:
MiMatriz = [B4:D13]
o bien
MiMatriz = Range("B4:D13")
y tambien funciona al reves:
[F4:H13] = MiMatriz
o bien
Range("F4:H13") = MiMatriz
Se ha de tener en cuenta que si declaras la matriz como una matriz dará error.

Declarando la variable como una matriz no funciona.

Me explico. Si prueba las cuatro siguientes macros:
Sub Matrix1()
Dim X(11, 5)
X = Range("B4:D13")
End Sub

Sub Matrix2()
Dim X As Double
X = Range("B4:D13")
End Sub

Sub Matrix3()
Dim X As Variant
X = Range("B4:D13")
End Sub

Sub Matrix4()
Dim X
X = Range("B4:D13")
End Sub
comprobara que las dos primeras macros dan error. Las que funcionan bien son la tercera y la cuarta.

Si lo que queremos es tomar un valor de la matriz (por ejemplo el primero, el de la celda B4) y luego dejarlo en la celda H1 hacemos lo siguiente.
Sub test()
Dim x
x = [B4:D13]
[H1]= x(1, 1)
End Sub

¿Cómo se puede conocer la dimensión de la matriz creada?

Supongamos que creo una función, como esta:
Function MiRef(Rng)
Dim X
X = [Rng]
'......
End Function
Para tratar los elementos de la matriz debo conocer su dimensión. Por
ejemplo, sería interesante saber si tiene 10 filas y 4 columnas, o la
dimensión de que se trate. ¿Cómo saber esto?.

Disponemos de dos métodos:

1) Tomar las dimensiones del rango a la hora de asignar:
Function test1(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = rng.Columns.Count
y = rng.Rows.Count
test1 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function
2) Tomar las dimensiones de la matriz:
Function test2(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = UBound(Matriz, 2)
y = UBound(Matriz, 1)
test2 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function

También puede interesarle ...

jueves, 16 de octubre de 2008

Ordenar Abecedario

Descargar el fichero: abecedario.xlsm

Se trata de un juego desarrollado en Excel. Consiste en ordenar afabéticamente 5 letras (ABCDE) que previamente nos dan desordenadas. Existen 10 niveles de dificultad. Lo primero que hemos de hacer es elegir el nivel de dificultad y luego ordenar las letras utilizando los bonotes: 1, 2, 3 y 4, que permuta cada uno de ellos un par de letras contíguas.


En la Hoja 2 disponemos de todas las permutaciones posibles que se pueden llegar a conseguir con las cinco letras. Se encuentran agrupadas según nivel de dificultad. Así el nivel 1 supone que el juego se resuelve pulsando un único botón (1, 2, 3 ó 4). De nivel 1 existen 4 posibles permutaciones (en color amarillo). De nivel 2 existen 9 posibles permutaciones (color rosa). De nivel 3 existen 15 posibles permutaciones (color verde). Y así hasta llegar a nivel 10 en el que la única combinación de letras es: EDCBA.


Para poder hacer operativo el juego se han de utilizar diversas macros. Unas para generar de forma aleatoria la combinación de letras elegida inicialmente. Por ejemplo, si el jugador elige nivel 3, el juego presenta de forma aleatoria una de entre las 15 posibles permutaciones que corresponden a este nivel.La macro 'genera' rellena la columna K de la Hoja 2 con las 120 permutaciones posibles que se obtienen al combinar 5 caracteres.



Las permutaciones en este caso se realizaan con números y no con letras. Posteriormente se transformaran esos números en letras.



Existe otra forma de obtener las 120 permutaciones de 5 letras. Esto se hace en la Hoja 3 utilizando la macro denominada 'MisPermutaciones'. No es un método muy ortodoxo pero funciona. Se basa en exigir que se cumpla que el producto sea el factorial de 5 (5!=120) y que la suma sea 15.


sábado, 4 de octubre de 2008

Simulación de Montecarlo: aplicación financiera

Descargar el fichero: simulabono.xls

En este fichero de Excel realizamos un caso de simulación de Montecarlo aplicado a Renta Fija. Más concretamente, lo que hacemos es trabajar con bonos en los que el cupón anual no es de cuantía cierta sino aleatoria. El cupón se ajusta a una distribución normal de media 500 y desviación 50. N(500;50).



=DISTR.NORM.INV(ALEATORIO();500;50)






Conocida la ETTI (Estructura Temporal de los Tipos de Interes) o Curva de Tipos podemos calcular el precio de un bono. Para el bono A (celda amarilla) el precio se calcula con la siguiente expresión:




{=-SUMA((Flujosa/(1+ETTI)^Tiempo))}



Los corchetes indican que se trata de una fórmula matricial que se introduce no pulsando INTRO, sino pulsando Control+Mayúsculas+INTRO.



En la celda F5 (color azul) indicamos en cuantos años madura el Bono B, utilizamondo la fórmula:



=ALEATORIO.ENTRE(8;9)


Esta fórmula genera números aleatorios enteros entre un mínimo y un máximo. En este caso hemos escrito entre 8 y 9. Se utiliza para indicar la duración variable en años del bono B.


Si la fórmula fuera =ALEATORIO.ENTRE(9;9) no supondría variación aleatoria, ya que el número de años de duración sería siempre 9.


Al pulsar la tecla F9 (recálculo manual) los valores aleatorios se recalculan. Esto supone que cambien el importe de los cupones de los bonos e incluso la duración del Bono B. Lo que implica que la TIR (Tasa Interna de Rentabilidad) que expresa la rentabilidad del bono sea una variable aleatoria.



Vamos a estudiar la TIR del Bono B. Al tratarse de una variable aleatoria la identificaremos estudiando su distribución de probabilidad. Por el Teorema Central del Límite sabemos que al trabajar con múltiples funciones de distribución, en el caso extremo (cuando n tiende a infinito, o en este caso cuando el número de iteraciones es suficientemente grande) casi siempre la distribución que se obtienen en una Normal o Campana de Gauss.


Para generar las iteraciones, recalculamos la TIR del Bono B un elevado número de veces, por ejemplo 10.000 y anotamos en al columna H las concrecciones de la TIR así obtenida. Con esos 10.000 valores de TIR efectuamos un análisis de frecuencias, creando un histograma de frecuencias tal y como se vió en un post anterior. El análisis del histograma nos permite ajustar la distribución a una Normal, cuya media y varianza podemos calcular.

En el caso de que la duración del Bono B sea aleatoria entre 8 y 9, obtenemos dos Campanas de Gauss.




Utilizamos una macro para ir anotando en la columna H las diferentes TIR que se obtienen en las iteraciones.



Sub montecarlo()
Dim n 'número de interaciones
Dim i
Application.ScreenUpdating = False
n = InputBox("Introduzca el número de iteraciones", _
"Entrada de datos", 10000)
For i = 1 To n
Cells(i + 5, 8) = Range("F19")
Next i
Application.ScreenUpdating = True
End Sub

miércoles, 1 de octubre de 2008

Glosario: Traducción de funciones


Muchas empresas trabajan con Excel en inglés para poder reportar informes a la matriz. Otro motivo por el que se necesita conocer la traducción de funciones español-inglés es porque al programar Macros todas las funciones a las que llamemos han de estar en inglés.

Los ficheros glosario.xls y glosarioALL.xls tienen la traducción entre español y diversos idiomas. La diferencia entre ambos ficheros radica en que el pimero tien únicamente las funciones básicas, y el segundo (glosarioALL.xls) contiene además de las básicas las complementarias. Por ejemplo, la función TIR.NO.PER que es una función financiera que calcula la TIR con fechas, es una función que complementaria.

Seguidamente damos un listado de las funciones básicas.



English Spanish
ABS ABS
ACOS ACOS
ACOSH ACOSH
NOW AHORA
RAND ALEATORIO
AREAS AREAS
ASIN ASENO
ASINH ASENOH
ATAN ATAN
ATAN2 ATAN2
ATANH ATANH
YEAR AÑO
DCOUNT BDCONTAR
DCOUNTA BDCONTARA
DSTDEV BDDESVEST
DSTDEVP BDDESVESTP
DGET BDEXTRAER
DMAX BDMAX
DMIN BDMIN
DPRODUCT BDPRODUCTO
DAVERAGE BDPROMEDIO
DSUM BDSUMA
DVAR BDVAR
DVARP BDVARP
CRITBINOM BINOM.CRIT
LOOKUP BUSCAR
HLOOKUP BUSCARH
VLOOKUP BUSCARV
CHAR CARACTER
CELL CELDA
CODE CODIGO
CORREL COEF.DE.CORREL
SKEW COEFICIENTE.ASIMETRIA
RSQ COEFICIENTE.R2
MATCH COINCIDIR
COLUMN COLUMNA
COLUMNS COLUMNAS
COMBIN COMBINAT
CONCATENATE CONCATENAR
COUNT CONTAR
COUNTBLANK CONTAR.BLANCO
COUNTIF CONTAR.SI
COUNTA CONTARA
COS COS
COSH COSH
COVAR COVAR
GROWTH CRECIMIENTO
QUARTILE CUARTIL
KURT CURTOSIS
DB DB
DDB DDB
FIXED DECIMAL
RIGHT DERECHA
OFFSET DESREF
STDEV DESVEST
STDEVP DESVESTP
DEVSQ DESVIA2
AVEDEV DESVPROM
DAY DIA
DAYS360 DIAS360
WEEKDAY DIASEM
ADDRESS DIRECCION
WEIBULL DIST.WEIBULL
BETADIST DISTR.BETA
BETAINV DISTR.BETA.INV
BINOMDIST DISTR.BINOM
CHIDIST DISTR.CHI
EXPONDIST DISTR.EXP
FDIST DISTR.F
FINV DISTR.F.INV
GAMMADIST DISTR.GAMMA
GAMMAINV DISTR.GAMMA.INV
HYPGEOMDIST DISTR.HIPERGEOM
LOGINV DISTR.LOG.INV
LOGNORMDIST DISTR.LOG.NORM
NORMDIST DISTR.NORM
NORMSDIST DISTR.NORM.ESTAND
NORMSINV DISTR.NORM.ESTAND.INV
NORMINV DISTR.NORM.INV
TDIST DISTR.T
TINV DISTR.T.INV
VDB DVS
CHOOSE ELEGIR
FIND ENCONTRAR
INT ENTERO
STEYX ERROR.TIPICO.XY
ISBLANK ESBLANCO
ISERR ESERR
ISERROR ESERROR
ISLOGICAL ESLOGICO
ISNA ESNOD
ISNONTEXT ESNOTEXTO
ISNUMBER ESNUMERO
TRIM ESPACIOS
ISREF ESREF
ISTEXT ESTEXTO
LINEST ESTIMACION.LINEAL
LOGEST ESTIMACION.LOGARITMICA
EXP EXP
MID EXTRAE
FACT FACT
DATE FECHA
DATEVALUE FECHANUMERO
ROW FILA
ROWS FILAS
FISHER FISHER
FREQUENCY FRECUENCIA
GAMMALN GAMMA.LN
DEGREES GRADOS
SEARCH HALLAR
HOUR HORA
TIMEVALUE HORANUMERO
TODAY HOY
REGISTER.ID ID.REGISTRO
EXACT IGUAL
INDEX INDICE
INDIRECT INDIRECTO
INFO INFO
INTERCEPT INTERSECCION
CONFIDENCE INTERVALO.CONFIANZA
LEFT IZQUIERDA
RANK JERARQUIA
LARGE K.ESIMO.MAYOR
SMALL K.ESIMO.MENOR
LEN LARGO
CLEAN LIMPIAR
CALL LLAMAR
LN LN
LOG LOG
LOG10 LOG10
MAX MAX
UPPER MAYUSC
MDETERM MDETERM
TRIMMEAN MEDIA.ACOTADA
HARMEAN MEDIA.ARMO
GEOMEAN MEDIA.GEOM
MEDIAN MEDIANA
MONTH MES
MIN MIN
LOWER MINUSC
MINUTE MINUTO
MINVERSE MINVERSA
MMULT MMULT
MODE MODA
DOLLAR MONEDA
FLOOR MULTIPLO.INFERIOR
CEILING MULTIPLO.SUPERIOR
N N
NEGBINOMDIST NEGBINOMDIST
NOT NO
NA NOD
PROPER NOMPROPIO
STANDARDIZE NORMALIZACION
NPER NPER
TIME NSHORA
ROMAN NUMERO.ROMANO
OR O
PMT PAGO
IPMT PAGOINT
PPMT PAGOPRIN
PEARSON PEARSON
SLOPE PENDIENTE
PERCENTILE PERCENTIL
PERMUT PERMUTACIONES
PI PI
POISSON POISSON
POWER POTENCIA
PROB PROBABILIDAD
PRODUCT PRODUCTO
AVERAGE PROMEDIO
FORECAST PRONOSTICO
CHITEST PRUEBA.CHI
CHIINV PRUEBA.CHI.INV
FTEST PRUEBA.F
FISHERINV PRUEBA.FISHER.INV
TTEST PRUEBA.T
ZTEST PRUEBA.Z
RADIANS RADIANES
SQRT RAIZ
PERCENTRANK RANGO.PERCENTIL
ODD REDONDEA.IMPAR
EVEN REDONDEA.PAR
ROUND REDONDEAR
ROUNDUP REDONDEAR.MAS
ROUNDDOWN REDONDEAR.MENOS
REPLACE REEMPLAZAR
REPT REPETIR
MOD RESIDUO
SECOND SEGUNDO
SIN SENO
SINH SENOH
IF SI
SIGN SIGNO
SLN SLN
SUBTOTAL SUBTOTALES
SUM SUMA
SUMSQ SUMA.CUADRADOS
SUMPRODUCT SUMAPRODUCTO
SUMIF SUMAR.SI
SUMX2PY2 SUMAX2MASY2
SUMX2MY2 SUMAX2MENOSY2
SUMXMY2 SUMAXMENOSY2
SUBSTITUTE SUSTITUIR
SYD SYD
T T
TAN TAN
TANH TANH
RATE TASA
TREND TENDENCIA
TEXT TEXTO
TYPE TIPO
ERROR.TYPE TIPO.DE.ERROR
IRR TIR
MIRR TIRM
TRANSPOSE TRANSPONER
TRUNC TRUNCAR
PV VA
VALUE VALOR
VAR VAR
VARP VARP
FV VF
NPV VNA
AND Y
FALSE FALSO
TRUE VERDADERO

lunes, 29 de septiembre de 2008

Solver y Buscar Objetivo

Descargar el fichero: doblar.xlsx

En Excel disponemos de dos magníficas herramientas para resolver ecuaciones sin necesidad de despejar la variable que deseamos obtener. Se trata de Solver y de Buscar Objetivo, aunque son mucho más que eso. En especial, Solver es una estupenda herramienta de optimización (cálculo de máximos y mínimos).


Buscar Objetivo
Utilicemos la ley de la capitalización compuesta.
Vamos a contestar a la pregunta siguiente:
¿En cuanto tiempo se doblara un capital en compuesta trabajando al 5%?
Para ello podemos utilizar la función financiera de Excel:
=NPER(tasapagova; vf; tipo)
=NPER(5%;;-1000;2000)
Que arroja un resultado de 14,20669908 años.
Pero en este caso vamos a resolverlo con Buscar Objetivo.
 
Pasos:
  1. Poner unos datos arbitrarios en las celdas C6:C8
  2. Calculamos en C9 el capital final aplicando la ley de la compuesta
  3. En C8 se pone el tipo de interés del 5% que es un dato del enunciado
  4. Se lanza ‘Buscar Objetivo’ que esta en el menú Herramientas (en Excel 2007 esta en Datos, ‘Análisis Y si’)
  5. En ‘Definir la celda’ siempre se debe poner la celda que lleva la fórmula, en este caso la C9
  6. En ‘Con el valor’ se debe poner a mano (no deja pinchar una celda) el valor al que se quiere llegar
  7. En ‘Para cambiar la celda’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C7.
Solver

Vamos ahora a resolver la siguiente cuestión:
Calcular a qué tipo se ha de trabajar en compuesta para doblar el capital en 10 años.
Se puede responder facilmente utilizando una función de Excel que calcula el tipo de interés de este tipo de operaciones. La función es:
=TASA(nper;pago;va;vf;tipo;estimar)
En nuestro caso:
=TASA(10;;-1000;2000)
que  da un resultado de: 7,17735% anual.
Podemos resolverlo con Solver al igual que lo haríamos con ‘Buscar Objetivo’. Aunque Solver es mucho mejor que ‘Buscar Objetivo’, no en vano es una potente herramienta de optimización.
Nosotros no la vamos a utilizar para calcular máximos y mínimos, sino para obtener valores a los que puede llegar una celda objetivo.
Solver sólo estará disponible si se activa el Complemento que permite su utilización. En Excel 2003 se activa en: Herramientas, Complementos, Solver. En Excel 2007 se activa pulsando el botón del Office (ese botón redonde que tienes arriba a la izquierda, le denominan The Ribbon), luego elige ‘Opciones de Excel’, y a la izquierda veras ‘Complementos’, selecciona Solver y activalo. Cuando actives Solver, de paso marca también ‘Herramientas para Análisis’ que permite disponer de muchas más funciones en Excel.
Una vez activado el complemento para usarlo en una hoja, en Excel 2003 lo tiene en Herramientas, y en Excel 2007 esta en Datos.




Pasos:
  1. Poner unos datos arbitrarios en las celdas C16:C18
  2. Calculamos en C19 el capital final aplicando la ley de la compuesta
  3. En C17 se ponen los años que según el enunciado son 10
  4. Se lanza ‘Solver’ que esta en el menú Herramientas (en Excel 2007 esta en Datos).
  5. En ‘Celda Objetivo’ siempre se debe poner la celda que lleva la fórmula, en este caso la C19
  6. Valor de la Celda Objetivo, marcar la casilla que pone ‘Valor de’ (no marcar ni Máximo, ni Mínimo) y poner el valor 2000
  7. En ‘Cambiando las celdas’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C18.



La versión de Office 2010 Starter es una versión básica que ahora viene instalada en muchos ordenadores nuevos pero que no tiene muchas de las opciones de menú, ni herramientas que necesitamos.

Esta es una imagen de la versión Starter donde puedes ver los recortados menús de que disfruta.




Activar las Macros para que funcione Solver

Solver es un complemento de Excel, que está programado en lenguaje de macros. Las macros las usaremos más adelante para hacer algunas formulas personalizadas. Eso ya os lo contaré más adelante, cuando veamos rentas. De momento tienes que saber que una Macro es un programa y que como tal hay gente que en su día programaron virus en lenguaje de macros. Por este motivo Microsoft estableció varios niveles de seguridad,
para indicarle a Excel si quieres que al abrir un fichero que lleve macros las habilite o no.

Si tienes Excel 2003 o versiones anteriores para habilitar las macros debes hacer lo siguiente:
Herramientas, Macros, Seguridad, Nivel bajo o Nivel Medio.

Lo recomendable suele ser poner nivel medio de seguridad, de esa forma cuando abras un fichero de Excel que lleva macros te preguntará que si quieres habilitarlas. Si te fías de la fuente o has pasado un antivirus le dices que SI quieres habilitar las macros, y si no te fías no las habilitas, pero entonces no son operativas.

Como todos tenemos antivirus más o menos actualizados y efectivos, puedes poner el nivel bajo de seguridad, así no te estarán preguntando cada vez que abras un fichero que lleve macros.

Para Excel 2007, la cosa se complica. Primero debemos obtener la pestaña PROGRAMADOR que es una pestaña que de entrada no aparece. Las que aparecen con la instalación son: Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar, Vista.

Para obtener la pestaña PROGRAMADOR (en Excel 2007) debes ir al botón redondo de la esquina superior izquierda (se llama 'el botón de Office' porque es común a todos los programas de Microsoft Office: Word, PowePoint,..). Pulsando en ese botón redondo, verás abajo que pone 'Opciones de Excel'. En la ventana 'Mas frecuentes' verás una opción que esta desmarcada y pone: "Mostrar ficha Programador en la cinta de opciones". Marca esa opción y pulsa Aceptar. Así conseguirás tener la pestañita (ficha) PROGRAMADOR.

Se supone que ya tenemos la ficha PROGRAMADOR. Pulsa en ella, y luego en 'Seguridad de Macros', y elige 'Habilitar todas las macros'.

De esta forma tendremos habilitadas las macros. Para que esto sea efectivo debemos salirnos del fichero y volver a entrar.

Y para todos, si es importante aprender a conseguir la ficha PROGRAMADOR (si tienes Excel 2007), y conseguir poner un nivel bajo de seguridad o nivel medio.



Buscar Objetivo

Puedes usar la función TASA que esta disponible en todos los ordenadores con Excel,
sean de la versión que sean. O puedes usar 'Buscar Objetivo' que aunque menos preciso
que Solver, para este ejemplo es más que suficiente. Y también esta disponible en todos
los ordenadores. No es necesario instalar nada adicional.





Para Excel 2003 y anteriores


¿Dónde esta Buscar Objetivo?


Esta en el menú Herramientas.

¿Dónde esta SOLVER?


Al ser Solver un complemento, primero se ha de instalar. Para instalarlo haz: Herramientas, Complemento, y marca Solver, y ya que estamos marca 'Herramientas para Análisis'. Al aceptar veras que el ordenador carga el complemento. Si en su día realizaste una instalación completa, tarda en cargar los complementos un segundo. Si en su día no realizaste una instalación completa de Excel te pedirá que introduzcas el disco con el programa Office que instalaste en su día.

Comprobemos ahora que ya tengo Solver disponible. Lo encontrarás en el menú Herramientas, Solver. Pruebalo con un ejemplo. Verás que es mucho mejor que Buscar Objetivo. Es importante conseguirlo ya que en la práctica 2 y sucesivas lo usaremos de forma intensa.

'Herramientas para Análisis' lo que hace es aumentar muchísimo las funciones disponibles. Esto nos resultará necesario para las próximas prácticas, ya que utilizaremos algunas de estas funciones complementarias.

Vídeo: Solver y Buscar objetivo



Vídeo: Buscar objetivo y Solver



Caso práctico: Equilibrar una operación financiera

Veamos un caso práctico de tipo financiero. Vamos a calcular la cuantía necesaria para cuadrar una operación financiera. La calcularemos por tres métodos siendo uno de ellos Solver y otro Buscar objetivo.




Caso práctico: Efectivo de una Letra del Tesoro

Préstamo Blindado

Puede descargar los siguientes archivos de Excel.

Un préstamo blindado es aquel en el que se fija el pago periódico (la mensualidad, por ejemplo) pese a estar contratado a tipo variable. Al variar el tipo de interés lo que varía es la duración del préstamo, de forma que si el tipo de interés aplicado aumenta la duración total del préstamo también ha de aumentar y si el tipo de interés disminuye conseguiremos disminuir los periodos necesarios para amortizar completamente el principal solicitado.
El préstamo blindado es el que mantiene el término amortizativo constante, pese a estar contratado a tipo variable, siendo esto posible al variar el plazo total del préstamo.



En el ejemplo se pagan todos los meses 800 € independientemente del tipo de interés aplicado.

Tenemos que determinar el primer mes en el que el Capital Vivo (deuda pendiente, o saldo financiero) tiene signo negativo. En nuestro caso, esto se produce en el mes 153. Esto supone que necesitamos hacer un nuevo cuadro en el que los 152 primeros meses son copia del anterior, pero en el mes 153 debemos ajustar para saldar la operación.

Para determinar el cierre del Cuadro de Amortización, se han de seguir estos pasos:

1º En el mes 153 se amortiza justo por el importe del Capital Vivo del periodo anterior. Por eso la fórmula de la celda U167 es =+V166.

2º Se calcula la mensualidad (término amortizativo mensual) como suma de la Cuota de Intereses (Is) más la Cuota de Amortización (As).

3º El Capital Vivo del mes 153 es igual al del mes anterior menos lo que hemos amortizado este mes. Este valor debe ser cero para que el Préstamo quede saldado.






Utilidad y peligros del Préstamo Blindado

En momentos de posibles subidas de tipos de interés futuros permite al prestatario una cierta estabilidad en la cuantía de los pagos comprometidos a futuro, a costa de no garantizar el plazo de vencimiento.

El importe de la mensualidad se garantiza hasta cierto punto ya que para tipos de interés muy altos y/o términos amortizativos muy ajustados pudieramos encontrarnos en el caso límite del préstamo americano. Esto es, si la mensualidad únicamente cubre los intereses el plazo se hace infinito, y si los tipos de interes siguieran aumentando es muy dificil que el prestamista nos permitiera mantener el pago de una mensualidad que no cubre ni siquiera los intereses, ya que en este caso, el capital vivo o deuda pendiente aumenta con el tiempo, y esta situación no se podría mantener a largo plazo.

Vídeo

Se denominan préstamos blindados a los préstamos que siendo a tipo de interés variable suponen el pago de una cantidad constante durante toda la vida del préstamo, salvo en el último periodo, donde se ha de pagar lo necesario para ajustar la operación. La duración no se conoce a priori debido a que los tipos de interés futuros pueden variar. No podemos disminuir el término amortizativo (lo que pagamos en cada periodo) tanto como quisiéramos ya que al menos se han de cubrir los intereses devengados.



domingo, 28 de septiembre de 2008

Préstamo geométrico con amortización anticipada

Descargar el fichero GeoTotal.xlsm

Este ejemplo resuelve el caso de un préstamo variable en progresión geométrica anual, fraccionado mensual, a tipo de interés variable, con un posible periodo de carencia y amortización anticipada en cualquier mes. Ha sido necesario crear una función en el Editor de Visual Basic para calcular la nueva mensualidad, al producirse un pago adicional que acelera la amortización.

Las celdas amarillas son datos y se pueden modificar. Podemos cambiar el Euribor de los 10 años, el principal del préstamo, la duración, la razón anual de la progresión geométrica (q), el diferencial que se aplica al Euribor y los años de carencia.

La mensualidad se calcula aplicando una fórmula que utiliza una función definida por el usuario. Estas funciones se programan en el Editor de Visual Basic que es donde se programan las Macros.

La fórmula de la primera mensualidad es:

=SI(B16<=$C$12;F16+J16;(Origen(C16;H15;$C$10;$C$9;D16)/vageo(1;$C$10;$C$9-B16+1;(1+D16)^12-1))/VF(D16;12;-1)+J16)

Utiliza dos funciones definidas por el usuario: VAgeo y Origen.

Código:

Function VAgeo(C, q, n, i)
If q = 1 + i Then
  VAgeo = C * n / (1 + i)
Else
  VAgeo = C * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
End If
End Function

Function Origen(s, Csm1, q, n, i12)
If q = 1 + i Then
  If s - Int(s / 12) * 12 = 1 Then
      Origen = Csm1
  Else
      n = n - Int(s / 12)
      If (s / 12) - Int(s / 12) = 0 Then n = n + 1
      s = s - Int(s / 12) * 12
      If s = 0 Then s = 12
      i = (1 + i12) ^ 12 - 1
      ro = i / (1 - (1 + i12) ^ (-s + 1))
      beta = ro * n / (1 + i)
      Origen = -Csm1 * (1 + i12) ^ (-s + 1) * beta / (1 - beta)
  End If
Else
  If s - Int(s / 12) * 12 = 1 Then
      Origen = Csm1
  Else
      n = n - Int(s / 12)
      If (s / 12) - Int(s / 12) = 0 Then n = n + 1
      s = s - Int(s / 12) * 12
      If s = 0 Then s = 12
      i = (1 + i12) ^ 12 - 1
      ro = i / (1 - (1 + i12) ^ (-s + 1))
      beta = ro * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
      Origen = -Csm1 * (1 + i12) ^ (-s + 1) * beta / (1 - beta)
  End If
End If
End Function

Para resolver el problema de la amortización anticipada a mitad de un año se recurre a la idea de calcular el capital vivo al inicio de ese año bajo el supuesto de que la mensualidad calculada tras la amortización anticipada sea la que se mantiene constante durante todo el año.