PLEASE PROVIDE THE FORMULAS USE TO DETERMINE AWNSER-NOT JUST THE AWNSER. THANK Y

Question

PLEASE PROVIDE THE FORMULAS USE TO DETERMINE AWNSER-NOT JUST THE AWNSER. THANK YOU!

Benedict’s Retirement Planning
Benedict is 37 years old and would like to establish a retirement plan. Develop a spreadsheet
model that could be used to assist Benedict with retirement planning.
Your model should include the following input parameters:
Benedict’s current age = 37 years
Benedict’s current total retirement savings = $259,000
Annual rate of return on retirement savings = 4 percent
Benedict’s current annual salary = $145,000
Benedict’s expected annual percentage increase in salary = 2 percent/year
Benedict’s own percentage of annual salary contributed to retirement = 6 percent
Benedict’s expected age of retirement = 65
Benedict’s expected annual expenses after retirement (current dollars) = $90,000
Rate of return on retirement savings after retirement = 3 percent
Income tax rate postretirement = 15 percent
Assume that Benedict’s employer contributes 6% of Benedict’s salary to his retirement fund in
addition to the money Benedict himself puts aside. Benedict can make an additional annual
contribution to his retirement fund before taxes (tax free) up to a contribution of $16,000.
Assume he contributes $6,000 per year. Also, assume an inflation rate of 2%.

Managerial Report
Your spreadsheet model should provide the accumulated savings at the onset of retirement as
well as the age at which funds will be depleted (given assumptions on the input parameters).
As a feature of your spreadsheet model, build a data table to demonstrate the sensitivity of the
age at which funds will be depleted to the retirement age and additional pre-tax contributions.
Similarly, consider other factors you think might be important.
Develop a report for Benedict outlining the factors that will have the greatest impact on his
retirement.

Explanation / Answer

To solve the problem let us gather the given data in the excel sheet, group the data according to the units for ease of understanding.

Step 1: Before retirement

In this step we will calculate Benedict's contribution to the retirement fund and the accumulated amount each year.

We will calculate the amount in today's value of money, hence inflation will play no role in this step.

The value of each column is calculated by using the respective formula as given below;

Age = Current Age (add +1 for each preceding year)

Salary = Salary in the previous year * (1 + Increment in %)

Contribution = Own contribution (6%) * Salary + Employer's contribution (6%) * Salary + $6000 (other contribution)

Expenses = 0 during the employment period

Savings (for first row only) = Current Savings

Savings (for the remaining rows) = Total Amount in the previous year + contribution in the current year

Interest Earned = Savings in the current year * Rate of return (4%)

Total Amount = Savings + Interest earned

Copy all the formulae in the rows till the Age column reaches 65.

Here we calculate the total amount as shown below;

Step 2 : After Retirement

The same columns to be copied here as well with addition of one column for post retirement tax calculation;

The formula to calculate respective values are mentioned below;

Age = Retirement age (add +1 for preceding years)

Salary and Contribution will be nil post retirement.

Expenses (In the first row only) = Annual expenses after retirement * (1 + inflation rate)^(Retirement age - current age)

Expenses (In the remaining rows) = Expenses in the previous row * (1+inflation rate)

Savings (In the first row only) = Retirement Fund - Expenses in the current year

Savings (In the remaining rows) = Total Amount after Tax in the previous year - Expenses in the current year

Interest Earned = Savings * rate of return after retirement (3%)

Amount = Savings + Interest earned

Total Amount after Tax = Amount * (1-Tax rate postretirement)

After repeating these formulaes for all the rows we will get the following table;

Here, at the age of 74 Benedict will runn out of funds since the Total Amount becomes negative.

Current age 37 Current Savings $         259,000 Rate of Return 4% Retirement age 65 Current Salary $         145,000 Increment 2% Annual expenses after retirement (current) $           90,000 Rate of Return (after retirement) 3% Own Contribution to reqtirement fund 6% Income tax postretirement 15% Employer's contribution 6% Inflation 2% Other contribution $             6,000

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.