0:03

In this video, Alex is going to help answer some client investment queries,

using Excel financial functions.

One of the problems with working on how much

an investment will be worth after a certain amount of time,

is calculating compound interest,

which can be a complex calculation.

For example, Kevin wants to invest $500,000

now and then add a further 15,000 to his investment every year,

for the next 25 years.

He's been given a fixed interest rate of five percent.

The calculation to work out what his investment will be

worth in 25 years time is shown over here.

A is an initial upfront investment and

P is a regular investment at the end of each period.

All investments earn a rate of interest R,

for a period that is N periods long.

All of these work together in a mathematical formula.

And as you can see, it seems a bit complex.

Fortunately, we don't need to worry about this complexity because Excel gives us

a range of financial functions that will solve these more complicated problems for us.

Before we have a look at them though,

there is some terminology we should become familiar with.

First of all, future value.

That's exactly what we're going to be calculating here.

Future value refers to how much an investment will be worth in the future.

It can also relate to the residual value alone but we'll look at that in the next video.

Present value is the amount you're going to invest or borrow at the current time.

It is also often referred to as the principle.

The rate, and Excel assumes it will be a fixed rate,

is the rate you're going to earn per payment period.

So in this case,

we're going to be investing a new amount annually.

So this is going to be an annual rate.

But if you are borrowing say for a car loan over four years,

and you're going to repay monthly,

then your rate would be a monthly rate.

N refers to the number of payment or repayment periods.

And again here, that's going to be 25.

But in our car loan example,

it will be 12 months over four years that would be 48 repayment periods.

And finally, PMT refers to the payment amount.

That refers to a regular payment we'll be making monthly or annually,

and again, Excel assumes that the payment amount will remain constant.

One other thing to bear in mind and that is the signing convention that Excel uses.

Any money flowing away from you,

so in this case when I invest the 500,000,

I'm putting that in the bank or into shares,

so that money is going away from me that shows as a negative.

Any money coming to you shows as a positive.

So with those out of the way,

let's see how we can calculate the value of this investment at the end of the 25th year.

The thing we're trying to calculate is the future value.

So we're going to use the future value function.

We start by typing,

equals FV, for future value, and then tab.

Now, the future value has three mandatory arguments and two optional ones.

Let's look at each of them. First of all, the rate.

Now again, this must be the rate per payment period.

In this case, we're paying annually.

We're looking at an annual interest rate of five percent,

then type a comma.

The next thing it wants is the number of repayment periods and that is our 25.

It then wants to know how much we're going to invest annually,

which is our payments amount.

So that's our 15,000 which we put a minus in front of.

You don't actually have to have an initial investment. It's optional.

If you leave it out, it seems zero.

But we have an amount,

so we're going to click on the 500,000.

And then finally, you actually have an option to choose when

the payment is going to be made: at the beginning or the end of the payment period.

Usually, we do it at the end, so that's the default.

If you leave this off,

it will assume zero.

However, if you want to pay at the beginning of the period,

you need to type in a one or select beginning of period.

Just this time, we will select the end of the period.

But in the future, we'll just leave it off and it will default to zero.

And now, when we press ENTER,

there is how much Kevin's investment will be worth in 25 years time.

Let's now look at a slightly different example.

On the next tab, Xiaoli,

also wants to invest some money but she knows that at the end of the 25th year,

she's looking to have earned one and a half million.

What she wants to know is how much does she have to invest upfront to achieve that goal.

This time, we have our future value.

What we're trying to calculate is our present value.

So we're going to use the PV function.

So I'm going to type equals PV,

for present value, and tab.

Now, she is also getting a rate of five percent,

so we're going to click on that.

And she's also investing for 25 years,

so there's a number of payment periods.

She's going to invest 10,000 at the end of each payment period,

so we're going to click on our 10,000.

And we would like to see,

at the end of that time,

a return of one and a half million,

so that is our future value.

Now, you'll notice that that is also optional in this situation,

which means you can go for a future value of zero.

Of course, with a future value of zero,

that would not give us much of a return.

So this investment we're going to select our million and

a half and we're not going to worry about the time this time.

But again, you have the option to choose the beginning or the end of the payment period.

We're just going to close our bracket and click enter,

and there's her answer.

She has to invest just over $300,000 now,

to achieve that one and a half million dollar goal.

Alright, one last example.

This time our friend, Pierre,

also has an investment goal.

He has only been offered a four percent interest rate

but he's going to invest for 30 years and he's

hoping to invest $50,000 now and to finish up with an investment of one million dollars.

We want to know how much he will have to pay in each period,

to achieve that goal.

This time, we have our future value,

we have our present value,

but what we need is our payment.

So we're going to click into B10 and type equals PM and tab to select payment.

Our rate is four percent.

Our number of period is 30.

Our present value is 50,000 and the future value we hope to achieve is one million.

We can now close our bracket, click enter,

and here is the amount,

he'll have to invest at the end of each period in order to achieve that goal.

So you can see Excel's financial functions are extremely easy to use,

as long as you make sure that you get the right values into each of those arguments.

Now, we focused very much on investments in this video.

In the next video, we're going to look at

an example of how we can use the payment function,

to see the payments we need to make on a loan.