Savingforanapartment

From Simulace.info
Jump to: navigation, search

Introduction

Problem definition

Jon has finally graduated to be an engineer and has found his first job. As he is living with his parents and doesn’t own his apartment, he made the decision to start saving so he can buy an apartment in the next 15 years. He already has 100.000 USD that he has saved so far just sitting in his bank account, so he will use that as an initial investment, and after that he will invest a fixed amount of 10.000 USD at the beginning of every year. He now must make a very important decision. Where should he invest his money? After doing some research, he focused on choosing between four different options:

1. Deposit money in the bank.

2. Purchase USA Treasury notes 5Y

3. Invest in NASDAQ.

4. Invest in Tesla.

Method

The problem was solved using Monte Carlo simulation and Excel as an calculation environment. MC was chosen because it's widely used and accepted for solving similar investment problems.

Model

The simulation is based on historic data found on Yahoo Finance. The behaviour of four given options is projected for the next 15 years using the historic data, while economic crisis effects are included with ability to set up the probability of such event.

Input parameters

  • Initial investment - Amount of USD invested at the beginning of first year.
  • Annual investment - Amount of USD invested at the beginning of every following year.
  • Crisis probability - Probability that crisis will happen as percentage (no matter if we are simulating by months or years).
  • Bank annual interest rate - For bank deposit scenario, in %.
  • Standard deviations and means - For volatile scenarios for standard times.
  • Standard deviations and means - For volatile scenarios for crisis times.

Scenarios

Bank Deposit

This is the simplest scenario. Jon is putting his money in the bank savings account. The annual interest rate is fixed and he will receive known amount of money after 15 years. No randomness in included, so no MC was used for this scenario.

Treasury notes

USA government treasury notes for 5 years are bit more risker for Jon, but they can be more profitable than simple deposits and their payment is guaranteed by the USA government. For the calculations of mean and standard deviation, the simulation uses historic yield data starting from 2010. For the crisis, mean and standard deviation are calculated from only historic yields that are below 1%. Since the attribute of the bonds that interest is received annually and the full worth of bond is paid after 5 years, the total value of the portfolio is calculated as the total invested amount after 15 years. The simulations assumes that all earned interest rates and repaid mature bonds are reinvested immediately. Portfolio is simulated for the 1000 times.

NASDAQ

The Nasdaq Composite Index covers more than 3,000 stocks, which makes them more riskier than government bonds, but still less volatile than investment in the individual stock. For the calculations of mean and standard deviation, the simulation uses historic yield data starting from 2010. For the crisis, mean and standard deviation are calculated from only historic data where value is lower than Mean-2*Standard Deviation from whole dataset values. The total portfolio value is calculated as sum of invested amount and gains. The simulations assumes that all gains are reinvested immediately. Portfolio is simulated for the 1000 times.

TESLA

Tesla is multinational automotive company which has been popular investing option in the recent past. As this scenario assumes investment in only one stock, it's the most volatile one. For the calculations of mean and standard deviation, the simulation uses historic yield data starting from 2010. For the crisis, mean and standard deviation are calculated from only historic data where value is lower than Mean-2*Standard Deviation from whole dataset values. As there was only one month like this, the mean is that value and std dev is set as 0.1%. The total portfolio value is calculated as sum of invested amount and gains. The simulations assumes that all gains are reinvested immediately. Portfolio is simulated for the 1000 times.

Metrics

  • End portfolio value in USD: Average, Median, Percentiles 5,25,75,95.
  • Total yield in USD - Difference between average end portfolio value and total money that's invested from outside.
  • Graph comparing total invested money and average end portfolio value during time.
  • Number of crisis periods.

Working with simulation

The first sheet in the excel is Overview page which has three main parts. Short instruction for using the simulation, table for adding input parameters and results overview. Input parameters are set up with the values that I used for my simulation, but Initial investment, Annual investment, Crisis probability, and Bank annual interest rate can be changed here and the simulation will be updated. There is also the option to change the Mean and Standard Deviation for the three volatile portfolios in the data sheets. Those can be changed by directly adding the values, or by changing the dataset and values will be again automatically calculated. The results overview contains main metrics of simulated results for all four scenarios. The sheets colored in red contain calculations, while the sheets in green contain data from Yahoo Finance and calculations of Mean and Std Dev.

Results

Bank Deposit

Input parameters

Initial investment - 100.000USD

Annual investment - 10.000USD

Annual interest rate - 1%

Portfolio results

Value - $267,066

Total yield - $27,066

Treasury notes

Input parameters

Initial investment - 100.000USD

Annual investment - 10.000USD

Crisis probability - 8%

Mean standard scenario - 1.60%

Std dev standard scenario - 0.81%

Mean crisis scenario - 0.66%

Std dev crisis scenario - 0.23%

Portfolio results

Number of crises years - 3

Average - $308,044

Median - $308,145

Std dev - $17,129

Percentiles 5% - $279,746

Percentiles 25% - $295,877

Percentiles 75% - $319,393

Percentiles 95% - $335,247

Total yield - $68,044

NASDAQ

Input parameters

Initial investment - 100.000USD

Annual investment - 10.000USD

Crisis probability - 8%

Mean standard scenario - 1.19%

Std dev standard scenario - 5%

Mean crisis scenario - -10.26%

Std dev crisis scenario - 2%

Portfolio results

Number of crises months - 16

Average - $339,269

Median - $288,593

Std dev - $212,507

Percentiles 5% - $119,763

Percentiles 25% - $195,623

Percentiles 75% - $414,463

Percentiles 95% - $740,428

Total yield - $99,269

TESLA

Input parameters

Initial investment - 100.000USD

Annual investment - 10.000USD

Crisis probability - 8%

Mean standard scenario - 4.58%

Std dev standard scenario - 18%

Mean crisis scenario - -36.73%%

Std dev crisis scenario - 0.1%

Portfolio results

Number of crises months - 18

Average - $1,146,819

Median - $107,767

Std dev - $6,838,684

Percentiles 5% - $13,187

Percentiles 25% - $40,935

Percentiles 75% - $435,656

Percentiles 95% - $3,412,859

Total yield - $906,819

Conclusion

The simulation was conducted for four different investment scenarios. Based on the simulation results, Jon should decide in which of the four options he should invest. The results are consistent with what would be expected on the real market. From the point of view of volatility, the most volatile option is investment in one stock, in our case Tesla, followed by investment in the index (NASQAD), government bond (USA 5Y Treasury note), and depositing the money in the bank. Since Jon has to buy an apartment, he probably would eliminate the deposit in the bank, as the gain is not big. The next two options are bonds and index funds, which in this case show similar results, even though index are a bit more volatile but also Jon can expect higher gains. Investment in one stock, Tesla, is potentially the most profitable option, where on average his yield would be around 9x bigger than in the next option (NASQAD), but the issue is that even on median value, he would be around 130.000 USD down on his portfolio - which means there is a 50% chance he won't have enough for an apartment. 

Given all the options, investing either in the index or in government bonds seems like the most appropriate decision, depending on Jon's risk appetite.

Sources

Code

File:Savingforanapartment.xlsx

Miln02 (talk) 23:01, 22 January 2023 (CET)