Question

Problem #5: You will be making monthly deposits of increasing size into a bank account for 5 years. The account will earn continuously compounding interest each month. Every 3 months (i.e. every quarter) the interest rate will increase. The initial deposit will be $227. At the beginning of each month thereafter, another deposit will be made increasing by size $30. The continuously compounded interest rate will be 4.44% per annum for the first quarter, but will then increase by 0.4% at the beginning of each subsequent quarter. Note that the rate is the same constant value for each of the 3 months in a given quarter. Create a spreadsheet with headings as shown: B Timestep Initial rate Rate increase Initial Deposit 0.083333 <your rate> <your value> <your value> Increase in deposit <your value> Ni00 Time Prev Total with interest Rate =C3 Deposit =E3 Total =D7+E7 =B7+$B$3 Each row of the table will represent a different month, with the first row being time zero. Programming note: You will be reusing this spreadsheet and using Goal Seek in the next question. So you should use best practices by having all parameters in individual cells at the top of your spreadsheet. Then inside the table, all the formulas should contain cell references only; no constant numerical values, as illustrated above. Suggestion: For ease in entering the formula for an increasing rate, the first three time steps have the identical rate, and then the rate in any later month equals the rate 3 months ago plus the increase. At the end of 5 years, after your 61st deposit, how much money do you have? Problem #5: Answer correct to 2 decimals

Answer 1

	Timestep	Initial Rate	Rate Increase		Initial Deposit	Increase in Deposit
	0.083333	4.44%	0.40%		227	30
Sr. No.	Time	Rate	Deposit	Interest	Prev. Total with interest	Total
	0	E3	C3		0	F9+ H9
1	-	4.44%	227	0	-	227.00
2	0.08	4.44%	257	0.84	228	257.84
3	0.17	4.44%	288	1.91	260	289.75
4	0.25	4.84%	320	3.22	293	322.96
5	0.33	4.84%	353	5.21	328	358.17
6	0.42	4.84%	388	7.22	365	395.40
7	0.50	5.24%	425	9.57	405	434.97
8	0.58	5.24%	465	13.30	448	478.26
9	0.67	5.24%	508	16.71	495	524.97
10	0.75	5.64%	555	20.63	546	575.60
11	0.83	5.64%	606	27.05	603	632.65
12	0.92	5.64%	663	32.71	665	695.36
13	1.00	6.04%	725	39.22	735	764.58
14	1.08	6.04%	795	50.03	815	844.61
15	1.17	6.04%	875	59.52	904	934.13
16	1.25	6.44%	964	70.53	1,005	1,034.65
17	1.33	6.44%	1065	88.84	1,123	1,153.49
18	1.42	6.44%	1183	105.24	1,259	1,288.73
19	1.50	6.84%	1319	124.49	1,413	1,443.22
20	1.58	6.84%	1473	156.30	1,600	1,629.52
21	1.67	6.84%	1660	185.77	1,815	1,845.29
22	1.75	7.24%	1875	220.88	2,066	2,096.17
23	1.83	7.24%	2126	278.23	2,374	2,404.40
24	1.92	7.24%	2434	333.65	2,738	2,768.05
25	2.00	7.64%	2798	400.81	3,169	3,198.87
26	2.08	7.64%	3229	509.15	3,708	3,738.02
27	2.17	7.64%	3768	618.77	4,357	4,386.79
28	2.25	8.04%	4417	754.09	5,141	5,170.88
29	2.33	8.04%	5201	970.06	6,141	6,170.94
30	2.42	8.04%	6201	1,199.01	7,370	7,399.95
31	2.50	8.44%	7430	1,487.39	8,887	8,917.34
32	2.58	8.44%	8947	1,944.28	10,862	10,891.61
33	2.67	8.44%	10922	2,451.34	13,343	13,372.95
34	2.75	8.84%	13403	3,103.86	16,477	16,506.82
35	2.83	8.84%	16537	4,134.41	20,641	20,671.22
36	2.92	8.84%	20701	5,329.73	26,001	26,030.95
37	3.00	9.24%	26061	6,903.41	32,934	32,964.36
38	3.08	9.24%	32994	9,391.55	42,356	42,385.91
39	3.17	9.24%	42416	12,402.12	54,788	54,818.03
40	3.25	9.64%	54848	16,461.85	71,280	71,309.88
41	3.33	9.64%	71340	22,914.24	94,224	94,254.13
42	3.42	9.64%	94284	31,044.17	1,25,298	1,25,328.29
43	3.50	10.04%	125358	42,285.77	1,67,614	1,67,644.06
44	3.58	10.04%	167674	60,312.74	2,27,957	2,27,986.80
45	3.67	10.04%	228017	83,929.54	3,11,916	3,11,946.34
46	3.75	10.44%	311976	1,17,447.80	4,29,394	4,29,424.14
47	3.83	10.44%	429454	1,71,855.54	6,01,280	6,01,309.69
48	3.92	10.44%	601340	2,45,875.53	8,47,185	8,47,215.22
49	4.00	10.84%	847245	3,53,797.07	12,01,012	12,01,042.29
50	4.08	10.84%	1201072	5,31,621.35	17,32,664	17,32,693.64
51	4.17	10.84%	1732724	7,82,599.96	25,15,294	25,15,323.60
52	4.25	11.24%	2515354	11,58,809.58	36,74,133	36,74,163.19
53	4.33	11.24%	3674193	17,89,562.42	54,63,726	54,63,755.60
54	4.42	11.24%	5463786	27,12,390.41	81,76,146	81,76,176.01
55	4.50	11.64%	8176206	41,35,509.82	1,23,11,686	1,23,11,715.83
56	4.58	11.64%	12311746	65,68,300.40	1,88,80,016	1,88,80,046.23
57	4.67	11.64%	18880076	1,02,55,641.11	2,91,35,687	2,91,35,717.34
58	4.75	12.04%	29135747	1,61,09,138.12	4,52,44,855	4,52,44,885.46
59	4.83	12.04%	45244915	2,63,29,507.01	7,15,74,392	7,15,74,422.47
60	4.92	12.04%	71574452	4,23,69,672.29	11,39,44,095	11,39,44,124.75
61	5.00	12.44%	113944155	6,85,94,363.10	18,25,38,488	18,25,38,517.85