Before we solve these problems in Excel, I wanted to just briefly explain how Excel deals with the signs or cash flows. Let's consider this blue circle over here to be the bank, or loan, or mortgage. Anything that goes into the bank or the loan from you is a negative monetary value. Anything that you get from the bank is a positive monetary value. So any sort of payment, a present value or a future value that is out of the bank cash flow into you is a positive value. Another way of looking at this is instead of that blue circle being the bank, this is you. So any payment or present value, future value to you is a positive value. And any payment or disbursement, any other values that go out of you is a negative value. So let's talk about how to solve these formulas in Excel using functions. In Part 1 of the course, I talked briefly about using financial functions in Excel, so this is a little bit of review. In order to calculate the future value of an investment, we can use the FV function, the future value function. And that essentially just uses this equation here, F = P (1 + r) raised to the N. If you rearrange this mathematical equation here on the left, if you divide both sides by (1 + r) raised to the N, we can solve for P. That means if you know the future value and you know the interest rate and the number of compounding periods, you can always calculate the present value. And this is the present value or the PV function in Excel. And we're going to use this here in an example. So these functions in Excel enable us to determine a future value's worth in terms of its present value and vice versa. Let's go ahead and go over to Excel. I'm going to show you how we can use these Excel functions to solve some of the examples that we've already talked about in the screencast. First of all, let's consider simple interest, and this in a file called interest.xlsx. So the first one here is simple interest. There's no simple interest formula in Excel, you just have to use that equation P times r times t. And what I'm going to do here is I'm just going to select those, I'm going to go up here to Formulas > Create from Selection. You guys should all remember how to create names. And so cell B3 is named P, B4 is r, and B5 is t. So I can just simply write P*r_* t. And that will determine then in five years time at simple interest the bank has earned $250. Let's talk about compound interest now. The first example we've already talked about, we solved this just using algebraic equations. You borrow $1,000 at an annual interest rate of 5%, interest is compounded annually. What will the loan balance or the future value be after five years? Again, if you're trying to calculate a future value, we're going to be using the FV function. We need to know the principal, the interest rate per year, and the time in years. Let's start with the interest rate. That's easy, it's given to us. It's a yearly percentage rate, so the time periods in compounding periods is just going to be five years or five compounding periods. The principal is $1,000, but we need to make sure that we're using the right sign. If we're going to borrow $1,000, then that's money that's coming towards us, so this is going to be a positive value. So we can just type in 1,000 because we're getting that from the bank. Now, the future value, this is the amount that we're going to have to pay back to the bank. And so you can imagine, when we do the calculation, this should be a negative value because we have to give it back to the bank, it's being disbursed from us. There's a future value function in Excel, we can just type in FV. Now, I've already used the names P, r, and t above, so I'm not going to use names here. I'm just going to click on the relative references. The rate is our 0.05, the number of periods, nper, is going to be our time. The payment, now, we're actually not making any payments, so I'm going to leave that as 0. We'll consider payments in some subsequent screencast. But the present value here, PV, is our present value there, that's the amount that we have borrowed. And then I can close this and I press Enter. And that means that in five years time, we're going to owe the bank $1,276. Let's take a look at the second example. The second example, you save $5,000 at an annual interest rate of 5%. Interest is compounded monthly. What will be the balance or the future value after four years? So let's first put in the interest rate, 0.05. The time, so the time in years is going to be four years. It's important to realize that compounding is not done every year, it's done monthly. So we're going to have to multiply that 4 by 12, as we did previously when I showed you how to do this by hand. Now, the principal, you save $5,000. So you're actually giving the bank money, which makes it negative, negative $5,000. The interest rate is 0.05 per year. And so now we can use the future value function, the rate. And you have to be really careful here because, again, it's the rate per compounding period, the compounding period is a month. It's 5% per year, we divide by 12 to make it per month. The number of periods here is going to be four years, but we have 12 months per period. We're not making any payments on this. And our present value, the loan amount, is negative 5,000 that we have invested. And then when I press Enter, this means that we have a positive amount that we can withdraw from the bank. So that positive 6,100 comes to us, which is a positive amount after four years. I've got another example here. How much money do you need to put in a savings account today to have $8,000 in ten years with an annual interest rate of 4%? So we have a present value, that's what we're trying to calculate, is the present value. How much money do you need today, so that's going to be a present value, to put in a savings account to have $8,000 in ten years? That's our future value. Our time is ten years, we have an annual interest rate of 4%, but it is compounded monthly. So we have this equation that we can rearrange. If we want to do this by hand, we can plug in $8,000, we can divide this, and we get $5,366.13. Let me show you how we can do this in Excel. So our future value, we want to have $8,000. And we want to put a principal or present value into the bank today, that's a negative amount. And then in the future, we're going to get this back. So we're going to get $8,000 back in the future, which is a positive number. The interest rate per year is 0.04, the time in years is ten years. Again, we can use Excel functions. In this case, we're going to use the present value function. The rate is 0.04 divided by 12 because it's compounded monthly. The number of compounding periods is going to be 12 months per year times our time here. So that's 120 months or compounding periods. We're not making any payments towards the savings account, nor are we taking any disbursements or payments from the savings account. The future value, we want this to be 8,000. In all of this, I'm neglecting the type because the default is 0, which means that it's compounded at the end of the month. And then I can close parentheses and press Enter. And what this means is that today the present value, it's a negative amount. That means we need to put $5,000 into the bank today in order to make $8,000 in ten years' time. And that's exactly what we got doing this by hand, $5,366. So in summary, in this screencast, we considered simple and compound interest. Compound interest is more common. We considered present value and future value calculations with no payments or disbursements. So we just lump sum, we either take a lump sum loan or make a lump sum payment towards a savings account. We'll consider situations with payments and disbursements, these are also known as annuities, in subsequent screencasts. So hopefully you learned a little bit more about simple and compound interests and how we can implement these formulas in Excel.
