Question

Joe's retirement scheme at work pays $500 at the end of each month. Joe puts his money in an account which earns nominal 12% converted monthly, the interest is reinvested at a nominal 4% converted monthly. Carol's account also pays $500 at end of each month, but earns nominal 12% convertible monthly (principal and interest both earn 12%). After 20 years, they retire. Carol has $184,465.8246 more than Joe at this time.

Same scenario above: How long until Carol's account exceeds Joe's by $1,000,000?

Answer 1

The formula for an annuity which is reinvested at different rate is given below:

Accumulated Future Value = A * (n + i% * ((((1+r%)^n-1)/(r%)) - n)/(i%))

Where i% = coupon interest earned

r% = reinvestment rate

n =number of periods

A = annuity amount

Using the formula, substituting the values, Joe's accumulated value after 20 years

FV(Joe) = =500*(240+1%*(( ((1+0.333%)^(240)-1)/(0.3333%))-(240))/0.3333%)

Where i and r are given in terms of monthly

FV(Joe) = $310,161.94

FV(Carol) = usual annuity with 1% monthly interest compounding

FV(carol) = 500 * ((1+1%)^240-1)/(1%) = $494,627.68

After 20 years, FV(Carol)- FV(Joe) = $494,627.68 - $310,161.94 = $184,465.74

Similar to the above, we can now find the period n at which difference is $1,000,000

FV(Carol)- FV(Joe) = 1,000,000

500 * (n + 1% * ((((1+4%/12)^n-1)/(4%/12)) - n)/(1%)) - 500 * ((1+1%)^n-1)/(1%) = 1000,000

For 300 months, the difference can be calculated using above method as $468,229

For 340 months, it is $818,051.59

For 350 months, the difference is 934,966.24

For 360 months, the difference is 1,066,407.96

Hence, the required months should be between 350 and 360 months. The approximate months can now be calculated using linear interpolation.

A difference of (1,066,407.96-934,966.24) involves a reduction of 10 months,

so, a difference of (1066,407.96 - 1,000,000) will involve a reduction of

Diff = (10/(1,066,407.96-934,966.24) *(1066,407.96 - 1,000,000) = 5.05

So, a difference of 1,000,000 will involve 360-5.05 months = 354.95 months (approx)

Hence the answer using linear interporation method is 355 months or 29.58 years.

(Note the entire above calculation can be easily done using excel sheet and excel's goal seek function by tabulating the values and using formulas. The above example is done to explain the concept

Using goal seek feature of excel, we can find that for a period of 355.09

FV(Carol)= $1,661,831.72

FV(Joe) = $ 661,831.72

Hence, it will take 355.09 months or 29.59 years for Carol's accumulated future value to exceed Joe's by $1,000,000