Please help! So confused on how to find the rate of return for the annuity! Please show all work in excel!
Carolyn Bowen, who just turned 55, is employed as an administrative assistant for the Xcon Corporation, where she has worked for the past 20 years. She is in good health, lives alone, and has two grown children. A few months ago her husband died, leaving her with only their home and the proceeds from a $75,000 life insurance policy. After she paid medical and funeral expenses, $60,000 of the life insurance proceeds remained. In addition to the life insurance proceeds, Carolyn has $37,500 in a savings account, which she had accumulated over the past 10 years. Recognizing that she is within 10 years of retirement, Carolyn wishes to invest her limited resources so she will be able to live comfortably once she retires. Carolyn is quite superstitious. After consulting with a number of psychics and studying her family tree, she is certain she will not live past 80. She plans to retire at either 62 or 65, whichever will allow her to meet her long-run financial goals. After talking with a number of knowledgeable individuals including, of course, the psychics-Carolyn estimates that to live comfortably in retirement, she will need $45,000 per year before taxes. This amount will be required annually for 18 years if she retires at 62 or for 15 years if she retires at 65. As part of her financial plan, Carolyn intends to sell her home at retirement and rent an apartment. She has estimated that she will net $112,500 if she sells the house when she is 62 and $127,500 if she sells it when she is 65. Carolyn has no financial dependents and is not concerned about leaving a sizable estate to her heirs. If Carolyn retires at age 62, she will receive from Social Security and an employer-sponsored pension plan a total of $1,359 per month ($16,308 annually); if she waits until age 65 to retire, her total retirement income will be $1,688 per month ($20,256 annually). For convenience, Carolyn has already decided to convert all her assets at the time of retirement into a stream of annual income and she will at that time purchase an annuity by paying a single premium. The annuity will have a life just equal to the number of years remaining until her 80th birthday. If Carolyn retires at age 62 and buys an annuity at that time, for each $1,000 that she puts into the annuity she will receive an annual benefit equal to $79 for the subsequent 18 years. If she waits until age 65 to retire, each $1,000 invested in the annuity will produce an annual benefit of $89.94 for the 15 years. Carolyn plans to place any funds currently available into a savings account paying 6% compounded annually until retirement. She does not expect to be able to save or invest any additional funds between now and retirement. What is the annual rate of return she earns on the annuities
