I'm just going to give you a brief introduction to Excel's financial functions later on in part two of the course. You will explore some more advanced financial functions and how you can Implement these financial functions in spreadsheets. But today I'm just going to go over a couple of simple financial functions. Just a couple examples, you borrow $20,000 to be repaid using a common 15 year payoff period. Your loan charges an annual rate of 5% compounded monthly. What is your monthly payment, and how much did you pay for your loan? Maybe you deposit $5,000 into a savings account, pays 2% per year, compounded annually. If all the money is allowed to accumulate, how much we have in 10 years. And the last one, if you want $20,000 in 5 years and you can make payments of $200 each month with an annual interest rate of 3.5%, how much do you need to put down today? So I'm going to show you how we can answer these questions using Excel. So Excel's got lots of financial functions. What's nice about Excel for financial calculations is not necessarily the functions but financial functions certainly help you but also the organizational aspect of Excel is really valuable. So in part 2, I'm going to cover more of Excel's financial functions as I alluded to earlier, talked about amortization schedules, different types of loans and lots more. But today, in this screencast, I'm going to talk about the payment function PMT, the future value function FV, and the present value function PV in Excel. The PMT or payment function calculates the periodic payment for a loan and it has three required arguments rate, nper and pv. There are two optional arguments indicated by square brackets. The rate is the interest rate if it's an annual percentage rate or APR, be sure if you're making monthly payments if it's compounded monthly that you're dividing by 12. Nper is the number of payment periods. So if you're doing a 15-year loan and you're paying monthly payments, then the total number of payment periods would be 12 times 15, or 180. PV is known as the present value, also known as the principal, FV future value, that's optional. It's the future value that you'd like the loan to have, the default is 0 but it certainly doesn't have to be. And the type is usually 0 so it's 0 for payments due at the end of period, 1 for payments due at the beginning. It's really important to note that anything you get from a loan or a bank is a positive monetary value in Excel's financial functions. But anything that you put towards your loan or into the bank is going to be a negative monetary value. So let's work through this first example, you borrow $20,000. So that's going to actually be your present value or your principal and that's out of the bank. So it's going to be positive, to be repaid using a common 15-year payoff period, your loan charges an annual rate of 5%. So the annual rate is 5% but it's compounded monthly. And the question is, what's your monthly payment and overall, how much did you pay for the loan? I've got this starter file available. The first question, we are basically asked for a monthly payment. So if you're asked for a payment, you're trying to solve for a payment you probably want to use the payment function. So I'm going to put in PMT. The first argument is our rate, which is our annual percentage rate divided by 12 because we're compounding monthly. The number of period is going to be the number of years times, there are 12 months per year. So you have to make sure you're multiplying by 12. The present value of the loan is how much we took from the bank which is $20,000. The fourth argument here is the future value. After we've paid off this loan, we want that to have a value of 0. So you can either enter 0 or you can just leave it blank. And in all of the examples that I'm going to be doing, the type is going to be left off because the default is 0, we make the payments at the end of the month. So when I press enter, we see that we have $158 in parentheses. Make sure that if you're working with currency, you've got this dollar sign selected and you have currency. So anything in parentheses means that it's a negative amount. Negative payments mean that you're paying the bank back. So every month, you're going to have to pay the bank $158 for the next 15 years. Now the total paid, we have 12 months for 15 years, that's a total of 180 months. And we're going to multiply that by, I'm going to do the negative of the payment just to make this a positive number. So we have paid $28,500. To calculate the total interest that we have paid the bank, we can subtract from the total that we've paid the bank over those 15 years, the initial loan amount that we got of $20,000. So over those 15 years, we paid the bank nearly $8,500. Now if you need to calculate the future value of an investment, whether or not you have payments, you can use the FV function. The FV function has three required arguments. We have rate, number of periods, the third argument is payment. So this is the payment made each period. Now if it is omitted, you have to provide the fourth optional argument. So a lot of times you're not making payments on a loan, maybe you're just putting a one-time amount that you're going to invest. And so sometimes you don't payments if you leave that out, then you need to provide the present value. So I've got an example here, you deposit $5,000 into a savings account that pays 2% per year compounded annually. If all the money is allowed to accumulate, how much we have in 10 years? So working over here in Excel. I can put the present value, so this is actually a savings account. So, anything we put into the bank is going to be negative. This is -5,000. The interest rate is 2% per year, and we're going to compound annually. So that's a little different than the previous example. If all the money is allowed to accumulate, how much will you have in 10 years? So we can use the future value function, the rate is going to be 0.02. Now, I'm not dividing by 12 because we're compounding annually. The number of periods will just be the number of years and the payment so we're not making payments to this account, so that's going to be 0. Now remember, if you put in 0 for the payment, then the fourth argument is required. Otherwise, you're not investing anything or you're not putting any payments into the bank. So I'm going to put in that present value of our savings and then I'm going to press enter. So that means that the future value after 10 years, $5,000 will become about $6100. And the amount earned, we can just take our future value and we can subtract adding a negative number just to get the difference there. That means we will have earned about $1,100 in 10 years. The last financial function in Excel that I'm going to talk about is the present value function. So this calculates the present value of a loan or investment and it's got similar arguments that has the rate. It has the number of payment periods, it's got the payment that is made each period. Again, if you if you omit that, then you have to provide the future value. So let's go through an example here, if you want $20,000 in 5 years. So that's going to be a future value of positive 20,000 and if you can make payments of $200 each month. So those payments are going to be negative with an annual interest rate of 3.5% compounded monthly, how much do you need to put down today? So we're trying to calculate how much to put down today, that's a present value. And if you're putting it into the bank, it's going to be a negative number. So in Excel, I can put my future value. My annual percentage rate is 3.5%, I can make a payment of $200 a month. So that's a negative value, and then I can calculate the present value. So this means, how much I need to put down today in order to get that $20,000 in 5 years making $200 payments every month. So the rate is going to be our annual percentage rate up there divided by 12. The number of periods is going to be 12 times the number of years, the payment I can make is right there. It's our $200 and then the future value I want, $20,000. So when I press enter, this means that I need to put down about $5800 today. So that's just a brief introduction to the financial functions in Excel. And if you stick around for part 2 of the specialization, then you will learn a lot more about using Excel's financial functions in creating things like amortization schedules, payment plans, loan calculations, and so on.
