Calculate Value of Savings/Retirement Plan in Excel – FV()

Calculate Value of Savings/Retirement Plan in Excel – FV(). 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.

So if you have a savings or Retirement Plan, say you want it for 40 years and you’ll get 5 percent and you deposit so much every year.

How much is it going to be worth so? This is a very basic example and it’s showing you how to use the future value. What I have here, I have two sets.

I have doing this annually right here. So what it’s going to do is say you deposit so much every year. At the end of the year or the beginning of the year and then down here, I assume that you deposit so much monthly.

The point is just to show you what you have to change to change the amount of periods.

Calculate Value of Savings/Retirement Plan in Excel – FV()

Now here I have the years so how many years you want to have it, I guess vesting the rate you think you’ll get the starting amount, so the starting amount is how much you already have any account or the Retirement Plan.

When you start the calculation and here the annual deposits, now really the only place you need, the formula is to get the value over here. So that’s the only place you’re going to have and it’s going to be a future value formula.

So let me go ahead and show you how to calculate the value of a series of investments in the future I’m going to eat this zoom in now. All you need to do is equals FV for future value. Open parenthesis now remember up here: it’s all annual!

It’S all yearly, so the first thing we want to rate it’s going to be in this cell here, comma. The next thing we want is the number of periods and that’s going to be equal to the years here.

The next thing we want is our payment. So how much we’re going to pay into it every period that’ll be our annual deposit, and the next thing is the present value. Now the present value in terms of the savings of Retirement Plan is going to be the starting amount in the account.

So how much you already have in there is going to be the present value. So I’m simply going to click the cell here and 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 and that by the way, 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, and now we have zero now down here we have the future value.

If we put our money in at the beginning of the year, so I’ll show you that the only difference here is the zero is now a 1.

Everything else is the same: let’s go ahead and try this out see how it works. We’Re going to have an investment for 40 years.

We think it will give us say: 5 %. We have 10,000 in the bank right now and we’re going to put 1,500 in every year for the next 40 years.

So one thing to note notice: before I explain it: this is red and 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 really 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 now also notice that if you put your money at the end of the year, you have less money at The end of the 40 years, then you do if you deposit the money at the beginning of the year now that simple time value of money, because you get to earn interest more interest on the money.

If you put it in at the beginning of the year, then if you do at the end of the year, but anyway 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 just look down here, so what we’re going to do is change the periods and all you have to do now, since it’s in monthly we’re not using years but periods. So this is this sell up here, be three times: 12 40 times 12.

So 480 periods and the interest rate we need to figure out what that’s going to be monthly.

Instead of yearly, the yearly interest rate is 5 %. But what is the monthly compounded interest rate and I’m soo min explained how to get that.

It is simply one plus the yearly interest rate raised to the power of that’s a little caret is 1/12, and then you subtract that entire thing by one now the 12 here are simply the periods within a year.

So it’s 12 months and if we’re semi-annually change it accordingly, but that’s what it means so: 1 plus 5 % raised to the power of 1 divided by 12 months and then minus 1.

That gives you the rate. So then the other thing I did. I left the starting amount, the same, but your deposit, I’m assuming now 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.

So I’m assuming you make the same amount throughout the year. Now those assumptions out of the way you’ve got these formulas here.

So it’s same future value formulas, just different interest rates and 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 say for semiannual, you can do the same thing. 40 would just become 80 and the interest rate you would adjust for getting two payments throughout the year.

So that’s the basics of it and if you want to get this spreadsheet, to follow along or to get the formulas from it go to teach Excel comm and you can check out this tutorial and get the spreadsheet you see here.