• Articles
• admin

Por lo que desea ejecutar simulaciones de Monte Carlo en Excel, pero su proyecto no es lo suficientemente grande o no realiza este tipo de análisis probabilístico lo suficiente como para justificar la compra de un complemento costoso. Bueno, has venido al lugar correcto. La funcionalidad incorporada de Excel permite el modelado estocástico, incluida la ejecución de tantas simulaciones como admita la potencia de procesamiento de su computadora, y esta breve publicación con video tutorial lo guía a través de la configuración y el proceso de ejecución de simulaciones de Monte Carlo en Excel sin necesidad de complementos.

Análisis probabilístico desde una Perspectiva de Bienes Raíces

Este es un blog de bienes raíces comerciales, y por lo tanto, este tutorial analiza el modelado estocástico desde la perspectiva de un profesional de bienes raíces. Sin embargo, la gran mayoría de las técnicas que se muestran en este post funcionarán en todas las disciplinas.

También observaré, varios de los conceptos que se muestran aquí adapté de la excelente tesis de posgrado de Keith Chin-Kee Leung sobre el tema: Más allá del Análisis DCF en el Modelado Financiero de Bienes Raíces: Evaluación Probabilística de Empresas Inmobiliarias.

Qué es Este Tutorial no es

Este post no es un curso sobre análisis de probabilidad. Como tal, asume que tiene un conocimiento básico de probabilidad, estadísticas, Excel y sabe lo que es una simulación de Monte Carlo. Si desea obtener un repaso sobre la probabilidad o las estadísticas en general, le recomiendo tomar un curso sobre el tema. Aquí hay un MOOC (curso en línea abierto masivo) gratuito ofrecido por Duke:

• Introducción a la Probabilidad y los datos

El escenario: Un acuerdo de apartamento

Antes de ejecutar sus simulaciones, necesitará un escenario para modelar. En este caso, vamos a ejecutar un flujo de efectivo básico con descuento en un edificio de apartamentos hipotético para determinar cuánto estaríamos dispuestos a pagar por la propiedad hoy. Esto es lo que sabemos:

1. La propiedad en cuestión tiene 10 unidades
2. La propiedad en cuestión cobra $1000 / mes por cada unidad; los alquileres crecen un 3% el año pasado
3. Hay una unidad vacante, y para simplificar, asumimos que siempre habrá una unidad vacante
4. Los gastos son de $3,000 por mes; los gastos crecen un 2% el año pasado
5. Las propiedades comparables se venden por una tasa de límite máximo del 5.5% al 6.0% hoy, pero se espera que las tasas de límite máximo crezcan unos 5 puntos básicos por año en los próximos años (tasa de límite de salida entre el 5.75% y 6.25%)
6. Planifique mantener la propiedad durante cinco años
7. Apunte a un rendimiento sin apalancamiento del 8%

Configuración del modelo

A continuación, configuré mi modelo de Excel en preparación para ejecutar las simulaciones (puede descargar el libro de Excel utilizado en este tutorial al final de esta publicación).

1. En la columna B y D, Introduzco mis suposiciones base
2. Celda G2 Etiqueta «Valor DCF»
3. En la fila 14, comenzando en la celda F14 hasta K14, agrego un encabezado de período con seis períodos, incluido un período cero
4. Celda E15 Etiqueta «Alquiler»
5. Celda E16 Etiqueta «Gasto»
6. Celda E17 Etiqueta «Ingresos netos de Explotación»
7. Celda E18 Etiqueta «Valor residual»
8. Celda L17 Etiqueta «Tapa de salida»
9. Celda E19 Etiqueta «Flujo de caja neto»
10. Celda D14 Etiqueta «Tasa de crecimiento»
11. En la celda G15 escribo la fórmula: =9*12*$D$4*(1+$D15)^(G14-1), lo que significa nueve unidades (10 unidades menos una vacante de la unidad), los tiempos de 12 meses, a veces $D$4 ($1000 de alquiler/unidad/mes), los tiempos de un plus de $D15 (la probable tasa de crecimiento calculado en la celda D15), elevado al período (G15) menos uno (yo restar uno, porque no queremos alquilar a crecer en el año uno). Dado que se han creado las referencias absolutas de celda adecuadas (por ejemplo, $D 4 4 y D D15), puedo copiar la fórmula directamente a la celda K15.
12. Sigo un proceso similar para los gastos, usando la fórmula: = = D 7 7 * 12 * (1+D D16)^(G14-1) en la celda G16 y luego copio esa fórmula en la celda K16.
13. En las celdas G17 a K17, resto los gastos del alquiler (por ejemplo, en G17 escribo =G15-G16) para obtener un ingreso operativo neto para cada año.
14. En la celda K18 escribo la fórmula: = K17 / L18, lo que significa dividir los ingresos netos de explotación del quinto año por la tasa de límite de salida probable (L18).
15. En las celdas G19 a K19, suma los flujos de efectivo netos para cada año: ingresos netos de explotación en los años uno a cuatro y ingresos netos de explotación más valor residual en el año cinco.
16. Finalmente, en la celda G3 calculo el valor actual de la corriente de flujo de caja en la fila 19 con descuento al 8% (el retorno no apalancado objetivo) utilizando la fórmula: =VAN(D12,G19:K19).

Con el DCF configurado, ahora puedo pasar a agregar probabilidad a mis suposiciones.

Agregar probabilidad usando la función RANDBETWEEN ()

En nuestro escenario anterior, tenemos un par de suposiciones que son inciertas, y por lo tanto serían grandes candidatos para agregar variabilidad. Primero, necesitamos elegir un tipo de distribución para nuestra probabilidad.

Tenemos una serie de opciones, las dos más comunes son la distribución uniforme (probabilidad constante donde todos los resultados son igualmente probables) y la distribución normal (piense en la probabilidad de curva de campana donde el valor resultante es probable que esté más cerca de la media). Por simplicidad, elegiremos una distribución uniforme.

1. En la celda D15, agrego variabilidad uniforme a la tasa de crecimiento del alquiler usando la fórmula: =RAND D 5 5*RANDBETWEEN(-500,2000)/1000, lo que significa tomar el 3% (el crecimiento del alquiler del año pasado de la celda D D 5 5) y multiplicarlo por un número aleatorio entre -0.5 y 2.0 (RANDBETWEEN (-50,200) / 100) para que la tasa de crecimiento de la renta resultante caiga aleatoriamente entre -1.5% y 6.0%.
2. En la celda D16, agrego variabilidad uniforme a la tasa de crecimiento de gastos usando una fórmula similar: =RAND D 8 8*RANDBETWEEN(-500,2000)/1000, solo que en este caso tomo la tasa de crecimiento de gastos del año pasado (2% de la celda D D 8 8) y la multiplico por un número aleatorio entre -0.5 y 2.0 (RANDBETWEEN(-50,200)/100) para que la tasa de crecimiento de gastos resultante caiga aleatoriamente entre -1,0% y 4,0%.
3. Finalmente, en la celda L18, agrego variabilidad uniforme a la tasa de límite de salida utilizando la fórmula: = D10 * RANDBETWEEN (958.3, 1041.7) / 1000, lo que significa tomar el 6% (el promedio entre el rango esperado de 5.75% y 6.25% para las tasas de límite de salida en el quinto año) y multiplicarlo por un número aleatorio entre 0.9583 y 1.0417(RANDBETWEEN (958.3,1041.7) / 1000) para que la tasa de límite de salida resultante caiga aleatoriamente entre 5.75% y 6.25%.

Verá ahora, cuando presione F9, que los valores de la tasa de crecimiento del alquiler, la tasa de crecimiento de los gastos y la tasa de capitalización de salida cambian aleatoriamente, lo que resulta en un cambio aleatorio en los flujos de efectivo y el valor total del flujo de efectivo descontado.

Ejecución de simulaciones de Monte Carlo mediante Tablas de datos

Con la probabilidad añadida a su modelo, puede comenzar a ejecutar sus simulaciones de Monte Carlo. Este proceso implica crear una tabla de datos, vinculada a su valor DCF (G3) para que cada simulación registre el valor DCF resultante de esa simulación.

Así es como ejecutamos las Simulaciones de Monte Carlo utilizando la función de Tabla de datos en Excel:

1. Celda B27 etiqueta «Simulación #»
2. Enlace la celda C27 al Valor DCF (=G3)
3. Número de celdas B28 a B1027 de 1 a 1000. Para hacer esto, primero establezco la celda B28 en 1. A continuación, introduzco la fórmula = B28 + 1 en la celda B29. Por último, copio la fórmula en B29 a la celda B1027.
4. Con las simulaciones numeradas y la celda C27 vinculada al valor DCF, selecciono las celdas B27 a C1027 y hago clic en la función «Tabla de datos» (Datos>Análisis hipotético>Tabla de datos).
5. Dejo el cuadro’ Celda de entrada de fila: ‘en blanco, y hago clic en el cuadro ‘Celda de entrada de columna’. Selecciono una celda vacía en la hoja de trabajo (qué celda no importa siempre que sea una celda que siempre esté en blanco), presiono enter y luego ‘OK’.
6. La tabla de datos se actualizará con 1000 iteraciones de nuestra simulación y listo, ha ejecutado una simulación de Monte Carlo en Excel utilizando la tabla de datos.

El Valor Esperado: Lo que podría estar Dispuesto a Pagar

La media (promedio) de todas las simulaciones es su «Valor Esperado» o lo que podría estar dispuesto a pagar por la propiedad del sujeto dadas sus suposiciones. En mi caso, el valor esperado es de aproximadamente 1,2 millones de dólares.

También me gusta calcular la desviación mínima, máxima y estándar de las simulaciones para tener una idea del rango de los valores. Así, por ejemplo, en este caso, el mínimo es de alrededor de $925,000 y el máximo es de alrededor de 1,5 millones de dólares. Lo que esto significa es que hubo un caso en el que tendría que pagar 9 925,000 para obtener una rentabilidad del 8% y hubo un caso en el que podría pagar $1.5 millones para obtener una rentabilidad del 8%.

No obstante, cuantas más simulaciones ejecute, más valores crearán un patrón normativo en el que tendrá una probabilidad del 68% de que el valor sea una desviación estándar de la media y una probabilidad del 95% de que el valor sea dos desviaciones estándar de la media (la regla 68-95-99). Por lo tanto, cuanto menor sea la desviación estándar, más seguro puede estar sobre su valor esperado.

En conclusión, para nuestro hipotético edificio de apartamentos, estaríamos dispuestos a pagar entre 9 925,000 y 1 1.5 millones, siendo 1 1.2 millones el precio de compra más probable.

Video Tutorial-Ejecutar Simulaciones de Monte Carlo en Bienes Raíces

Como complemento del tutorial escrito anteriormente, he grabado un video que recorre haciendo sus propias simulaciones de Monte Carlo para bienes raíces en Excel.

Siga Usando el Archivo de Excel del Video

Para hacer que este tutorial de simulación de Monte Carlo sea accesible para todos, se ofrece sobre la base de «Pagar lo que pueda» sin mínimo (ingrese $0 si lo desea) o máximo (su soporte ayuda a mantener el contenido disponible – los módulos de cursos de bienes raíces similares se venden por 1 100 – +300+). Simplemente ingrese un precio junto con una dirección de correo electrónico para enviar el enlace de descarga y, a continuación, haga clic en «Continuar». Si tiene alguna pregunta sobre nuestro programa «Pague lo que pueda» o por qué ofrecemos nuestros modelos sobre esta base, comuníquese con Mike o Spencer.