Quiero proyectar una renta peri'odiaca / Excel + rentas financieras / Capitalización
Hola! Quiero hacer una hoja que, seleccionando una prima/importe, una frecuencia y periodo me calcule un valor final acumulado. Además, que tenga en cuenta un tipo de inter'es y un increment anual en la prima. Como lo he planteado inicialmente es así,
En una pestaña tengo una base de datos con importes/primas, periodos, incrementos de prima porcentual y capital acumulado final a medida que van pasando los periodos/meses. En otra pestaña tengo una especie de formulario que en función de unos datos que introduzco, quiero obtener como resultado ese Valor final (Vf). Por ejemplo: una prima de 150 euros/mes x 12 meses y x 10 años (120 periodos) al un tipo del 2% anual y con un incremento de prima del 3% anual, tengo un Vf resultante de 22.749 euros. El Vf lo intento solucionar con el Indice + Coincidir, pero a la que le pongo más condiciones no me funciona.
En resumen,
- Tengo un importe inicial (o no) junto con unas primas periódicas, con una frecuencia a determinar en el desplegable.
- Prima mensual durante unos años a determinar (he puesto 10 por defecto), pero que dependa de lo que se ponga en esa celda.
- Que remunere el acumulado mensual a un tipo (he puesto el 2%), pero que dependa de lo que se ponga en esa celda.
- Que anualmente se pueda revalorizar la prima (he puesto un 3%), pero que dependa de lo que se ponga en esa celda. Y que esa progresión en prima también se vea reflejado en la remuneración anterior.
Podéis ayudarme en como tendría que plantearlo. Adjunto pantallas de lo que estoy intentando hacer.
Lo he intentado con funciones Vageo, etc.. Con una tabla como la que adjunto, etc.. Pero no logro encontrar una soluci'on v'alida.
Si preferís que os adjunte el archivo, sin problema.
Gracias!
1 Respuesta
·
En principio no se si hay una fórmula para una renta que crece geométricamente pero no en cada cuota sino cada 12. Pero seguramente se podrá deducir.
Mándame el libro con algún ejemplo y explicaciones.
Vamos a encontrar la fórmula.
Sea C la cuota del primer año
Sea m el número de años
Sea n el número de periodos anuales
Sea i el interés efectivo de cada periodo. Si tenemos la TAE se calcularía asi
$$\begin{align}&i = (1+TAE)^{1/n}-1\\&\\&\text{Por ejemplo con n=12 y TAE = 2%=0.02}\\&\\&i=(1.02)^{1/12}-1=1.001651581-1 =\\&\\&0.001651581\end{align}$$Sea q la razón de la progresión de revalorizaciones. Si se revaloriza el 3% anual será q=1.03
Para abreviar llamaré
T=1+TAE
por ejemplo, si TAE = 2% = 0.02
T = 1.02
El valor final al cabo de esos m años será
$$\begin{align}&V_{m·n}= C·q^0·\frac{(1+i)^n-1}{i}·T^{\,m-1}+\\&\\&C·q·\frac{(1+i)^n-1}{i}·T^{\,m-2}+\\&\\&C·q^2·\frac{(1+i)^n-1}{i}·T^{\,m-3}+\\&...\\&\\&Cq^{m-1}\frac{(1+i)^n-1}{i}·T^{0}=\\&\\&C·\frac{(1+i)^n-1}{i}·\sum_{k=0}^{m-1}q^k·T^{\,m-1-k}=\\&\\&C·\frac{(1+i)^n-1}{i}·T^{\,m-1}·\sum_{k=0}^{m-1}\left(\frac{q}{T}\right)^k=\\&\\&\text{Por la fórmula de la suma de geométricas}\\&\\&=C·\frac{(1+i)^n-1}{i}·T^{\,m-1}·\frac{\left(\frac qT\right)^m-1}{\frac{q}{T}-1}=\\&\\&=C·\frac{(1+i)^n-1}{i}·T^{\,m-1}·\frac{\frac{q^m-T^m}{T^m}}{\frac{q-T}{T}}=\\&\\&=C·\frac{(1+i)^n-1}{i}·\frac{q^m-T^m}{q-T}\\&\\&\text{Esto sirve si }q\neq T\\&\\&\text{Cuando q=T}\\&\\&V_{m·n}=C·\frac{(1+i)^n-1}{i}·T^{\,m-1}·\sum_{k=0}^{m-1}\left(1\right)^k=\\&\\&C·\frac{(1+i)^n-1}{i}·T^{\,m-1}·m\\&\\&\\&\\&\end{align}$$Y la fórmula es esta que va de la celda N8 a la N34 de la hoja capitaliza que me has mandado.
=SI(I8=K8;E8*(((1+L8)^G8-1)/L8)*(1+I8)^(H8-1)*H8 +D8*(1+K8)^H8;E8*(((1+L8)^G8-1)/L8)*((1+I8)^H8-(1+K8)^H8)/(I8-K8)+D8*(1+K8)^H8)
Saobre la capitaliza2 no me gusta, tanta busqueda debe consumir muchos recursos del ordenador.
·
Y eso es todo, ojala sea lo que querías y funcione.
¡Gracias! Muchas muchas gracias!! Me has ayudado un montón porque me había quedado bloqueado y no sabía encontrar la mejor solución! Lo único que veo es que si pongo incremento prima 0% y TAE 0% da un error, pero por lo demás esta genial!! Gracias!
Es verdad. No me fijé que el denominador tiene el interés periódico y claro está, si es cero da un error. También T^(m-1) es un denominador luego la TAE 0 daría error, pero ambas dan 0 a la vez luego la corrección es solo una.
Para corregirlo puedes poner esta fórmula.
=SI(L8=0;D8+E8*J8;SI(I8=K8;E8*(((1+L8)^G8-1)/L8)*(1+I8)^(H8-1)*H8 +D8*(1+K8)^H8;E8*(((1+L8)^G8-1)/L8)*((1+I8)^H8-(1+K8)^H8)/(I8-K8)+D8*(1+K8)^H8))
