The Depth of a Financial Model¶
Explores building a more complex and realistic model with Excel. Here we focus on extending the simple retirement model to have dynamic salary growth.
Resources¶
Simple Retirement Model Assumptions¶
Notes¶
Part of the reason we begin with such a seemingly simple problem is to show how complex it is to model any real-world situation with a high degree of accuracy
You may have thought we solved the retirement problem already, but there is a lot that we left out intentionally to make the problem simpler
Assumptions are key to any model. They allow you to simplify the problem so it is actually feasible to solve. The stronger your assumptions, the less accurate the model, but the simpler it is to implement.
No real-world model will ever be perfectly accurate. The world is just too complex to capture it all. We always have to settle for some level of assumptions
The assumptions form the base of the model, from which we can generate the logic and equations
There can be many different possible assumptions for a given model. It is a big part of the modeler’s job to pick the best assumptions that can get the model as accurate as needed while balancing the costs of building and maintaining complex models
Transcript¶
- hey everyone
- nick dierbert is here teaching you
- financial modeling and today we're going
- to talk about the depth of a financial
- model
- and extending a simple retirement model
- in excel
- so we started out in this class
- with a very simple model to work with
- just a very basic
- retirement model that most people would
- solve on a calculator
- and you know part of that was so that
- you know we can focus more on the
- modeling part rather than
- having to know any you know complex
- finance
- concepts to get started but the other
- reason
- was to really highlight just how complex
- the world is and that even a simple
- model
- actually uh can become very very complex
- once you really try to bring in all the
- particulars
- of the real world and so with every
- model there's always a trade-off
- you can keep making it more and more
- realistic and that makes it more and
- more complex
- and so you ultimately have to make a
- decision
- about you know what you're using the
- model for how accurate does it need to
- be
- for how much you're going to really try
- to get close to the real world
- ultimately the real world is just too
- complex
- to be able to model every single aspect
- of the real world and so ultimately
- we're always
- making some simplifications to make our
- lives
- easier and keep the complexity of the
- model down
- so we're going to
- just extend this retirement model that
- we've been working with already
- ultimately solving basically the same
- problem
- but trying to get at some more realistic
- assumptions
- going along with that
- so when we break down any model
- it has a few conceptual parts to it
- so we have the equations the logic and
- the assumptions
- going into the model and really the
- assumptions
- are the base of the model once you
- decide
- what your assumptions are then you can
- go and figure out
- what the actual logic and equations
- involved in the model are going to be
- and those assumptions are really where
- we're thinking about
- you know how close do we want to be to
- the real world what do we want to assume
- to make our lives easier uh and make the
- model less complex
- uh so that we can actually get to the
- solution in a reasonable amount of time
- and then once you've decided on you know
- these are the things uh that i'm going
- to assume
- then out of those assumptions you can
- create these logic and equations which
- actually
- drive the model and so
- you know we already built out this very
- simple retirement model where we just
- said
- you know this person has a certain
- amount of desired cash
- we want to know how long it's going to
- take to retire they can
- you know save a certain amount each year
- and they invest it at a certain rate
- uh how long does it take
- so when we're thinking about the more
- general question of just
- how long does it take to retire uh
- simplifying it down to the problem that
- we looked at last time there were
- actually quite a few
- assumptions that went into that so let's
- break that down a little bit uh
- so we see
- a few assumptions here on a slide one
- here is that the salary
- is constant over time and
- uh you know that is definitely not
- a very realistic assumption right as you
- go through your career
- you're going to get promotions you're
- going to switch jobs
- and that is going to increase your wage
- typically people earn
- higher salaries as they get later into
- their career
- and so just having one constant salary
- really does not
- capture that very well and then
- going to number two the savings rate
- we just had a single consistent savings
- rate
- in the original model but really
- the savings rate is also going to change
- over time
- uh you know you think about if you're
- bringing more money in
- then it's likely that you'll be able to
- save a larger percentage of that
- um and other things can affect that you
- know if it's a
- recession uh you might not be able to
- save as much
- etc and then going to number three
- thinking about the
- investment returns so the investment
- returns
- uh we just had a single constant rate uh
- but really we know that
- investments you know they're going to
- return
- different risky investments are going to
- return different amounts
- each year and so just having a single
- constant investment rate is not going to
- be able to capture that
- very well um you know really there
- should be you know some kind of
- randomness
- to that investment rate and
- you know ideally it could be affected by
- you know expansionary
- periods and recessionary periods should
- have lower returns
- during a recession so there's a lot more
- that we could do
- with the investment rate you could
- really even take that
- even further you could build out an
- entire portfolio model
- uh you know say all the different
- investments that this person is
- investing in
- and then calculate the returns on each
- one of those individual investments and
- aggregate those all into a portfolio
- uh to ultimately determine the
- investment return in each year
- uh and then going to number four uh
- the amount needed into retirement is
- given by a fixed amount of desired cash
- we just said you know 1.5 million that's
- how much we're going to need to retire
- uh but they're you know where did that
- 1.5 million come from
- you know really it should be driven by
- you know what are your expenses going to
- be in retirement
- how long are you expecting to live you
- know under you know what different
- scenarios of if you live longer how much
- more
- money are you going to need um and
- thinking about all of these kinds of
- issues
- and then uh the the the last one five
- kind of ties in here with with what i
- just said that
- uh the retirement uh
- cash needed was just a totally fixed
- number in the model there was nothing
- uh internal to the model that was
- driving that such as
- you know what if right when you retire
- uh we just hit a recession
- right at that point and so all your
- investments have gone down
- just as you're going into retirement how
- does that ultimately affect things you
- then you know probably would need to
- keep working until
- the market can rebound or you would
- evaluate you know if you are drawing on
- the portfolio
- uh while it's down then uh you know how
- much is that
- going to hurt your position overall so
- there's a lot
- a lot that we could consider and you
- know what
- seemed on the surface like a very simple
- problem uh
- you know just oh five keys on the top of
- the financial calculator and we're done
- right
- or a single function in excel or a
- single function in python we're done
- but no there's there's so many things
- that we assumed
- in going to that simple problem when in
- reality
- the real world is much much more complex
- than that there are so many different
- factors
- going on and bringing all the things
- that i just mentioned
- into one single model i mean that that
- would be a very massive
- model and so there's always a trade-off
- here
- uh you know if you just need to get a
- rough idea
- you know am i going to retire somewhere
- between 30 and 35 years
- like in that case your model really does
- not need to be that accurate
- and you can have all these assumptions
- because you don't need a very accurate
- result
- um but you know in another situation
- where
- uh you know maybe you're a large company
- building out a model that's
- you know making decisions for the
- company that's moving billions of
- dollars around and
- being off by one percent is going to be
- you know millions of dollars of
- profit difference in that case you know
- you care a lot about accuracy and then
- you you do want to build in
- as many uh uh
- approximations of the real world as you
- can rather than simplifying them
- with assumptions so we're going to come
- back next time to talk
- more specifically about the assumption
- that we're going to relax
- in the salary model to make it more
- realistic
- and after that we'll go into actually
- building
- the model out in excel so thanks for
- listening
- and see you next time
Relaxing the Salary Assumption¶
Notes¶
We will experience first-hand the tradeoff in relaxing assumptions: greater accuracy but also greater complexity
We are going to now a fairly realistic assumption for salary. It is still not however the most realistic possible. For example, promotions should be less frequent and smaller raises during recessions and more frequent/larger raises during expansionary periods. Promotions should not come at a fixed interval, they should be faster in the early stages of the career and there should be randomness to it. The model should be run many times to understand the distribution of years to retirement based on the randomness in the salary.
Obviously that would lead to quite a complex model already, and that’s only thinking about the salary assumption.
In deciding how much to relax assumptions, the desired degree of accuracy of the model is critically important. For what decisions is the model being used? Will those decisions be any different if the model increases in accuracy? What is the cost of being wrong?
Transcript¶
- hey everyone
- nick dear bertis here teaching you
- financial modeling today we're going to
- talk about relaxing the salary
- assumption
- in our simple retirement model as part
- of the depth of a financial model series
- focusing on building out a more involved
- excel
- model so
- we've been working on this basic
- retirement problem
- and uh we talked about how
- uh basically we made a lot of
- assumptions in this original
- model um which made it a very simple
- calculation
- but also not very realistic
- and so we um can try to
- relax an assumption to get closer to the
- real world
- um so most of the time
- you're not able to completely get rid of
- an assumption uh you're just going to
- replace it
- with an assumption which is closer to
- how the real world works
- so the one that we're going to look at
- relaxing here
- is the salary assumption
- whereas before we said that
- the salary is just constant it's you
- know 50 000
- or whatever and that's going to be the
- salary for
- the entire life of this individual but
- of course
- that's not very realistic um and so
- we're going to replace it with something
- which gets fairly close
- to reality and that's that
- you know we want to work in the idea
- that people's salary grows over time
- throughout their career right
- as they uh you know get promotions
- or you know change companies uh
- you know they're going to get a raise
- associated with that
- most of the time and you know such that
- at the end of their career they're
- making substantially more than the
- beginning of their career
- and not only that but also
- typically most companies will give a
- cost of living
- raise every year uh regardless
- of whether you're getting a promotion or
- anything else
- you know they're just going to give you
- you know one two percent something like
- that
- uh raised just to account for inflation
- over time so
- we're going to work both of these things
- into our assumption
- that you know every certain number of
- years
- this individual gets a promotion and
- they get a raise
- associated with that a certain
- percentage raise
- and then every year they're also getting
- a certain percentage
- cost of living raise now
- this is definitely a lot closer to how
- the real world works
- but of course we're not getting all the
- way there there's a lot more that we
- could potentially do
- with this salary assumption but i think
- what we have here is already getting you
- know fairly close such that the results
- are probably
- not going to be all that different going
- to more realistic
- situations of course to make this more
- realistic
- uh you know one key thing to think about
- would be
- the business cycle you know where is the
- economy
- in any given year are we in a recession
- are we in an expansion period
- is it kind of a normal uh just baseline
- kind of year
- um and you would think that uh you would
- be less likely to get a promotion
- in a recession more likely in an
- expansion
- and also you know given that you do get
- a promotion you would expect it to be
- a larger raise in an expansionary period
- versus a recession and then also with
- our current assumption we're assuming
- you know every five years or whatever a
- fixed interval
- in between uh the promotions but in
- reality
- that would be uh more random than that
- there would be
- you know sometimes you get a promotion
- quickly sometimes it's going to take
- longer but also there's going to be a
- trend for most people that
- the promotions are going to come slower
- the later you get into your career
- you know as you get higher up on the
- ladder there are less steps to go and so
- uh you get promotions less often so
- um you know that would be getting even
- more realistic building in the the
- economic
- conditions as well as you know trying to
- get this trend of less promotions over
- time
- and you know giving some randomness to
- when the promotions are actually going
- to occur
- you could also add some randomness to
- the the level of the raise that you're
- going to get
- and a promotion so there's a lot that we
- could do
- to try and go even further and get even
- more realistic
- but you always have to kind of cut it
- off at some point
- we talked in the last video we would be
- you know
- spending weeks and weeks on this model
- if we tried to
- uh you know go after you know everything
- to make it as realistic as possible
- everywhere
- and that's really just not going to be
- feasible for most models there just
- isn't enough time
- to approximate the real world that
- closely
- and so we just have to find a nice
- balance of
- we're getting pretty close to the real
- world uh
- and we're not going to need to be any
- closer for the purposes of the decision
- that we're making based off the model so
- with that we'll stick with this constant
- cost of living raise growth every year
- every
- x number of years we're going to get a
- promotion and it's going to be
- a fixed percentage raise with each one
- of those promotions
- so you know i mentioned how
- [Music]
- when modeling you know you kind of start
- with the assumptions and then you get
- the logic and the equations
- out of that assumption right so we just
- came up with a new assumption here about
- how the salary should work
- and so that brings us then to the
- equation
- which represents that salary over time
- so breaking down this equation here
- uh we can think of it kind of in three
- parts
- so uh the first part here is
- um the initial salary whatever
- salary you started at uh at the
- beginning of your career
- and this is ultimately to get you know
- the salary in any given year
- so whatever salary you started from and
- then the next part
- is the um
- the cost of living raised
- so it's you know one plus whatever the
- return for cost of living is you know
- one or two percent or whatever
- uh the company gives for cost of living
- raise uh to the power of however many
- years
- uh you've gotten the raise so you know
- then just thinking about this part of
- the equation
- and you're going to start out at your
- initial salary
- and then this is going to be to the
- power 0. so you'll just have your
- initial salary and your 0
- but then coming to one year um then
- you're gonna have
- your initial salary plus you know one or
- times 1.02
- if it's a two percent um
- cost of living raise and then that's
- just going to compound year over
- year uh going forward uh so
- kind of common uh just compound interest
- kind of logic that went into forming
- this equation
- and then the last part here is the
- promotion raises
- so again we can think of uh you know the
- promotions as being an additional return
- on the salary uh cumulative
- return here so you know just
- like the cost of living raised part it's
- going to be
- uh you know you're getting one plus the
- promotion
- return you know the the percentage raise
- that you get
- as part of a promotion and you're going
- to get that
- for as many promotions as you've had
- up until that point so
- we can think of it in these three parts
- the initial salary
- and then we can call this part the cost
- of living factor
- and then we can call this part the
- promotion factor so we can kind of
- decompose this
- into three parts that we can think about
- separately
- so that's an overview of uh
- you know the the new logic and equations
- that are going to go
- into the new model um and we're going to
- come back next time to cover
- some skills that we need to actually
- implement
- the new model and excel before we go and
- implement
- it and the video after that so thanks
- for listening
- and see you next time
Skills for the Advanced Excel Model¶
Notes¶
Larger Excel models can get very messy quickly. Then it becomes difficult for anyone to consume your model or to improve it later. Therefore it is important to stay organized.
As before, we want to keep the inputs separate from the outputs. But in a larger model, we need to take this a step further. We can break the model into various parts or stages, each which have their own inputs and outputs. Each part should have its own dedicated worksheet and there should be an overview page which has the main inputs and outputs.
Conditionals are necessary to control the flow and output of a model under different conditions. We use =IF, =AND, =OR in Excel to accomplish this
Use modulo (=MOD) to get the division remainder. You might not need this every day in modeling but it is very useful for this particular salary situation we are modeling
=VLOOKUP is very useful to find items in a table and can make your model much more flexible
Transcript¶
- hey everyone
- nick dear burtis here teaching you
- financial modeling
- and today we're going to talk about
- skills to build out a more
- advanced excel model and this is part of
- our segment on the depth of a financial
- model and extending a simple retirement
- model in excel
- so in the prior videos we talked about
- this simple retirement model and how
- we're going to add this
- assumption to it that the salary should
- grow with promotions as well as cost of
- living raises
- so now let's look at some skills that we
- need
- to build out the excel model for this
- so one thing that we need to pay a lot
- of attention to now
- uh that this is gonna be a bit larger
- excel model
- is we have to pay a lot of attention to
- the structure
- and organization of the model with excel
- you can very quickly uh get into a spot
- where
- it's very difficult to understand how
- everything flows through the model
- where your inputs where your outputs
- where does that
- where does one calculation go to the
- next
- since it's all very free form and you
- can kind of put anything anywhere
- as far as the layout then it's not
- very clear as someone just coming into
- the model
- you know how everything flows through
- unless
- you take the time to structure it very
- well
- so that it's easy for any uh consumer of
- your model or someone who's going to
- come and work on your model later
- which may even be you you know later
- down the road after you haven't worked
- on it for a while
- um this structure is very helpful for
- that
- um so the structure that i'm talking
- about
- um you know is a little bit what we've
- talked about before
- and that you want to have clear
- separation
- of your inputs and outputs that's the
- biggest thing
- is you know you have to know what is
- the coming into the model what can the
- the user of the model change around as
- the inputs
- and what are the main outputs from the
- model and having those presented in a
- clear way
- and that's true for any excel model you
- should have that
- but as we get to a larger model we also
- need to think about
- you know basically we're not just doing
- one single calculation
- anymore in a larger model there's going
- to be
- really multiple different sub problems
- that you're solving as part of the
- larger
- problem and so we want to take
- break it down into these sub problems
- and we can put each of these sub
- problems on its own
- sheet of the excel model
- and then each sheet we can kind of treat
- as its own mini
- sub model and within that sheet you
- should have the inputs
- going into that calculation you should
- have the outputs
- from that calculation clearly separated
- and then you should have an overview
- page for the model which has
- all the inputs and the main outputs from
- the model and everything should be
- linked together
- all through the different sheets and
- this is the way that you can stay very
- organized
- even with a larger excel model
- um so that's the structure
- now let's look at a few different
- functions that we're going to use to
- actually build out
- this dynamic salary retirement model
- so conditionals
- are very important in financial modeling
- as well
- as general programming you know
- if something is true then you want to do
- something or get some value
- and so we have the if function in excel
- which accomplishes that we can also use
- that
- in combination with other logical
- operators like
- and and or you know and so we can look
- at multiple different conditions
- at once in a single if statement
- um and
- let's look at some examples of how this
- works in excel
- so looking at how we would actually call
- the if function
- you know first we have our logical test
- so that's the condition that we want to
- determine
- whether it's true or false
- and so in my first example here i have
- if five equals five five does equal five
- that's true um and so
- uh we're going to go into then the value
- if true and so that's why uh here
- second argument we have the value of
- true and third we have the value if
- false
- and so what this function is doing is
- just
- if this condition is
- true then it's going to give you this
- value
- if it is false then it's going to give
- you the other value
- and so that's why this call here uh
- if 5 equals 5 it does it's going to give
- you this
- but then if we called it again if 4
- equals 5
- of course it does not that's false then
- it's going to give us
- that it's going to give us the other
- value here the value of false
- and so this if command is basically just
- allows us to choose from two different
- values based on some kind of condition
- and we'll definitely look at the
- applications of that as we go to build
- out the model
- then modulo is
- another function or concept which is
- used a lot in general programming
- it's you know probably not going to come
- up and you know just everyday
- financial modeling but it is i think
- very useful to build
- out this specific problem that we're
- looking at
- uh where we have the promotions coming
- every
- uh number of years uh the modulo is
- helpful to
- uh work with that so the modulo uh
- is basically just the remainder of doing
- division
- um and so you know if you think about
- three divided by four what's the
- remainder uh
- the remainder is three and so that's
- what uh this mod function is doing is
- just giving you the remainder
- of a division uh and then if you think
- you know seven over two what's the
- remainder of that
- uh that becomes uh three
- and one half and so one is the remainder
- from that um so it's just giving us the
- remainder
- and this is going to be useful for
- seeing you know how many years
- we have left until the promotion
- and then uh the other function we're
- looking at
- to help us build out this model beyond
- you know the basic ones that i would
- expect you to know just coming into the
- class
- is vlookup so vlookup
- is a little bit more advanced and it
- helps you find
- things in a table uh by the row
- so we can look at an example here uh we
- have a little table
- of uh you know four different
- um foods here and then in the second
- column
- we have the food group that that food
- belongs to it's either a vegetable
- or a fruit so uh
- with vlookup we can say you know we know
- we want to
- figure out whether celery is a fruit or
- a vegetable
- and so we can pass celery to
- vlookup and we give it the
- range of the table and then we say
- we want to look up the value from the
- second column
- and then that's going to give us
- vegetable
- um so um
- that's the basic idea you're just
- looking up a certain column's value
- by uh the first columns
- rows value um is
- how vlookup works
- and the lookup it does have to be the
- first
- column of the table or at least that you
- know the range
- that you're targeting here and you do
- have to sort
- the items in ascending order uh
- in order to ensure that you're going to
- get the correct lookup so those are the
- two
- kind of gotchas with using vlookup
- but basically you know you're just
- looking up something in a table
- uh by the first columns
- value
- so those are all the additional skills
- um and and patterns that we're thinking
- about as we go into building
- this more advanced dynamic salary excel
- model
- next uh video we're going to come back
- and actually
- build out that model so thanks for
- listening
- and see you next time
Implementing the Dynamic Salary Model¶
Notes¶
We are building this model from scratch as it is so different from the original
We can break this larger problem down into three sub-problems: determining salary over time, determining wealth over time, and determining when the individual can retire. Each of these become worksheets in the model
In the overall inputs, it is good to have sections if inputs are relevant only to individual sub-problems. Also reference over the inputs onto the individual sheet for both visual organization of having everything for the calculation on one sheet as well as keeping cell references shorter
=IF and =MOD combined can figure out whether it is a promotion year
A cumulative sum can be used to determine how many promotions have occurred up until a given point in time
Calculating factors helps split out the calculation and make it more clear
Resources¶
Transcript¶
- hey everyone
- nick dear british here teaching you
- financial modeling and today we're going
- to be talking about
- implementing a dynamic salary retirement
- model
- in excel as part of our lecture series
- on
- the depth of a financial model focusing
- on excel
- so we have been talking about this
- retirement model and we already
- implemented the simple
- retirement model with a static salary
- and now we're going to go to
- implement this one where the salary
- increases
- every certain number of years with a
- promotion and also increases annually
- with a cost of living
- raise so as we go to
- build our model um you know now this
- model is going to be larger than the one
- we looked at before
- and really we can break it down into
- multiple sub-models or sub-problems
- that we can tackle individually so
- the first sub-problem here is
- determining
- the salary in any given year as we go
- through time
- throughout this person's career
- and then the next is determining the
- wealth of this
- individual as they take that salary and
- save a certain portion of it
- and invest that and earn an investment
- return on that
- and then finally the retirement side of
- the model which
- looks at the wealth uh to determine when
- this person
- can actually retire um
- so we've got these three parts of the
- model and they're gonna become three
- separate worksheets here in the model um
- but when you have a model which has
- these multiple parts uh it's also good
- to have an overview page
- which gives you uh you know the main
- inputs and outputs
- from the model overall so let's go ahead
- and build out that inputs and outputs
- page
- so you could call it overview or you
- could call it inputs and outputs
- um or you know something along those
- lines which just says you know this is
- the main page this is the starting point
- for the model
- if you want to change things and have it
- flow through the model you change the
- inputs here
- and you'll see the outputs here as well
- so first i'm going to create the inputs
- section
- and for the purposes of going through
- this i'm not going to be really adding
- formatting for the most part to this
- when you look at the completed example
- on
- the site you will see that it has all
- the formatting in place so
- definitely you should be adding
- formatting to your models before you
- um you know present them to anybody
- but just in the interest of time we're
- going to focus here
- on you know actually building out the
- logic and the layout of the model
- uh and i'll leave it as an exercise for
- you to to add the formatting
- so uh in the inputs here uh you know
- it's good to be as
- clear as possible about you know what
- each input is about
- um and here you know we've got the three
- different parts of the model
- and so we can kind of separate the
- inputs into those three parts as well
- so we have the salary inputs
- for whatever inputs are going to go into
- ultimately determining that salary year
- over year
- so we have the starting salary
- um and we said yeah we can say that
- starts at sixty thousand
- um and then
- you know we we've been saying that um
- there's going to be you know every
- certain number of years there's to be a
- promotion
- for this individual they're going to get
- a certain raise
- so let's just start it out saying every
- five years
- they're going to get a promotion and you
- know that'll be an input to the model so
- we can adjust
- how quickly those promotions are going
- to come
- which would definitely make sense you
- know for people on different career
- paths
- uh plugging in numbers which are
- appropriate
- to their particular career how often
- they're going to get promoted and
- how big of a raise they're going to get
- upon promotions
- so then we have
- the cost of living raise is how much
- they're just getting annually year over
- year every year
- as a raise to compensate for inflation
- uh and then the uh promotion raise
- is going to be uh you know whenever one
- of those
- promotions occurs every five years or
- whatever the values of the inputs
- then they're gonna get that much of a
- raise in that year
- on top of the cost of living raise
- so those are the salary inputs and then
- we're going to have some inputs going
- into the wealth portion of the model
- so that's going to be the savings rate
- [Music]
- which will tell us you know out of that
- salary they're earning how much they
- actually
- invest and not just use on daily
- expenses
- and also the interest rate how much
- they're going to earn
- on the investment um
- and then we have the desired retirement
- inputs so that's just
- the desired cash of you know currently
- having that
- at 1.5 million dollars
- um so certainly we want to add all the
- number formatting
- and uh you know bold the the header
- areas and you know put this in a box and
- everything to kind of separate it but
- we'll leave it
- there for now you can take a look at the
- completed example for the formatting
- and then for our you know main outputs
- we're just going to have one output here
- it's going to be
- the years until retirement so we'll just
- you know put that placeholder there and
- then we're going to bring our main
- result to here
- at the end so that's the overview sheet
- um and this should really be the the one
- spot that your hard coding
- values is for your inputs here the rest
- uh everything is going to be
- cell references um
- so next we can go to build out the
- salary
- portion of the model
- so i'm going to create a new worksheet
- here in the model
- salary worksheet and in here
- we're going to figure out what is the
- salary in each year
- going through time um
- and i mentioned before how um you know
- as we
- you know have our more complex larger
- excel model and we're breaking it down
- into basically
- sub models of one per worksheet we also
- want to maintain that same
- input and output separation in
- the individual sub models or worksheets
- as well
- so i'm also going to create an input
- section on
- this worksheet which will just be
- bringing over
- those inputs from the
- main tab over here and because you know
- we already separated everything out
- really nicely over here it's easy just
- to
- reference over to the other tab um and
- then
- we can just drag it uh to be able to get
- all those values over here
- so now we have those same inputs coming
- to
- this tab as well
- and then we can go on to you know create
- a separate area which has
- the calculations for our salary um
- and there's you know two main reasons to
- bring those same inputs over here even
- though they were already
- defined over here uh so one
- is that then each uh you know sub
- each worksheet here each sub model is
- you know self isolated everything you
- need
- for that individual part of the problem
- is right here
- on this worksheet um and then the other
- part
- um is you know as you do calculations
- with these things you reference them
- here and you can see we just have you
- know b2
- as the reference there where as you look
- at these references
- it's inputs and outputs b3 inputs and
- outputs b4
- etc and so if you have these in all of
- your different calculations going
- through it actually becomes
- very difficult to read your calculations
- as you know the references become so
- long becomes difficult to tell what's
- going on
- so you can keep the formulas nice and
- short if you're referencing things
- only on the same tab in the calculations
- and these are just
- you know directly linking to those
- inputs
- from the main sheet
- um so we want to ultimately determine
- uh you know year over year what is the
- salary going to be
- um so we can start with uh year one
- and uh you know in excel if you type out
- a couple numbers in a row
- from there you can drag it it's going to
- be able to figure out the pattern
- um so we do that and
- then we're able to let's go a little bit
- uh further
- on that let's say go out to
- 40 years um
- so then we have our full you know one
- through 40
- different years to build out the
- different salaries
- and i think it's you know always a good
- idea in excel models
- to try and split out the calculations as
- much as possible you don't want to have
- really long cell formulas that's just
- going to become
- very difficult to understand what you're
- doing
- if you ever do have a long formula you
- want to make sure that you add a comment
- that explains
- what that formula is doing but it's even
- better if you can just
- make the comp each calculation very
- clear and straightforward
- and uh you know larger calculations are
- split across multiple cells
- so that it's much more clear without
- even having to add that
- comment so
- i'm gonna tackle uh figuring out the
- salary in a few steps here
- first is i'm gonna figure out is this a
- year
- where we're getting a promotion uh and
- then i'm gonna figure out
- how many promotions have we had up until
- this year
- and then uh figure out um
- you know the cost of living raises
- factor in the promotion raises to bring
- it all together to the
- salary so first
- let's think about is it a promotion
- year um and so for this
- you know i mentioned in the prior video
- that
- this is where the modulo uh becomes
- really useful uh because if we
- you know do equals mod of
- the year and then the promotions
- every number of years and make that a
- fixed reference
- um then as you drag this down and you
- look at it
- you'll see we go one two three four zero
- one two three four zero
- every year that uh
- it is actually a promotion here because
- it's every five years so year five year
- ten year fifteen
- every one of those is coming up as a
- zero
- so we know that it's a promotion year if
- the value we're getting here
- is zero and uh you know we can look
- at you know changing to a different
- number of promotion years let's say
- three
- now we can see that it's every three
- years we're getting a zero so this zero
- always tells us
- that we are in the promotion year
- um so i'll change that back and then
- we can see that we just need to build
- one additional step on this to be able
- to tell
- is this a promotion year
- so that additional step is we can add an
- if
- function around that um
- so remember if we think about a logical
- condition
- and then if that logical condition is
- true we're going to get one value
- if it's false we're going to get the
- other value so the condition that we can
- make here
- is is this calculation that we did we've
- just been looking at
- is that value equal to zero
- uh because we know if it's equal to zero
- then that is the promotion here right
- and so if
- it's equal to zero aka if this is a
- promotion year
- then we want to put a one because one
- can represent
- true easily and we can use
- zero to represent false
- so then with that
- do that and we can double click to
- complete that
- all the way down and now you'll see it's
- giving us a one
- in those promotion years and it's giving
- us a zero otherwise
- and if we change this um to a different
- number every two years now we have every
- two years we're getting that it's
- a promotion year um
- so now we know which years we're getting
- the promotions
- um but if we go back to you know the
- formula
- that we're trying to implement here um
- we don't need to know uh as far as this
- formula is concerned
- uh you know whether it's promotion year
- what we need to know is
- how many promotion years have occurred
- up until this point in time so that's
- what this p
- in this formula is representing so then
- the next step
- is then to figure out the number
- of promotions that have occurred
- up until that point
- and so um
- we can do a very common pattern in excel
- here to determine this
- and that's what's called a cumulative
- sum so that's just
- you know summing up from wherever you're
- starting
- up until the end of the range as you
- drag it down
- and so the way you do that is with the
- regular sum
- function but here we're going to sum
- from the first number to the first
- number um and that sounds like wait why
- would we sum
- from something to itself what was the
- point of doing that
- uh it's because we're going to make the
- beginning of that sum range a fixed
- reference while leaving the other one
- as a relative reference and what that
- allows that to do
- is when we complete this down we can see
- that
- as soon as we get a promotion it goes up
- each time we hit a promotion it goes up
- and then it keeps adding to that
- and that's because if we look at what
- happened in one of these individual
- calculations
- that the bottom of the sum range keeps
- moving because it's a relative reference
- while the top stays fixed and so it's
- going to be adding
- whatever has occurred up into the point
- the uh
- the sum range just keeps increasing as
- we go year over year so it's a
- cumulative
- sum so then with that
- we know in any given year how many
- promotions have occurred
- up until that year
- so um now that we have figured out the
- promotions
- piece uh now we can turn our attention
- to
- um you know thinking back to this
- formula we split it down into three
- parts the initial salary
- um times the
- what we can call here the cost of living
- factor
- is this portion times what we can call
- the promotion
- factor and so now we can go and
- calculate
- each of these two pieces as individual
- factors
- and that's part of what i'm talking
- about in splitting up your calculation
- in excel
- it's certainly possible to just go and
- do this all in a single cell
- but it's going to be difficult to
- understand what you're doing
- so splitting the calculation up it
- becomes a lot more clear
- and you can you know analyze individual
- parts of the calculation a lot easier
- and
- ensure that it's working properly
- um so we're going to calculate the cost
- of living factor and that is you know
- simply what you saw
- in the equation it's just one plus the
- cost of living race
- i'm going to make that a fixed reference
- because we want it to always be that
- particular cell
- and then i'm going to take that to the
- power of the current
- year and that one we do want to be
- relative
- so it can move down over time
- um so then we see um you know
- from these costs of living raises
- basically by 40 years
- you're gonna be making more than double
- your initial salary
- just on the basis of having two percent
- inflation
- each year
- then we can go and calculate
- the promotion factor
- so for the promotion factor it's going
- to be a similar format
- uh one plus the um
- the promotion raised this time instead
- of the cost of living raise
- uh but now we're going to take it to the
- power of the number of promotions
- that have occurred up until that point
- uh and that one we do want to move down
- with us so then we can see you know in
- the first
- four years where there hasn't been a
- promotion yet it's not going to
- ultimately change the salary it
- multiplies it by one
- but as soon as we get a you know our
- first promotion a fifteen percent raise
- now it's multiplying it by one point one
- five and so that's a fifteen percent
- raise and we see every five years of
- that factor goes up
- representing that we got a raise
- and so we can see you know if every five
- years you get a 15 percent
- uh promotion uh then you're going to be
- making three times
- as much as when you started after 40
- years
- just based on those promotions alone
- and so then we can
- put those two things together remember
- that in the formula they were just
- multiplied together
- and so i added that here just multiply
- the two together
- both relative to get the total factor
- so then we can see you know with the
- inflation
- as well as the promotions by 40 years
- we would be making close to seven times
- what the initial salary was
- so then the last step here is just to
- actually calculate that salary
- and uh for that then
- um it's just putting everything together
- um so you know we already put
- um the
- uh each of these two factors and we just
- multiplied them together
- into a single number um as the total
- factor
- and so now we just have to multiply that
- by the initial
- salary um so we're just going to take
- the initial salary and make that a fixed
- reference
- it's the same for each one of these and
- multiply it
- by the total factor as a relative
- reference which is going to come down
- with us so we do that and so we see
- you know starting at a 60 000 salary 40
- years later
- you would then be making 400 000 based
- off of these numbers
- so that's the salary portion of the
- model
- and then we can move on to building out
- the wealth
- portion of the model next so
- in the wealth portion of the model
- we're now going to be using this salary
- that we calculated
- year over year to ultimately
- figure out the wealth in any given year
- so you know again with all these
- sub models we're going to have our own
- uh
- individual input section and as part of
- this
- we're actually going to be bringing over
- this entire column
- of the salary year over year um and so
- you know that's not going to fit well
- into this inputs box
- you know it'd be nice just to have it as
- part of our our table of calculations
- and so what i like to do in those cases
- is i just put a note
- that salaries are computed on the salary
- tab
- then reference here
- and you know that's just saying you know
- we're going to have the salaries going
- through here
- it just makes it very clear to the
- consumer of the model
- that those were calculated over here and
- it is a lot clearer how everything fits
- together
- so then the other things that we'll want
- to bring over
- for this are the starting salary
- we can get that from here as well
- as then we can
- reference the wealth specific inputs the
- savings rate
- and the interest rate
- so then we
- have our input section and we can go on
- to do
- the wealth calculations um
- so we're gonna again have you know
- calculations year over year so you can
- either redo the same thing with the time
- or
- i'm just gonna go ahead and reference
- this column here to get each of those
- years
- and then we also need to bring over the
- salary from the other tab
- so now i'm going to reference that over
- to the salary
- over here so now we have the salary year
- every year on this tab as well and it's
- very clear that that was
- calculated on the other worksheet and
- brought over to here
- so then there's kind of two steps here
- and calculating
- the wealth so the first is seeing
- you know how much did this person save
- in this particular year and then the
- other is applying the
- investment return to the existing
- portfolio as well as
- the new savings or to the existing
- portfolio and then adding the new
- savings to come up with the wealth
- in that given year um
- so um next we can calculate the amount
- saved
- and so the amount saved is simply going
- to be
- the salary earned in that year times
- the savings rate and we want it to be
- fixed to the savings rate
- as we always want to use the same
- savings rate
- so we complete that and then we have the
- amount saved year every year and then we
- can go to calculate the
- well so the wealth
- in the first year here would be
- just we don't have anything in the
- portfolio yet so it's
- just uh the amount that they saved and
- then it's going to be invested
- over the following year so then
- going forward after that it's always
- going to be taking
- the prior wealth and it's going to be
- multiplying that
- by one plus the interest rate
- and we want that to be fixed always use
- the same interest rate
- and then we're going to also add in the
- amount that was saved in that year
- because that
- uh was just earned it hasn't been
- invested yet so that part doesn't earn
- the investment return
- until it's included in the following
- year um
- so then with that um that is the formula
- we need for the rest
- of this um and that will get us to
- ultimately uh this person earning 3.9
- million dollars
- in their portfolio for retirement by 40
- years from now with these basic
- inputs so then
- the last portion of this is then the
- retirement portion
- of the model so the retirement portion
- of the model um is about
- uh figuring out when this person can
- retire based off of the wealth they've
- accumulated and how much cash they need
- in retirement um so we can again you
- know make our input section as always
- and here we're going to need the wealth
- year over year
- to determine this so i'm going to again
- um
- you know put a note like i did on the
- other tab that
- you know we can't fit all the will the
- wealth up in this corner
- um and so we're just going to say that
- wells are
- computed
- on the wealth tab then referenced here
- um and then the only other input that
- we're going to
- need is the desired cache so we'll bring
- that
- over as well
- and then uh we can
- then make the area for the calculations
- retirement calculations
- um and
- um so
- here um we can
- you know go ahead and and bring over the
- time
- um you know like we did for the weld tab
- reference it um from the other tab or
- you could
- you know just do the same completion
- thing that
- we did to create the
- time column in the first place here
- going up to
- 40 years
- so then
- we need to
- [Music]
- basically we need to bring over the
- wealth next
- um so there again we're just going to
- reference over to
- the other tab and bring that over
- and then we want to
- figure out in any given year is this
- person
- able to retire and the condition to be
- able to retire
- is that we have saved enough money right
- that we
- have a wealth greater than the desired
- cash
- so again we can represent that
- with an if statement here so if
- the wealth in that year is greater than
- the desired cash the fixed desired cash
- that we use every year
- uh then one is going to represent
- yes we can retire and xero
- cannot retire um so then we complete
- that
- and we should see right after we cross
- over that 1.5 million threshold indeed
- it says we can then retire so this is
- saying after 28 years
- then this person can retire
- so then
- there's just you know basically we have
- our main result here
- but we want to be able to get you know a
- single number
- coming over onto the inputs and
- outputs tab
- and so then this last part
- you may have noticed uh that
- i left this first column intentionally
- blank here
- and that's because i was planning on
- doing a vlookup
- on this table to determine uh
- the the row in which uh
- they first are able to retire
- um and so for that
- um i can uh
- you know the issue with you know using
- vlookup with
- you know this kind of column is it goes
- from zero and then it goes to one
- and so if you uh you know want to look
- up
- the one you know it should be this one
- but um
- you know it could be more direct to
- uh figure out the number of years that
- they've been retired
- uh which would just be a cumulative sum
- so again that same pattern of you know
- from the
- first cell to itself but the first part
- of that
- is going to be fixed and then we can
- complete that down
- and then we can see that starts to count
- up after this person is able to retire
- and so that if we try to look
- up the row that has a one we can be
- certain that it's going to be this row
- 28 years which is the first year that
- they're able to
- retire so then the last part
- is then just coming over to the
- uh you know main inputs and outputs tab
- and we're going to actually write
- the vlookup function
- and so here um we want to look up the
- value
- 1 right we were talking about the number
- of years retired
- as soon as that hits one they have
- retired in that year
- um and then we want to
- look at the retirement table
- so i'm gonna go and
- uh you know reference this table here
- and we really only need the first two
- uh columns because we're trying to look
- up the
- uh year value here
- we don't care about the other values for
- this lookup
- and then we want to get the second
- columns value right the column which has
- the year in it so we do that
- and then we can see that we indeed get
- 28 as
- our result um so that way you know we
- have everything on one tab
- that allows us to easily say you know
- what if
- i could uh you know get a better job
- starting out and start out at seventy
- thousand dollars
- oh well that changes the years to
- retirement now to 26 years
- so um and then you can look at the other
- tabs you see
- everything you know the salary came
- through it changed all the salaries over
- here and that came through to changing
- the wealth over here
- and we can see the retirement all the
- calculations have changed here
- and so everything flows through the
- entire model to ultimately get us to our
- main answer here
- um and structuring our model in this way
- makes it really easy for someone who is
- not familiar with all the inner workings
- of this model to just come in
- and play around with it and think about
- you know different values for all of
- these inputs
- uh you know whatever makes sense uh for
- their situation
- and they can just see the result uh very
- easily without having to
- get into the details of how this model
- is implemented
- but if they do need to dig into that you
- know say
- uh you know this is someone coming and
- they want to
- you know build out a better wealth
- assumption they want to you know build
- in
- uh you know recessionary and
- expansionary periods and have the
- uh you know investment returns respond
- to that then you know they can focus
- just on this wealth tab and they don't
- have to worry about
- how the salaries are being determined
- and how the retirement is being
- determined
- they can just work on the wealth portion
- and that makes it a lot easier because
- you can just focus on this one
- sub model rather than having to think
- about how everything fits together
- by having very clear inputs and outputs
- from each
- sub model it is a lot easier to maintain
- the model
- going forward so
- that wraps up the content for
- our section on the depth of a financial
- model
- and implementing that in excel and we're
- just going to come back to discuss
- the lab exercise to conclude this
- section of the material
- so thanks for listening and see you next
- time
Lab Exercise¶
Notes¶
Feel free to work from the example model though I would recommend you build that out yourself following the prior videos
The new calculation being added is simple, with the main focus being adding new functionality to an existing model and keeping it organized
Hint: if you cut the desired cash cell (Ctrl/Cmd + x), you can move it from the inputs into a calculation area and retain all the references to it
Transcript¶
- hey
- this is nick duraburtis here teaching
- you financial modeling
- and today we're going to talk about the
- lab exercises for
- the segment of the course on the depth
- of a financial model
- focusing on extending a simple
- retirement model in excel
- uh so there's just one lab exercise here
- uh to go along with this lecture um
- and so that is then extending even a
- little bit further
- beyond what we built out in the prior
- video
- of this dynamic salary retirement
- model so i would highly recommend that
- you follow along
- with the previous videos and you build
- that
- all out yourself following the video
- and then you can continue to add on that
- with this
- lab exercise you can also
- you know download the existing one and
- work off of that for this
- if you know the initial exercise was was
- that basic
- for you that you don't feel that you
- need to go through that
- but either way that you want to go on
- this
- and so you know the main focus here is
- that the new calculation that we're
- gonna do here
- is you know pretty straightforward um
- here
- you know it's just instead of uh saying
- that there's
- you know we need 1.5 million dollars
- where did that not
- number come from we don't know we just
- need that much instead it's
- uh now we want to spend a certain amount
- of cash
- each year in retirement that's going to
- become an input
- and we're going to live for a certain
- number of years
- in retirement um
- so then considering those numbers uh how
- long uh or how much money then do you
- need
- to retire and working that into the
- model
- such that everything flows through now
- desired cash is no longer
- an input to the model but it's actually
- an intermediate output
- of the model um so this exercise
- is mostly about just being able to take
- an existing model
- and extend it uh and have everything
- flow through
- without you know breaking anything and
- you know keeping everything
- organized so one nice hint
- here that will help you out with this is
- that you know right now you have desired
- cache as an input you want it to become
- an
- output but you want everything to stay
- linked together
- you know you don't want to have to redo
- how
- uh you know we're ultimately determining
- the year
- uh that they retire you know that part
- should still work the same even if now
- desired cash becomes a calculated value
- rather than an inputted value
- so the way that you can move that over
- is if you actually cut the cell which
- has that
- so that's you know control x on windows
- command x
- on mac and then you paste it somewhere
- else
- that will move the cell and move
- also all the references to that cell so
- you can move the cell
- and have the model work exactly like it
- did before because all the references
- move at the same time so that allows you
- to then
- move that cell from the inputs to the
- outputs
- for your organizational purposes very
- easily
- and then from there you can then
- actually
- change it so that instead of just
- inputting that value
- it's the result of doing a calculation
- [Music]
- so then you're able to easily you know
- not really have to change anything in
- the existing model it will all just keep
- working
- you just then add on this part to do
- this additional calculation
- to determine the desired cache
- so that's the idea behind the
- lab exercise um so please uh work on
- that and and complete that
- uh this week and then we will also be uh
- having a
- lab session where you can uh work on
- that
- and ask questions as well
- so that concludes our segment on the
- depth of a financial model
- and focusing on excel
- and the next segment we're going to be
- looking at
- some basics in python so that we can
- work up to implementing the same model
- in python as well so thanks for
- listening
- and see you next time