What will be the size of each installment?

Question

Yumi's grandparents presented her with a gift of $21,000 when she was 9 years old to be used for her college education. Over the next 8 years, until she turned 17, Yumi's parents had invested her money in a tax-free account that had yielded interest at the rate of 4.5%/year compounded monthly. Upon turning 17, Yumi now plans to withdraw her funds in equal annual installments over the next 4 years, starting at age 18. If the college fund is expected to earn interest at the rate of 5%/year, compounded annually, what will be the size of each installment? (Assume no interest is accrued from the point she turns 17 until she makes the first withdrawal. Round your answer to the nearest cent.)

Answer 1

This is a multi-step time value of money problem.

Step 1: Calculate the future value (FV) of the initial investment, which earns 4.5% compounded monthly interest

1.a. From excel, use the formula FV. Go to Formulas, Financial and select FV from the list

1.b. Populate with given values.

1.c. Step 1: FV = $30,079.66

Step 2: Build a cash flow model to calculate the FV of Step 1 with following conditions:

2.a. Ensure withdrawal is subtracted each year before applying 5% annual interest

2.b. Year 1 FV is Year 2 Initial Investment value, Year 2 FV is Year 3 Initial Investment, Year 3 FV is year 4 Initial Investment

2.c. Assume the "equal" withdrawal for each year will be ¼ of the Year 1 initial investment value, $30,079.66, in order to get the model up and running

2.d. Run multiple iterations of the model adjusting the “withdrawal” value until future value in year 4 is $0.

Periods

Iteration 1                                        Year 1                Year 2                Year 3                Year 4

Initial Investment Value              $30,079.66        $23,687.73             $16,976.21        $9929.11

Equal Annual w/drawal               ($7519.91)        ($7519.91)       ($7519.91)      ($7519.91)

Interest rate                                  5%

FV of Investment minus               $23,687.73        $16,976.21         $9,929.11          $2,529.65

Annual w/drawal

Periods

Iteration 5                                        Year 1                Year 2                Year 3                Year 4

Initial Investment Value              $30,079.66        $23,100.82             $15,773.04        $8078.88

Equal Annual w/drawal               ($8078.88)         ($8078.88)       ($8078.88)        ($8078.88)

Interest rate                                  5%

FV of Investment minus               $23,100.82        $15,773.04         $8078.88           $0.00

Annual w/drawal

Answer: ($8,078.88) rounded to the nearest cent