Question

Sunrise Industries wishes to accumulate funds to provide a retirement annuity for its vice president of research, Jill Moran. Ms. Moran, by contract, will retire at the end of exactly 12 years. Upon retirement, she is entitled to receive an annual end-of-year payment of $42,000 for exactly 20 years. If she dies prior to the end of the 20-year period, the annual payments will pass to her heirs. During the 12-year “accumulation period,” Sunrise wishes to fund the annuity by making equal, annual, end-of-year deposits into an account earning 9% interest. Once the 20-year “distribution period” begins, Sunrise plans to move the accumulated monies to an account earning a guaranteed 12% per year. At the end of the distribution period, the account balance will equal zero. Note that the first deposit will be made at the end of year 1 and that first distribution payment will be received at the end of year 13.

4.       How much would Sunrise have to deposit annually during the accumulation period if Ms. Moran’s retirement annuity were a perpetuity and all other terms were the same as initially described?

This is what I have: but I know it's not right. Do I use the PMT function in Excel or the PV function? Please show all work. Thank you very much.

Annual Payment (PMT)	$42,000.00
Number of Periods (NPER)	12
Market Rate of Interest (RATE)	12%
Future Value (FV)	$313,716.63
Annual Payment (PMT)	($340,686.96)

Answer 1

Amount required after 12 Years:

PV of Annuity:

Annuity is series of cash flows that are deposited at regular intervals for specific period of time.

PV of Annuity = Cash Flow * [ 1 - [(1+r)^-n]] /r
r - Int rate per period
n - No. of periods

Particulars	Amount
Cash Flow	$          42,000.00
Int Rate	12.0000%
Periods	20

PV of Annuity = Cash Flow * [ 1 - [(1+r)^-n]] /r
= $ 42000 * [ 1 - [(1+0.12)^-20]] /0.12
= $ 42000 * [ 1 - [(1.12)^-20]] /0.12
= $ 42000 * [ 1 - [0.1037]] /0.12
= $ 42000 * [0.8963]] /0.12
= $ 313716.63

Amount of deposit during 12 Years:

FV of Annuity :
Annuity is series of cash flows that are deposited at regular intervals for specific period of time.

FV of Annuity = CF [ (1+r)^n - 1 ] / r
r - Int rate per period
n - No. of periods

Particulars	Amount
FV of Annuity	$   3,13,716.63
Int Rate	9.0000%
Periods	12

FV of Annuity = Cash Flow * [ [(1+r)^n ] - 1 ] /r
$313716.63 = Cash Flow * [ [ ( 1 + 0.09 ) ^ 12 ] - 1 ] / 0.09
$313716.63 = Cash Flow * [ [ ( 1.09 ) ^ 12 ] - 1 ] / 0.09
$313716.63 = Cash Flow * [ [ ( 2.8127 ] - 1 ] / 0.09
$313716.63 = Cash Flow * [ 1.8127 ] / 0.09
Cash Flow = $ 313716.63 * 0.09 / 1.8127
Cash Flow = $ 15576.24
Annual deposit shall be made is $ 15576.24

Problem 4:

Amount required after 12 Years = Annual withdrawl / Int rate

= $ 42000 / 12%

= $ 350000

Annual deposit during 12 Years:

Particulars	Amount
FV of Annuity	$   3,50,000.00
Int Rate	9.0000%
Periods	12

FV of Annuity = Cash Flow * [ [(1+r)^n ] - 1 ] /r
$350000 = Cash Flow * [ [ ( 1 + 0.09 ) ^ 12 ] - 1 ] / 0.09
$350000 = Cash Flow * [ [ ( 1.09 ) ^ 12 ] - 1 ] / 0.09
$350000 = Cash Flow * [ [ ( 2.8127 ] - 1 ] / 0.09
$350000 = Cash Flow * [ 1.8127 ] / 0.09
Cash Flow = $ 350000 * 0.09 / 1.8127
Cash Flow = $ 17377.73
Annual deposit is $ 17377.73

Using Excel:

Use PMT formula.