Finance Case Help!!

[cdragneel97]

I'm supposed to be doing an Excel Spreadsheet for this but I'm confused on where to even begin with it. Can someone help me?

SUNRISE INDUSTRIES HAS OFFERED THEIR VP, JILL MORAN, A RETIREMENT PACKAGE. THEY WILL PAY HER $42,000 EACH YEAR AFTER SHE RETIRES 12 YEARS FROM TODAY. THE $42,000 WILL EARN 12% ANNUAL COMPOUND RETURN DURING THIS DISTRIBUTION PERIOD OF 20 YEARS OF RETIREMENT.
A.WHAT MUST BE IN JILL’S ACCOUNT AT THE END OF 12 YEARS SO THAT SHE CAN WITHDRAW THE $42,000 PER YEAR DURING HER RETIREMENT?

B. HOW MUCH DOES SUNRISE HAVE TO PUT AWAY EACH YEAR DURING THE ACCUMULATION PERIOD OF 12 YEARS WHILE JILL IS STILL WORKING TO ACCUMULATE THE PROPER SUM FROM WHICH JILL WILL WITHDRAW $42,000 PER YEAR DURING HER RETIREMENT IF SUNRISE CAN EARN 9% ON THE DEPOSITS.

C. JILL HAS NOW NEGOTIATED WITH SUNRISE TO HAVE $60,000, RATHER THAN JUST $42,000, EACH YEAR IN RETIREMENT, THE DISTRI BUTION PERIOD, COMPOUNDED AT 12% PER YEAR. HOW MUCH MORE MUST SUNRISE CONTRIBUTE EACH YEAR DURING JILL’S ACCUMULATION PERIOD IF THEY CAN STILL EARN 9% COMPOUND RETURNON THEIR DEPOSITS?

D. REWORK PART B IF SUNRISE CAN ONLY EARN 6% ON THE DEPOSITS.

 

[tkhunny]

Well, you should get started!
Column B, Cell 1: "Accumulation"
Column C, Cell 1: "Retirement
Column B, Cell 2: 0.00
Column C, Cell 2: 42000
Column B, Cell 3: 0.09
Column C, Cell 3: 0.12
Starting in Cell 4, 5, 6, ...Number 0, 1, 2, 3, 4, ..., 12, 13, 14, ..., 32
Column B, Cell 4 -- =B2
Column B, Cell 5 -- =B4*(1+B$3)+B$2
Column B, Cells 7-17 -- Copy the formula from B5
Column C, Cell 17: =B17 - C2
Column C, Cell 18: =C17*(1+C$3) - C$2 and copy this formula to the end of the index in column A.

Well, I was writing this without actually looking at a spreadsheet, but that is the idea. You may have to tweak it a bit. Use the Goal Seek to fill in Cell B2 so that the money runs out after 20 years of retirement. You should be able to use this model to answer other questions.

 

[cdragneel97]

Well, you should get started!
Column B, Cell 1: "Accumulation"
Column C, Cell 1: "Retirement
Column B, Cell 2: 0.00
Column C, Cell 2: 42000
Column B, Cell 3: 0.09
Column C, Cell 3: 0.12
Starting in Cell 4, 5, 6, ...Number 0, 1, 2, 3, 4, ..., 12, 13, 14, ..., 32
Column B, Cell 4 -- =B2
Column B, Cell 5 -- =B4*(1+B$3)+B$2
Column B, Cells 7-17 -- Copy the formula from B5
Column C, Cell 17: =B17 - C2
Column C, Cell 18: =C17*(1+C$3) - C$2 and copy this formula to the end of the index in column A.

Well, I was writing this without actually looking at a spreadsheet, but that is the idea. You may have to tweak it a bit. Use the Goal Seek to fill in Cell B2 so that the money runs out after 20 years of retirement. You should be able to use this model to answer other questions.

Sorry, I'm not really a Excel expert, or even finance at that since it's not my major, so I'm a little confused D: I'm confused at the part where you said "Starting in Cell 4, 5, 6, ...Number 0, 1, 2, 3, 4, ..., 12, 13, 14, ..., 32". I don't know what you're meaning by that

Thank you so much for your help by the way!!

 

[tkhunny]

[TABLE="width: 304"]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]Accumulation
[/TD]
[TD]Retirement
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"]42000
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD="align: right"]0.09
[/TD]
[TD="align: right"]0.12
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[/TABLE]