• Articles
• admin

então você deseja executar simulações de Monte Carlo no Excel, mas seu projeto não é grande o suficiente ou você não faz esse tipo de análise probabilística o suficiente para garantir a compra de um suplemento caro. Bem, você veio ao lugar certo. A funcionalidade integrada do Excel permite a modelagem estocástica, incluindo a execução de tantas simulações quanto o poder de processamento do seu computador suportará, e este breve post com tutorial em vídeo orienta você na configuração e no processo de execução de simulações de Monte Carlo no Excel sem nenhum suplemento necessário.

análise probabilística da perspectiva Imobiliária

este é um blog imobiliário comercial e, portanto, este tutorial analisa a modelagem estocástica da perspectiva de um profissional imobiliário. No entanto, a grande maioria das técnicas mostradas neste post funcionará em todas as disciplinas.

notarei também, vários dos conceitos aqui apresentados adaptei da tese de Pós-Graduação excelente de Keith Chin-Kee Leung sobre o tema: Além da análise DCF em modelagem financeira imobiliária: avaliação probabilística de Empreendimentos Imobiliários.

o que este é Tutorial não é

este post não é um curso sobre análise de probabilidade. Como tal, assume que você tem uma compreensão básica de probabilidade, estatística, Excel e sabe o que é uma simulação de Monte Carlo. Se você gostaria de obter uma atualização sobre probabilidade ou Estatística em geral, recomendo fazer um curso sobre o assunto. Aqui está um MOOC gratuito (massive open online course) oferecido pela Duke:

• Introdução à probabilidade e aos dados

o cenário – um Acordo de apartamento

Antes de executar suas simulações, você precisará de um cenário para modelar. Neste caso, vamos executar um fluxo de caixa com desconto básico em um prédio de apartamentos hipotético para determinar quanto estaríamos dispostos a pagar pela propriedade hoje. Aqui está o que sabemos:

1. a propriedade assunto tem 10 unidades
2. a propriedade assunto cobra $1000 / mês para cada unidade; aluga crescer 3% no ano passado
3. Existe uma unidade vago, e, por simplicidade, assumimos sempre haverá uma unidade vago
4. Despesas são de us $3.000 por mês; despesas crescer 2% no ano passado
5. propriedades Comparáveis vender para 5,5% – 6.0% taxa cap de hoje, mas taxas de capitalização devem crescer cerca de 5 pontos base por ano nos próximos anos (sair cap rate entre 5.75% e 6.25%)
6. Plano para manter o imóvel por cinco anos
7. Alvo em 8% sem alavancagem do retorno

configurar o Modelo de

em seguida, configurar um modelo em Excel, em preparação para a execução de simulações (você pode baixar a pasta de trabalho do Excel usada neste tutorial no final do post).

1. Na coluna B e D, de eu cair na minha pressupostos base
2. Célula G2 eu etiqueta “DCF Valor”
3. Na linha 14, começando na célula F14, através de K14, Eu add um período de cabeçalho com seis períodos, incluindo um período de zero
4. Célula E15 eu rótulo de “Alugar”
5. Célula E16 eu rótulo de “Despesa”
6. Célula E17 eu rótulo de “receita Operacional Líquida”
7. Célula E18 eu rótulo de “Valor Residual”
8. Célula L17 eu rótulo de “Sair ” Cap”
9. Célula E19 eu a etiqueta de “Fluxo de Caixa Líquido”
10. Célula D14 eu rótulo de “Taxa de Crescimento”
11. Na Célula G15 eu escrever a fórmula: =9*12*$D$4*(1+$D15)^(G14-1) o que significa nove unidades (10 unidades a menos de um vago unidade), vezes 12 meses, tempos $D$4 (r$1000 alugar/unidade/mês), tempos mais $D15 (a provável taxa de crescimento calculada na célula D15), elevado para o período (G15) menos um (eu subtrair um, porque não queremos alugar para crescer em um ano). Como as referências absolutas de células adequadas foram criadas (por exemplo, $D$4 e $D15), posso copiar a fórmula diretamente para a célula K15.
12. eu sigo um processo semelhante para despesas, usando a fórmula: = $D $ 7 * 12 * (1+$D16)^(G14-1) na célula G16 e, em seguida, copiando essa fórmula para a célula K16.
13. nas células G17 a K17, subtraio as despesas do aluguel (por exemplo, no G17 escrevo = G15-G16) para chegar a uma receita operacional líquida para cada ano.
14. na célula K18, escrevo a fórmula: = K17 / L18, o que significa dividir o lucro operacional líquido do ano cinco pela provável taxa de limite de saída (L18).
15. nas células G19 a K19, somo os fluxos de caixa líquidos para cada ano: lucro operacional líquido nos anos um a quatro e lucro operacional líquido mais valor residual no ano cinco.
16. finalmente, na célula G3, calculo o valor presente do fluxo de caixa na linha 19 descontado de volta em 8% (o retorno sem destino) usando a fórmula: = NPV (D12,G19:K19).

com o DCF configurado, agora posso passar a adicionar probabilidade às minhas suposições.

adicionando probabilidade usando a função RANDBETWEEN ()

em nosso cenário acima, temos algumas suposições que são incertas e, portanto, seriam grandes candidatos para adicionar variabilidade. Primeiro, precisamos escolher um tipo de distribuição para nossa probabilidade.

temos várias opções, sendo as duas mais comuns a distribuição uniforme (probabilidade constante em que todos os resultados são igualmente prováveis) e a distribuição normal (pense na probabilidade da curva de bell onde o valor resultante provavelmente estará mais próximo da média). Por uma questão de simplicidade, escolheremos uma distribuição uniforme.

1. na célula D15, adiciono variabilidade uniforme à taxa de crescimento do aluguel usando a fórmula: =$D$5*RANDBETWEEN(-500.2000)/1000, o que significa tomar 3% (crescimento do aluguel do ano passado da célula $D$5) e múltiplo por um número aleatório entre -0,5 e 2.0 (RANDBETWEEN (-50.200)/100) para que a taxa de crescimento do aluguel resultante caia aleatoriamente entre -1,5% e 6,0%.
2. Na célula D16, eu adicionar uniforme variabilidade para a despesa taxa de crescimento usando uma fórmula semelhante: =$D$8*RANDBETWEEN(-500,2000)/1000, só que neste caso eu tire do ano passado despesa taxa de crescimento (2% de célula $D$8) e multiplicá-lo por um número aleatório entre -0.5 e 2.0 (RANDBETWEEN(-50,200)/100), de modo que a resultante de despesa taxa de crescimento cai aleatoriamente entre -1.0% e 4.0%.
3. finalmente, na célula L18, adiciono variabilidade uniforme à taxa de limite de saída usando a fórmula: =D10*RANDBETWEEN(958.3,1041.7)/1000, o que significa tomar 6% (a média entre a 5.75% e de 6,25%, intervalo previsto para sair taxas de capitalização no ano, cinco) e multiplique por um número aleatório entre 0.9583 e 1.0417 (RANDBETWEEN(958.3,1041.7)/1000), de modo que a resultante sair cap rate cai aleatoriamente entre 5.75% e de 6,25%.

você verá agora quando pressionar F9, que a taxa de crescimento do aluguel, a taxa de crescimento das despesas e os valores da taxa de limite de saída mudam aleatoriamente, resultando em uma mudança Aleatória nos fluxos de caixa e no valor geral do fluxo de caixa com desconto.

executando simulações de Monte Carlo usando tabelas de dados

com probabilidade adicionada ao seu modelo, você pode começar a executar suas simulações de Monte Carlo. Esse processo envolve a construção de uma tabela de dados, vinculada ao seu valor DCF (G3) para que cada simulação registre o valor DCF resultante dessa simulação.

aqui está como executamos as simulações de Monte Carlo usando o recurso de tabela de dados no Excel:

1. célula B27 eu rotulo “simulação #”
2. eu vinculo a célula C27 ao valor DCF (=G3)
3. eu numero as células B28 a B1027 de 1 a 1000. Para fazer isso, primeiro defini a célula B28 como 1. Em seguida, insiro a fórmula =B28+1 na célula B29. Por fim, copio a fórmula em B29 até a célula B1027.
4. com as simulações numeradas e a célula C27 ligada ao valor DCF, seleciono as células B27 a C1027 e clico no recurso ‘tabela de Dados’ (Dados>What-If-Analysis>tabela de dados).
5. deixo a caixa’ célula de entrada de linha: ‘em branco e clico na caixa’ célula de entrada de coluna’. Eu seleciono uma célula vazia na planilha (qual célula não importa, desde que seja uma célula que esteja sempre em branco), pressione enter e, em seguida, ‘OK’.
6. a tabela de dados será atualizada com 1.000 iterações de nossa simulação e voila você executou uma simulação de Monte Carlo no Excel usando a tabela de dados.

O Valor Esperado – o Que Você Pode estar Disposto a Pagar

médio (média) de todas as simulações é o seu “Valor Esperado” ou o que você pode estar disposto a pagar para a propriedade subject dada seus pressupostos. No meu caso, o valor esperado é de cerca de US $1,2 milhão.

eu também gosto de calcular o mínimo, máximo e desvio padrão das simulações para ter uma ideia do intervalo dos valores. Assim, por exemplo, neste caso, o mínimo é de cerca de US $925.000 e o máximo é de cerca de US $1,5 milhão. O que isso significa é que havia uma instância em que eu precisaria pagar US $925.000 para atingir um retorno de 8% e havia uma instância em que eu poderia pagar US $1,5 milhão para atingir um retorno de 8%.

no Entanto, o mais simulações que você executar, mais os valores de criar um padrão normativo, onde você tem 68% de probabilidade de que o valor será um desvio-padrão da média e uma probabilidade de 95% de que o valor será de dois desvios-padrão da média (a regra 68-95-99). Portanto, quanto menor o desvio padrão, mais certo você pode ser sobre o seu valor esperado.Portanto, em conclusão, para o nosso hipotético prédio de apartamentos, estaríamos dispostos a pagar algo entre US $ 925.000 e US $1,5 milhão, com US $1,2 milhão sendo o preço de compra mais provável.

Tutorial em vídeo-executando simulações de Monte Carlo em imóveis

como complemento ao tutorial escrito acima, gravei um vídeo que percorre fazendo suas próprias simulações de Monte Carlo para imóveis no Excel.

siga usando o arquivo Excel do vídeo

para tornar este tutorial de simulação de Monte Carlo Acessível a todos, ele é oferecido em uma base “pague o que puder” sem mínimo (insira $0, se desejar) ou máximo (seu suporte ajuda a manter o conteúdo chegando – módulos de cursos imobiliários semelhantes são vendidos por $100 – $300+). Basta inserir um preço junto com um endereço de E-mail para enviar o link de download e clicar em ‘Continuar’. Se você tiver alguma dúvida sobre o nosso Programa “pague o que puder” ou por que oferecemos nossos modelos com base nisso, entre em contato com Mike ou Spencer.