please solve the follwing question using excel
\begin{tabular}{|c|c|c|c|c|} \hline & A & B & c & D \\ \hline 2 & & \multicolumn{2}{|c|}{ Interest rates } & \\ \hline \multicolumn{5}{|l|}{3} \\ \hline 4 & & Month & \multicolumn{2}{|c|}{ Actual interest rate } \\ \hline 5 & & 1 & \multicolumn{2}{|l|}{0.47%} \\ \hline 6 & & 2 & \multicolumn{2}{|l|}{3.14%} \\ \hline 7 & & 3 & \multicolumn{2}{|l|}{2.12%} \\ \hline 8 & & 4 & \multicolumn{2}{|l|}{2.44%} \\ \hline 9 & & 5 & \multicolumn{2}{|l|}{0.12%} \\ \hline 10 & & 6 & \multicolumn{2}{|l|}{0.47%} \\ \hline 11 & & 7 & \multicolumn{2}{|l|}{4.40%} \\ \hline 12 & & 8 & \multicolumn{2}{|l|}{0.85%} \\ \hline 13 & & 9 & \multicolumn{2}{|l|}{1.64%} \\ \hline 14 & & 10 & \multicolumn{2}{|l|}{1.98%} \\ \hline 15 & & 11 & \multicolumn{2}{|l|}{0.66%} \\ \hline 16 & & 12 & \multicolumn{2}{|l|}{0.55%} \\ \hline 17 & & 13 & \multicolumn{2}{|l|}{0.21%} \\ \hline 18 & & 14 & \multicolumn{2}{|l|}{0.65%} \\ \hline 19 & & 15 & \multicolumn{2}{|l|}{7.49%} \\ \hline 20 & & 16 & \multicolumn{2}{|l|}{0.64%} \\ \hline 21 & & 17 & \multicolumn{2}{|l|}{1.86%} \\ \hline 22 & & 18 & \multicolumn{2}{|l|}{1.56%} \\ \hline 23 & & 19 & \multicolumn{2}{|l|}{2.82%} \\ \hline 24 & & 20 & \multicolumn{2}{|l|}{0.61%} \\ \hline 25 & & 21 & \multicolumn{2}{|l|}{5.31%} \\ \hline 26 & & 22 & \multicolumn{2}{|l|}{5.34%} \\ \hline 27 & & 23 & \multicolumn{2}{|l|}{1.15%} \\ \hline 28 & & 24 & 1.80% & \\ \hline 29 & & 25 & 0.12% & \\ \hline 30 & & 26 & 2.61% & \\ \hline 31 & & 27 & 0.65% & \\ \hline 32 & & 28 & 1.10% & \\ \hline 33 & & 29 & 4.44% & \\ \hline 34 & & 30 & 1.68% & \\ \hline 35 & & 31 & 1.00% & \\ \hline 36 & & 32 & 2.94% & \\ \hline 37 & & 33 & 1.19% & \\ \hline 38 & & 34 & 0.89% & \\ \hline 39 & & 35 & 1.23% & \\ \hline 40 & & 36 & 4.37% & \\ \hline 41 & & & & \\ \hline \end{tabular} QUESTION FIVE The commercial arm of the Strathmore Centre for Value Investing (SCVI) has developed an investment product Motocas! (Motocas factorial.) The name of the product is not at all creative. However, it describes the purpose of the product: To enable people save up enough money over a period of 36 months to enable them purchase a motor car. Here's how it works. Investors deposit a fixed sum of KES 25,000 at the beginning of each month into the fund. The fund provides a guaranteed return of 1% per month on the fund value at the beginning of each month. Now the actual returns carned by the fund will vary from the guaranteed rate! Where the returns are higher than the guaranteed rate, the excess interest carned is transferred into a reserve account. And where the returns are lower than guaranteed rate, the shortfall is withdrawn from the reserve account and added to the fund value so that it will still have earned the guarantecd rate in the month. The reserve account however earns no interest and is simply an account where excess or shortfall is deposited or withdrawn as needed. Based on complex (very complex) statistical and time series analysis, the actual interest rates over the 36-month period are expected to be as shown on Interestrates data. Required. 1. Calculate the following summary statistics for actual rates of retum Minimum maximum mean standard deviation. and coefficient of variation [2 markel 5. Plot values of actual fund versus guaranteed fund value at the end of each month for 36 months. [2 marks] 6. Calculate the amount that should be invested cach month so that the final fund value after 36 months is KES 1 Million. [2 marks]
