Business owners & professionals
In this presentation we will compare and contrast present value and future value just to get a better
idea of present value and future value we're going to have the data in the white sails we're going to
enter our data our answers into the blue areas up top and down below
Once again our goal is to just look at that relationship
Obviously the present value future value are related we're looking at the time value of money
So let's get an idea of those relationships with the Excel formulas the two formulas we want to know
are the present value and the future value
We're going to use the same function for the present value of one present value of an annuity
Therefore if we know the present value formula of one and an annuity the one formula for both the present
value of 1 and present value of annuity and the future value for both present value of 1 and present
value of annuity
We can in essence just know those two formulas and then the relationship between them and we can get
a lot of use from that information
So here's going to be our data
We've got the amount up top we've got the number of periods we've got the rate and now we're going to
figure out the present value with that data and then we just want to think of that that answer the present
value answer that we get we're gonna use to then calculate the future value just so we can see the relationship
between these formulas these amounts
Let's see what we're talking about here let's run the present value formula
I'm going to do that with a negative and we're going to say present value double click on the present
value
We're going to select the rate and then comma the number of periods which is gonna be five
Remember these are years we're going to say comma the payment amount is going to be zero because remember
this is a present value of 1 rather than in an annuity
The payment would be for an annuity comma
We're going to say the future value is going to be that ten thousand
So we're gonna bring that future value back to the one time amount five years so it's ten thousand in
the future what would it be in today's dollars
Select enter or hit enter
That's going to be the four thousand 972
So in other words five years later if we used 15 percent as the discount rate after that five years
would be in present dollars four thousand nine seventy two
Now we're going to use that four thousand nine seventy two and put that into our future value formula
So we're gonna say this is the present value and we're gonna have a number of years five years the rate
15 percent
Let's calculate our future value
The other formula that we're taking a look at
Within Excel this is going to be negative future value f v DoubleClick the future value
We're gonna take the rate which is 15 percent comma the number of periods five years comma we don't
have a payment because we're not talking annuity here comma we want to take the present value in other
words
This is today's dollars
How much would be equivalent for an amount ten years or five years out
And that would be of course the ten thousand
So you could see a relationship of course between these amounts you can you can better see the relationship
between these amounts or another way to see the relationship between these amounts is to run basically
a table to calculate each year
So if we were to do that we'd say OK what if we took years one two three four and five and then we had
the total and then we picked up the starting amount and we're going to say OK this is the present value
Let's start with the present value amount and then let's increase it by 15 percent each year
So that means each year we're gonna basically increase it by 15 and that means we're gonna have 100
percent plus 15 percent
So therefore the rate is going to be this 15 percent plus one 115 or want 100 percent plus 15 percent
And then let's say we'll get well what would happen after in year one
So it should be one to two three four and five
And we don't need a total
So after year 1 we're going to say this equals the prior period before 9 7 two times one hundred and
fifteen percent
In other words the 15 percent plus the 100 percent that's already there
One point one five
And that'll give us the 5 7 1 2
Then we'll do this again we'll say Okay here's the fit 115 percent let's take the 5 7 1 5 times the
150 percent for year two
That leaves us is with six thousand five seventy five
Okay let's do it again for year three this equals the prior number times one hundred and fifteen percent
giving us seven thousand five sixty two four year three
Now let's do this again we're going to say this is the prior number of times one hundred and fifteen
percent eight thousand six ninety six
Same number here
And then one more time prior number times the 115 gives us the 10000 so you can do a calculation like
that
We could see the relationship between the present value and the future value
Getting a better idea of it
Another way to consider this is to use gold seek to find some other area gold see going to be a function
that we can use within Excel
So let's take either one of our data here are data set and let's say that we're going to solve for the
So I want to solve for the rate and say that that's going to be the unknown rather than in the first
case the present value
So if I'm going to solve for the rate and I'm going to use gold seek to do it what I'm going to do is
I'm going to guess the rate I'm just going to pick a rate at random then I'm going to use the present
value formula
Then I'm going to say hey Excel would you used gold seek to figure out what the rate should be to get
the present value to know what what I know that it should be
So we know the present value is going to be the four thousand four 9 7 2
I don't want to plug it in here I want to use a formula to do so however and I want to hit it
I want to enter a rate just to start off with
So I have some value for the rate
So I'm gonna say all right what if the rate was you know 2 2 percent let's put in our present value
formula this equals or let's say negative present value double click the present value
We want the rate 2 percent which is guessed that's just a guess comma a number of periods five five
years comma the payment is zero comma and then the future value we're gonna say is ten thousand
Now when we say enter we're hoping it's gonna be four thousand nine seventy two it's not because we
just guessed this rate now we could try to figure out OK what what would this rate be
To get this to four thousand nine seven What if I chose four percent right
And we could keep doing this
It's getting closer
Let's go ahead and let Excel do that
We'll do that by going up top
We're gonna go to the Data tab up top
We're gonna go to the forecasting section
We're gonna go to the what if analysis and go to Gold seek goal seek
Now we don't have to be on any particular cell here to use goal setting we're just going to say right
Excel This is what we would like you to do
We'd like you to set this cell to be what we know it should be
Set it to the value of 4 9 7 2
I want you to do that we'd like you to do that by changing this cell
So let's review that one more time or saying hey excel we know what this should be
It should be four thousand nine seventy to change this cell B 24 to 4000 972 please by adjusting the
related cell that's in a related function of related formula this rate this 5 percent just do whatever
you gotta do to make that 5 percent in Excel we'll actually test everything to figure out what it should
be
So we'll say OK
And that's at 15 percent
There it is
So now we have that that 15 percent as our we can do a similar type of calculation what if we said that
this is going to be our present value amount and now we use the future value formula to figure out the
rate
And we're just going to use the goal seek in the same format
So we know the future value has to be ten thousand
That's going to be the future value but we don't know what the rate is let's pretend it should be 15
percent but we don't know that
So we're gonna do the same thing like what if I guess the rate what if I said Well I think it's going
to be 25 percent on the year I don't know
And then use future value to figure out what this rate should be
So now we'll use the future value formula same kind of goal seek analysis we'll say rightNegative future value double click for future value
We want the rate which we don't know we just entered a random number comma number of periods five comma
and then the payment amount is zero comma and the present value is the 4 9 7 2 and enter
Now again we want it to be ten thousand
It's not
So we can have to change is 25
How about we change it to 24
That's better
How about 20
So we need to go down a lot
Let's let Excel do that
I'm going to put my cursor on any cell to use goal seek and we'll go to about data tab forecast what
if analysis we're looking for the goal seek goal seek and then we're going to say alright excel we want
you to change we want you to set this so to be ten thousand by doing something to the cell
In other words do whatever you gotta do to the 10 to 20 percent
Let's let's take a look about one more time
We're saying Excel we'd like you to change this cell and be 30 to ten thousand
The value by doing whatever you need to do to this cell which we know is wrong
We just want you to keep testing numbers till you find something that makes this thing be ten thousand
So we'll say okay
And it just tries everything says 15 percent
That's the one
So that's another way we can test this week hopefully
Give us an idea of the relationships between present and future value that two formulas and how they're
related and we can use goal seek and under either condition if we don't know basically the percentage
that's one format and a one tool that's going to be very useful to use what you really want to know
and Excel is you want to know the present value formula you want to know the future value formula and
you want to know the difference in calculating them between an annuity and a single value
So therefore you only need to know those two functions but you need to know how to use them for present
value versus an annuity if you know that then you're pretty good and you can solve a lot of other problems
including if you don't know the interest rate and you know everything else and you don't know the amount
possibly or the number of periods any one factor that you don't know you can then use goal C to kind
of figure out what the missing component will be
So the present value formula formula is very important to understand
idea of present value and future value we're going to have the data in the white sails we're going to
enter our data our answers into the blue areas up top and down below
Once again our goal is to just look at that relationship
Obviously the present value future value are related we're looking at the time value of money
So let's get an idea of those relationships with the Excel formulas the two formulas we want to know
are the present value and the future value
We're going to use the same function for the present value of one present value of an annuity
Therefore if we know the present value formula of one and an annuity the one formula for both the present
value of 1 and present value of annuity and the future value for both present value of 1 and present
value of annuity
We can in essence just know those two formulas and then the relationship between them and we can get
a lot of use from that information
So here's going to be our data
We've got the amount up top we've got the number of periods we've got the rate and now we're going to
figure out the present value with that data and then we just want to think of that that answer the present
value answer that we get we're gonna use to then calculate the future value just so we can see the relationship
between these formulas these amounts
Let's see what we're talking about here let's run the present value formula
I'm going to do that with a negative and we're going to say present value double click on the present
value
We're going to select the rate and then comma the number of periods which is gonna be five
Remember these are years we're going to say comma the payment amount is going to be zero because remember
this is a present value of 1 rather than in an annuity
The payment would be for an annuity comma
We're going to say the future value is going to be that ten thousand
So we're gonna bring that future value back to the one time amount five years so it's ten thousand in
the future what would it be in today's dollars
Select enter or hit enter
That's going to be the four thousand 972
So in other words five years later if we used 15 percent as the discount rate after that five years
would be in present dollars four thousand nine seventy two
Now we're going to use that four thousand nine seventy two and put that into our future value formula
So we're gonna say this is the present value and we're gonna have a number of years five years the rate
15 percent
Let's calculate our future value
The other formula that we're taking a look at
Within Excel this is going to be negative future value f v DoubleClick the future value
We're gonna take the rate which is 15 percent comma the number of periods five years comma we don't
have a payment because we're not talking annuity here comma we want to take the present value in other
words
This is today's dollars
How much would be equivalent for an amount ten years or five years out
And that would be of course the ten thousand
So you could see a relationship of course between these amounts you can you can better see the relationship
between these amounts or another way to see the relationship between these amounts is to run basically
a table to calculate each year
So if we were to do that we'd say OK what if we took years one two three four and five and then we had
the total and then we picked up the starting amount and we're going to say OK this is the present value
Let's start with the present value amount and then let's increase it by 15 percent each year
So that means each year we're gonna basically increase it by 15 and that means we're gonna have 100
percent plus 15 percent
So therefore the rate is going to be this 15 percent plus one 115 or want 100 percent plus 15 percent
And then let's say we'll get well what would happen after in year one
So it should be one to two three four and five
And we don't need a total
So after year 1 we're going to say this equals the prior period before 9 7 two times one hundred and
fifteen percent
In other words the 15 percent plus the 100 percent that's already there
One point one five
And that'll give us the 5 7 1 2
Then we'll do this again we'll say Okay here's the fit 115 percent let's take the 5 7 1 5 times the
150 percent for year two
That leaves us is with six thousand five seventy five
Okay let's do it again for year three this equals the prior number times one hundred and fifteen percent
giving us seven thousand five sixty two four year three
Now let's do this again we're going to say this is the prior number of times one hundred and fifteen
percent eight thousand six ninety six
Same number here
And then one more time prior number times the 115 gives us the 10000 so you can do a calculation like
that
We could see the relationship between the present value and the future value
Getting a better idea of it
Another way to consider this is to use gold seek to find some other area gold see going to be a function
that we can use within Excel
So let's take either one of our data here are data set and let's say that we're going to solve for the
So I want to solve for the rate and say that that's going to be the unknown rather than in the first
case the present value
So if I'm going to solve for the rate and I'm going to use gold seek to do it what I'm going to do is
I'm going to guess the rate I'm just going to pick a rate at random then I'm going to use the present
value formula
Then I'm going to say hey Excel would you used gold seek to figure out what the rate should be to get
the present value to know what what I know that it should be
So we know the present value is going to be the four thousand four 9 7 2
I don't want to plug it in here I want to use a formula to do so however and I want to hit it
I want to enter a rate just to start off with
So I have some value for the rate
So I'm gonna say all right what if the rate was you know 2 2 percent let's put in our present value
formula this equals or let's say negative present value double click the present value
We want the rate 2 percent which is guessed that's just a guess comma a number of periods five five
years comma the payment is zero comma and then the future value we're gonna say is ten thousand
Now when we say enter we're hoping it's gonna be four thousand nine seventy two it's not because we
just guessed this rate now we could try to figure out OK what what would this rate be
To get this to four thousand nine seven What if I chose four percent right
And we could keep doing this
It's getting closer
Let's go ahead and let Excel do that
We'll do that by going up top
We're gonna go to the Data tab up top
We're gonna go to the forecasting section
We're gonna go to the what if analysis and go to Gold seek goal seek
Now we don't have to be on any particular cell here to use goal setting we're just going to say right
Excel This is what we would like you to do
We'd like you to set this cell to be what we know it should be
Set it to the value of 4 9 7 2
I want you to do that we'd like you to do that by changing this cell
So let's review that one more time or saying hey excel we know what this should be
It should be four thousand nine seventy to change this cell B 24 to 4000 972 please by adjusting the
related cell that's in a related function of related formula this rate this 5 percent just do whatever
you gotta do to make that 5 percent in Excel we'll actually test everything to figure out what it should
be
So we'll say OK
And that's at 15 percent
There it is
So now we have that that 15 percent as our we can do a similar type of calculation what if we said that
this is going to be our present value amount and now we use the future value formula to figure out the
rate
And we're just going to use the goal seek in the same format
So we know the future value has to be ten thousand
That's going to be the future value but we don't know what the rate is let's pretend it should be 15
percent but we don't know that
So we're gonna do the same thing like what if I guess the rate what if I said Well I think it's going
to be 25 percent on the year I don't know
And then use future value to figure out what this rate should be
So now we'll use the future value formula same kind of goal seek analysis we'll say rightNegative future value double click for future value
We want the rate which we don't know we just entered a random number comma number of periods five comma
and then the payment amount is zero comma and the present value is the 4 9 7 2 and enter
Now again we want it to be ten thousand
It's not
So we can have to change is 25
How about we change it to 24
That's better
How about 20
So we need to go down a lot
Let's let Excel do that
I'm going to put my cursor on any cell to use goal seek and we'll go to about data tab forecast what
if analysis we're looking for the goal seek goal seek and then we're going to say alright excel we want
you to change we want you to set this so to be ten thousand by doing something to the cell
In other words do whatever you gotta do to the 10 to 20 percent
Let's let's take a look about one more time
We're saying Excel we'd like you to change this cell and be 30 to ten thousand
The value by doing whatever you need to do to this cell which we know is wrong
We just want you to keep testing numbers till you find something that makes this thing be ten thousand
So we'll say okay
And it just tries everything says 15 percent
That's the one
So that's another way we can test this week hopefully
Give us an idea of the relationships between present and future value that two formulas and how they're
related and we can use goal seek and under either condition if we don't know basically the percentage
that's one format and a one tool that's going to be very useful to use what you really want to know
and Excel is you want to know the present value formula you want to know the future value formula and
you want to know the difference in calculating them between an annuity and a single value
So therefore you only need to know those two functions but you need to know how to use them for present
value versus an annuity if you know that then you're pretty good and you can solve a lot of other problems
including if you don't know the interest rate and you know everything else and you don't know the amount
possibly or the number of periods any one factor that you don't know you can then use goal C to kind
of figure out what the missing component will be
So the present value formula formula is very important to understand
No comments