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.

Â