You will be making monthly deposits of increasing size into a bank account for 5 years....

Transcribed Image Text:

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 $278. At the beginning of each month thereafter, another deposit will be made increasing by size $20. The continuously compounded interest rate will be 6.18% per annum for the first quarter, but will then increase by 0.2% 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: 1 2 3 4 5 6 7 8 A B C Time 0 =B7+$B$3 D Timestep Initial rate Rate increase Initial Deposit 0.083333 <your rate> <your value> <your value> Rate =C3 E Deposit Prev Total with interest =E3 0 F Answer correct to 2 decimals Increase in deposit <your value> Total =D7+E7 G 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? 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 $278. At the beginning of each month thereafter, another deposit will be made increasing by size $20. The continuously compounded interest rate will be 6.18% per annum for the first quarter, but will then increase by 0.2% 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: 1 2 3 4 5 6 7 8 A B C Time 0 =B7+$B$3 D Timestep Initial rate Rate increase Initial Deposit 0.083333 <your rate> <your value> <your value> Rate =C3 E Deposit Prev Total with interest =E3 0 F Answer correct to 2 decimals Increase in deposit <your value> Total =D7+E7 G 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?