Savingforanapartment
Introduction
Contents
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.
