Model Sensitivity Analysis Assessment 3 Answer

Assignment 3 Model Sensitivity Analysis

Background

The year is 2020, and Alex is now at the age of 25. His parents own an investment property (without debts) in the VIC. Assume the property is a median-size house-with-land property, valued the same as your assigned suburb in Assignment 1. Due to the unexpected COVID-19, Alex started to think that the human life is so fragile, and he wanted to enjoy every moment of today. With that thought, he was exploring the possibility that "living without having to work."

Task 1

Currently, Alex is staying with his parents, both of whom are 50-year-old. As a spoiled child, Alex is strongly supported by his parents, so he does not have to work to earn incomes, if that is what he wants. Also, Alex will inherit their investment property as a heritage, after both his parents die. Once that occurs, Alex will sell the property to gain cash at the market price, which will be saved in a bank to support his daily life. Alex has no other sources of savings and/or incomes. For simplicity, assume that everyone will die at the age of 90.

Suppose that the rental return of the investment property is 5% per year, which is paid once annually. For the received rent, 95% will be used to support the living of Alex’s family, and 5% will be re-invested to the investment property to increase its market value. In practice, it can be maintenance work and/or simple add-on of the house. For instance, if the house is worth 1 million at the start of year, $50,000 rent will be earned at the end of year, among which $2,500 is re-invested, such that the house will be worth at its end-of-year market value + $2,500.

Other essential assumptions are listed below:

·       The market value of a house will increase at the average rate as over the dataset of your suburb in Assignment 1;

·       The cash saving rate is 1.5% per year, paid once at the end of year;

·       Inflation rate is 2% per year;

·       Average yearly expense is $80,000 in 2020, which is to be charged once at the end of year; and

·       The GST of the yearly expense is 10%.

With the above information, build a spreadsheet model to explore the age at which Alex’s assets will be depleted. Also, use a data table to demonstrate the sensitivity of the age at which Alex’s assets will be depleted to the cash saving rate and inflation rate. Use a scenario manager to consider at least three scenarios. For each one, consider changing at least three factors among constant rental return of the investment property, maximum of human life, average yearly expense, inflation rate, cash saving rate, GST of the yearly expense and yearly percentage increasement in the market value of the house.

 Task 2

Due to the recent recession in VIC’s real estate market, Alex wanted to explore alternative investment options instead of holding the investment property. Suppose that Alex persuaded his parents to sell the house at the current market price (as of 2020-prelim in your assigned suburb for Assignment 1). The gained cash will be invested to a mutual fund, which will distribute a certain percentage (d) of annuity at the end of year (note that the annuity is paid by the mutual fund company, not from the balance of the fund). For instance, if d=5%, and the end-of-year mutual fund balance is $1,000,000, Alex's family will receive an annuity of $50,000 at the year end. Also, 10% of this annuity will be re-invested into the fund, whereas the rest will be used to pay for life expenditure of Alex’s family. In additional, the mutual fund will grow at a rate of r each year. You are told that:

·       Both d and r are variant in each year. For instance, d of 2020 and of 2021 may be different;

·       In each year, the dividend rate d follows a uniform distribution with lower and upper bounds of 0 and 10%, respectively; and

·       The fund growth rate r follows a normal distribution with mean and standard deviation of 8% and 6%, respectively.

With the above information, build a spreadsheet model to explore the balance of the mutual fund when Alex’s parents die, using at least 1000 simulations. Demonstrate your results under three scenarios: the maximum of human life is 85, 90 and 95 years, respectively. Also, suppose that the investment property will grow at the constant average rate as over the dataset of your suburb in Assignment 1 (note that you do not need to consider the re-invested rents this time). Discuss whether or not Alex should invest in the mutual fund.