1. FV & PV in Excel
So back in the Excel workbook you can see the demonstration that we
completed in an earlier lesson. So if you just scroll down we can
see that we've got some templates set up to help replicate the example
that we just saw in the video. So I'm just gonna scroll down
and in the first example we want to calculate the future value when
we know that the present value is 100,
the annual compound interest rate is 5% and the term is 3 years.
Now here is the algebraic formula that we introduced in the lesson, the
future value equals the present value times 1 plus I all raised to
the power of n. So let's see if we can get the same
future value of 115.76. So I'm gonna start by typing equals the present
value times open bracket 1 plus the annual compound interest rate of 5%
all raised and to do the raised button in Excel you hold the
shift, press 6 for the arrow up to the power of 3 years.
And when I hit enter you can see that I've got 115.76. So
you can see the handy little formula there in cell D46 just to remind ourselves
what the formula is. Now really usefully in Excel, Excel has something called
the future value function or the FV function.
And so if I start typing FV, the FV function comes out and
if I just hit tab the bracket opens so you can see what
the inputs are. The first input is the rate and the rate in
this example is the interest rate of 5% so let me just reference
5%. NPER stands for the number of periods and in this example the
number of periods was 3 years. Now along the way there's no payments
being made or received, we're trying to figure out how much we have
in 3 years time so there's no annual payments to worry about so
in this specific example I'm just gonna type 0 for no payments.
Now what's the present value? Well we know
the present value, the present value is the 100 that we have up
in cell D41. And finally we have type and we've got 2 options
here, either 0 or 1. 0 means any cash flows paid happens at
the end of the period, the end of the year
and that's really the most common scenario when we do these sorts of
calculations. Although if there was a payment at the start of the year
we would input 1. So I'm just gonna type 0 here because
we want to know how much we've got at the end of each
year and then when I hit the close bracket and just press tab to
tab across, we can see that we get the same answer but it's
a negative, negative 115.76. And that's because Excel is just assuming that
the present value cash flow is moving in the opposite direction to the
future value cash flow. We either borrow 100 today so we receive 100
and then we repay 115.76 in 3 years time so there we have
the cash flow moving in the opposite direction. So there is a couple
of things that we could do to fix this up, we can just
activate the cell by pressing F2 and we can just go to the
start of the function and we can just put a little negative sign
in front there and that'll just change our answer to be a positive
number. And so there we have, we've proven the future value of 115.76. So
what about working back the other way? What about when we know the
future value and we want to find the present value, in other words
we want to do some discounting? Well we know,
let's use the same numbers that we can see on the screen there.
So 115.76 is our future value. So in other words when we discount
that at 5% annual compounding for 3 years, we should get a present value
of 100 because it's just doing the opposite to what we've just looked
at. We've got the formula written handily there in cell H46, the present
value equals the future value times 1 divided by 1 plus I, close[all raised
to the power N]. So there's a couple of brackets there that we
have to make sure we put in the right place. So the future
value is 115 times, open bracket, 1 divided by, and then there's another
open bracket, 1 plus the compound interest rate, the annual compound interest
rate of 5%, all raised to the power of 3, because it's 3 years,
and we want to make sure that we close the bracket there.
So hopefully having that formula written just underneath where you're inputting
that formula, having the equation written out there, just helps you remember
a little bit. And if I enter, you can see that yep,
we get 100, so that's spot on. Now unsurprisingly, Excel has a future
value function, it also has a present value function. So I can start
typing that in. This time I'm gonna put my negative in at the start
so I don't have to go back and do that, and I'll type
PV and open bracket. So very similar inputs, we have our rate of
5%, we have our number of periods of 3, there's no payments being
made or received along the way so again I'm gonna input 0, we
know the future value, 115.76, and the type is 0 again.
And if we end up closing the bracket, and just hitting tab to
tab along, we can see that we get a present value of 100.
2. Let's practice!