Bob Davidson’s Retirement Planning Financial Model”

QUESTION

Create retirement planning financial model in MS Excel to answer the case questions. Note that for Week Four you don’t need to answer the case questions, but your model must have the ability to generate the data needed to answer the questions.

Need excel formulas for each question and answer

RETIREMENT PLANNING

Bob Davidson is a 46-year-old tenured professor of marketing at a small New England business school. He has a daughter, Sue, age 6, and a wife, Margaret, age 40. Margaret is a potter, a vocation from which she earns no appreciable income. Before she was married and for the first few years of her marriage to Bob (she was married once previously), she worked at a variety of jobs, mostly involving software programming and customer support.

Bob’s grandfather died at age 42; Bob’s father died in 1980 at the age of 58. Both died from cancer, although unrelated instances of that disease. Bob’s health has been excellent; he is an active runner and skier. There are no inherited diseases in the family with the exception of glaucoma. Bob’s most recent serum cholesterol count was 190.

Bob’s salary from the school where he works consists of a nine-month salary (currently $95,000), on which the school pays an additional 10 percent into a retirement fund. He also regularly receives support for his research, which consists of an additional two-ninths of his regular salary, although the college does not pay retirement benefits on that portion of his income. (Research support is additional income; it is not intended to cover the costs of research.) Over the 12 years he has been at the college his salary has increased by 4 to 15 percent per year, although faculty salaries are subject to severe

compression, so he does not expect to receive such generous increases into the future. In addition to his salary, Bob typically earns $10,000 to 20,000 per year from consulting, executive education, and other activities.

In addition to the 10 percent regular contribution the school makes to Bob’s retirement savings, Bob also contributes a substantial amount. He is currently setting aside $7,500 per year (before taxes). The maximum tax-deferred amount he can contribute is currently $10,000; this limit rises with inflation. If he were to increase his savings toward retirement above the limit, he would have to invest after-tax dollars. All of Bob’s retirement savings are invested with TIAA-CREF (Teachers Insurance and Annuity Association-College Retirement Equities Fund; home page: www.tiaa-cref.org), which provides various retirement, investment, and insurance services to university professors and researchers. Bob has contributed to Social Security for many years as required by law, but in light of the problems with the Social Security trust fund he is uncertain as to the level of benefits that he will actually receive upon retirement. (The Social Security Administration’s website is www.ssa.gov.)

ANSWER

Bob Davidson’s Retirement Planning Financial Model”

Creating a retirement planning financial model in MS Excel for Bob Davidson involves various aspects, including estimating retirement expenses, projecting income, determining savings needs, and considering investment returns. Below are Excel formulas and explanations for each question to help Bob plan for his retirement:

Estimating Retirement Expenses

Question: What are Bob’s estimated annual expenses in retirement?

To estimate Bob’s retirement expenses, we need to consider his current expenses and adjust them for inflation. We’ll assume a 3% inflation rate.

Excel Formula: =Current_Expenses * (1 + Inflation_Rate) ^ (Retirement_Years - Current_Age)

Projecting Income

Question: What will be Bob’s sources of income during retirement?

Bob’s sources of retirement income may include his pension, Social Security, and investment returns.

Excel Formula (Pension): =Annual_Salary * 9/12 * (1 + Salary_Increase_Rate) ^ (Retirement_Years - Current_Age)

Excel Formula (Social Security): =Estimated_Social_Security_Benefit

Excel Formula (Investment Returns): =Total_Retirement_Savings * Annual_Investment_Return

Determining Savings Needs

Question: How much does Bob need to save for retirement?

We can use the future value of annuity formula to calculate the required savings.

Excel Formula: =FV(Annual_Savings, Retirement_Years - Current_Age, 0, -Current_Retirement_Savings)

Considering Investment Returns

Question: How much will Bob’s retirement savings grow with investment returns?

To estimate the future value of Bob’s retirement savings, we can use the compound interest formula.

Excel Formula: =PMT(Annual_Investment_Return, Retirement_Years - Current_Age, 0, -Current_Retirement_Savings)

Assessing Social Security Benefits

Question: What will be Bob’s estimated Social Security benefits?

Bob can use the Social Security Administration’s website or calculator to estimate his future benefits.

 Tax Implications

Question: How will taxes affect Bob’s retirement income?

We’ll need to estimate Bob’s effective tax rate during retirement based on his income sources and tax laws at that time.

Flexibility in Savings

Question: How flexible is Bob’s retirement savings plan?

Bob can calculate the maximum tax-deferred contribution and determine if he can save more after-tax dollars if needed.

Excel Formula: =MIN(Max_Tax_Deferred_Contribution, Additional_Annual_Savings)

Monitoring Progress

Question: How can Bob track his retirement savings progress?

Bob can use Excel to create a retirement savings tracker that calculates his current savings and how they align with his retirement goals.

Excel Formula: =Current_Retirement_Savings + (Annual_Savings - Annual_Expenses)

Bob can use the above Excel formulas and create a comprehensive financial model that considers various factors affecting his retirement planning. Additionally, he should periodically review and adjust his retirement plan as circumstances change, ensuring he remains on track to meet his retirement goals.