Simple Model Relax Assumptions Advanced Excel
The Depth of a Financial Model
Extending a Simple Retirement Model in Excel
Nick DeRobertis1
1 University
of Florida
Department of Finance, Insurance, and Real Estate
December 30, 2020
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 1 / 15
Simple Model Relax Assumptions Advanced Excel
Table of Contents
1 The Simple Model
2 Extending the Model by Relaxing Assumptions
3 Advanced Excel Modeling
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 2 / 15
Simple Model Relax Assumptions Advanced Excel
From Simple to Complex
In the last class, we built a simple retirement model
Today we will see how any financial model can become complex very
quickly
We will continue building the model in both Excel and Python, later
combining the two
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 3 / 15
Simple Model Relax Assumptions Advanced Excel
The Conceptual Parts of a Model
Equations
Logic
Assumptions
Model
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 4 / 15
Simple Model Relax Assumptions Advanced Excel
What Did we Assume?
We made a few assumptions last time in building a general retirement
model
Assumptions
1 The salary is constant over time
2 The savings rate is constant over time
3 Investment returns are constant over time
4 The amount needed in retirement is given by a fixed amount of
desired cash
5 The amount needed in retirement does not depend on market
conditions or life situations
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 5 / 15
Simple Model Relax Assumptions Advanced Excel
Table of Contents
1 The Simple Model
2 Extending the Model by Relaxing Assumptions
3 Advanced Excel Modeling
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 6 / 15
Simple Model Relax Assumptions Advanced Excel
Relaxing the salary assumption
Assumptions can be relaxed to create a more realistic model
Often we still need an assumption, but it can be a more realistic one
We shall relax the constant salary assumption
New assumption: The salary grows at a constant rate for cost of
living raises, and every number of years the salary grows at an
additional rate for a promotion.
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 7 / 15
Simple Model Relax Assumptions Advanced Excel
Relaxing the salary assumption
The Equation from the New Assumption
St = S0 (1 + rl )t (1 + rp )p
St : Salary at year t
S0 : Starting wealth
rl : Return for cost of living
rp : Return for promotion
t: Number of years
p: Number of promotions
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 8 / 15
Simple Model Relax Assumptions Advanced Excel
Table of Contents
1 The Simple Model
2 Extending the Model by Relaxing Assumptions
3 Advanced Excel Modeling
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 9 / 15
Simple Model Relax Assumptions Advanced Excel
An Organized Structure of an Advanced Excel Model
We are going to build our first
complex Excel model
It is important to start
structuring your model so that it
is navigatable
Inputs in one area, outputs in
one area, sub-models in
individual tabs
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 10 / 15
Simple Model Relax Assumptions Advanced Excel
Modeling Salary Growth in Excel - If Command
We need to learn a few formulas and patterns in Excel to model the
new assumption
=IF(5=5, "this", "that") -> ”this”
=IF(4=5, "this", "that") -> ”that”
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 11 / 15
Simple Model Relax Assumptions Advanced Excel
Modeling Salary Growth in Excel - Modulo
Returns the remainder after a number is divided by a divisor
=MOD(3, 4) -> 3
=MOD(7, 2) -> 1
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 12 / 15
Simple Model Relax Assumptions Advanced Excel
Modeling Salary Growth in Excel - Table Lookup
Use VLOOKUP when you need to find things in a table or by row
=VLOOKUP("Celery", J3:K6, 2) -> ”Vegetable”
Lookup column must be first column, and must be sorted in
ascending order.
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 13 / 15
Simple Model Relax Assumptions Advanced Excel
Salary Growth in Excel
Extending the Excel Retirement Model for Realistic Salaries
I will now relax the assumption that salary is a fixed number in the
Excel model.
As this will be quite different from the last model, I will start from
scratch.
I have uploaded the finished product to the course site as Dynamic
Salary Retirement Model
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 14 / 15
Simple Model Relax Assumptions Advanced Excel
Dynamic Desired Cash in Excel
Determining Desired Cash in the Dynamic Salary Retirement Excel
Model
1 We want to relax the assumption that the amount needed in
retirement is given by a fixed amount of desired cash
2 Add new inputs to the model, ”Annual Cash Spend During
Retirement” and ”Years in Retirement”
3 Calculate desired cash based on interest, cash spend, and years in
retirement
4 Use the calculated desired cash in the model to determine years to
retirement
Answers: Slide 17 Resources: Slide 18
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 15 / 15
Lecture Resources
Lecture Resources
1 Slides - The Depth of a Financial Model
2 Lecture Notes - The Depth of a Financial Model
3 Dynamic Salary Retirement Model - Excel
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 16 / 15
Dynamic Desired Cash in Excel, Answers
Determining Desired Cash in the Dynamic Salary Retirement Excel
Model, Answers
1 If annual spend is 40k for 25 years in retirement, $563,757.78 should
be the retirement cash and there should be 18 years to retirement.
Exercise: Slide 15 Resources: Slide 18
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 17 / 15
Dynamic Desired Cash in Excel Resources
Determining Desired Cash in the Dynamic Salary Retirement Excel
Model Resources
1 Dynamic Salary Retirement Model - Excel
2 Slides - The Depth of a Financial Model
Exercise: Slide 15 Answers: Slide 17
DeRobertis (UF) TVM Deep Dive Excel December 30, 2020 18 / 15
