How to Calculate Future Value Using Excel or a Financial Calculator

posted on 06-07-2019

Future value is one of the most important concepts in finance. Luckily, once you learn a few tricks, you can calculate it easily using Microsoft Excel or a financial calculator.

Let's look at an example to illustrate the process.
 
Assume you are trying save up enough money to buy a car at the end six months.  You already have $10,000 in an account that pays 5% interest per year.  You can add $1,000 to the account at the end of each month.  How much will you have to spend on a car at the end of six months?

How to Calculate Future Value Using Excel:

1. The process will be easiest if you use the spreadsheet as a table to keep track of the different variables and periods you'll need for your calculation. First, label the cells in column A as follows:

      A1 = the time period -- in this case, A1 = Months

      A2 = Periodic Rate

      A3 = Payment Amount

      A4 = Present Value (PV)

      A5 = Future Value (FV)

2. Next, fill in the information for the cells in each row.

      B1-H1 = Months 0 - 6

      B2-H2 = 0.417%  (to calculate the periodic rate, take the annual rate from the example and divide by the number of periods per year. Using our example, Periodic Rate = 5.0% / 12 = 0.417%)

      C3-H3 = -$1,000

      B4 = -10,000

Note that because we're not making a payment today (at time 0), we don't put anything in cell B3. Also note that we've decide to make the amount negative because the $1,000 payments are coming out of our wallets and going in to the bank's account. They are cash outflows relative to you, the investor. We use the same idea for the present value of the money we've already set aside.

Your Excel spreadsheet should now look like this:

3. Now that we have our table, we are ready to calculate FV.  First, select the cell at B5.

Next, click on the function button (fx) which is located right above the column labels. Once you click fx, a box will pop up.

4. Select the "Financial" category from the drop down menu and choose the FV function from the list. Then click "OK."

5. A new box will come up asking you to type in the amounts for each variable given.

Use your table to help you fill in the boxes -- you're being asked for all the information you already used to create your table. Once you have entered the amounts, the solution to FV will automatically appear in the bottom left corner of the box and be labeled "Formula result."

6. When you click "OK" to accept the solution, it will be displayed in the cell you selected next to future value.  Your Excel spreadsheet should now look like this with this correct solution for FV:

How to Calculate Future Value Using a Financial Calculator:

Note: the steps in this tutorial outline the process for a Texas Instruments BA II Plus financial calculator.

1. Using our car example we will now find the future value of an investment by using a financial calculator.  Before we start, clear the financial keys by pressing [2nd] and then pressing [FV]. 

2. Now we're ready to enter in all the information from our example. First, enter in the number of payments by pressing [6] and then [N].  The screen should now say N = 6.

3. Next, enter the periodic interest rate. To be precise, hit [CE/C] for a clear screen. Calculate the periodic rate by dividing 5 by 12 (which gives you 0.41666…) and then hitting [I/Y].
NOTE: For whatever reason, you don't key in 0.05 for 5% when using a financial calculator -- you key in the whole number "5." If you forget this, you will end up grossly under-calculating the interest rate used in the calculation.

4. Enter the payment amount for each month by keying (-$1000) and pressing [PMT].  Note that this has to be -$1,000 because the payments represent cash outflows with respect to the investor.  

5. Finally, enter the present value amount (-$10,000) and press the [PV] key.  It is a negative value for the same reason as the payment amounts.

6. Now you are ready to command the calculator to solve for future value.  To calculate FV, simply press the [CPT] key and then [FV].  Your answer should be exactly $16,315.47. If you're off by a few cents, it is probably because you used fewer decimal places in your periodic interest rate.

Now that you've mastered future value, click here to learn How to Calculate Present Value Using Excel or a Financial Calculator. Or click here to see the financial calculators we've developed especially for InvestingAnswers' readers, including Return, Mortgage and Yield Calculators.

by Christian Hudspeth What's even better than earning rewards for spending on your credit cards? Getting paid hundreds of dollars worth in sign-up bonuses in three months or sooner -- just for tr...
by Christian Hudspeth Tired of dragging credit card debt around with you? Taking 15 minutes to transfer your debt to a credit card with generous balance transfer perks could save you thousands in...
by Christian Hudspeth If you're going to spend money anyway, then why not get paid for it?Whether you're looking for credit cards with up to 6% cash back, double flight miles, or even a free hote...
by Christian HudspethIn times where interest rates are on the rise, you may start hearing financial advisors and bankers sing the praises of an income strategy called "CD laddering" (short for ce...
by Susan Campbell Those of us familiar with selling property know real estate agents don't come cheap. With real estate agent commission and fees amounting to as much as 6% of the sel...
Beverly Harzog is a nationally recognized credit card expert, author, and consumer advocate. She blogs about credit cards at BeverlyHarzog.com. Being in credit card debt is the pits. I've bee...
by Christian Hudspeth If you haven't already felt the pressure to refinance your mortgage, you're probably really feeling it now. Mortgage rates are still hovering near historic lows. But ...
by Christian Hudspeth If you or someone you know is thinking about getting a home mortgage, you may want to know about the thousands of dollars in hidden charges that some lenders are quietly...
by Christian Hudspeth Money market accounts (MMAs) and savings accounts make great places to set aside your emergency fund money and earn some interest income at the same time.Simply put, these s...
by Christian Hudspeth It's true that auto loans and home loans offer attractively-low annual percentage rates (APRs), while credit cards offer borrowing power without the risk of ever seeing the ...
by Christian HudspethWant to keep your emergency fund safe while earning interest yields that are three to five times higher than a typical savings account? Putting your money into an FDIC-insure...
by Christian Hudspeth Question: Hi there. I need your advice. I'm only 19 and I really need to start investing. Where can I start? -- Tirelo M., Gaborone, Botswana Answer: You've defini...