boomerpat's problem
An exercise in Present Values
Motivated by a recent e-mail

Problem:
Joe is planning to retire in five years, on December 31, 2010 (his 65th birthday). His company pension plan will pay him $2,000 per month with the first payment deposited in his checking account on January 1, 2011. Every year, these payments will increase by 3% (all payments made in 2011 are $2,000, all payments made in 2012 are $2,000 (1.03), and so on.) Suppose that Joe passes away in an unfortunate accident on July 1, 2007. If his widow, Jill, is entitled to 80% of the present value of his expected pension payments upon his death, and Joe was expected to live until December 31, 2020, then how much money will Jill receive? Assume an APR of 6% with monthly compounding.


Initial Monthly Payments (in 2011) = $P   (Example: $2000)
Annual Increase Factor = x   (Example: 1.03)
Monthly APR Factor = y   (Example: 1 + 0.06/12, so the "Present Value" reduces each payment by 1/y, for each month)
Magic Formula: 1 + z + z2 + ... + zn-1 = (1 - zn)/(1 - z)


The twelve monthly payments during 2011 are each P.
Present Value of these twelve payments (for Jill, as of July 1, 2007, 36+6 = 42 months earlier than the first payment on Jan 1, 2011) is:
[1]     P/y42 + P/y43 + P/y44 + ... + P/y53 = (P/y42) ( 1 + 1/y + 1/y2 + ... + 1/y11) = (P/y42)(1 - y-12)/(1 - 1/y) ... using the Magic Formula with z = 1/y

The twelve monthly payments during 2012 are each Px   ... having increased by the Annual Increase Factor = x
Present Value of the twelve 2012 payments of Px (as of July 1, 2007, 42+12 = 54 months earlier than the first 2012 payment) is:
[2]     Px/y54 + Px/y55 + Px/y56 + ... + Px/y65 = (Px/y54) ( 1 + 1/y + 1/y2 + ... + 1/y11) = (Px/y54)(1 - y-12)/(1 - 1/y)

etc. etc.

The twelve monthly payments during 2020 are each Px9   ... having increased nine times, by the Annual Increase Factor = x
Present Value of the twelve 2020 payments of Px9 (as of July 1, 2007, 150 months earlier than the first 2020 payment) is:
[10]     (Px9/y150 + Px9/y151 + Px9/y152 + ... + Px9/y161)(1 - y-12)/(1 - 1/y) = (Px9/y150)(1 - y-12)/(1 - 1/y)

The factor (1 - y-12)/(1 - 1/y) appears in every term, being the reduction of twelve payments to the Present Value at the first of each year.

The Present Value of all payments (as of July 1, 2007) is ... adding [1]+[2]+ ... +[10]:
(P/y42 + Px/y54 + Px2/y66 + ... + Px9/y150)(1 - y-12)/(1 - 1/y) = (P/y42)(1 + x/y12 + x2/y24 + ... + x9/y108)(1 - y-12)/(1 - 1/y)

Using the Magic Formula with z = x/y12:
1 + x/y12 + x2/y24 + ... + x9/y108 = (1 - z10)/(1 - z) = [ 1 - (x/y12)10] / (1 - x/y12) = (1 - x10y-120) / (1 - xy-12)

We then get for the Present Value of all 120 monthly payments (as of July 1, 2007) as:
(P/y42) {1 - x10y-120) / (1 - xy-12)} {(1 - y-12)/(1 - y-1)}


In general, if:

  • N is the number of months to the first monthly payment   (Example: 42)
  • m is the number of years of (expected) monthly payments   (Example: 10)
  • Then the present value is:
    (P/yN) {1 - xmy-12m) / (1 - xy-12)} {(1 - y-12)/(1 - y-1)}

Example:
For P = $2000, x = 1.03, y = 1+.06/12 = 1.005, n = 42 months and m = 10 years, then we get:
Present Value = $165,893.90

To check the reasonableness of this result, note that the total of 120 payments
($24K per year, increasing at 3% per year, from Jan 1, 2011 to Dec 1, 2020) is:
24,000(1+1.03+1.032+...+1.039) = 24,000(1-1.0310)/(1-1.03) which is about $275K

We can "estimate" the Present Value of this total, reduced by 6% per year for (roughly) the average between 42 and 161 months.
That's about 102 months or 8.5 years: 275K/1.068.5 or about $168K ... so $165,893.90 seems about right
P.S. The formula can probably be simplified ... but it's too early in the morning for me to do it  

>And that's a correct formula, right?
Yes ... or your money back.

>So how much does Jill get?
I'd say ... making a rough guess ... 80% of that Present Value.

>And I'm supposed to use that %#$@!*! formula?
Yes ... or this:

Initial Monthly Income = $
Annual Increase = %
Years of Income =
Months until first Payment =
Interest Rate = %
Present Value = $