I need someone that can show the calculations that are needed to get these answers. Thanks.

Part 1 : Retirement Planning Analysis A case study (100 points)

Wow.Planning for retirement can be so HARD to understand!

Read the following articles:

Adjusting Expectations

http://www.fa-mag.com/news/adjusting-expectations-13473.html

Drifting Off Track

http://www.fa-mag.com/news/drifting-off-track-13452.html

You are CPA's and your largest client asks you for assistance with retirement planning providing you with the following data make sure your numbers are correct their future depends upon it.

Case Data:

a) They will begin investing a monthly amount on 1/1/14 at age (a) 25, (b) 45, Yrs Old.

b) They will retire at age (a) 55, (b) 70, on 1/1/XX of that year.

c) They will stop contributing upon retirement and begin withdrawing a monthly amount of $3,500. (Their net nest egg will continue to earn interest)

d) They will earn an annual rate of: (a) 2%, (b) 8%.

e) They will invest 10% of their gross annual salary of $60k on an equal monthly basis.

f) When will their retirement money run out in each scenario if it does not last until they are 85? (Provide both the Number of Total Months and also Years and Month)

g) How much will they leave to their beneficiaries, should they die at age 85 on 1/1/XX of that year, in each scenario (Include this short comment within your Excel file)?

h) What advice would you give to yourself and your family members regarding retirement investing? (Include this short comment within your Excel file)

Provide your client with the following: (This is what you hand in to me)

A) An Excel-based, multi-sheet, data file with the first sheet being a tabular summary with one additional WS Tab for each of the scenarios providing all monthly calculation tables (Note: I provided you with an example of how to start this projectuse this and you will be in great shape!). (40 pts)

B) Link all amounts/figures shown in this Summary to the WS pages from which they originate. (40 pts)

NOTE 1: Make sure that you are utilizing the power of excel's calculations and formulas at every stage in the table. You will be graded on your ability to use excel.

NOTE 2: Utilize one (1) Excel file with multiple sheets for each of the many scenarios (name each sheet appropriately), with the first sheet (Sheet 1) being the "Final Analysis" table appropriately named as such, followed by the first scenario tabbed (25, 55, 2%), then (25, 55, 8%), (25, 70, 2%) and so on and so forth.

NOTE 3: This is a planning toolformat and formulate it as suchas in any research case start with the main details as inputs for all spreadsheet calculations to utilize. This should provide a dynamic, not static, planning tool.

NOTE 4: Place a quotation from each article above plus one additional quote that you find on your own, regarding the importance of starting your retirement planning early (remember to utilize proper citations).

Short Summary
Age	Retire	Annual Interest	Money Run Out	Money short at Age	Shortfall for months	Money Left for beneficiaries
25	55	2%	Yes	61 years 3 months	285 Months
25	70	2%	Yes	81 years 9 months	39 months
25	55	8%	No			$2,952,378
25	70	8%	No			$7,560,178
45	55	2%	Yes	56 years 8 months	340 Months
45	70	2%	Yes	74 years 11 months	157 months
45	55	8%	Yes	57 years 5 months	331 months
45	70	8%	No			$363,760.04
As the question is silent about compounding, it is assumed that the investment are made at monthly compounding interest rates at annual interest rate of 2%.
Retirement investing are very important , it should be planned in a proper manner, and made sure that sufficient amount is saved by person at the time of retirement, so that once the earning stops, it should last till one dies, and some money are left for the beneficiaries. Due to character limitations, not able to share the detailed calculations.