• Articles
• admin

więc chcesz uruchomić symulacje Monte Carlo w Excelu, ale twój projekt nie jest wystarczająco duży lub nie wykonujesz tego typu analizy probabilistycznej na tyle, aby uzasadnić zakup drogiego dodatku. Dobrze trafiłeś. Wbudowana funkcjonalność programu Excel pozwala na modelowanie stochastyczne, w tym uruchamianie tylu symulacji, ile będzie obsługiwać moc obliczeniowa komputera, a ten krótki post z samouczkiem wideo przeprowadzi Cię przez konfigurację i proces uruchamiania symulacji Monte Carlo w programie Excel bez żadnych niezbędnych dodatków.

Analiza probabilistyczna z perspektywy Nieruchomości

jest to blog o nieruchomościach komercyjnych, dlatego ten poradnik patrzy na modelowanie stochastyczne z perspektywy profesjonalisty w dziedzinie nieruchomości. Jednak zdecydowana większość technik pokazanych w tym poście będzie działać w różnych dyscyplinach.

zwrócę również uwagę, że kilka pojęć pokazanych tutaj zaadaptowałem z doskonałej pracy dyplomowej Keith Chin-Kee Leung na temat: Beyond DCF Analysis in Real Estate Financial Modeling: Probabilistic Evaluation of Real Estate Ventures.

Co To jest Tutorial nie jest

ten post nie jest kursem na temat analizy prawdopodobieństwa. W związku z tym zakłada się, że masz podstawową wiedzę na temat prawdopodobieństwa, statystyki, Excela i wiesz, co to jest symulacja Monte Carlo. Jeśli chcesz uzyskać odświeżenie prawdopodobieństwa lub statystyki w ogóle, polecam wzięcie udziału w kursie na ten temat. Oto darmowy MOOC (massive open online course) oferowany przez Duke:

• Wprowadzenie do prawdopodobieństwa i danych

Scenariusz – Umowa o mieszkanie

przed uruchomieniem symulacji, będziesz potrzebował scenariusza do modelowania. W tym przypadku przeprowadzimy podstawowe zdyskontowane przepływy pieniężne na hipotetycznym budynku mieszkalnym, aby ustalić, ile będziemy w stanie zapłacić za nieruchomość dzisiaj. Oto co wiemy:

1. obiekt ma 10 jednostek
2. obiekt pobiera opłatę w wysokości 1000 USD / miesiąc za każdą jednostkę; czynsze rosną o 3% w ubiegłym roku
3. jeden lokal jest wolny, a dla uproszczenia Zakładamy, że zawsze będzie jeden lokal wolny
4. wydatki wynoszą 3 000 USD miesięcznie; wydatki rosną o 2% w ubiegłym roku
5. porównywalne nieruchomości sprzedają się za 5,5% – 6,0% CAPP rate today, ale oczekuje się, że stawki cap wzrosną o około 5 punktów bazowych rocznie w nadchodzących latach (wskaźnik kapitalizacji wyjściowej między 5,75% i 6.25%)
6. planuj trzymać nieruchomość przez pięć lat
7. celuj w 8% bez zwrotu

Konfigurowanie modelu

następnie skonfigurowałem mój model Excela w przygotowaniu do uruchomienia symulacji (możesz pobrać skoroszyt programu Excel użyty w tym samouczku na końcu tego postu).

1. w kolumnach B I D spadam w moich założeniach bazowych
2. Komórka G2 i Etykieta „Wartość DCF”
3. w wierszu 14, począwszy od komórki F14 do K14, dodaję nagłówek okresu z sześcioma okresami, w tym okresem zero
4. Komórka E15 i etykieta „Rent”
5. Komórka E16 i etykieta „koszty”
6. Komórka E17 i etykieta „dochód operacyjny netto”
7. Komórka E18 i Etykieta „Wartość końcowa”
8. komórka L17 i etykieta „limit wyjścia”
9. komórka E19 i etykieta „przepływy pieniężne netto”
10. Komórka D14 i etykieta „tempo wzrostu”
11. w komórce G15 zapisuję wzór: =9*12 * $D$4 * (1+$D15)^(G14-1) co oznacza dziewięć jednostek (10 jednostek mniej jednej pustej jednostki), razy 12 miesięcy, razy $D$4 ($1000 czynsz/Jednostka/miesiąc), razy jeden plus $D15 (prawdopodobna stopa wzrostu obliczona w komórce D15), podniesiona do okresu (G15) minus jeden (odejmuję jeden, ponieważ nie chcemy, aby czynsz wzrastał w pierwszym roku). Ponieważ odpowiednie bezwzględne odwołania do komórki zostały utworzone (np. $d$4 i $D15), mogę skopiować formułę prawo do komórki K15.
12. wykonuję podobny proces dla wydatków, używając formuły: = $D$7 * 12 * (1+$D16)^(G14-1) w komórce G16, a następnie kopiuję tę formułę do komórki K16.
13. w komórkach od G17 do K17 odejmuję wydatki od czynszu (np. w G17 piszę =G15-G16), aby uzyskać dochód operacyjny netto za każdy rok.
14. w komórce K18 zapisuję wzór: =K17/L18, co oznacza podzielenie piątego roku dochodu operacyjnego netto przez prawdopodobną stopę ograniczenia wyjścia (L18).
15. w komórkach G19 do K19 sumuję przepływy pieniężne netto za każdy rok: dochód operacyjny netto w latach od pierwszego do czwartego oraz dochód operacyjny netto plus wartość rezydualna w roku piątym.
16. wreszcie, w komórce G3 obliczam wartość bieżącą strumienia przepływów pieniężnych w wierszu 19 zdyskontowanego z powrotem na poziomie 8% (docelowy niewykorzystany zwrot), stosując wzór: =NPV(D12, G19:K19).

po skonfigurowaniu DCF mogę teraz przejść do dodawania prawdopodobieństwa do moich założeń.

dodawanie prawdopodobieństwa za pomocą funkcji RANDBETWEEN ()

w naszym powyższym scenariuszu mamy kilka założeń, które są niepewne, a zatem byłyby świetnymi kandydatami do dodawania zmienności. Po pierwsze, musimy wybrać typ rozkładu dla naszego prawdopodobieństwa.

mamy wiele opcji, z których dwie najczęściej to rozkład jednorodny (stałe prawdopodobieństwo, gdzie wszystkie wyniki są jednakowo prawdopodobne) i rozkład normalny (pomyśl o prawdopodobieństwie krzywej Dzwonkowej, gdzie wartość wynikowa jest prawdopodobnie bliższa średniej). Dla uproszczenia wybierzemy jednolity rozkład.

1. w komórce D15 dodaję równomierną zmienność stopy wzrostu czynszu za pomocą wzoru: =$D$5*RANDBETWEEN(-500,2000)/1000, co oznacza, że bierzemy 3% (zeszłoroczny wzrost czynszu z komórki $D$5) i mnożę go przez losową liczbę między -0,5 a 2.0 (RANDBETWEEN (-50,200)/100) tak, że wynikająca z tego stopa wzrostu czynszu spada losowo między -1,5% a 6,0%.
2. w komórce D16 dodaję jednolitą zmienność do tempa wzrostu kosztów, używając podobnego wzoru: =$D$8*RANDBETWEEN(-500,2000)/1000, tylko w tym przypadku biorę zeszłoroczną stopę wzrostu kosztów (2% z komórki $D$8) i mnożę ją przez losową liczbę między -0,5 a 2,0 (RANDBETWEEN(-50,200)/100), aby wynikająca z tego stopa wzrostu kosztów spadła.losowo od -1,0% do 4,0%.
3. na koniec, w komórce L18, dodaję jednolitą zmienność do współczynnika kapsuły wyjściowej za pomocą wzoru: =D10*RANDBETWEEN(958,3,1041,7)/1000, co oznacza przyjęcie 6% (średnia między 5,75% i 6,25% oczekiwanego zakresu dla stóp kapitalizacji wyjściowej w piątym roku) i wielokrotność go przez losową liczbę między 0,9583 i 1,0417 (RANDBETWEEN(958,3,1041,7)/1000) tak, że wynikająca z tego stopa kapitalizacji wyjściowej spada losowo między 5,75% i 6,25%.

po naciśnięciu klawisza F9 zobaczysz, że wskaźnik wzrostu czynszu, wskaźnik wzrostu kosztów i wartości współczynnika kapitalizacji wyjściowej zmieniają się losowo, co powoduje losową zmianę przepływów pieniężnych i ogólnej zdyskontowanej wartości przepływów pieniężnych.

uruchamianie symulacji Monte Carlo korzystanie z tabel danych

po dodaniu prawdopodobieństwa do modelu możesz rozpocząć uruchamianie symulacji Monte Carlo. Proces ten polega na zbudowaniu tabeli danych, powiązanej z wartością DCF (G3), tak aby każda symulacja rejestrowała wynikową wartość DCF z tej symulacji.

oto jak uruchamiamy symulacje Monte Carlo przy użyciu funkcji tabeli danych w Excelu:

1. Komórka B27 i etykieta „Symulacja #”
2. i połączenie komórki C27 do wartości DCF (=G3)
3. i komórki liczbowe B28 do B1027 od 1 do 1000. Aby to zrobić, najpierw ustawiłem komórkę B28 na 1. Następnie wpisuję formułę =B28+1 do komórki B29. Na koniec kopiuję wzór w B29 do komórki B1027.
4. z numerami symulacji i komórką C27 połączoną z wartością DCF, wybieram komórki od B27 do C1027 i klikam funkcję „Tabela danych” (Dane>Analiza co jeśli>Tabela danych).
5. zostawiam pole „Row input cell:” puste i klikam pole „Column input cell”. Wybieram pustą komórkę w arkuszu roboczym (która komórka nie ma znaczenia, o ile jest to komórka zawsze pusta), naciśnij enter, a następnie „OK”.
6. Tabela danych zaktualizuje się o 1000 iteracji naszej symulacji i proszę, przeprowadziłeś symulację Monte Carlo w programie Excel przy użyciu tabeli danych.

wartość oczekiwana-co możesz być skłonny zapłacić

średnia (średnia) wszystkich symulacji jest twoją „wartością oczekiwaną” lub co możesz być skłonny zapłacić za nieruchomość przedmiotową, biorąc pod uwagę twoje założenia. W moim przypadku wartość oczekiwana wynosi około 1,2 miliona dolarów.

lubię również obliczać minimum, maksimum i odchylenie standardowe symulacji, aby uzyskać wyczucie zakresu wartości. Na przykład, w tym przypadku, minimum wynosi około 925 000 dolarów, a maksimum około 1,5 miliona dolarów. Oznacza to, że był przypadek, w którym musiałbym zapłacić $925,000, aby osiągnąć 8% zwrotu i był przypadek, w którym mogłem zapłacić $1.5 miliona, aby osiągnąć 8% zwrotu.

niemniej jednak, im więcej symulacji wykonasz, tym więcej wartości stworzy wzór normatywny, w którym masz 68% prawdopodobieństwo, że wartość będzie jednym odchyleniem standardowym od średniej i 95% prawdopodobieństwo, że wartość będzie dwoma odchyleniami standardowymi od średniej (zasada 68-95-99). Dlatego im mniejsze odchylenie standardowe, tym większa pewność co do wartości oczekiwanej.

podsumowując, za nasz hipotetyczny budynek mieszkalny, bylibyśmy skłonni zapłacić gdzieś pomiędzy $925,000 a $ 1.5 miliona, przy czym 1.2 miliona jest najbardziej prawdopodobną ceną zakupu.

samouczek wideo-uruchamianie symulacji Monte Carlo w Nieruchomościach

jako uzupełnienie pisemnego samouczka powyżej, nagrałem film, który przechodzi przez robienie własnych symulacji Monte Carlo dla nieruchomości w Excelu.

postępuj za pomocą pliku Excel z filmu

aby ten samouczek symulacji Monte Carlo był dostępny dla wszystkich, jest oferowany na zasadzie” Zapłać, ile możesz ” bez minimum (wprowadź $0, jeśli chcesz) lub maksimum (Twoje wsparcie pomaga utrzymać zawartość – podobne Moduły kursu nieruchomości sprzedają się za $100 – $300+). Po prostu wprowadź cenę wraz z adresem e-mail, na który chcesz wysłać link do pobrania, a następnie kliknij „Kontynuuj”. Jeśli masz jakiekolwiek pytania dotyczące naszego programu” Zapłać ile możesz „lub dlaczego oferujemy nasze modele na tej podstawie, skontaktuj się z Mike’ em lub Spencerem.