Getting Started with Python and Excel¶
Discusses the basics of financial modeling in both Python and Excel. Explores a simple time-value of money problem and how to build a model for it in both Python and Excel.
Resources¶
Introduction and an Example Model¶
Notes¶
In the beginning of the course, we will do everything with both Excel and Python to understand the differences. Later we will focus on choosing the best tool for the task at hand and the ability to combine the two tools.
Everyone should know how to solve this simple time-value of money investment problem
Many would think to reach for a financial calculator and use the five keys
Or to directly type some values into the =NPER function in Excel
With either of these approaches, you are doing a calculation rather than building a model
If you realize you need to adjust the inputs, you need to do the calculation again
With a model, the calculations are linked from the inputs to the outputs, so changing the inputs changes the outputs. This increases reproducibility and efficiency.
Transcript¶
- hey everyone
- nick dear bird is here teaching you
- financial modeling and today we're going
- to talk about
- building a basic model in both excel and
- python
- in this first video in this series we're
- just going to introduce the basic
- model and talk about the structure
- before we dive into
- actually implementing that model in
- excel in python
- in the following videos so
- let's go ahead and look at what we're
- talking about with this basic model
- so really the focus here is that we want
- to get
- just familiar working in both excel and
- python
- and for the first part of the course
- we're going to just do
- everything the same things in both excel
- and python so you can get a good feel
- for both of the tools
- and then in the future we're going to go
- to actually
- combining excel in python and
- get more into you know how you should be
- choosing which of those tools you're
- using to solve a particular
- problem
- so the problem that we're going to look
- at to model
- is uh intentionally very simple
- it's basically the simplest finance
- problem out there just a time value of
- money problem
- and i've chosen this problem because it
- lets
- us not focus very much on the actual
- content of the model everyone should
- know basically what's involved
- with the calculations we're just going
- to focus on how you actually implement
- that
- in each of these two tools and how we
- make that a model rather than just doing
- a single calculation
- so you know for this kind of problem
- you may have used a calculator in the
- past
- thinking about a retirement problem
- how much how soon can someone
- retire given that they are earning a
- certain amount
- saving a certain amount and they
- have a certain amount of cash that they
- need to be able to retire
- um so this is kind of the traditional
- uh you know five keys on the financial
- calculator
- uh for those that are familiar with
- those
- you know that type of problem so
- certainly very easy to do on a
- calculator
- but we're going to look at how to
- actually build a model for this
- and so just to quickly break down what
- this model
- looks like so again going back to this
- idea that a model is just a
- representation
- of a real world process
- and the real world process here is that
- this individual
- is saving they have wages coming in and
- they save a certain amount of that
- and then they invest that money at a
- certain
- rate of return up until they reach
- however much cash they want to have
- and that's going to tell us what the
- number of years is
- um so in the model of this
- uh we're gonna bring in you know the the
- wages
- the cash coming from the wages um
- you know involving the savings rate we
- got to use the savings rate to determine
- how much
- actually um cash is going to be coming
- into the investment portfolio
- each year and of course we need the
- interest rates to determine
- how much is going to be earned on these
- investments
- and then the model is taking those
- inputs and converting that
- ultimately into the years
- until retirement so that's the basic
- model
- and in the next video we're going to
- look at how we can implement this basic
- model
- in excel thanks for listening
Building a Simple Excel Model¶
Notes¶
It is crucial that all your Excel calculations are linked together by cell references. If you hard-code values in your calculations you are just using Excel as a calculator.
It is important to visually separate the inputs from the outputs. This makes it much more clear for the consumer of your model, especially in more complex models
More complex models should be broken into multiple sheets with each sheet dedicated to a concept or calculation
Cell formatting can be used in combination with the layout to separate them
For small models, intermediate outputs/calculations may be kept in the outputs section, while for larger models it makes sense to have separate calculation sections
Resources¶
Transcript¶
- hey everyone
- nick dear burtis here teaching you
- financial modeling and today
- we're going to talk about implementing a
- basic financial model
- in excel so we left off last time just
- talking about
- uh you know the kind of model that we're
- gonna implement here a basic retirement
- model
- uh but here we're gonna just jump right
- into building this out in excel
- so uh you know it's easy to use excel
- and just kind of type the numbers right
- into
- your functions but if you use it that
- way you're basically just treating excel
- as a calculator rather than building a
- model with a model
- everything should flow from the inputs
- to the outputs
- so you change an input and you should
- see the change
- in the output uh of course if you just
- hard code the numbers
- into the formulas themselves
- then you're not going to have that
- flexibility of being able to
- look at a bunch of different inputs and
- understand the different
- outputs that come out as a result
- so the way that you can make sure that
- everything stays linked
- together in excel is to use cell
- references
- rather than just typing the numbers in
- for your formulas
- so you know those are like you know
- a2 or whatever is a cell reference
- and then we also have fixed versus
- relative
- references and that comes into play
- when you want to drag the formula
- to have it repeat iterate across
- multiple different cells when you do
- that drag
- for fixed references the reference is
- not going to move as you drag it
- and for relative references it is going
- to move as you drag it and relative
- references
- are the default so that should become a
- lot more clear
- as we get into the extension
- of the excel model with iteration
- that we'll do in a follow-on video
- um but for now we can just uh look at
- how to implement the basic model
- in excel of solving this retirement
- problem um so
- we can uh pull up excel here
- and so you know the first thing that you
- usually want to do
- with your model is you want to lay out
- all the different inputs that you have
- so in this problem we
- had the salary we had the savings rate
- we have the interest rate
- um and we have the desired cash
- and so um we can go ahead and put in
- those default numbers
- for those inputs from the problem so the
- salary
- was 60 000 the savings rate
- 25 the interest rate is
- 5 and the desired cash
- is 1.5 million
- um and when you're structuring your
- excel models
- you definitely want to keep your inputs
- separate from your outputs that way it
- becomes
- very clear as a user of the model what
- you can change around
- and what is going to be the result of
- changing those inputs around
- it makes it a lot easier to consume your
- model
- so here we'll make a separate outputs
- section
- and in that outputs section then we can
- do our actual
- calculations now if you're building a
- more complex model
- you can kind of break this out more and
- have different spots for calculations
- and
- and one spot for final outputs and and
- we'll get into that structure
- as we go on in the course but for now
- this is very simple
- let's just put all the uh calculations
- and outputs
- in the outputs section the biggest thing
- is just keeping your inputs
- separate from everything else so for our
- outputs um
- we could go directly to uh
- doing the actual main calculation of the
- model getting the number of years to
- retirement
- um but i'm going to do first an
- intermediate calculation
- and that intermediate calculation is
- going to be
- the annual cash saved
- which is simply going to be you know
- taking the salary
- and what percentage of that salary are
- we saving
- each year um and so
- you know you might uh people who are not
- experienced with excel might think to
- just you know type in
- uh 60 000 times you know 0.25 or
- something like that and that does get
- you to the right
- answer but the problem is if you change
- uh the salary here then that does not
- change the cash saved
- so what we want to do instead is we want
- to reference the cells
- that are used in the calculation never
- typing
- the actual values within the calculation
- part
- so we're going to reference the salary
- and we're going to take that salary and
- multiply it by
- this savings rate and then we get the
- same 15 000
- but if we change the salary then we see
- that the annual cash saved
- updates as well and then we're going to
- go ahead
- and do our actual main calculation next
- which is figuring out the
- years to retirement so for
- the years to retirement excel has you
- know all those
- uh functions corresponding to those
- typical five keys
- on the financial calculators uh so to
- figure out the number of
- periods for this standard uh problem
- there is an nper function number of
- periods
- um and so into that uh mper
- function you can see it pops up uh you
- know the things that we need to input
- so the first thing that we need to input
- is the rate uh so i'm going to reference
- the interest rate and then next
- it needs the payment so that would be
- the annual
- cash saved and then we need the
- present value and the present
- value is going to be zero we're not
- starting with any cash
- and then the future value uh is going to
- be
- our desired cash now
- this will give not an answer because you
- do need to be careful about the signs of
- things
- um that it should be a negative payment
- here
- because you're paying in to
- this investment and then you're gonna
- have a positive
- cash at the end as a result so once we
- have that negative in there
- then we indeed get to an answer here of
- 36.7
- years to retirement and because
- we linked everything together uh we have
- an actual model
- and not just a calculation and so then
- we go
- and we update the salary um that's a
- really low salary
- uh update the salary we can see if we
- earn ten thousand dollars less
- it takes us you know three years longer
- to retire
- um and we can play around with the
- savings rate as well
- um let's say we could save ten percent
- more that actually uh
- is able to bring up or bring down the
- years to retirement by about five years
- um so already we're getting a lot more
- insight
- than you know just doing a single
- calculation
- and it's very easy to change around the
- inputs and see the result
- so that's the implementation of the
- model the other thing
- you would want to generally do is add
- some formatting
- to the model and
- you know everything that i'm posting um
- as the examples
- on the site is going to have the full
- formatting
- i'll just quickly show you know some
- formatting for
- this top table but you can look
- take a look at the examples for
- how to do some of the formatting
- so here you know this inputs is
- you know really a header for both of
- these columns
- so i'm going to highlight both and merge
- and center
- now that inputs is over the entire thing
- there
- you might want to you know put some kind
- of color
- on that and might want to go
- and say you know put outside borders on
- all of it so it's really clear
- uh and then all borders on this and now
- you know we have a nice box uh
- containing all of the inputs it's very
- clear
- what's an input and what is not and you
- know you would want to do something
- similar
- for the outputs as well um
- and the other thing you want to pay
- attention to excel is the number
- formatting
- as well so for the number
- formatting um all that is
- up here and so for this we can use
- currency
- formatting for these and we can take it
- down to zero
- uh decimal places so
- you know this is kind of a level of
- formatting that you would probably want
- uh at a minimum you know you can always
- do more with the formatting but you want
- to have
- enough that is very clear uh you know
- what each of these values really mean is
- it a dollar amount is a percentage
- etc and be able to clearly separate the
- different
- sections of your model
- so we'll come back next time to then
- build
- out the basic python model thanks for
- listening
Building a Simple Python Model¶
Notes¶
In Python, we keep things linked together by using variables. If you hard-code values in your calculations, you are just using Python as a calculator
Basic math in Python is mostly what you might expect, it is the same as Excel only exponents are specified by ** and not ^
Jupyter allows us to create an interactive model complete with nicely formatted text, equations, tables, graphs, etc. with relative ease
Inputs should be kept at the top in a separate section, the main outputs should be kept at the bottom in a separate section.
More complex models should be broken into sections and subsections with sections dedicated to a concept or calculation
Resources¶
Transcript¶
- hey everyone
- nick dear bird is here teaching you
- financial modeling and today
- we're going to talk about implementing a
- basic model in
- python so from previous videos we've
- already been talking about this
- very basic retirement model we already
- implemented it
- in excel and now we're going to look at
- the analogous implementation
- in python so
- [Music]
- we've gotta um do a little bit of
- background on python before we can just
- jump right into actually building out
- the model
- um so you know from the introduction we
- talked about how
- python and other programming languages
- they basically live
- in the terminal they're text-based uh
- you input text
- you get text as the output um but of
- course you know we would like to have a
- little bit more than that
- and so there's been a whole ecosystem uh
- built around
- nicer ways to use python and other
- programming languages
- um and so we've got this very nice tool
- called jupiter that we can use and will
- use for the entire class
- and jupiter is a project that
- brings in all sorts of things
- along with the code so you can have
- nicely formatted text
- you can have equations you can have
- graphics
- you can have uh you know
- basically anything that you could have
- on a web page
- you can get into a jupiter notebook and
- control
- through your code even like interactive
- stuff like
- sliders and things that change how your
- model works
- um so tons of stuff you can do there
- and it's generally just a lot nicer to
- work with than just working in a
- terminal
- so we're just going to focus 100 percent
- on using jupiter
- to work with python in this class
- and jupiter was actually born out of
- python
- it was a python project originally it
- was actually called
- ipython notebook before it was called
- jupiter
- but the reason they kind of rebranded is
- they have expanded out into
- now more than 40 programming languages
- this has just become
- such a popular way to
- build things that so many people have
- jumped on this
- from so many different programming
- languages
- so we're going to keep our focus here
- now when you're using jupiter there's
- two main versions
- of jupiter that you can use jupiter
- notebook
- and jupiter lab so jupiter notebook is
- kind of the most basic
- interface uh and jupiter lab is
- taking the jupiter notebook and kind of
- expanding on it so
- um you can have multiple different
- uh notebooks open at once in lab as
- as different tabs uh you can view other
- kinds of files other than just
- jupyter notebooks so it has a lot of
- features
- and and jupiter lab is where
- the future of jupiter is all the new
- development
- and jupiter is going towards lab and not
- notebook
- and so we're going to focus on lab and
- the class
- so i would recommend you use lab but
- if you really don't like lab you really
- want something just a little bit simpler
- feel free to use jupiter notebook it's
- going to be
- you know essentially the same experience
- as far as
- just working within it it's really not
- going to be
- substantially different between jupiter
- notebook and jupiter lab
- so you should you know at this point
- pause the the video and try to get your
- jupiter
- environment set up so you can kind of
- follow along
- as i go and implement this model so
- we've got some instructions here
- on how to get uh
- jupiter lab started
- so we can look at that quickly how we
- would do that
- so the easiest way to do this
- is to search for anaconda navigator
- and you see this thing come up here as i
- search that
- you'll see a bit of a delay before the
- thing
- actually comes up
- and then once it comes up you can launch
- jupiter lab
- from that now if you don't have
- navigator
- or you don't want to use that you can
- just open a terminal and type
- jupiter lab that's also going to
- launch it for you but this is what
- um anaconda navigator looks like
- um and so from here you just click
- launch jupiter lab um and it's going to
- go ahead
- and launch that for you
- so then that will you know
- launch jupiter like
- this
- [Music]
- so let's go back and look at a couple
- more python
- basics before we actually start using
- this
- but that's basically how you would get
- jupiter
- launched on your system
- so you know most people are going to be
- you know fairly familiar with excel at
- this point so i think it's helpful to
- kind of compare and contrast to excel
- to understand how we can use python to
- do similar things
- so in excel everything is about the
- cells right you have these different
- cells
- you put the values in the cells you can
- make references
- to the cells so the basic unit is
- a cell or python the basic unit
- is an object and that's kind of an
- abstract thing that probably isn't going
- to have a lot of meaning for you yet
- um but it's just basically the way
- of thinking about things in python
- everything
- is an object um and we'll see
- more what that means as the course goes
- on
- so for excel you know you can have
- numbers
- in your cells or you can have you know
- characters letters that would be called
- a string
- so you can have numbers or strings as
- your data types
- for your excel cells
- and python we also have
- numbers we also have strings we also
- have other types
- and each object has a type
- so it might be an object which has a
- string
- type or it might be something else
- so each everything is an object and
- every object has a type
- and that tells you you know what kind of
- data is actually being represented
- within that object
- and then you know thinking about
- referencing
- uh our values so in excel we did
- cell references right you put the values
- and then you do equals you click on the
- cell
- equals a2 or whatever and that will
- reference
- the value contained within that cell so
- you know you have definitely a similar
- concept in python
- but instead of using cells we give a
- name
- to an object so you can uh whatever
- object you can give it a name
- and then you whenever you use that name
- that's going to reference back to that
- object um so
- you know here is the analogous um
- way of representing these things in
- python um
- so in excel you know you would type six
- in a cell and you would type abc another
- cell and then you would
- reference back to whatever those cell
- references are
- to be able to get that value to flow
- through
- whereas in python we just give a name
- and then equals and then whatever our
- object is
- so my number here the name is my
- underscore
- number and the value the object the
- object which has the type of a number
- is six here and then we have my string
- another name um equals
- here abc is string the the object with
- type
- string is this abc on the right hand
- side
- and then going forward after we've
- defined these we can then
- uh say my number or my string and be
- able to get back to six
- or abc so that's how everything can flow
- through
- and then thinking about uh how we do
- math
- and python um so the basic
- operations are going to be
- straightforward um
- you know two plus five equals seven you
- can literally just type two plus five
- and it will give you seven
- right six minus two it's going to give
- you four so all these
- uh kind of math basic math operations
- that you would expect
- uh just just work like you would think
- um and then beyond that
- um you know i mentioned a little bit in
- the introduction
- about how uh python is a very flexible
- language with a lot of
- potential add-on features right in the
- form of these open source
- packages so there are all these
- different uh you know more than 250 000
- different packages out there which add
- little bits of functionality
- to python and so numpy
- is one of those packages
- which add functionality to python
- and with numpy we can get all those
- kind of financial functions that we have
- out of the box in excel
- so you just bring numpy
- into your python session and then you
- can use
- all these functions and
- you you know i told you to download
- anaconda
- in order to get python and packaged
- along with
- anaconda you already have numpy numpy
- is one of the most popular python
- packages
- in the world um and so it comes out of
- the box with anaconda
- you just have to bring it into your
- session that's that'll be
- via an import we'll look at what that
- looks like in a moment here
- but you just you know one line of code
- to add it into your session and then
- you'll be able to use
- all these functions if it was some
- package
- where it wasn't already included in
- anaconda
- then you would have to install it before
- you could use it
- but we'll deal with that a little bit
- later in the course once we need to go
- to some things which are outside of
- anaconda
- and then i just have a note on here
- about a deprecation
- warning so this isn't really anything to
- worry about
- these financial functions in numpy are
- actually getting moved
- to a separate package in the future
- numpy financial
- um and so what happens right now if you
- use these functions
- it will show a warning which just says
- hey we're going to move these functions
- in the future it doesn't affect anything
- for right now
- it's just that you're going to see a
- warning when you use the functions so
- just don't worry
- about that warning
- so let's go ahead and look at what this
- implementation
- actually looks like
- so we're still talking about this simple
- retirement model
- where uh you know we had a few different
- inputs we have
- uh the salary savings rate investment
- rate desired cash
- so we would want to first
- you know kind of just like we did in
- excel right we made an input section and
- then we defined all the inputs
- we want to do a similar thing here in
- our jupyter model we want to maintain
- that same separation of inputs and
- outputs
- that we're talking about doing in excel
- so um in jupyter you can have
- uh you know a section at the top which
- has all your inputs
- and then at the bottom you should have
- another section which has your main
- outputs
- and then in between you can have other
- sections for uh you know any kind of
- intermediate calculations
- but here you know it's a very direct
- model
- so
- two so you see these uh cells here
- in jupiter and you can type code in any
- one of the cells
- and you can run cells individually
- um and there are also you know two main
- types of cells
- that we'll think about using in this
- class um
- so um
- we have the um
- the code cell and the markdown cell are
- the two
- main ways that we'll use it in the class
- um so the code cell you're you're typing
- code
- and then the markdown cell you're typing
- these this nicely formatted text
- so we'll get definitely more into what
- you can do with this text formatting
- uh as we dig more into this stuff
- but here you know just know if you put
- this uh
- hash then that can give you a header um
- so
- i'm going to say you know inputs here
- and then when i run that you can see it
- comes as this nice header
- and you know i can put some regular text
- uh here and you can see that comes in a
- regular size there
- and to run this i'm holding shift and
- pressing enter
- uh you can also uh do this play button
- that does the same thing and it's the
- same thing for
- these markdown cells as well as the code
- cells
- you run them the same way so
- now we have you know a nice header for
- our input
- section um now let's go ahead and type
- out our various
- inputs so first let's put the salary
- salary equals 60 000.
- then we have the savings rate um
- and you'll notice i didn't do saving
- space rate
- because variable names cannot have
- spaces
- in python so the convention is you know
- make them all lowercase and instead of
- spaces you use underscores
- and that's what you'll find in pretty
- much any python code
- and then we have also the investment
- rate
- equals 0.05 and the desired
- cash is going to be 1.5 million
- um and then you see i hit shift enter
- and now that it's a code cell
- you can see actually this execution
- count uh go up
- each time that i run it um
- and another thing you'll note is that
- unlike in excel
- i did not type a percentage sign in here
- i typed it in decibel format
- you cannot just put
- you know a percent like you can in excel
- you can see it actually gives a syntax
- error
- if we do that because that basically
- means syntax error means
- python doesn't understand what you're
- trying to do
- so percent has another special meaning
- in python we're we're not going to cover
- that
- um it's not important for what we're
- doing
- but just know that you can't just you
- know put a percent
- sign and expect it to know that you're
- talking about a percentage
- you have to just put it in decimal is
- the way that it's going to know
- so we have our inputs defined now
- and so then we can make the next section
- determining cash saved
- um and you'll notice you know whenever
- i'm in the last cell and i run it
- it does create another cell um also you
- can
- um you know insert cells above and below
- um
- the the here you know it's a
- a and b are able to insert cells above
- and below
- and d twice is able to
- delete cells and all of these things
- are in the menus as well
- so you can you know if you prefer to
- just
- use the menus you can find all the
- things in there
- uh but i always just use the hotkeys um
- so b for below a for above
- dd for delete um
- and you'll notice also one more thing is
- that
- uh we basically have two different
- modes here you can see that switching
- here command mode
- or edit mode and what i'm doing here is
- i'm hitting
- enter to go into edit mode and i'm
- hitting escape to go back out
- into command mode and when you're in
- command mode you can do these a
- b d d uh et cetera
- where as when you're in edit mode then
- those would be you know just typing
- characters into the cell
- so uh we have our next section
- determining cache saved
- and now we're going to do our actual
- calculation
- of what is the cash saved
- so let's call this variable annual cash
- and it's going to be salary times the
- savings rate
- and you'll see you know one nice feature
- of jupiter
- is we can get auto completion for our
- variables if you can't remember what it
- was called or you just don't want to go
- all the way through with typing it
- if you're in the middle of typing
- something you can hit tab
- and that's going to bring up the
- possibilities
- and then as you type it's going to
- narrow down the possibilities
- and you hit enter to pick the option or
- you can up and down to
- pick whichever option so here we want
- the
- savings rate um and so now we have our
- calculation
- the annual cash is the salary times the
- savings rate
- okay let's run it and now you might say
- where
- is the annual cache i just calculated it
- where is it
- right and that's uh you know one thing
- which is definitely difficult for people
- who are used to working in excel
- in excel you know you do your
- calculation and it shows you the result
- right away right
- with python and this is generally the
- case with any programming language
- it's not going to show you anything
- unless you tell it to show you
- something so the general way to
- show something in python is with the
- print command
- so if we print the annual cache then we
- can see it is indeed
- uh 15 000.
- but there is a nice shortcut in jupiter
- so
- in jupiter uh you can
- uh just put uh
- whatever you want to see as the last
- thing in the cell
- and then it's going to basically print
- it for you
- so it's just you know lets you save a
- little bit of typing whatever
- is on the last line of the cell it's
- going to print
- so if we just wanted to do our
- calculation and then see it right away
- in the same cell
- then you can do it like that just put
- whatever variable
- on the last line and then you're going
- to see it right away
- and you don't have to do all of that
- stuff
- so we have now the annual cash and so
- the last
- would then be calculating the
- years to retirement um
- and you know definitely take a look at
- the completed
- model for this because it you know shows
- you know how you can be you know putting
- more text and a little more formatting
- in this to really
- uh you know make it a more polished
- model
- but here you know we're just going kind
- of quickly
- uh it also explains a little bit more
- all the various steps
- that are going on um
- so we want to calculate the years to
- retirement and we talked about how the
- numpy
- library has the nper number of periods
- function
- which is going to allow us to do this
- but how do we use that so you don't have
- access to it immediately in your session
- you do have to actually import the
- package
- to get access to it um
- so if we import numpy
- uh then run that then all of a sudden
- we get access to everything which is in
- numpy so then we can do
- uh you know numpy dot mper
- and you might say well how do i actually
- use this thing
- so a nice another feature in jupiter
- is you can put a question mark after
- something and it will tell you about it
- so here we can get all the information
- about this
- function we can see that it takes the
- rate first then the payment then the
- present value
- then the future value which defaults to
- zero and then
- uh you know the the annuity part annuity
- due or not annuity due
- um another way you can go about this
- is you know uh after you type the
- parentheses here to call this function
- uh if you hold shift and press tab then
- you're gonna see
- the same thing just come up as a little
- pop-up here
- where you can take a look at that so i
- use this all the time shift tab
- to quickly look at how to use a function
- so we're going to then pass in
- our uh arguments uh for
- this um so
- our arguments are going to be um
- you know first we got to put the rate so
- that was the
- investment rate and the next
- we got to put the payment
- so the payment is
- the minus annual cash and then we've got
- to put
- the present value so the present value
- is zero we're not starting with any cash
- and then we've got to put the um
- desired cache
- and so we do that and we can see that we
- get the same result
- that we did in excel
- um now um
- this works just fine but there's a
- couple things that we want to do to
- clean this up a little bit um and you
- can see this
- this morning that i mentioned came up
- that's nothing to worry about just
- ignore this warning that comes up
- deprecation warning you can just ignore
- that
- um so there's a couple things we want to
- do to make this a little bit cleaner
- one is there is actually a convention
- that everyone uses for
- importing numpy if you google anything
- about
- numpy you'll see everyone using this
- same convention
- and that's to actually import numpy as
- np and that basically just renames numpy
- to np so that then we can do np
- number of periods and it does the same
- thing so functionally
- no difference but this is just the
- convention that everybody uses
- um then the other thing is
- that um you know the formatting of this
- is not very good we would like to get
- a better formatting of this result
- so we can go ahead and save that result
- to a variable
- so we can say years to retirement
- equals that right
- and so we look at years to retirement it
- is that
- um but now we want to add some
- formatting to it
- so the way that we can do easy
- formatting and also you know include it
- in a sentence or anything like that
- is with f strings and we'll dig more
- into f strings when we go
- into the python basics lecture
- but basically we can say you know martha
- has
- and then we can drop in our variable
- years to retirement and then we can
- specify
- uh the format of that um
- and then we finish the string and then
- we say uh you know that she has 3.67
- 36.7 years to retirement so this is a
- lot nicer way to see the result
- you know if someone just sees this at
- the end they don't necessarily know what
- you're talking about
- but putting it into a sentence here
- with you know nice number formatting it
- becomes very clear
- what you're talking about
- so we'll dig into a lot more of these
- python concepts as we go into the basics
- this was just a very quick run through
- on how to build a basic model
- and then you know there's a useful
- shortcut up here
- kernel restart kernel and run all cells
- so if we say change this to 40 000
- and then we restart kernel and run all
- cells
- then we'll see that it updates the
- year's retirement down here so that's
- the easy way that you can just rerun
- everything in the model
- in order to be able to
- get the new result for whatever change
- in the inputs
- so next time we'll come back and we'll
- look at
- how we can extend these basic excel and
- python models
- so thanks for listening and i'll see you
- next time
Basic Iteration¶
Notes¶
Iteration is a key concept in financial modeling (as well as programming)
Using iteration, we can complete the same process for multiple inputs to yield multiple outputs
As the same process is applied to each input, the process only needs to be created once and any updates to the process can flow through all the inputsIteration can be internal or external to the main model. You can use iteration within your model, or you can iterate the model itself
To iterate in Excel, drag formulas. To iterate in Python and other programming languages, use loops.
Transcript¶
- hey nick dear bird is here teaching you
- financial modeling
- today we're talking about basic
- iteration and how it applies to
- financial models
- this is part of the segment getting
- started with python and excel
- where we're exploring building a basic
- retirement model in both excel and
- python
- we've already built out the most basic
- excel and python models
- and now we're looking at how we can
- extend those models
- so we uh you know have the basic result
- how long is it going to take for martha
- to retire which was this you know 36.7
- years
- with our basic inputs that we decided on
- but there's a number of assumptions that
- went into that
- uh you know both in terms of the inputs
- values as well as the structure of the
- model
- um but one important key assumption here
- is that we assumed that martha's going
- gonna earn five percent
- on her investments uh but in reality we
- know
- that you know an investment rate is you
- know
- very rarely guaranteed uh most likely
- she's going to end up earning a
- different rate of return on her
- investment
- and so we really need to evaluate how
- long it's going to take to retire
- under a bunch of different scenarios for
- the investment rate now
- we want to do that but you know say we
- want to look at
- you know 10 different investment rates
- we don't want to go and
- implement everything that we just did 10
- separate times right that would be a lot
- of work
- even for you know such a basic model as
- we have built
- so we need to actually bring this
- concept of iteration
- into our models to be able to answer
- these kinds of questions without having
- to repeat
- what you've done so you know in both
- financial modeling as well as just in
- programming in general
- it's often the case that you have some
- process
- and you want to repeat that process on a
- bunch of different inputs
- to get a bunch of different outputs
- so that is what we refer to
- as iteration is just doing the same
- process
- on a bunch of different inputs
- so the way that you accomplish this in
- excel
- is once you've got your formula in the
- cell
- which you know has cell references
- then you click on the corner of the cell
- and you drag that uh in whichever
- direction
- and that's going to then uh
- repeat that formula over and over and
- it's going to take the cell references
- and if they're relative references it's
- going to move them
- by the same amount that you're moving
- the
- formula dragging the formula um whereas
- if it's a fixed reference then it's
- going to stay
- where it is and so that allows you to
- have you know say you have a column of a
- bunch of different inputs
- and you have a calculation over here you
- reference
- uh your number and the input over here
- well then you can drag it down and have
- it apply to all those different
- inputs and we'll look at a concrete
- example of that
- in the next video but that's the basic
- idea
- dragging formulas is your main way of
- doing iteration
- in excel without having to go to vba
- so then in python uh as well
- as you know other programming languages
- as well
- iteration is all about loops uh this
- this this construct that we call a loop
- um
- and we're going to explore you know one
- main kind of loop
- um in this class uh and that's a
- for loop there are other kinds of loops
- but uh you know that's going to be the
- primary one we're going to focus on
- and that just says you know for for each
- different input
- i want to do something um that's all
- that a for loop means is you know
- whatever code goes in the for loop
- i'm going to run that for each one of my
- different inputs
- so that's the basic concept
- so just doing doing whatever operation
- whatever code you have
- within that loop whatever process you're
- just going to repeat that
- for each one of your inputs
- so you know just uh you know bird's eye
- kind of view
- here's python iteration versus excel
- iteration doing the exact same
- calculation
- so you know made this a very simple
- calculation so that we can just try to
- understand
- the structure of the of the iteration
- that makes it apply to multiple inputs
- so this calculation here is just adding
- two to whatever the input is so if we
- put in five we should get seven
- if we put in 15 we should get 17.
- um and so you know the idea here is that
- our main calculation we should only have
- to type it once right it's not typing
- five plus seven
- and then ten plus twelve or sorry five
- plus two and then ten plus two and then
- fifteen plus two
- we just wanna type you know our
- reference
- plus two and then do our iteration to
- get it to apply
- to each one of these now of course in
- this very simple example
- it's you know probably even a little bit
- more work to do it this way
- but as you go to more complicated things
- you know it could be that your entire
- model
- runs with it within this loop right and
- then you're looking at your entire model
- for a bunch of different inputs
- uh or all sorts of different cases uh
- and so there's you know far more complex
- things
- we can do with these loops so we're just
- learning the structure here
- um so our three different inputs into
- this
- add to calculation are 5 10 and 15
- and we're expecting to get 7 12 and 17
- as our outputs here um so
- you know in excel it would be you know
- so these would be the hard-coded numbers
- here
- and then these would be where the
- calculations are and here i'm just
- showing
- you know what would have been typed into
- this into this cell
- to be able to uh produce the output
- so you know you would start here and you
- would type equals
- and you would click this uh input cell
- that would get the b4 and
- you do plus two and then you know from
- there you drag it down
- and this b5 this b6 those are
- automatically happening
- those were not typed uh you type this
- once and then you're able to drag it
- down
- and it's able to repeat this calculation
- this adding two calculation
- for each one of the inputs
- so uh in python it looks a little bit
- different but conceptually
- we're doing the exact same thing so
- here we've got our inputs 5 10 and 15.
- we have these three numbers in a list
- we're going to talk more about that as
- we get into the python basics
- but a list is a type of object which
- holds
- other objects so here we have a list of
- numbers it's just a container basically
- to hold these three different numbers
- in one single variable um
- so we've got our 5 10 15 all in one
- variable here
- and then uh we do our actual
- for loop so with the for loop we have
- four item and inputs so that's saying
- you know each time that this
- loop runs it's going to run three times
- because we have three inputs
- each time it's going to pull one
- of these out of the loop so the first
- time that it runs
- we're going to get five coming out the
- second time that it runs we're going to
- get 10
- coming out and the third time we're
- going to get 15.
- so you do this 4 item in inputs gotta
- have a colon at the end
- gotta have this indentation for
- everything within the for loop those are
- both very important
- and then once you're in the for loop we
- can do
- our calculation so then we're taking our
- item
- which the item here is our loop variable
- uh so the loop variable is just you call
- this whatever you want
- um and it represents each item that
- you're getting
- as you go through so the first time you
- run
- or so as it runs it's gonna execute
- three times in here
- first time it's going to be five so it's
- going to add five to two
- you're printing that out so that's why
- we get seven and then
- it's going to then you know come back
- around because we have more inputs
- and the next time it's going to give us
- 10 as item
- it's going to do 10 plus 2 12 put that
- in a new value
- print that new value and so we get 12.
- and you know final time again it goes
- gets 15 this time for the item
- adds 15 to 2 gets our new value is 17
- and prints that new value
- so that's the basic idea of uh
- iteration and you know basically how you
- implement it
- in both python and excel um in the next
- a couple of videos we're going to
- actually implement that
- on our existing basic retirement models
- uh to see a little bit more in detail
- how these things
- work so thanks for listening and see you
- next time
Extending a Simple Excel Model¶
Notes¶
Essentially the model with iteration is the same, we just drag the formula to cover multiple inputs
It is crucial to set up fixed and relative cell references appropriately before you drag formulas
Transcript¶
- hey everyone
- nick dierbert is here teaching you
- financial modeling today we're talking
- about how to extend a simple excel model
- to add
- basic iteration this is part of our
- segment on getting started
- on python and excel where we're building
- out basic retirement model
- with both tools so
- uh looking at how we would add this
- iteration so here i'm going to start
- from what we had from the prior video
- where we built out this basic
- excel model to get the years to
- retirement
- uh based off of these inputs but i'm
- going to go ahead and just
- copy it to a new sheet um
- and then we can call this uh you know
- model with iteration
- so uh here let's look at say three
- different interest rates and what's the
- years to retirement
- for each one of those rates um
- and we want to be sure to be able to do
- this without
- having to retype any formulas or copy
- paste or
- anything like that we want to be able to
- take the same formula that we've written
- and just apply it
- to multiple different inputs and
- you know with such a simple calculation
- it doesn't make a big difference
- but once you're you know running an
- entire complicated model here
- uh you're definitely not going to want
- to copy your model multiple times that
- becomes a huge maintenance nightmare
- and so iteration is important
- so we want to look at a couple other
- interest rates
- so let's say you know we want to also
- look at six and seven
- percent um of course you know you would
- want to update the formatting so it can
- capture
- all of this um but i'm just going to
- leave that as an exercise for you on
- your own so we don't have to spend time
- with it here
- um now we want to take this years to
- retirement calculation
- and apply that to not only the five
- percent rate
- but also the six and seven percent rate
- um so the first thing you might think to
- do uh you know i've already
- mentioned how uh iteration in excel you
- drag
- the formula right and so you can see you
- know on this box you can see that this
- bottom right corner that box there has
- uh you know a larger corner and you can
- see the cursor changes
- uh to this other one when i go over that
- and that's signifying that you're able
- to
- drag it um so you just click that and
- hold and drag it
- and then you can go in you know whatever
- direction
- you want from there so
- you know your your thought here might be
- just okay let's just drag it over
- and uh then we'll get the calculation
- again
- for the other inputs and to some extent
- that's true
- but you'll see the issue here right we
- can see that
- the reference for the interest rate
- indeed moved over
- as we wanted it to but the uh reference
- for desired cash and annual cash save
- also moved over even though we only have
- a single desired cash
- and annual cash saved and so one way you
- might think
- to solve this is okay well i'll just
- you know repeat these numbers uh you
- know
- in each one and then it should work
- right and
- uh
- and it's close to working you know if
- this was a hard-coded number that would
- have worked but again this
- face the same problem right this was
- actually a calculation
- uh and so that also dragged over well
- let's just say you know we had
- 15 000 in here okay now it is working
- uh but what if you want to update the
- desired cache now
- it's you know 1.6 million oh wait that
- only updated the one calculation that
- didn't update the others
- uh and so there's definitely issues with
- this approach
- plus it just looks repetitive um
- so the solution to this is uh
- to use fixed references so
- by uh you know carefully picking which
- references
- should be fixed which should be relative
- and
- you know if you're uh depending on the
- situation you can
- fix just in one direction on the row or
- the column uh
- we'll get into that more as the course
- goes on
- but uh for now we can just think about
- fixed or relative
- so the fixed reference is not going to
- move as you drag
- the calculation while the relative
- reference will
- um and so you want to make all those
- changes to your formula before
- you go and drag it so if we click into
- the formula we again
- you know see those three references here
- highlighted in the same colors
- in the formula bar here
- and we want to then not fix the interest
- rate
- but we do want to fix the desired cash
- and the annual cash saved
- so we're going to leave the interest
- rate alone and we're going to come over
- here
- to the annual cash saved and the way
- that you can
- fix the inputs so on windows it's the f4
- key and you'll see when i press that i
- now have dollar signs
- in front of the b and in front of the
- eight that means it is a completely
- fixed reference
- then if i hit f4 again you'll see now
- the dollar sign is just in front of the
- eight
- that means that it's fixed on the on the
- row
- but not on the column that means it will
- move right and left but it will not
- move up and down and then hit it again
- now we have fixed on the column
- uh oops we have fixed on the column
- uh but not on the row so now
- it would move up and down but it would
- not move left
- or right and then you hit f4 again and
- you're back to just the regular relative
- reference and for mac users
- uh it's command t gets you the same
- thing as
- f4 on windows um
- and if you forget the key or you can't
- figure it out or it doesn't work on your
- system or something
- you can always just type the dollar
- signs it's not
- you know quite as convenient but it does
- work it'll do the exact same thing as
- the f4 command t
- keys um so then coming over
- to the uh desired cache we're gonna also
- fix that as well
- and for both we're just doing completely
- fixed uh references
- you know fix just on the column would
- have worked as well
- uh but here uh it doesn't matter which
- one we choose for this purpose
- so now uh interest rate relative
- desired cash and annual cash saved are
- fixed
- so i hit enter you can see the original
- number did not change
- but now we can drag this over and we can
- see we get the actual results
- and when we click into what happened we
- can see the interest rate
- indeed moved over while the desired cash
- and annual cash save
- stayed where they are because those are
- fixed
- references so you know with this kind of
- technique
- we can repeat the same calculation uh
- for a bunch of different inputs in excel
- uh to be able to uh look at a bunch of
- different outcomes
- from our model and then next time we're
- going to come back
- to do the same thing with our python
- model
- so thanks for listening and see you next
- time
Extending a Simple Python Model¶
Notes¶
To add iteration to the Python model, just wrap the existing code in a loop
We must also collect or show the output in some way, as we can no longer take advantage of the Jupyter shortcut to show the output without printing.
Transcript¶
- hey everyone
- nick dear burtis here teaching you
- financial modeling today we're going to
- talk about
- extending a basic python model to add
- iteration
- this is part of our getting started with
- python and excel
- segment where we've already built out a
- basic retirement model
- in both excel and python in the last
- video we
- added iteration to the excel model
- so we are now looking at how we can add
- this iteration
- to the python model so
- from uh you know the the prior video
- which went over the basic iteration
- we kind of compared the python iteration
- to the excel iteration
- uh you know in python you're going to
- use loops here we're looking at a
- for loop um and in excel
- you are dragging formulas so ultimately
- accomplishing the same
- result
- now the the python version is quite a
- bit more flexible because you can
- do absolutely anything under that loop
- uh
- whatever whatever operations you want to
- do you can do them whereas excel you
- know it's just
- uh you know taking one formula and
- repeating it so
- you know excel is maybe a little bit
- easier to get into for the iteration
- but python ultimately is a lot more
- flexible and powerful
- and we'll see that over time as the
- course progresses
- let's dig a little bit more into uh this
- python iteration
- and what all this stuff actually means
- um
- so there's a few different parts in here
- the first we kind of you know glossed
- over this a little bit
- last time in that we had this first line
- inputs equals uh you know left square
- bracket
- 5 comma 10 comma 15 right square bracket
- so those square brackets there
- and putting commas in between things
- this is
- a new type of object that we hadn't
- worked with before
- it's not a string it's not a number this
- type of object is called a list
- so just by doing those brackets adding
- those
- commas potentially if there's multiple
- things in there
- you're making a list an object of type
- list
- so this inputs here we have as
- an objective type list and
- a list is one of the data types
- in python which can hold other objects
- within
- itself um so here
- this list is holding three different
- numbers five
- ten and fifteen um so certainly we're
- gonna dig
- a lot more in the list as we go on
- throughout the course
- but for now you can just know it's an
- object which holds other objects
- here we're using it to hold three
- different numbers
- and you can add or remove things from
- the list which we're not even going to
- do in this exercise
- but that's the basic idea behind a list
- you can
- hold multiple things you can add things
- you can remove things and you can look
- up
- specific uh items from the list
- or you can loop through and get each
- item one by one as we
- are doing here so
- we've got you know these three different
- numbers as our inputs 5 10 15 and we've
- stored those into a list
- now we come to the next line the second
- line here for item in
- inputs so this line is what defines the
- loop
- and this is a for loop so
- we know it's a for loop because the
- first word there is 4
- and it has this 4 in syntax
- so it's 4 and then
- whatever we want to call our loop
- variable so that could be
- whatever you want in uh
- and then whatever you want to iterate
- through so we want to iterate through
- this list of inputs
- so it's you know four item item is
- ultimately going to become each one of
- these as we go through
- in inputs that's referring back to this
- list of
- three numbers so
- then it's important that we end this
- line with a colon that's how
- python knows that we're going to start a
- new block here
- a block under the for loop the logic
- which should execute
- in each loop each iteration of the for
- loop
- and then we have the next important
- thing here is the indentation you need
- to have this indentation here
- for python to know we want this stuff to
- run
- under the for loop if we had another
- line of code out here
- that would not be under the loop that
- would run a single time
- after the loop has finished
- um so then you know within the loop
- we're doing two things
- one is we're doing our calculation here
- of adding two
- to whatever our item is either 5 10 or
- 15
- as we go through assigning that to a new
- variable name new value and then
- printing out
- uh that new value and so that's how we
- get
- 7 12 17 because first time it goes
- through with five as
- item five plus two is seven it prints
- that
- then ten comes through his item ten plus
- two is twelve it prints that
- then fifteen comes through on the last
- loop fifteen plus two is seventeen and
- then it will be done with this loop it
- would
- go on to any code which is beyond that
- uh or finish if that's the last code
- that you have
- so that's the basic idea let's go ahead
- and
- look at how we actually
- work with this in the context of our
- existing
- model so i have the python model that we
- built out
- in the last exercise here so
- we already have uh you know getting the
- years to retirement
- for martha based on this
- single uh investment rate of five
- percent here
- so now we want to look
- at multiple uh interest rates
- um and for that
- let's say we want to look at uh
- four or five or let's say five six and
- seven percent
- so we can say you know investment
- rates equals and then brackets and the
- brackets is how we're going to have a
- list here
- um so then we can do five percent six
- percent seven percent
- and you'll notice i'm putting commas uh
- in between
- each of the items the spaces are
- optional they just look nice and people
- normally do them
- um the important thing is that you have
- the commas to separate the items in the
- list
- so then you know we can look at our
- investment rates
- uh and you can see that we indeed get
- back that same list
- of these three numbers
- and so then that takes us to the for
- loop
- next um so we can say
- you know for i rate and investment rates
- let's just print out that um investment
- rate
- this is you know pretty much the
- simplest loop that you could have
- so we can see it prints five and then it
- prints six and then it prints seven
- and you know if we had some code out
- here
- uh then that's gonna run
- once at the end of the loop after it's
- done
- uh if we put that into the loop then
- it's going to run
- once per loop so that's you know where
- the importance of this indentation
- comes in anything which is indented is
- going to run as part of the loop so it's
- going to run over and over
- for as many inputs as you have that's
- why we have three dones here
- we move it back and we're back to one
- done
- because it does this print line three
- times and then this is not
- indented so then it does this after it
- has finished
- the for loop and if you try to have
- no indent at all it's going to give you
- an indentation error
- expected in indented block and that's
- because you know any time
- you're doing a colon like this in python
- it's expecting you to start a new
- indented block
- and so we need to have that for it to be
- valid python
- so that's the basic uh way to loop
- through these investment rates so now
- let's
- bring in the actual calculation that we
- want to do
- so we have our calculation here from
- before
- but we don't want to use the single
- investment rate
- from before we want to use this irate
- which is
- you know each uh investment rate so i
- run that
- and then you don't see any output right
- because again
- we have to actually tell python that we
- want to see what we calculated it is
- doing the calculations but
- it just doesn't show them by default so
- we're going to bring this
- print statement also into that loop
- and now you can see that we get the
- three different
- uh years to retirement here but uh
- it's you know not uh quite there yet
- because
- it's not clear which user retirement
- goes with which interest rate
- so let's include that into the message
- as well
- so if she earns a
- irate and then put the number formatting
- we'll go over more of what all this
- number formatting
- need means when we get into the python
- basics
- um but now we can see that that's
- included in the string as
- well so now we have you know same thing
- that we had in excel
- uh now we're using a loop we have the
- same code
- uh which which executes for all three
- iterations
- and we see the results for each one and
- again that
- indentation is important if we just put
- the print here it's only going to show
- us
- the last one because it finishes the
- loop and then it has everything set up
- from the last loop
- uh so make sure to pay a lot of
- attention to that indentation
- uh so that everything that you're
- executing as a part of the loop
- is indented and everything which you
- aren't is not
- so uh you know that's the uh
- way we can add iteration to our python
- models and functions we're going to come
- back
- next time to discuss the lab exercise
- for this material so thanks for
- listening
- and see you next time
Getting Started with Python and Excel Labs¶
Notes¶
This is our first real lab exercise (must be submitted). Be sure to complete the same exercise in both Python and Excel
We often want to iterate over more than one input. Here we want to look at the pairwise combinations of the savings rate and interest rate possibilities.
Excel hint: there is a nice way to lay this out so you only need to type the formula a single time
Python hint: It is possible to nest loops to loop over the combination of two different inputs
Transcript¶
- hey everyone
- nick dear british here teaching you
- financial modeling today we're going to
- talk about
- the lab exercise to go along with the
- getting started with python and excel
- segment of the course so
- the lab exercise and this is our first
- uh lab exercise where you actually have
- something to
- submit so the idea here is we're going
- to go a little bit further
- with this concept of adding iteration to
- our
- models so we already looked at you know
- how to vary the
- uh the interest rate uh
- for both our excel and python models
- so now this is about let's not only vary
- the interest rate
- but let's also vary the savings rate at
- the same time
- so not only are we going to look at
- these three different interest rate
- cases
- we're also going to look at three
- different savings rate cases
- and every pairwise combination of those
- two
- so there should ultimately be nine
- values uh
- for uh the model for you know ten
- percent savings rate with four percent
- interest rate
- ten percent savings rate with five
- percent interest rate uh ten percent
- with six percent
- then twenty five percent with four
- twenty five with five and so on
- um nine different cases and
- you're going to need to implement this
- entire thing
- and both excel and python
- so as far as implementing it you can go
- ahead and
- you know download the completed example
- model and start to work from that and
- then just go
- and add this additional iteration
- on the savings rate um
- and uh as far as giving you a little bit
- of help
- on getting started um so for excel
- hopefully things are fairly clear on
- what you need to do there
- but i will say there is a nice way that
- you
- can lay everything out
- where you still only need to type the
- formula once and you can drag it in both
- directions
- uh to get all nine different values uh
- and that's going to require
- using uh you know some fixed references
- which are fixed only on the column and
- some which are fixed only on the row
- then on the python side
- uh the uh you know one good way to go
- about this
- is to do a nested for loop
- so that uh definitely trips a lot of um
- beginners up so let's take a look at
- uh what that looks like um
- so let's say you know we have you know
- one
- uh list here and then we have
- another list here um
- one list of numbers one list of strings
- so you can
- uh right
- two loops
- inside of each other
- so um or sorry this would be print
- a item print the item to get the actual
- loop variable
- um so there you can see we get the same
- kind of effect that we're going for
- with this model right we get uh you know
- one
- with a and then we get one with b and
- then we get one with c
- and then we get two with a and then two
- of b two and so on
- so this is the same kind of you know
- pairwise combination
- that we're looking for with this model
- and then of course you know rather than
- just printing you would want to actually
- put your logic inside of that inner loop
- and you will notice you know each for
- loop needs its own
- indentation right so we write the
- original for loop
- and then we have that four loops ending
- and now we write another for loop and
- then we have that for loops indent on
- top of that so two
- levels of indentation here to ultimately
- write
- our main logic so hopefully
- with you know what we've covered as well
- as this information
- that should be everything you need to be
- able to complete these labs
- now they are meant to you know challenge
- you a little bit and going just a little
- bit farther
- than the material that we covered but
- that's why we also have
- the uh you know lab sessions where you
- know you can come and you can ask
- questions
- and get feedback on everything so you're
- not going this
- 100 alone so
- that's how we will wrap up this getting
- started with python and excel
- segment next time uh the whole next
- segment is going to be about
- uh you know building a more in-depth
- excel model and what that looks like
- so thanks for listening and see you next
- time