Question

1. Derek wants to withdraw $12,424.00 from his account 5.00 years from today and $12,469.00 from his account 11.00 years from today. He currently has $3,967.00 in the account. How much must he deposit each year for the next 11.0 years? Assume a 6.98% interest rate. His account must equal zero by year 11.0 but may be negative prior to that.

Answer Format: Currency: Round to: 2 decimal places.

2. Derek currently has $13,919.00 in an account that pays 6.00%. He will withdraw $5,788.00 every other year beginning next year until he has taken 5.00 withdrawals. He will deposit $13919.0 every other year beginning two years from today until he has made 5.0 deposits. How much will be in the account 30.00 years from today?

Answer Format: Currency: Round to: 2 decimal places.

Answer 1

Question 1:

Derek will make a deposit at the end of Years 1-4 and withdraw $12,424.00 at the end of Year 5. There would be no deposit in Year 5.

Derek will again make a deposit at the end of Years 6-10 and withdraw $12,469 at the end of Year 11. There would be no deposit in Year 11.

We shall calculate this in Microsoft Excel:

• First, we construct the Excel sheet with columns as shown below
• Cell B3 and F2 are the beginning account balance
• Interest is calculated at 6.98% on the account balance at the start of the year
• Balance at the end of the year is = Balance at the start of the year + Interest earned + Deposit - Withdrawal
• The deposit column is left blank except for Years 5 and 11 - these are zero (0) as there would be no deposit in these years
• The Excel sheet should now look like this:

• Now, we simply input an approximate value for the deposit in Cell C3 and copy the same value in all the cells of Column C except C7 and C13 (Years 5 and 11 - No deposits, remember!)
• If the Balance at the end of Year 11 (Cell F13) is negative, we increase the input value in Column C
• If the Balance at the end of Year 11 (Cell F13) is positive, we decrease the input value in Column C
• Through trial and error, and less than 10 iterations, we can arrive at an exact input value which makes the Balance at the end of Year 11 equal to zero.
• We can see that this value is $1,715.46

Answer to Question 1: $1,715.46

Question 2:

Once again, we calculate this in Excel.

• The schedule is extended for 30 years
• 5 deposits and 5 withdrawals are input in the appropriate cells as seen below
• Interest is calculated at 6% on the Balance at the start of the year
• Balance at the end of year 30 is calculated to be $239,670.86