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 total invested money and average end portfolio value.
- 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 directly in the calc sheets. 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. If you change the dataset there, the new mean and std dev will be calculated and simulation will be automatically updated with new results.
