How to Calculate the Future Value of an Investment Using Excel. Using Microsoft Excel to calculate the future value of a potential investment is a relatively simple task once you have learned the required formula's syntax. Follow these easy steps while inputting your own criteria. You will soon learn how to calculate future value using Microsoft Excel.
Understand the concept of future value. Future value is a Time Value of Money calculation. Future value answers questions such as, "If I invest a certain amount of money each month, given the market interest rate for that type of investment, what will my nest egg be worth when I retire?" In other words, the future value calculation measures the power of compound interest.
Open Microsoft Excel. Click in the cell in which you wish the result of your formula to show. Type the following formula starter: =FV(
Observe the screen tip that pops up as soon as you type your opening parenthesis. It looks like this: (rate, nper, pmt, [pv], [type]). This screen tip design helps you know which values to input.
Enter the interest rate you think the investment should achieve (rate), divided by the number of times per year you expect to make deposits into the investment account. For example, if the market interest rate for a bank savings account is 3 percent, paid monthly to depositors, enter ".03/12" (without the quotations) in the rate section of the formula. Type a comma to move to the next section of the formula.
Type in the number of periods (nper) you expect to hold the investment. If you plan to retire in 25 years, enter 25*12 in this field, because the number of periods is 25 years times 12 months, for a total of 300 monthly deposits into the savings account. Type a comma to move to the next section of the formula.
Fill in the monthly amount (pmt) you would like to deposit into the savings account preceded by a minus sign (since the payment is considered to be money leaving your pocket, it is expressed as a negative, even though you will get the money back when you retire). If you would like to deposit $100 per month, enter "-100" (minus the quotations). Type the ending parenthesis of the equation and press "Enter." Note you do not have to fill in the "pv" or "type" fields; they are optional.
Note the results of your equation. If you put $100 per month into a savings account earning 3% interest over 25 years, your nest egg will be worth $44,600.78 when you retire at that time. Try a second problem to make sure you have the hang of the FV equation in Excel. You want to deposit $500 twice a year into an investment that pays semi-annual interest of 10.2 percent. You wish to take the money out in 15 years. What will your money be worth at that time? Your formula should look like this: =FV(.102/2, 15*2, -500). You should get $33,795.56 as an answer.