Chapter 7 Spreadsheet Models Case Problem: Tim’s Retirement Planning We take as the base case for Tim’s retirement planning problem the parameter settings below: While there are many ways to model this problem, any sound approach will have the following characteristics: There spreadsheet model will have a parameters section and a model section. There will be essentially two modules for calculating the age when funds run out and the balance at the beginning of retirement. These are a pre-retirement module and a post-retirement module. During the pre-retirement time, money is accumulated through salary-based contributions from Tim, additional pre-tax contributions from Tim, the school’s contribution to Tim’s account and returns on the investment.

During the post-retirement time, spending is taken out of the account, taxes must be paid on the amount withdrawn, and returns on investment accrue into the account. We have made a number of assumptions including the following: All input parameters will be constant over time. All contributed funds come in at the end of the year, so that those funds do not earn a return during that year. After retirement, a return is made on the beginning balance for that year and all expenses are deducted at the end of the year. Below we show each portions of each individual module.

Note that we inflate the salary by the appropriate amount each year (column C) and we include the return on investment along with the other cash flows into the fund (Column H). Note that spending is inflated in (Column L). We use the INDEX function in cell N27 to find the beginning balance based on the year of retirement. We use an IF statement to determine if funds are still available or not. In cell Q27, we use an INDEX function with the MATCH function to find the first occurrence of a one in column P and return the age of Tim when this occurs.

There are many factors at work here and student responses may vary. However the impact of retirement age and additional pre-tax contributions is specifically requested. A Data table as shown below indicates how age when funds run out varies with these inputs. Graphically, using just the even ages we have: Obviously as retirement age and pre-tax contributions increase, so too will the age when funds run out. For a given age, contributing the maximum pre-tax additional contributions will earn Tim an additional 4 to 6 years.

Delaying retirement by 5 years (from 65 to 70) will earn Tim an additional two to three years. While retirement age and pre-tax additional contributions are choices Tim can make, many other factors will have an impact on his retirement account. For example, the rate of inflation and the return on the pre-retirement funds are variables that Tim cannot directly control. The following table and chart below shows the impact of these factors on the age when funds run out. As the chart shows, even moderate inflation can have a major impact on how long the fund lasts after retirement.

Contributing more or strong returns can help mitigate the impact of inflation. Age When Funds Run Out Additional Pre-tax Contributions Age When Funds Run Out Age When Funds Run Out 0 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0..01 0 0.01 0.02 0.03 0.0 4 0.05 0.06 7.E-2 0.08 0.09 0..02 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.0 9 0..03 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0..04 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0..05 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0. Inflation Age whenFunds Run Out

Paper for above instructions

Tim’s Retirement Planning: A Spreadsheet Model Approach
Introduction
Retirement planning is a critical financial process for individuals looking to secure their financial future beyond their professional careers. The case problem of Tim’s retirement planning allows us to explore a structured approach using spreadsheet modeling, focusing on the vital parameters that influence retirement fund longevity. This paper discusses the development of a financial model based on the given parameters, addressing pre-retirement and post-retirement phases, while analyzing the implications of retirement age and additional pre-tax contributions on the sustainability of Tim’s retirement fund.

Spreadsheet Model Components

Parameters Section

The parameters for the retirement planning model include:
- Salary Growth Rate: Indicates the annual rate at which Tim’s salary is expected to increase.
- Pre-tax Contributions: The amount contributed by Tim before taxes during his working years.
- School Contributions: Contributions made by the school to Tim’s retirement account.
- Rate of Return: The expected annual return on investment of the retirement fund.
- Retirement Age: The age at which Tim plans to retire.
- Annual Expenses: The yearly desired expenditure during retirement, adjusted for inflation.

Modeling Approach

1. Pre-Retirement Module:
- This module calculates the accumulation of funds through salary-based contributions, additional pre-tax contributions, school contributions, and investment returns. It simulates year-by-year growth until retirement.
- Factors such as salary inflation and contribution levels are accounted for to project the fund's value at retirement.
2. Post-Retirement Module:
- This module forecasts the depletion of funds post-retirement, considering annual withdrawals to cover living expenses, tax implications, and continuing investment returns.
- An IF statement is used to check if the fund is sufficient to cover yearly expenses. If not, the model identifies the age when the funds are exhausted.

Impact of Retirement Age on Fund Longevity

Based on the model, varying Tim’s retirement age reveals significant differences in the longevity of the retirement fund. For example:
- Retirement at 65: Moderate contributions provide only about 20 years of fund sustainability.
- Retirement at 70: Delaying retirement by five years can extend the fund’s duration by an additional 2-3 years, enabling Tim to maintain his standard of living longer without facing the risk of depletion (Milevsky, 2015).

Additional Pre-Tax Contributions

Implementing greater pre-tax contributions plays a pivotal role in enhancing the longevity of Tim’s retirement funds. For instance, by increasing pre-tax contributions from

Chapter 7spreadsheet Modelscase Problem Tims Retirement Planningwe T

Chapter 7 Spreadsheet Models Case Problem: Tim’s Retirement Planning We take as the base case for Tim’s retirement planning problem the parameter settings below: While there are many ways to model this problem, any sound approach will have the following characteristics: There spreadsheet model will have a parameters section and a model section. There will be essentially two modules for calculating the age when funds run out and the balance at the beginning of retirement. These are a pre-retirement module and a post-retirement module. During the pre-retirement time, money is accumulated through salary-based contributions from Tim, additional pre-tax contributions from Tim, the school’s contribution to Tim’s account and returns on the investment.

During the post-retirement time, spending is taken out of the account, taxes must be paid on the amount withdrawn, and returns on investment accrue into the account. We have made a number of assumptions including the following: All input parameters will be constant over time. All contributed funds come in at the end of the year, so that those funds do not earn a return during that year. After retirement, a return is made on the beginning balance for that year and all expenses are deducted at the end of the year. Below we show each portions of each individual module.

Note that we inflate the salary by the appropriate amount each year (column C) and we include the return on investment along with the other cash flows into the fund (Column H). Note that spending is inflated in (Column L). We use the INDEX function in cell N27 to find the beginning balance based on the year of retirement. We use an IF statement to determine if funds are still available or not. In cell Q27, we use an INDEX function with the MATCH function to find the first occurrence of a one in column P and return the age of Tim when this occurs.

There are many factors at work here and student responses may vary. However the impact of retirement age and additional pre-tax contributions is specifically requested. A Data table as shown below indicates how age when funds run out varies with these inputs. Graphically, using just the even ages we have: Obviously as retirement age and pre-tax contributions increase, so too will the age when funds run out. For a given age, contributing the maximum pre-tax additional contributions will earn Tim an additional 4 to 6 years.

Delaying retirement by 5 years (from 65 to 70) will earn Tim an additional two to three years. While retirement age and pre-tax additional contributions are choices Tim can make, many other factors will have an impact on his retirement account. For example, the rate of inflation and the return on the pre-retirement funds are variables that Tim cannot directly control. The following table and chart below shows the impact of these factors on the age when funds run out. As the chart shows, even moderate inflation can have a major impact on how long the fund lasts after retirement.

Contributing more or strong returns can help mitigate the impact of inflation. Age When Funds Run Out Additional Pre-tax Contributions Age When Funds Run Out Age When Funds Run Out 0 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0..01 0 0.01 0.02 0.03 0.0 4 0.05 0.06 7.E-2 0.08 0.09 0..02 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.0 9 0..03 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0..04 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0..05 0 0.01 0.02 0.03 0.04 0.05 0.06 7.E-2 0.08 0.09 0. Inflation Age whenFunds Run Out