Write a (1)
(2) (3)
(4)
(5)
Assignment 1
Due Date: February 14, 2018 before 11:59 pm
VBA program to evaluate the performance of an investment fund over a four-year period.
Using InputBox, ask the user to input the three possible returns (R1, R2 and R3 where R3 > R2 > R1, R3 > 0, and R2 > 0) in year 1 and their associate probability (p1, p2, and p3).
Using UserForm, ask the user to input the four parameters (a, b, c, and d where d > c > b > a, a < 0 and c > 0) in part (5) below and number of iterations (Iter), then run the simulation.
If the actual first year return is greater than ER (the expected return in year 1), the fund manager will select an investment strategy that the second year return will be Normally distributed with mean of ER and standard deviation of StdR (standard deviation of return in year 1). Otherwise, the fund manager will select an investment strategy that the second year return will be Uniformly distributed with mean of ER and standard deviation of StdR.
If the actual second year return is greater than ER+StdR, the fund manager will select an investment strategy that the third year return will be Uniformly distributed with mean of ER and standard deviation of 0.5*StdR. Otherwise, the fund manager will select an investment strategy that the third year return will be Normally distributed with mean of ER+StdR and standard deviation of 2*StdR.
In the fourth year, the return will follow the below distribution no matter what happened in the first three years:
h A
B
2h
C
(6)
abcd
Prepare a report on the fund performance over the four-year period. The report should also disclose all values (R1, R2, R3, p1, p2, p3, a, b, c, d, Iter, ER, StdR, h, A, B, C) in a professional way.
It is a good idea to show a progress bar (e.g., Application.StatusBar) to let the user know the program is running.