Welcome to week four and welcome, Nicky. Hi, Prashan. This week, we are going to be looking at some of Excel's financial functions. So Nicky, what financial functions are we going to be looking at this week? Well, there so many to choose from, but we are going to go with the ones that are used most frequently. So, we are going to look at the Payment function, Future value, Present value, Net present value, and Internal rate of return. And the good news is, all of these functions are really easy to use. I think the only thing that's difficult is understanding, how and why we would apply them. So let's say I borrowed a million dollars from my business to buy some assets, and I've got a loan that's 20 years long, and I know the interest rate is five percent. I need to work out the payments on my loan. How can Excel help me? Excel's got a really brilliant function for that, it's called the PMT or Payment function, and it will give you those loan repayments. And what is brilliant about it is the actual maths to calculate compound interest is very complex, and the payment function takes all that complexity away from you. The only thing is to understand the arguments in the brackets, and I'll just quickly rattle through them. First up, is the RATE now that is the Period rate. So if you repaying annually, that would be an Annual rate, but if you repay monthly, you need to take that annual rate and divide by 12. The second argument is, NPER and that stands for your Number of Periods, and that's the number of repayment periods in the loan. So again, if you repay monthly, you're going to need to take your years and times by 12. The next one is the amount of money you are going to borrow, which is abbreviated to PV for Present Value, and off that there's an optional argument which is FV for Future Value. And that was if you were going to have the residual amounts in your loans, so you were going to repay the whole amount, that is how much you would have left at the end. So let's say I want a million dollars target in 10 years time and the interest rate is five percent. How can Excel help me work out how much I need to invest today so that I have the target in the future? Well the great thing is, a lot of those arguments I have just been talking about are actually functions in their own right. So what you're asking about is, what is the present value? And there's a PV function that will calculate exactly that for you. And if you were to do the problem the other way around and say, "Okay. I'm going to invest this amount, what will my final investment look like?" You can use the FV which is your Future Value. So basically, what you're saying is businesses need to consider the time value of money. I could have an investment project which needs a million dollars upfront, and I could expect to have $600,000 by the end of the first year. Another $600,000 of revenue by the end of the second year. So two $600,000 of revenue, add up to 1.2 million, which looks like it's more than the million dollars I put up front. But actually, that 1.2 million dollars revenue is occurring in the future. I need to work out what that 1.2 million dollars over those two years is actually worth today, so that I can compare the two. How can Excel actually help us with this? So for that, Excel has a Net Present Value function, which we just write as NPV, and again very straightforward to use. All you need to do is specify your cash flows and your discount rate. And I think, the only difficulty people have is working out where they get that discount rate from. And actually, I was hoping you would explain that discount rate for us. Well, there are so many ways to calculate the discount rate. But usually, the discount rate is your firm when they're borrowing money from a bank, how much interest does the bank charge you? So, if the bank is charging you five percent interest when you borrow money, we would use the discount rate to be five percent, which then gets on to something called the Internal Rate of Return. When I invest in a project, when my company is running a project, we would like to know what rate of return that investment is generating, and that's known as the Internal Rate of Return. Now, can Excel help us with this? Of course, Excel has an IRR function which does exactly that. To use it, you just specify your cash flows, so very straightforward. But there is something to bear in mind, those cash flows must be sitting next to each other in the workbook. So bear that in mind, when designing your worksheet. So basically, this entire discussion highlights that dates are very important when it comes to financial calculations in Excel. We will need to set up a loan schedule and we need to set up the exact dates. How can Excel help us with this? Well, excel has a fantastic range of date functions. We have looked at a few previously, but we are now going to look at some more financial based ones. So we're going to look at the End of Month function, which will give you the last day in any given month. And we are also going to look at the EDATE function, which allows us to get a particular period after a certain date. So you get the next month or the next year. So some really great little date functions to help us with that. And the loan schedule we were talking about before, let's say, we borrowed money for a car for our business. And it cost us $50,000. But it's actually going to depreciate over time, because of wear and tear, because the car becomes obsolete, how can Excel help us with this? Excel actually has quite a range of depreciation functions we can choose from. And in the videos, we are just going to look at three of the most commonly used ones. So we are going to look at Straight Line Depreciation, Sum of Year Digits, and the Double Declining Balance. So, lots to look forward to in terms of financial functions and date functions in Excel. Thank you so much Nicky. Now, we've got some practice videos coming up for you. Make sure you download the Excel workbooks, so that you can work alongside us step by step. Test yourself with the quizzes and make sure you check out the practice challenge, so that you can test your new Excel skills in a totally different context. Make sure you check out this week's tool box as well as this week's great ninja tip. Now, it's over to you.
