Description: In the article which centers on retirement calculator, the author teaches you how to use the Future Value function in excel in order to calculate how much a savings or retirement plan will be worth in so many years. You will learn how to do this on an annual basis and how to modify that to work for any number of deposits per year or period.
In this tutorial I’m going to show you how to use the future value function and Excel in order to figure out how much a series of annual deposits are going to give you in certain amount of time. If you have a savings or retirement plan, you want it for 40 years, you’ll get 5 percent and you deposit so much every year, how much is it going to be worth?
This is a very basic example, it’s showing you how to use the future value, I have two sets, I can do this annually, what it’s going to do is if you deposit so much every year at the end of the year or the beginning of the year, then down here I assume that you deposit so much monthly, the point is to show you what you have to change to change the amount of periods.
I have the years, you need to put in how many years you want to have it, you have the rate, you’ll get the starting amount, the starting amount is how much you already have any account or the retirement plan when you start the calculation.
You have the annual deposit, the only place you need the formula to get the value is over here value in years, that’s the only place you’re going to have, it’s going to be a future value formula, let me show you how to calculate the value of a series of investments in the future.
All you need to do is to put in equals, FV for future value, open parenthesis, the first thing we need rate, it’s going to be in this cell, comma, the next thing we want is the number of periods, that’s going to be equal to the years, the next thing we want is our payment which is how much we’re going to pay into it every period, that will be our annual deposit.
The next thing is the present value, the present value in terms of the savings of retirement plan is going to be the starting amount in the account, how much you already have in there is going to be the present value, I’m simply going to click the cell, the last thing is the type of investment.
Are you going to be putting your money in at the beginning of every year or at the end of every year? If you’re going to do it at the end of every year, simply put a zero there, that is default, if you leave that blank, it’s going to default to assuming that you put your money in at the end of every year.
Close the parentheses, hit enter, we have zero, we have the future value if we put our money in at the beginning of the year, I’ll show you that the only difference is the zero, everything else is the same, let’s try this out to see how it works.
We’re going to have an investment for 40 years, it will give us 5%, we have 10,000 in the bank, we’re going to put 1,500 in every year for the next 40 years, one thing to notice before I explain it is that this is red, I let you see that on purpose because it’s one of the values, future present value has to be negative for the calculation to work, but the point is that is a positive number, the way we get it to look positive is before the FV function simply put a minus or a negative sign in there, hit enter and it becomes positive.
Notice that if you put your money at the end of the year, you have less money at the end of the 40 years than you deposit the money at the beginning of the year, because you get to earn interest more interest on the money if you put it in at the beginning of the year.
If you do at the end of the year, that’s how you can calculate the future value for annual payments, now if you want to do it monthly, you have to change a little bit not too much, look down here, what we’re going to do is to change the periods.
Since it’s in monthly, we’re not using years but periods, so 40 times 12, 480 is in the periods, we need to figure out what the interest rate is going to be monthly instead of yearly, the yearly interest rate is 5%, but what is the monthly compounded interest rate?
I’m going to explain how to get that, it is simply one plus the yearly interest rate raised to the power of one divided by 12, then you subtract that entire thing by one, now the 12 here are simply the periods within a year, it’s 12 months.
If we’re semi-annually change it accordingly, that’s what it means, 1 plus 5% raised to the power of 1 divided by 12 months and then minus 1, that gives you the rate, then the other thing I did is that I left the starting amount the same but in your deposit I’m assuming that you’re not going to have 1,500 to deposit every month, but you’re going to have 1500 divided by 12 to deposit every month.
I’m assuming that you make the same amount throughout the year, you’ve got these formulas, it’s same future value formula, it has different interest rates, that’s how you use the future value function to calculate how much you’re going to have in a certain amount of time.
If you want to adjust this for semiannual, you can do the same thing, 40 would become 80, you would adjust the interest rate for getting two payments throughout the year, so that’s the basics of it, if you want to get this spreadsheet to follow along or to get the formulas from it, go to teach Excel.com, you can check out this tutorial and get the spreadsheet.