Creación de una referencia circular

Lo más habitual, es que el mensaje que nos devuelve Excel de referencia circular sea un error involuntario, un fallo a la hora de introducir las referencias o de marcar las celdas. Pero otras veces puede que sea un problema buscado. Hoy veremos una de esas situaciones.

Supongamos que queremos comprar un objeto y no disponemos de dinero en este momento para ello. Nos disponemos a pedir un préstamo para solventar esta situación de escasez de tesorería, pero no tenemos dinero ni para el principa , ni para los intereses. Si el importe del prestamos solo cubre el precio del objeto, no tendremos dinero para pagar los intereses, pero si pido dinero extra para los intereses, tendré que pagar interés sobre esos intereses, así que pido algo más para pagar los intereses delos intereses delos intereses,….. ¡Una referencia circular!

Para verlo vamos a trabajar con un sencillo y pequeño modelo, introducimos la cantidad de dinero que necesitamos para la compra  en la celda B4, B5 los intereses anuales que pagaremos, supongamos un 6% anual del importe total solicitado al banco y B7, introducimos la suma del precio y de los intereses, que será la cantidad solicitada.

Para mayor claridad, en la columna C que figura las fórmulas introducidas en la columna B.

Y ZAS! Cuando trato de validar la fórmula de la suma en B5, Excel me dice que no se puede calcular la fórmula porque hay una referencia circular:

Se trata de una garantía normal porque lo más a menudo referencias circulares son totalmente involuntarios, son los errores de diseño de del modelo.

Sin embargo en nuestro caso no es un error, queremos calcular realmente el resultado de la convergencia. Hay muchos casos de estos: calcular el precio de venta de un bien, incluyendo la comisión del vendedor, impuestos, etc.. 

Para validar las referencias circulares de cálculo, es necesario ir al comando Archivo, y en Opciones  de Excel, ficha "Fórmulas" y marcar la casilla "Habilitar cálculo iterativo" como lo hemos hecho a continuación.

Ahora, en la celda B6 aparece el resultado final, la cantidad que deberíamos solicitar al banco para que nos llegue para pagar la compra y los intereses, 106,38 eur.

Así de fácil. También podríamos haber dividido el importe de la compra por uno menos el tipo de interés y obtener el importe final, no es más que la solución al límite que sustenta este problema. Nos debería de dar el mismo importe. Lo incluyo en la celda B8, y en la celda B9 comparo si son iguales B8=B6, y me da FALSO, es decir, que el resultado es distinto, aunque no parezca pues lo he redondeado a 2 decimales, pero internamente trabaja con todos los decimales y hay pequeñas diferencias, y cada vez que pulsamos la tecla F9, la de recalcular, cambia. ¿Qué está pasando?. Es un problema del número de iteraciones, el modelo convergerá incrementando el número de iteraciones, o pulsando varias veces F9.

Puedes descargar el modelo que hemos visto, modificarlo y enredar en él, en wannannotes y colaborar con el blog, pulsando aquí.

En los siguientes post  veremos cómo solucionar este desfase y para qué sirven los dos ajustes que tiene esta ficha: Número máximo iteraciones y la desviación máxima. Inspirado por Ms. Thiriez. 
Hasta el próximo post.