In previous screencasts, I showed you how we can make payments to a loan, and we can pay down or reduce the principal, the loan amount over time. In this screencast, we're going to look at the flip side where you have a savings account and you can either have payments or disbursements to that saving account. I'm going to show you how we can solve these types of problems using Excel. What if we make payments to a savings account over time? We start with some principle shown here and over time is going to earn interest. But if we want to add more money, we can make a monthly payment. Payments to a savings account don't have to be done just at the beginning when you start that savings account, so we put a monthly payment in there, and the principal or the current savings amount is going to increase. Then the next month, we're going to earn a little bit more interest because we've added to that savings account balance, and so we can grow the size of that savings account over time. We can keep going and going, and we can get a lot more money in that savings account. There are three things of interest. The first is the present value, that's also known as the principal or the current loan amount, the future value, what the savings will be worth in the future, and the payments that are made or the disbursements that are withdrawn from the savings account. This is similar to amortization, where we need two of the three variables above in order to calculate the third of those variables. Oftentimes, one of these two variables will be zero. We can use in Excel, future value, present value, and payment functions like we have before. So let's work through a couple examples here. The first example, we invest $5,000, then put in $500 per month. Interest is compounded monthly at a rate of four percent. How much will we have in the savings account after 10 years? I've got this in a file called savings account with payments. The first example is shown here, we invest $5,000. So that's the amount that we put in the bank. That's actually a negative cash flow, we're going to put in negative 5,000. I left these blank just to give you guys practice in using the correct sign of the principal and if there are different things that are involved in these functions. Our annual interest rate is 0.04, that's compounded monthly and we're going to invest for 10 years. The amount or the payment that we're putting into the savings account is $500 per month, so that's what we put in there. I'm just going to put a per month in there, so we know that that's per month. Now to calculate what that savings account will be worth in the future, that's a future value, I can use the FV function. The rate is going to be our annual interest rate, but that's compounded monthly, so I'm going to divide by 12. The number of compounding periods is going to be 12 per year times 10 years, and now here we can put the payment. The payment is going to be $500 per month, and again, that's negative because we're giving that money to the bank, and the present value is going to be our principal up here, our type is just going to be zero, so we're going to leave that off. When I press "Enter", that means if we continue to do this for 10 years, $500 per month, and we add that to the initial investment of 5,000, we're going to have 81,000. The second example, we need to have $20,000 in 10 years. Maybe we're saving up for a kid's college fund or something else, we can invest $5,000 today. So that's going to be our principal, our present value is 5,000, we need to have 20,000, that's going to be our future value. So we're trying to solve for is the payment, what monthly payment is required if the interest rate is 3.5 percent and compounded monthly? So let's go ahead and solve this in Excel. Our principal or the present value is what we can invest today, that's 5,000, now is that going to be positive or negative? That's right. That's an initial investment, that's going to be negative $5,000, the annual interest rate is 0.035 compounded monthly, we have 10 years, the future value, we want this to have a value of $20,000 in 10 years. Now, is the future value of $20,000 is going to be a positive amount or a negative amount in terms of Excel's financial functions? That's right, we're going to get that back from the bank in 10 years, that's positive 20,000. We can use the payment function here to calculate the monthly payment required in order to get this scenario to work out. Our rate is 0.035 divided by 12, the number of payment period is 12 per year times 10 years, because we're compounding monthly. Our principal or present value is our 5,000 up here, and our future value is going to be 20,000. So I can go ahead and press "Enter", and that means that every month, we only need to invest $90 to take our initial investment of $5,000 over 10 years to get $20,000 for this particular savings account. Now we can also take disbursements or withdrawals over time. Here we've got our principal over time, it's going to earn interest in that month, but then we can take out a monthly disbursement. I'm going to assume that the disbursement we take is less than the interest, although it doesn't have to be. We take our withdrawal, after two months we have a certain amount, we might take another disbursement, and we can keep going. At some point, if we take enough of these disbursements, even though this is our money, it's our savings account, it's not a loan, it's a savings account, at some point, we're going to remove and we're going to take away all of the money in our savings account. Let's work through an example. We start with $10,000, and wish to withdraw a quarterly amount over five years, at which point the savings account will be empty. Our present value is 10,000, the future value's going to be zero, and what the question is here, how much can we withdraw each quarter over these five years if the annual interest rate compounded quarterly is four percent? So let's work through this. We have a present value, that's $10,000 in a savings account, so we have disbursed that, that's going to be negative 10,000, our annual percentage rate is 0.04, we're compounding quarterly years, keep that in mind. Our future value is going to be zero, we can just use the PMT function, the rate is 0.04, but that's per quarter, that's compounded quarterly. The number of payments per year is going to be four times the number of years, so that's a total of 20. Present value is our negative 10,000, our future value is zero, and when I press "Enter", this is going to tell us the amount that we can withdraw each month, so we withdraw our $10,000 down to zero over those five years, and that's 554, this is a positive value because it's money that it's cash flow into us. I got one last example here, it has a twist to it. We start with $10,000 in a savings account, we wish to withdraw $250 each quarter until it's empty. Very similar to the previous example, we start with 10,000, we withdraw until it's empty, but in this case, I have a fixed withdrawal rate each quarter, $250, and we want to determine how many total disbursements we're going to be able to take. Now to do this, Excel has a nice NPER function. It calculates the number of periods for an investment based on periodic constant payments. Also assumes a constant interest rate. The arguments are very similar to what we've had in the past. We have a negative $10,000 present value, our starting amount, annual interest rate of 0.04, now the payments they're actually going to be withdrawals of 250, so that's positive 250. The number of disbursements is what we're trying to find. We can use the NPER function to determine the number of periods that are going to make this happen. The rate is our 0.04, again, we have to divide by four because interest is compounded quarterly. The payment amount is 250, the present value is negative 10,000, and our future value is going to be zero. Then I can go ahead and press "Enter", and this says that, it's going to take 51 periods, or payments or disbursements in this case, in order to go from 10,000 to zero under these circumstances. We can easily convert this to the number of years. I can take the integer part of the number of disbursements, that's the number of quarters and divide by four. What that does is, it converts that to the number of years, so it's about 12 and three-quarter years, 12 years and nine months, and we have a little bit extra, we are going to have about a third of a payment left for that 10th payment of the 12th year. So hopefully, you learned in this screencast how we can deal with savings accounts, with payments and disbursements.
