La fecha de liquidación es la fecha en que se compra el cupón, por ejemplo un bono. La fecha de vencimiento es la fecha en que expira el cupón. Por ejemplo se emite un bono el 1 de enero de 2001, a 20 años, seis meses después es adquirido por un comprador. La fecha de emisión será el 1/1/2001, la fecha de liquidación el 1/7/2001, y la fecha de vencimiento el 1/1/2021, es decir 20 años de la fecha de emisión.
La celda B5 muestra el numero de dias del periodo de un cupón que se encuentra adosado al bono.
Celda B5=CUPON.DIAS(B1;B2;B3;B4)
Función CUPON.DIAS.L1
Calcula él numero de días desde el principio del periodo de un cupón hasta la fecha de liquidación
Sintaxis CUPON.DIAS.L1(liq; vencimiento;frec;base)
Liq: es 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, cuando el comprador adquirió el valor bursátil. Fecha de compra.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Frec: es él numero de pagos de cupones que se pagan por año. Para pagos anuales frec=1
Para pagos semestrales frec= 2, para pagos trimestrales, frec= 4
Base= determina en que tipo de base debe contarse los días.
Celda B5=CUPON.DIAS.L1(B1;B2;B3;B4)
En la celda B5 se calcula él numero de días desde el principio del periodo de un cupón hasta la fecha de vencimiento
Función CUPON.DIAS.L2
Calcula él numero de días desde la fecha de liquidación o compra hasta la fecha del próximo cupón
Sintaxis CUPON.DIAS.L2(liq; vencimiento;frec;base)
Liq: es 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, cuando el comprador adquirió el valor bursátil. Fecha de compra.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Frec: es él numero de pagos de cupones que se pagan por año. Para pagos anuales frec=1
Para pagos semestrales frec= 2, para pagos trimestrales, frec= 4
Base= determina en que tipo de base debe contarse los días.
Celda B5= CUPON.DIAS.L2(B1;B2;B3;B4) se calcula él numero de días comprendidos entre la fecha de compra y la fecha del próximo cupón
Comprobación
CUPON.DIAS.L1 22
+
CUPON.DIAS.L2 161
CUPON.DIAS 183
Calculo de fechas de vencimiento de cupones
La fecha en la celda de calculo debe tener Formato-Celda-Fecha-Tipo, caso contrario la función devuelve él numero de serie correspondiente.
Función CUPON.FECHA.L1
Calcula la fecha del cupón anterior a la fecha de liquidación (compra)
Sintaxis CUPON.FECHA.L1(liq;vencimiento;frec;base)
Liq: es 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, cuando el comprador adquirió el valor bursátil. Fecha de compra.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Frec: es él numero de pagos de cupones que se pagan por año. Para pagos anuales frec=1
Para pagos semestrales frec= 2, para pagos trimestrales, frec= 4
Base= determina en que tipo de base debe contarse los días.
Celda B5= CUPON.FECHA.L1(B1;B2;B3;B4)
Celda D5= CUPON.FECHA.L1(B1;B2;B3;B4)
Función CUPON.FECHA.L2
Calcula la fecha del próximo cupón después de la fecha de liquidación
Sintaxis CUPON.FECHA.L2(liq;vencimiento;frec;base)
Liq: es 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, cuando el comprador adquirió el valor bursátil. Fecha de compra.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Frec: es él numero de pagos de cupones que se pagan por año. Para pagos anuales frec=1
Para pagos semestrales frec= 2, para pagos trimestrales, frec= 4
Base= determina en que tipo de base debe contarse los días.
Celda B5= CUPON.FECHA.L2(B1;B2;B3;B4)
Celda D5= CUPON.FECHA.L2(B1;B2;B3;B4)
Las fechas de acuerdo a la frecuencia corresponden a la fecha final de semestres =2, o trimestres =4
Calculo de la cantidad de cupones pendientes
Función CUPON.NUM
Calcula él numero de cupones pagaderos entre las fechas de liquidación y vencimiento, redondeando al numero entero del cupón más cercano.
Sintaxis CUPON.NUM(liq;vencimiento;frec;base)
Liq: es 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, cuando el comprador adquirió el valor bursátil. Fecha de compra.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Frec: es él numero de pagos de cupones que se pagan por año. Para pagos anuales frec=1
Para pagos semestrales frec= 2, para pagos trimestrales, frec= 4
Base= determina en que tipo de base debe contarse los días.
Celda B5= CUPON.NUM(B1;B2;B3;B4) BONO 1
Celda D5= CUPON.NUM(B1;B2;B3;B4) BONO 2
El primer bono con cupones semestrales le quedadan 4 semestres hasta el 24/12/01
El segundo bono con cupones cuatrimestrales le quedan 8 cupones por vencer
Funciones referidas a la rentabilidad del bono
Función DURACION
Devuelve la duración de (método de Macauley) un bono considerando un valor nominal supuesto de $ 100. La duración se define, como el promedio ponderado del valor presente de los recursos generados y se usa como medida de la respuesta del precio de un bono a los cambios en el rendimiento.
Sintaxis DURACION(liq; vencimiento; cupon; rendimiento; frec.; base)
Liq: es la fecha de liquidacion del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Cupón: es la tasa de interés nominal anual( interés de los cupones) de un valor bursátil.
Rendimiento: es el rendimiento anual de un valor bursátil.
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días , adoptamos 3 correspondiente a 365 días al año.
Observaciones:
Si el argumento liq, o vencimiento no es una fecha valida, DURACION devuelve el valor de error #¡ NUM!
Si el argumento cupón <0 o si el argumento de rendimiento <0, DURACION devuelve el valor de error #¡ NUM!
Si el argumento frec. Es un numero distinto de 1, 2 o 4 DURACION devuelve el valor de error #¡NUM!
Si el argumento liq. >=vencimiento, DURACION devuelve el valor de error #¡ NUM!
Celda B8=DURACION(B2;B3;B4;B5;B6;B7)
Celda C8=DURACION(C2;C3;C4;C5;C6;C7)
Celda D8=DURACION(D2;D3;D4;D5;D6;D7)
El Bono 1 tiene una duración mayor que el Bono 2, por cuanto el Bono 2 tiene una tasa nominal de 2 puntos superior, por lo tanto el tiempo de recuperación de la inversión del Bono 2 es menor.
Si se compara el Bono 1 y el Bono 3, si bien en este el rendimiento baja 2 puntos con respecto al Bono 1, no compensa el incremento del 1% en la tasa nominal, y el periodo de recuperación
del Bono 3 es mas corto que el Bono 1. Por lo tanto se puede medir la relación entre la tasa nominal y el rendimiento
Función DURACION.MODIF
Calcula la duración por método modificado de un valor bursátil con valor nominal de $ 100
Sintaxis DURACION.MODIF(liq; vencimiento; cupón; rendimiento; frec.; base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Cupón: es la tasa de interés nominal anual( interés de los cupones) de un valor bursátil.
Rendimiento: es el rendimiento anual de un valor bursátil.
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días , adoptamos 3 correspondiente a 365 días al año.
Celda B8=DURACION.MODIF(B2;B3;B4;B5;B6;B7)
Celda C8=DURACION.MODIF(C2;C3;C4;C5;C6;C7)
Celda D8=DURACION.MODIF(D2;D3;D4;D5;D6;D7)
Función INT.ACUM
Calcula el interés acumulado de un valor bursátil que tenga pagos de interés periódico.
Sintaxis INT.ACUM(emision;primer_interes;liq;tasa;valor nominal;frec;base)
Emisión: es la fecha de emisión del valor bursátil
Primer_interes: es la fecha del primer pago de interés de un valor bursátil
Liq: es la fecha de liquidación del valor bursátil
Tasa: es la tasa de interés nominal anual (interés en los cupones) de un valor bursátil
Valor nominal: es el valor nominal del valor bursátil. Si se omite el valor nominal, INT.ACUM emplea $ 1.000
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días , adoptamos 3 correspondiente a 365 días al año.
Celda B9= INT.ACUM.(B2;B3;B4;B5;B6;B7;B8)
Celda C9= INT.ACUM.(C2;C3;C4;C5;C6;C7;C8)
Celda D9= INT.ACUM.(D2;D3;D4;D5;D6;D7;D8)
Como puede apreciarse en la Función INT.ACUM no interviene el rendimiento, sino exclusivamente la tasa de interés nominal. Si la tasa se duplica, él calculo de intereses también se duplica como puede se observa entre el Bono 1 y Bono 3
Función INT.ACUM.V
Calcula el interés acumulado de un valor bursátil con pagos de intereses al vencimiento.
Sintaxis INT.ACUM.V(emision;liq;tasa;valor nominal;base)
Emisión: es la fecha de emisión del valor bursátil
Liq: es la fecha de liquidación del valor bursátil
Tasa: es la tasa de interés nominal anual (interés en los cupones) de un valor bursátil
Valor nominal: es el valor nominal del valor bursátil. Si se omite el valor nominal, INT.ACUM emplea $ 1.000
Base: determina en que tipo de base deben ser contados los días , adoptamos 3 correspondiente a 365 días al año.
Celda B7= INT.ACUM.V(B2;B3;B4;B5;B6)
Celda C7= INT.ACUM.V(C2;C3;C4;C5;C6)
Celda D7= INT.ACUM.V(D2;D3;D4;D5;D6)
En los Bonos 1 y 3 se calcula el interés, manteniendo los Bonos un periodo irregular, en el caso del Bono 3 al tomar un año de plazo, se verifica que el importe que se obtuvo es el que corresponde a la tasa efectiva para ese periodo
Función PRECIO
Calcula el precio por $ 100 de valor nominal de un valor bursátil que paga una tasa de interés periódica
Sintaxis PRECIO(liq;vencimiento;tasa; rendimiento;valor_de_rescate;frec;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Tasa: es la tasa de interés nominal anual (interés en los cupones) de un valor bursátil
Rendimiento: es el rendimiento anual de un valor bursátil.
Valor_de_rescate: es el rendimiento del valor bursatil por cada $ 100 de valor nominal
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días , adoptamos 3 correspondiente a 365 días al año.
Celda B9= PRECIO(B2;B3;B4;B5;B6;B7;B8)
Celda C9= PRECIO(C2;C3;C4;C5;C6;C7;C8)
Celda B9= PRECIO(D2;D3;D4;D5;D6;D7;D8)
La función aplicada en las celdas B8, calcula el precio de compra el 15/3/94 por cada $ 100 de valor nominal.El Bono vence el 31/12/02, la tasa nominal de interés es de 4,85% y el rendimiento anual del 5,80%.
Se puede observar en los Bonos 2 y 3 que cuando se acerca la fecha de compra a la fecha de vencimiento, el precio se acerca mas al valor de rescate.
Función PRECIO.DESCUENTO
Calcula el precio por $ 100 de valor nominal de un valor bursátil con descuento
Sintaxis PRECIO(liq;vencimiento;descuento;valor_de_rescate;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Descuento: es la tasa de descuento en valor bursátil
Valor_de_rescate: es el rendimiento del valor bursátil por cada $ 100 de valor nominal
Base: determina en que tipo de base deben ser contados los días , adoptamos 3 correspondiente a 365 días al año.
Celda B7= PRECIO.DESCUENTO(B2;B3;B4;B5;B6)
Celda C7= PRECIO.DESCUENTO(C2;C3;C4;C5;C6)
Celda D7= PRECIO.DESCUENTO(D2;D3;D4;D5;D6)
La función en la celda B7 calcula el precio de compra el 15/3/94, por cada $ 100 de valor nominal, considerando una tasa de descuento del 7,5%
Función PRECIO.PER.IRREGULAR.1
Calcula el precio de un valor bursátil con un periodo irregular por cada $ 100 de valor nominal.
Sintaxis PRECIO.PER.IRREGULAR.1(liq;vencimiento;emision;prox_cupon;tasa;rendimiento;
valor_de_rescate;frec;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Emisión: es la fecha de emisión del valor bursátil
Prox_cupon: es la fecha del primer cupón del valor bursátil
Tasa: es la tasa de interés del valor bursátil
Rendimiento:es rendimiento anual del bono
Valor_de_rescate: es el rendimiento del valor bursátil por cada $ 100 de valor nominal
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Celda B11= PRECIO.PER.IRREGULAR.1(B2;B3;B4;B5;B6;B7;B8;B9;B10)
Con vencimientos semestrales el 15/10/89, se emite un bono, pero con un primer vencimiento el 1/3/90.
La fecha de vencimiento del bono es el 1/3/02. El bono tiene una tasa de rendimiento del 6,30% anual. La función en la celda B11 calcula el precio correspondiente a una compra por cada $ 100 nominales para la fecha 11/11/89
Función PRECIO.PER.IRREGULAR.2
Calcula el precio de un valor bursátil con un periodo irregular por cada $ 100 de valor nominal.
Sintaxis PRECIO.PER.IRREGULAR.2(liq;vencimiento;ultimo_interes;tasa;rendimiento;
valor_de_rescate;frec;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Ultimo_interes: es la fecha del ultimo cupon a vencer
Tasa: es la tasa de interés del valor bursátil
Rendimiento:es rendimiento anual del bono
Valor _de_rescate:es el rendimiento del valor bursatil por cada $ 100 de valor nominal.
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Celda B10= PRECIO.PER.IRREGULAR.2(B2;B3;B4;B5;B6;B7;B8;B9)
Con vencimientos de cupones semestrales, se compra un bono el 11/3/01. El 19/11/00, es la fecha de vencimiento del ultimo cupón a la compra.
El bono paga una tasa anual del 7,50% un rendimiento anual del 6,10%.
La función en la celda B10 calcula el precio por cada $ 100 de valor nominal.
Función PRECIO.VENCIMIENTO.
Calcula el precio por $ 100 de valor nominal de un valor bursátil que paga interés a su vencimiento.
SintaxisPRECIO.VENCIMIENTO(liq;vencimiento;emision;tasa;rendimiento;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Emisión: es la fecha de emisión del valor bursátil
Tasa: es la tasa de interés del valor bursátil
Rendimiento:es rendimiento anual del bono
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Las funciones escritas en las celdas B8 y C8 calculan el precio de compra al 13/02/01 y 5/04/96, respectivamente por cada $ 100 de valor nominal
Celda B8= PRECIO.VENCIMIENTO(B2;B3;B4;B5;B6;B7)
Celda C8= PRECIO.VENCIMIENTO(C2;C3;C4;C5;C6;C7)
Función RENDTO
Calcula el rendimiento de un bono, que paga intereses periódicos.
SintaxisPRECIO.VENCIMIENTO(liq;vencimiento;tasa;precio;valor_de_rescate;frac;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil.
Tasa: es la tasa de interés del valor bursátil
Precio:es el precio del valor del bono por cada $ 100 de valor nominal
Valor_de_rescate: es el rendimiento del valor bursatil por cada $ 100 de valor nominal.
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Se pagan $68 por $100, nominales el 20/05/93 de un bono que vence el 31/12/01. La tasa que paga el bono por cupones semestrales es de 7,50%.
La operación tiene un rendimiento que es calculado por la función introducida en la celda B9
Celda B9= RENDTO(B2;B3;B4;B5;B6;B7;B8)
Función RENDTO.DESC
Devuelve el rendimiento anual de un valor bursátil con descuento.
SintaxisPRECIO.VENCIMIENTO(liq;vencimiento;precio;valor_de_rescate;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil
Precio:es el precio del valor del bono por cada $ 100 de valor nominal
Valor_de_rescate: es el rendimiento del valor bursátil por cada $ 100 de valor nominal.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Se pagan $68 por $100, nominales el 20/05/93 de un bono con descuento que vence el 31/12/01
La operación tiene un rendimiento que es calculado por la función introducida en la celda B7
Celda B7= RENDTO.desc(B2;B3;B4;B5;B6)
Función RENDTO.PER.IRREGULAR.1
Calcula el rendimiento de un valor bursátil con un primer periodo irregular.
SintaxisRENDTO.PER.IRREGULAR.1(liq;vencimiento;emision;prox_cupon;tasa;precio; valor_de_rescate;frec;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil
Emisión: es la fecha de emisión del valor bursátil
Prox_cupon: es la fecha del primer cupón del valor bursátil
Tasa: es la tasa de interés del valor bursátil
Precio:es el precio del valor del bono por cada $ 100 de valor nominal
Valor_de_rescate: es el rendimiento del valor bursátil por cada $ 100 de valor nominal.
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Observaciones:
La fecha de liquidación, es la fecha en que se compra el cupón, por ejemplo un bono. La fecha de vencimiento es la fecha que expira el cupón.
Si el argumento liquidación, vencimiento, emisión, o prox_cupon, no es una fecha valida, RENDTO.PER.IRREGULAR 1 devuelve el valor de error #¡NUM!.
Si el argumento tasa >0, o si el argumento precio >=0, RENDTO.PER.IRREGULAR 1 devuelve el valor de error #¡NUM!.
Las fechas deben satisfacer la siguiente condición:
Vencimiento > prox_cupon >liquidación > emisión.
De lo contrario RENDTO.PER.IRREGULAR 1 devuelve el valor de error #¡NUM!.
Con vencimientos semestrales pero con un primer vencimiento el 1/3/90, se emite un bono el 15/10/89, con vencimiento el 1/3/02. La tasa de interés que paga el bono, es del 7,80%. Se calcula en la celda B11, aplicando la función respectiva el rendimiento correspondiente a una compra realizada el 11/11/89, por $ 85,50 respecto a $ 100 de valor nominal.
Celda B11=RENDTO.PER.IRREGULAR.1(B2;B3;B4;B5;B6;B7;B8;B9;B10)
Función RENDTO.PER.IRREGULAR.2
Calcula el rendimiento de un valor que tiene un ultimo periodo irregular.
SintaxisRENDTO.PER.IRREGULAR.2(liq;vencimiento;ultimo_interes;tasa;precio; valor_de_rescate;frec;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil
Ultimo_interes: es la fecha del ultimo cupón
Tasa: es la tasa de interés del valor bursátil
Precio:es el precio del valor del bono
Valor_de_rescate: es el rendimiento del valor bursátil por cada $ 100 de valor nominal.
Frec: es el numero de cupones que se pagan por año
Pagos anuales frec=1, semestrales frec.=2, cuatrimestrales frec.=4.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Con fecha 17/05/01, se compra un bono que vence el 12/07/01, con vencimientos semestrales de cupones. La fecha de vencimiento del ultimo cupón anterior a la compra fue el 11/11/00
El bono paga una tasa de interés del 5,80%. La función incluida en la celda B11 calcula el rendimiento del valor de la operación, suponiendo que el precio de compra es de $98,85 por cada $ 100 de valor nominal.
Celda B11=RENDTO.PER.IRREGULAR.2(B2;B3;B4;B5;B6;B7;B8;B9)
Función RENDTO.VENCTO
Calcula el rendimiento anual de un valor bursátil que paga interese al vencimiento.
SintaxisRENDTO.VENCTO(liq;vencimiento;emision;tasa;precio;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil
Emisión: es la fecha de emisión del valor bursátil
Tasa: es la tasa de interés en la fecha de emisión del valor bursátil
Precio:es el precio del valor del bono por cada $ 100 de valor nominal
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Bono 1: El 10/11/00 se emite un bono a un año, con vencimiento el 10/11/01. Se efectúa una compra el 22/03/01 de $ 100,15 por cada $ 100 de valor nominal. La celda B8 contiene la función que calcula el rendimiento de la operación su vencimiento.
Celda B8= RENDTO.VENCTO(B2;B3;B4;B5;B6;B7)
Bono 2: El 11/11/96 se emite un bono a cinco años, con vencimiento el 10/11/01. Se efectúa una compra el 22/03/98 de $ 88,15 por cada $ 100 de valor nominal. La celda C8 contiene la función que calcula el rendimiento de la operación su vencimiento.
Celda C8= RENDTO.VENCTO(C2;C3;C4;C5;C6;C7)
Inversiones financieras
Rendimiento de Inversiones
Funciones para realizar todos los cálculos relativos a: Valuación de Inversiones, Plazos, rendimientos etc.
Funciones
1) Función CANTIDAD RECIBIDA
2) Función TASA.DESC
3) Función TASA.INT
4) Función TASA NOMINAL
5) Función TIR
6) Función TIR.NO.PER
7) Función TIRM
8) Función VA
9)Función VF
10) Función VF.PLAN
11) Función VNA
12) Función VNA.NO.PER
Función CANTIDAD.RECIBIDA
Calcula la cantidad recibida al vencimiento de un valor bursátil completamente invertido.
Sintaxis. CANTIDAD.RECIBIDA(liq;vencimiento;inversion;descuento;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil
Inversión: es la cantidad de dinero que se ha invertido en el valor bursátil.
Descuento: es la tasa de descuento en el valor bursátil.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Observaciones:
La fecha de liquidación, es la fecha en que se compra el cupón, por ejemplo un bono. La fecha de vencimiento es la fecha que expira el cupón.
Si los argumentos liq o vencimiento no es una fecha valida, CANTIDAD.RECIBIDA, devuelve el valor de error#¡NUM!
Si el argumento inversión <=0 , o si el argumento descuento <=0, CANTIDAD.RECIBIDA devuelve el valor de error #¡NUM!
Si el argumento liq >= vencimiento, CANTIDAD.RECIBIDA devuelve el valor de error #¡NUM!
Ejemplo
Bono 1:
Se emite un bono de deuda a vencer el 25/06/01, a una tasa de descuento del 4,65%.
Significa que el bono pierde anualmente un 4,65% hasta su vencimiento.
En este caso se produce la compra 3 años antes de su vencimiento. La función incluida en la celda B7 calcula el monto a responder al vencer el mismo, que significa en este caso que la compra se realizo en un 13,95% menos
Celda B7=CANTIDAD.RECIBIDA(B2;B3;B4;B5;B6)
$ 644.973,85 | *13,95% | $ 89.973,85 |
$ 644.973,85 | -$ 89.973,85 | $ 555.000,00 |
Bono 2:
Se emite un bono de deuda a vencer el 25/06/01, a una tasa de descuento del 4,65%.
En este caso la compra se produce un año antes de su vencimiento.
En la celda C7 se calcula el monto a responder al vencer el bono, que significa que la compra se realizo a un valor de 4,64% menor
Celda C7=CANTIDAD.RECIBIDA(C2;C3;C4;C5;C6)
$ 581.988,31 | *4,64% | $ 26.988,31 |
$ 581.988,31 | -$ 26.988,31 | $ 555.000,00 |
Función TASA.DESC
Calcula la tasa de descuento de un valor bursátil
Sintaxis TASA.DES(liq;vencimiento;precio;valor_de_rescate;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil
Precio: es el precio por $ 100 de valor nominal del valor bursátil
Valor_de_rescate: es el rendimiento del valor bursátil por cada $ 100 de valor nominal
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Bono 1: El 26/06/98, se efectúa la compra del bono con fecha de vencimiento el 25/06/01.
La cotización del bono en el momento de compra es del 86,05% de su valor nominal, es decir $86,05 por cada $100. La función en la celda B7 calcula la tasa de descuento de la inversión
Celda B7=TASA.DESC(B2;B3;B4;B5;B6)
Bono 2: El 7/06/95, se efectúa la compra del bono con fecha de vencimiento el 5/06/01.
La cotización del bono en el momento de compra es del 69,54% de su valor nominal, es decir $69,54 por cada $100. La función en la celda C7 calcula la tasa de descuento de la inversión
Celda C7=TASA.DESC(C2;C3;C4;C5;C6)
Función TASA.INT
Calcula la tasa de interés para la inversión total en un valor bursátil
Sintaxis TASA.DES(liq;vencimiento;inversion;valor_de_rescate;base)
Liq: es la fecha de liquidación del valor bursátil. Fecha de compra del cupón.
Vencimiento: es la fecha de vencimiento del valor bursátil
Inversión: es la cantidad de dinero que se ha invertido en el valor bursátil.
Valor de rescate: es el valor que se recibirá en la fecha de vencimiento.
Base: determina en que tipo de base deben ser contados los días, adoptamos 3 correspondiente a 365 días al año.
Bono 1: Con fecha 26/06/98, se compra el bono cuyo vencimiento se produce el 25/06/01. El importe invertido en la compra es de $ 555.000, recibiéndose al vencimiento del bono $644.973,85. La tasa de interés de la inversión calculada en la celda B7 alcanza a 5,50%
Celda B7=TASA.INT(B2;B3;B4;B5;B6)
Bono 2: Con fecha 7/06/95, se compra el bono cuyo vencimiento se produce el 5/06/01. El importe invertido en la compra es de $ 550.000, recibiéndose al vencimiento del bono $750.000. La tasa de interés de la inversión calculada en la celda B7 alcanza a 6,06%
Celda C7=TASA.INT(C2;C3;C4;C5;C6)
Función TASA.NOMINAL
Calcula la tasa de interés anual, si se conocen la tasa efectiva y él numero de periodos (cuotas) de interés compuesto por año
SintaxisTASA.NOMINAL(tasa_efectiva;num_per)
Tasa_efectiva: es la tasa de interés por año
Num_per: es el numero de pagos de interés por año
Una tasa de interés efectiva anual del 12,60% en una financiación de 12 cuotas anuales, equivale a una tasa nominal del 11,93%.
Igual tasa efectiva anual pero en una financiación de 4 cuotas anuales equivale a una tasa nominal del 12,04%
Celda B4=TASA.NOMINAL(B2;B3)
Celda C4=TASA.NOMINAL(C2;C3)
Función TIR
Calcula la tasa interna de retorno de una inversión, o las ganancias por reinversion representadas por los números del argumento valores.
Estos flujos de caja no tienen por que ser constantes, como es el caso de una anualidad. Pero si los flujos de caja deben ocurrir en intervalos regulares, como meses o años. La tasa interna de retorno equivale a la tasa producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en periodos regulares.
SintaxisTIR(valores;estimar)
Valores: es una matriz o referencia a celda que contengan los números para los cuales se quiere calcular la tasa interna de retorno.
El argumento valores debe contener al menos un valor positivo y uno negativo para calcular la tasa interna de retorno.
TIR interpreta el orden de los flujos de caja siguiendo el orden del argumento valores. Deben introducirse valores de los pagos e ingresos en el orden correcto.
Estimar: es un numero que se estima que se aproxima al resultado TIR.
En la mayoría de los casos no se necesita proporcionar el argumento estimar, se supone que es 0,1 (10%)
Proyecto de Inversión
Como se puede apreciar el primer proyecto de inversión es él más ventajosa, en las celdas G3, G4 y G5 se calcula la tasa interna de retorno de la inversión, equivalente a la tasa de interés producida, al quinto año. Como referencia se efectúa el mismo calculo pero al cuarto año.
Celda G3=TIR(A3:F3), Celda H3= TIR(A3:E3)
Celda G4=TIR(A4:F4), Celda H4= TIR(A4:E4)
Celda G5=TIR(A5:F5), Celda H5= TIR(A5:E5)
Función TIR.NO.PER
Calcula la tasa interna de retorno para un flujo de caja no necesariamente periódico.
Sintaxis TIR.NO.PER( valor;fecha;estimar)
Valor: es una serie de flujo de caja que corresponde a un calendario de pagos determinados por el argumento fecha. El primer pago es opcional y corresponde al costo o pago que se efectúa al principio de la inversión. Todos los pagos sucesivos se descuentan basándose en un año de 365 días.
Fechas: es un calendario de fechas de pago correspondientes a los pagos del flujo de caja. La primera fecha de pago indica el principio del calendario de pagos. El resto de las fechas deben ser posteriores a esta, y pueden estar en cualquier orden.
En el primer caso se considera una inversión con un pago inicial y un ingreso de seis periodos sucesivos.La función en la celda B9, calcula la tasa interna de retorno con intervalos irregulares.
Celda B9 =TIR.NO PER(B1:B7;A1:A7)
En el segundo caso, se considera una inversión con dos pagos iniciales y un ingreso de cinco periodos sucesivos en intervalos irregulares.
Celda E9 =TIR.NO PER(E1:E7;D1:D7)
Función TIRM
Calcula la tasa interna de retorno modificada, para una serie de flujos periódicos, considerando costo de la inversión e interés al volver a invertir el efectivo.
Sintaxis TIRM(valores;tasa_financiamiento;tasa_reinversion)
Valores: son números que representan pagos, (valores negativos) e ingresos (valores positivos) que se realizan en periodos regulares.
El argumento valores debe contener por lo menos un valor positivo y otro negativo, para calcular la tasa interna modificada. De lo contrario TIM devuelve el valor de error #¡DIV/O!
Tasa_financiamiento: es la tasa de interés que se abona del dinero utilizado en el flujo de caja.
Tasa_reinversion: es la tasa de interés obtenida de los flujos de caja a medida que se reinvierten.
Mediante prestamos obtenidos a una tasa de interés anual del 11,5% se han producido las inversiones especificadas en la columna A celdas A3, A4 y A5, obteniéndose ingresos en los próximos cinco años por cada inversión según columnas B,C,D,E y F, reinvirtiendose las ganancias obteniéndose un beneficio anual del 13%.
Las celdas G3, G4 y G5, calcula el TIRM para ingresos producidos en 5 años, y la reinversion respectiva
Celda G3=TIRM(A3:F3;$B$7;$B$8)
Celda G4=TIRM(A4:F4;$B$7;$B$8)
Celda G5=TIRM(A5:F5;$B$7;$B$8)
Como ejemplo comparativo, que permite ver la evolución que se produce, se calculo el TIRM en las columnas H e I, con ingresos y reinversion hasta 3 y 4 años respectivamente.
Celda H3=TIRM(A3:E3;$B$7;$B$8)
Celda I3=TIRM(A4:D4;$B$7;$B$8)
Las formulas se deben trasladar al resto de las celdas.
Función VA
Calcula el valor actual de una inversión. El valor actual es el valor que tiene actualmente la suma de una serie de pagos que se efectúan en el futuro.
Sintaxis VA(tasa;nper;pago;vf;tipo)
Tasa: es la tasa de interés por periodo.
Nper:es él numero total de periodos en una anualidad.
Pago: es el pago que se efectúa en cada periodo y que no cambia durante la vida de la anualidad.
Vf: es el valor futuro o saldo en efectivo que se desea lograr después de efectuar él ultimo pago. Si el argumento vf se omite, se considera que el valor es cero. ( un préstamo por ejemplo)
Tipo: es él numero 0 (vencimiento de los pagos al final del periodo), o 1 (vencimiento al inicio del periodo)
Ejemplo
Se estudia la compra de una póliza de seguros que pague $ 650 al final de cada mes durante los próximos 15 años.
El costo es de $ 50.000, y el dinero pagado devenga un interés anual del 11,50%.
Para determinar si la compra de la póliza es una buena inversión, se emplea la función VA, para calcular el valor actual de la anualidad.
Celda B6= VA(B1/12;B2*12;B3;B4;B5)
El resultado en la celda B6 es negativo, ya que muestra el dinero que pagaría (flujo de caja negativo).
El valor actual de la anualidad, ($55.641,64) es mayor que lo que se pagaría ($50.000)
Función VF
Calcula el valor futuro de una inversión conformada por pagos periódicos constantes y con una tasa de interés constante.
Sintaxis VF(tasa;nper;pago;va;tipo)
Tasa:es la tasa de interés por periodo
Nper: es él numero total de pagos de una anualidad
Pago: es el pago que se efectúa cada periodo y que no puede cambiar durante la vigencia de la anualidad.
Va: es el valor actual de la cantidad total de una serie de pagos futuros. Si el argumento se omite, se considera 0 (cero)
Tipo: es el numero 0 o 1 por el cual se indica cuando vencen los pagos.
Si el argumento tipo se omite , se considera cero
Tipo Los pagos vencen
0 al final del periodo
1 al inicio del periodo
Observaciones
Si se realizan pagos mensuales de un préstamo a 3 años con interés anual del 14%, usar 14%/12 para tasa y 3*12 para nper. Si se realizan pagos anuales del préstamo usar 14% para tasa y 3 para nper.
Para los argumentos, el efectivo que se paga, por ejemplo en depósitos, están representados por números negativos, el efectivo que se recibe, están representados por números positivos.
El presente ejemplo consiste en un proyecto de ahorro, para aplicar dentro de 10 meses a partir de la fecha. Se deposita $ 1.500 en una cuenta de ahorro que devenga un interés anual del 7,50%, que se capitaliza mensualmente (interés mensual). Se planea depositar $ 130 el primer día de cada mes durante los próximos 10 meses.
El ejercicio planteado, calcula cuanto se acumula de capital en la cuenta al final de los 10 meses.
Se resolvió de las dos maneras, según los depósitos se abonen al inicio del periodo ( tipo 1), o al final del periodo (tipo 0)
Celda B6= VF((B1/12);B2;B3;B4;B5)
A efectos de poder observar como se van acumulando los depósitos mensualmente se realiza el desarrollo del ejercicio por periodos y según se plantee como tipo 1, tipo 0
La función aplicada en la celda B22 calcula el total del capital acumulado en los 10 meses
Celda B22=VF((D9/12);A19;B10;B20;B21)
En el caso de la columna C donde se calcula el acumulado mes por mes, la formula se aplica en la celda C10 y se traslada hasta la celda C19
Celda C10=VF(($D$9/12);A10;B10;$B$20;$B$21)
Ejercicio Practico
Ejercicio practico aplicando la función VF, en forma anidada en primer lugar y combinando con otras funciones de Excel, funciones de Información y Lógica.
El trabajo consiste como el ejemplo anterior en un proyecto de ahorro, para aplicar dentro de 36 mese (3 años) a partir de la fecha. Presenta las características que difieren del anterior, en que el interés devengado anual que se capitaliza mensualmente varia en mas cada año.
Ejercicio: Se deposita el primer día de cada mes $ 150, (tipo 1) durante 36 meses en una cuenta de ahorro que devenga un interés anual que se capitaliza mensualmente (interés mensual). El interés anual es variable, comprendiendo, 1°año 7,50%, 2° año 9,50%, 3° año 11,50%.
El ejercicio planteado calcula cuanto se acumula de capital en la cuenta al final del periodo de ahorro, teniendo en cuenta la variación de interés anual.
La función introducida en la celda B6, calcula el capital acumulado al final de periodo de ahorro
Celda B6=VF(D1/12;B2/3;B3;-(VF(C1/12;B2/3;B3-(VF(B1/12;B2/3;B3;B4;B5));B5));B5)
Como se puede observar se produce un anidamiento de la función VF. El mismo consiste en realizar él calculo de VF en el primer año, e incorporarlo como Va, en el segundo año, Calculado el VF de segundo año, incorporarlo coma Va en él calculo de VF del tercer año
Ejercicio de desarrollo anual
El siguiente ejercicio consiste en desarrollar la acumulación de capital por cada periodo (mes a mes), por cada año de ahorro teniendo en cuenta la variación de la tasa de interés.
Por otro lado aprovechando todas las posibilidades que ofrece Excel a través de sus funciones con aplicación directa o combinada de las mismas, el desarrollo se ejecutara en forma automática a través de una planilla de carga, que permite tener la opción de realizar él calculo en forma individual por año y por mes hasta el tercer año, o simplemente calcular el capital ahorrado en un año, o dos solamente, con la aplicación de los intereses, sin necesidad de tener que hacer el calculo para 3 años sin opción.
Entrada de datos
Se prepara una serie de celdas de carga y resultado que abarcan el rango A8:H11. En la celda B9, se introduce el monto de la inversión que como deposito se resuelve ingresar mensualmente. En las celdas E3, E4 y E5, se colocan los intereses que devengan anualmente la inversión.
Las celdas G3, G4 y G5, muestran el monto acumulado anual de la inversión realizada con los intereses devengados. Los montos los extrae de la planilla desarrollada, debiéndose colocar en las celdas : Celda G3=B29, Celda G4=E29, Celda G5=H29.
El calculo se puede hacer para los tres años, dos o uno.
Para realizar la operación se debe colocar en todos los casos el monto mensual a depositar en forma mensual, y el paso siguiente colocar solamente el interés devengado en los años que se quiere obtener el resultado ( a 3 años, 3 interés, 2 años, 2 intereses etc.)
Planilla de desarrollo
La planilla se desarrolla dentro del rango A13:J29.
En la columna A, periodo se incorpora los doce meses de un año, en celda A15=1 hasta celda A26=12.
En la celda A27 se coloca Va, celda A28, Tipo y en la celda A29 ,VF
La incorporación en la columna A son datos fijos, en el resto de las columnas se irán incorporando las formulas que permitan realizar en forma automática los cálculos respectivos.
Desarrollo
Celda D14= E9
Celda G14= E10
Celda J14= E11
Celda B15= SI(D14>0;-B9;" ")
En la formula se establece que si la celda D14 donde se traslada el interés devengado, es mayor que cero (>0), es decir se aplica interés, a la celda B15 se traslada la inversión mensual con signo negativo, en caso contrario, si no se fija interés se anula la operación.
Celda B16= B15 trasladando hasta la celda B26
Celda B28= 1 ( indica que la inversión se realiza el primer día del mes que corresponde.
CeldaB29=SI(ESERROR(VF(D14/12;A26;B15;B27;B28));" ";
VF(D14/12;A26;B15;B27;B28))
En este caso se aplica la combinación de 3 funciones, lógica SI, de información ESERROR y financiera VF
Cómo la planilla calcula en forma automática tomando los datos de la planilla de entrada de datos, en caso de no incorporarse interés a devengar, la columna B de importes queda en blanco, produciéndose en la celda B29 el valor de error tipo #¡VALOR!
Empleándose para solucionar el problema la función ESERROR, que considera Verdadero este tipo de error.
La función SI, se aplica, teniendo en cuenta que si se produce el error, coloque celda vacía (""), caso contrario, se aplique la función VF.
Surgiendo por lo tanto una combinación de tres funciones.
Con referencia a la columna C, que desde la celda C15 hasta la celda C26, calcula el acumulado mensual, de no encontrare valores en la columna B, también produce el valor de error #¡VALOR!. En este caso se emplea la misma combinación de funciones pero por periodos.
Celda C15= SI(ESERROR(VF($D$14/12;A15;B15;$B$27;$B$28));" ";
VF($D$14/12;A15;B15;$B$27;$B$28))
Esta formula debe trasladarse hasta la celda C26, juntamente en esa celda el acumulado coincide con la celda B29
Idéntico criterio se produce con el resto de las columnas que integran el 2° y 3° año.
2° Año
Celda E15= SI(G14>0;B15;" ")
Celda E16= E15 trasladar hasta la celda E26
Celda E27= SI(G14>0;-B29;" ")
En este caso también se aplico la función SI, donde establece que si existe interés a devengar, traslade a esa celda el valor de la celda B29, (calculo de VF del 1° año) para ser incorporado como Va en el 2° año.
Celda E28= B28 En este caso se traslada el criterio adoptado de inversión a partir del primer día del mes correspondiente.
CeldaE29=SI(ESERROR(VF(G14/12;A26;E15;E27;E28));" ";
VF(G14/12;A26;E15;E27;E28))
Celda F15= SI(ESERROR(VF($G$14/12;A15;E15;$E$27;$E$28));" ";
VF($G$14/12;A15;E15;$E$27;$E$28))
Esta formula debe trasladarse hasta la celda F26, juntamente en esa celda el acumulado coincide con la celda E29
3° Año
Celda E15= SI(J14>0;E15;" ")
Celda H16=H15 trasladar hasta la celda H26
Celda H27= SI(J14>0;-E29;" ")
Celda H28= E28
CeldaH29=SI(ESERROR(VF(J14/12;A26;H15;H27;H28));" ";
VF(J14/12;A26;H15;H27;H28))
Celda I 15=SI(ESERROR(VF(J14/12;A15;H15;H27;H28));" ";
VF(J14/12;A15;H15;H27;H28))
Esta formula debe trasladarse hasta la celda I 26, juntamente en esa celda el acumulado coincide con la celda H29
Función VF.PLAN
Calcula el valor futuro de un capital inicial de aplicar una serie de tasas de interes compuesto.
Se emplea VF.PLAN para calcular el valor futuro de una inversion con tasa variable o ajustable.
Sintaxis VF.PLAN(capital;plan_serie_de_tasas)
Capital: es el valor presente
Plan_serie_de_tasas : es una matriz con las tasas de interes que se aplican
Ejemplo
Se realiza una inversion de $ 100.000. Durante el 1° año, la tasa sera de 7,50%, durante el 2° de 9,50%, durante el 3° de 10,50% y durante el 4° de 8,50%. La funcion en la celda B7 calcula el capital acumulado al cabo de los cuatro años.
Celda B7=VF.PLAN(B1;B3:B6)
Función VNA
Calcula el valor neto presente de una inversión a partir de una tasa de descuento y una serie de pagos futuros (valor negativo) e ingresos (valores positivos)
Sintaxis VNA(tasa;valor 1; valor 2;…….)
Tasa: es la tasa de descuento durante un periodo
Valor 1; valor 2….. son de 1 a 29 argumentos que representan los pagos e ingresos.
Valor 1; valor 2.. deben tener la misma duración y ocurrir al final de cada periodo.
VNA usa el valor 1; valor 2; …. para interpretar el orden de los flujos de caja. Deberá introducirse los valores de pagos y de los ingresos en el orden adecuado.
Los argumentos que consisten en números, celdas vacías, valores lógicos, se cuentan, los argumentos que consisten en valores de error o texto que no se pueden traducir a números se pasan por alto.
Observaciones
La inversión VNA comienza un periodo antes de la fecha del flujo de caja de valor 1 y termina con él ultimo flujo de caja de la lista. Él calculo VNA se basa en flujos de caja futuros. Si el primer flujo de caja ocurre al inicio del primer periodo, el primer valor se deberá agregar al resultado VNA, que no se incluye en los argumentos valores.
Ejemplo
En el primer caso, (columna B) se considera una inversión que comienza al principio del periodo.
La inversión se considera de $ 35.000 y se espera recibir ingresos durante los seis primeros años. La tasa de descuento anual es de 7,50%
En la celda B9 se obtiene el valor neto actual de la inversión.
Celda B9=VNA(B1;B3:B8)+B2
No se incluye el costo inicial de $ 35.000 como uno de los valores porque el pago ocurre al principio del primer periodo.
Segundo caso (columna D)
Se considera una inversión de $ 20.000 a pagar al final del primer periodo y se recibirá ingresos anuales durante los próximos cinco años
Suponiendo una tasa de descuento anual del 8,50%, en la celda D9 se calcula el valor actual de la inversión.
Celda D9= VNA(D1;D2:D7)
Función VNA.NO.PER
Calcula el valor neto actual para un flujo de caja que no es necesariamente periódico.
Sintaxis VNA.NO PER(tasa;valores;fechas)
Tasa: es la tasa de descuento que aplica a los flujos de caja.
Valores: es una serie de flujos de caja que corresponde a un calendario de pagos determinado por el argumento fechas.
El primer pago es opcional y corresponde al costo o pago en que se incurre al principio de la inversión. Todos los pagos sucesivos se descuentan basándose en un año calendario de 365 días.
Fechas: es un calendario de fechas de pago que corresponde a los pagos del flujo de caja. La primera fecha de pago indica el principio del calendario de pagos. El resto de las fechas deben ser posteriores a esta, pero pueden ocurrir en cualquier orden.
Ejemplo
1° caso rango A1:B10
Se considera una inversión que requiere 2 pagos en efectivo el 5/08/00 y el 5/09/00, y un retiro en 6 pagos con fechas irregulares.Suponiendo que los flujos de caja se descuentan al 6,5%, el valor actual se calcula en la celda B10
Celda B10=VNA.NO.PER(B1;B2:B9;A2:A9)
2° caso rango D1:E10
Se plantea una inversión que requiere un pago en efectivo el 1/01/00 y un retiro en cinco pagos con fechas irregulares.
Suponiendo que los flujos de caja se descuentan al 5,30% el valor neto actual se calcula en la celda E10
Celda E10=VNA.NO.PER(E1;E2:E7;D2:D7)
En este caso el resultado del valor actual neto es negativo. Teniendo en cuenta que el valor negativo representa dinero que se deposita y valor positivo dinero que se retira, un valor actual neto negativo, como en este caso, indica que la inversión no es conveniente a la tasa dada
EJERCICIO PRACTICO
FONDO DE INVERSION
Ejemplo practico de fondo de inversión, donde el ingreso y egreso de montos se produce en forma mensual.
El presente trabajo permite ingresar y extraer montos, desarrollando su comportamiento a través de una planilla con un máximo de 20 años.Se fijo 20 años, simplemente por problemas de extensión, pudiendo en caso de ampliación trasladar las formulas respectivas. En la planilla se aplico una combinación de funciones que permite en caso de optar el desarrollo de la misma a menos años, no produzca errores por celdas vacías.
El ejercicio en cuestión consiste en realizar un aporte de inversión en forma mensual de $ 60 a una tasa anual del 11,50%, durante 15 años y producir un retiro mensual del monto acumulado, durante 20 años a igual tasa de rendimiento.
Considero el ejercicio en su fas practica, interesante por cuanto permite desarrollar mediante una combinación de funciones, al incorporar las mismas, la planilla en blanco sin carga e ir testando en la misma dentro de los limites impuestos.
Desarrollo
Planilla de ingreso de datos y cálculos totales
Se desarrollo dentro del Rango A1:B7
Celda B1 se fija el aporte mensual en este caso $ 60,00
Celda B2 , tasa anual en este caso 11,50%
Celda B3 se fija la cantidad de años de aporte de capital en este caso 15 años
Celda B4= VF(B2/12;B3*12;B1;0)
Como se puede observar se aplico la función que calcula el valor futuro de una inversión. Se opto por efectuar los aportes al final del periodo es decir Tipo 0
Celda B5 se fija el periodo de retiro de fondos, en este caso 20 años
Celda B6 contiene el rendimiento del monto acumulado, se adopto el mismo que el de aporte es decir 11,50%
Celda B6= SI(ESERROR(PAGO(B6/12;B5*12;B4;0));"";PAGO(B6/12;B5*12;B4;0))
En este caso se aplico para calcular la cuota de retiro mensual la función PAGO, pero se la combino con las funciones lógica SI y de información ESERROR a efectos de no producirse el error tipo #¡DIV/0!, en caso de no completarse el dato correspondiente a la celda B5, cantidad de años de retiro. La función ESERROR reconoce el error de ese tipo como verdadero y en ese caso coloca celda vacía.
Cabe aclarar que la cuota calculada corresponde al retiro a fin de cada periodo es decir tipo 0
Planilla de desarrollo del monto acumulado por periodo
El desarrollo se efectúo para un periodo de 20 años y se encuentra en el rango A9:B249
En la columna A se incorpora en forma automática cada periodo y en la columna B el monto que se acumula en el periodo. Se mantuvo la totalidad de las filas sin ocultar ninguna, a efectos de que el interesado pueda observar la variación por periodos, como también verificar otros datos que se extraerán de la misma, que se verán en próximos pasos.
Celda A10= SI(Y($B$1>0;($B$3*12)>0);1;"")
Se a introducido dentro de la formula las funciones lógicas SI e Y. La función Y anidada dentro de la SI establece como condición que si las celdas B1 y B3 de la planilla de ingreso de datos no fueron cumplimentadas, coloque celda en blanco, caso contrario él numero 1 inicio del periodo.
Celda B10=SI(A10="";"";VF($B$2/12;A10;$B$1;0))
En este caso se introduce la función lógica SI para establecer que en el caso que la celda A10 este vacía, coloque la celda B10 en blanco, caso contrario, se aplica la función VF para el primer periodo solamente, que como se estableció al principio, corresponde al fin del periodo Tipo 0, por lo tanto no acumula interés.
Celda A11=SI(ESERROR(SI(Y($B$1>0;(A10+1)<=$B$3*12);(A10+1);""));"";
SI(Y($B$1>0;(A10+1)<=$B$3*12);(A10+1);""))
En este caso se combinan 3 funciones lógicas SI e Y y de Información ESERROR
Las dos funciones lógicas establecen entre si que en caso de no estar con datos, las celdas de carga de datos, celda B1, aporte mensual y cantidad de años de aporte celda B3, coloque celda vacía, pero con esto no queda completa la celda, por cuanto Excel en caso de faltar el dato de la celda B1, aporte mensual devuelve error tipo #¡VALOR!, por lo tanto, se introduce la función ESERROR que reconoce este tipo de error como verdadero, y en ese caso si Excel devuelve celda vacía. De no resolverlo de esta manera, este tipo de error afectaría también a la celda B11.
Celda B11. Se traslada la formula de celda B10, desde celda B11 a celda B249
Celda A12 Se traslada la formula de celda A11, desde celda A12 a celda A249
Planilla de desarrollo de disminución por retiros periódicos del monto acumulado
Se incluyeron también dos columnas F y G, donde se desarrollan los porcentajes de incremento y disminución de montos por periodo de aporte y retiro
La planilla en su totalidad se desarrollo en el Rango D9:G249
Se empleo el mismo criterio para resolver toda la planilla que en el caso anterior, evitando que la misma devuelva algún tipo de error, y permitir trabajar con la misma desde cero es decir vacía.
Celda D10=SI(Y($B$1>0;($B$5*12)>0);1;0)
Celda E10=SI(ESERROR((-VF($B$2/12;$B$3*12;$B$1;0)*(1+($B$6/12)))-($B$7*D10));"";
(-VF($B$2/12;$B$3*12;$B$1;0)*(1+($B$6/12)))-($B$7*D10))
Se introdujo la función VF con numero negativo para obtener montos de disminución por periodo positivo.
Celda F 10= SI( ESERROR(100%*(-B10-B1)/$B$1);""; 100%*(-B10-B1)/$B$1)
Celda G 10= SI(ESERROR(SI(E10="";"";((-$B$4-E10)/$B$4)));"";
SI(E10="";"";((-$B$4-E10)/$B$4)))
Celda D11= SI(Y($B$1>0;(D10+1)<=$B$5);(D10+1);"")
Celda E11= SI(D11="";"";SI(ESERROR(E10*(1+($B$6/12))-$B$7);"";
E10*(1+($B$6/12))-$B$7))
Esta formula se traslada hasta la celda E249
Celda F11= SI(ESERROR(SI(A11="";"";B11/(-$B$1*A11)-1));"";
SI(A11="";"";B11/(-$B$1*A11)-1))
Esta formula se traslada hasta la celda F 249
Celda G11= SI(ESERROR(SI(E11="";"";((-$B$4-E11)/$B$4)));"";
SI(E11="";"";((-$B$4-E11)/$B$4)))
Esta formula se traslada hasta la celda G 249
Planilla de análisis de un periodo determinado
La función de la presente planilla permite en primer lugar mostrar el monto acumulado, junto con el porcentaje correspondiente para un periodo que se establece en la celda E4
En segundo lugar, mediante una selección del periodo que se realiza en celda E7, establecer el saldo acumulado y el porcentaje de disminución.
Por otro lado existe una combinación con la planilla de ingreso de datos en lo que respecta a cantidad de años de aportes fijados y también en años, el periodo de retiro de fondos.
Estos datos en la planilla de análisis, son mostrados en periodos correspondientes a cantidad de meses.
Con estos totales de periodos se puede adoptar el análisis de uno determinado.
Desarrollo
Se efectuó dentro del rango D2:G7
Celda E2=SI(ESNOD(BUSCARV(E4;A10:B249;2;FALSO));"";
BUSCARV(E4;A10:B249;2;FALSO))
En la celda E2 se trata de establecer el monto acumulado del capital depositado, al periodo estipulado en la celda E4
En primer lugar se emplea la función de búsqueda y referencia BUSCARV, es decir la función busca el periodo indicado en la celda E4, en la planilla de desarrollo del monto acumulado por periodo que se ubica en el rango A10:B249.
En segundo lugar en caso de estar la planilla vacía o estimar un periodo superior al total adoptado, la función devolverá el valor de error #N/A (valor no disponible), por lo tanto se resolvió el problema anidando la función BUSCARV en la función de información ESNOD, que reconoce dicho error como verdadero, y en ese caso deja la celda en blanco.
Celda G2=SI(ESNOD(BUSCARV(E4;A10:F249;6;FALSO));"";
BUSCARV(E4;A10:F249;6;FALSO));"";
El mismo criterio se adopto que el empleado en la celda E2
Celda E4=B3*12
Multiplica los años por doce meses.
Celda E4 Se establece el periodo que se desea analizar.
El mismo criterio se adopta para analizar un periodo en el saldo y porcentaje de disminución del capital.
Celda E5=SI(ESNOD(BUSCARV(E7;D10:E249;2;FALSO));"";
BUSCARV(E7;D10:E249;2;FALSO))
Celda G5=SI(ESNOD(BUSCARV(E7;D10:G249;4;FALSO));"";
BUSCARV(E7;D10:G249;4;FALSO))
Celda E6=B5*12
Multiplica los años por doce meses.
Celda E7 se establece el periodo que se desea analizar.
Conversiones
Funciones relativas a la conversion de Moneda
Conversion de un valor bursatil, expresado en forma decimal o fraccionaria.
Funciones
Moneda
Moneda.Dec
Moneda.Frac
Función MONEDA
Convierte un numero en texto usando un formato de moneda, con el num_decimales redondeado a la posicion decimal especificada.
Sintaxis MONEDA(numero;num_de_decimales)
Numero: es un numero de dígitos a la derecha del separador decimal. Si num_de_decimales es negativo, el argumento numero se redondea hacia la izquierda del separador decimal. Si se omite el argumento num_de_decimales, su valor predeterminado es 2
Observaciones
La principal diferencia entre dar formato a una celda que contiene un numero con el comando Celdas de menú Formato y dar formato a un numero directamente con la función MONEDA es que MONEDA convierte el resultado en texto. De cualquier manera se pueden seguir usando en formulas porque, al calcularlos, Microsoft Excel convierte los números introducidos como valores de texto en números.
Ejemplo
En la celda A2 se aplica la función Moneda que convierte él numero que se encuentra enA1 en texto con formato de moneda elevando él numero de decimales a 2
Celda A2=MONEDA(A1;2)
En el segundo caso con el agregado decimal –2 queda sin decimales
Celda A4=MONEDA(A1;-2)
Función MONEDA.DEC
Convierte la cotizacion de un valor bursatil, expresada en forma fraccionaria, en decimal
Sintaxis MONEDA.DEC( moneda_fraccionaria;fraccion)
Moneda_fraccionaria es un numero expresado como fraccion.
Fraccion es el entero que se usa como denomindor de la fraccion, indica la cantidad de fracciones por unidad (8 para octavos, 4 para cuartos etc.)
Observaciones
Si el argumento fraccion no es un entero, se trunca.
Si el argumento fraccion es < o = cero. MONEDA.DEC devuelve el valor de error #¡NUM!
Celda B2=MONEDA.DEC(B1;8)
Celda B5=MONEDA.DEC(B4;4)
Celda B8=MONEDA.DEC(B7;2)
Función MONEDA.FRAC
Convierte la cotizacion de un valor bursatil, expresada en forma decimal, en fraccionaria
Sintaxis MONEDA.FRAC( moneda_decimal;fraccion)
Moneda_decimal es un numero decimal
Fraccion es el entero que se usa como denomindor de la fraccion, indica la cantidad de fracciones por unidad (8 para octavos, 4 para cuartos etc.)
Celda B2=MONEDA.FRAC(B1;8)
Celda B5=MONEDA.FRAC(B4;4)
Celda B8=MONEDA.FRAC(B7;2)
Letras de Tesorería
Realiza todos los cálculos relativos a inversiones de características similares a las letras de tesorería. Estas son de corto plazo (normal 12 meses), poseen tasa de descuento de la letra y cotizan en el mercado de valores.
Función LETRA.DE.TES.EQV.A.BONO
Calcula el rendimiento de un bono equivalente a una letra de tesorería.
Sintaxis LETRA.DE.TES.EQV.A.BONO(liq;vencto;descuento)
Liq es 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, cuando el comprador adquirió el valor bursátil.
Vencto es la fecha de vencimiento del valor bursátil. La fecha de vencimiento es cuando expira el valor bursátil.
Descuento es la tasa de descuento de la letra de tesorería.
Observaciones
Los argumentos Liq y vencto se truncan en enteros.
Si el argumento liq. y vencto no es una fecha valida, LETRA.DE.TES.EQV.A.BONO devuelve el valor de error #¡NUM!.
Si el argumento liq>vencto, o si la fecha de vencto es posterior en mas de un año a la liquidación, la función devuelve el valor de error #¡NUM!
Ejemplo
El 31 de marzo de 2002 se hace una compra de un bono que vence el primero de junio del mismo año.
El bono se emite a una tasa de descuento del 9,10%.
La función en la celda B5 calcula la tasa de interés equivalente a una letra de tesorería.
Celda B5=LETRA.DE,TES.EQV.A.BONO(B1;B2;B3)
Función LETRA.DE.TES.PRECIO
Calcula el precio por 100$ de valor nominal de una letra de tesoreria.
Sintaxis LETRA.DE.TES.PRECIO(liq;vencto;descuento)
Liq es 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, cuando el comprador adquirió el valor bursátil.
Vencto es la fecha de vencimiento del valor bursátil. La fecha de vencimiento es cuando expira el valor bursátil.
Descuento es la tasa de descuento de la letra de tesorería.
Ejemplo
El 31 de marzo de 2002 se hace una compra de un bono que vence el primero de junio del mismo año.
El bono se emite a una tasa de descuento del 9,10%.
La función en la celda B5 calcula el precio equivalente a una letra de tesoreria por cada $ 100 de valor nominal.
Celda B5=LETRA.DE,TES.PRECIO(B1;B2;B3)
Función LETRA.DE.TES.RENDTO
Calcula el rendimiento de una letra de tesoreria
Sintaxis LETRA.DE.TES.RENDTO(liq;vencto;precio)
Liq es 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, cuando el comprador adquirió el valor bursátil.
Vencto es la fecha de vencimiento del valor bursátil. La fecha de vencimiento es cuando expira el valor bursátil
Precio es el precio de la letra de tesoreria por cada 100$ de valor nominal.
Ejemplo
El 31 de marzo de2002 se pagan $ 98,15 por $ 100 de valor nominal de un bono que vence el primero de Junio del mismo año.
La funcion en la celda B5 calcula el rendimiento de esta operación equivalente a una letra de tesoreria.
Celda B5=LETRA.DE,TES.RENDTO(B1;B2;B3)
Autor:
Gonzalo Héctor Fernández
Pcia Bs. As Argentina
enviado por:
Luis Angel Francia Borda
| Página siguiente |