How to Calculate Future Value, and Why It Matters

by Julie Rains on 1 August 2012 5 comments

I love financial functions on spreadsheets, and one of my favorites is @FV. This function enables you to calculate the future value of a stream of payments. You have to make assumptions about interest rates, but you can use the function to project the value of investments. For example, a simple-to-make formula lets you know that if you set aside $2,000 per year for 10 years and earn 5% each year, you'll have $25,000 at the end of the term. (See also: Investing 101: 5 Essential Steps)

Where to Find Future Value (@FV) Functions

You'll find @FV available in spreadsheets from Microsoft (Excel), Google Drive (or its predecessor Google Docs), Open Office, and more. Look for financial and other types of functions on the toolbar under "Insert" or the icon that looks like an E. I particularly like Microsoft's version because there are prompts embedded in the function that help me write the formula.

How to Write an @FV Formula

To create a formula that calculates future value, put in a series of numbers based on your best estimate. In Excel, the formula is @FV (rate, nper, pmt, [pv], [type]). Here's what those abbreviated words and acronyms mean:

Rate

The rate is the interest rate or investment return that you'll earn over the life of the investment. You can enter this number in a couple of ways. For example, 6% annual rate could be entered as 6% or .06. Note that if you are making payments monthly or quarterly, then you would need to divide the interest rate by 12 (months) or 4 (quarters). Enter .06/12 for monthly payments; .06/4, quarterly payments.

NPER

NPER is the number of time periods in which you will make payments or contributions. For example, if you are planning on contributing $2,000 to a Traditional IRA, Roth IRA, or SEP-IRA for 20 years, then the number of periods equals 20. However, if you are contributing $300 monthly to a 401(k) over 20 years, then the number of periods is 20 (years) X 12 (months) = 240. (Remember to divide your interest rate by the number of periods if you are making contributions periodically during the year; the interest rate for the 401(k) formula would be .06/12, which is the interest rate divided by number of months).

PMT

This number is the fixed payment or contribution made without fail over the number of periods specified.

PV

This number is the present value, upfront contribution, or the starting balance of the investment.

Typically, when I do future value calculations on a stream of payments, I use "0" (that is, zero) as the present value because the account is new and has no value yet. But, in reality, you often start saving with a base amount (see my calculations of the future value of current retirement savings in How Much Money Will You Need to Retire? spreadsheet). For example, you can calculate the future value of your 401(k) in 20 years based on a 5% interest rate, annual contribution of $3,000, and amount that you have amassed in the account. If the account value is $12,000 now, then the formula is @FV (5%,20,-3000,-12000,0) = $131,037.

Note that you can omit the present value altogether if the starting account balance is zero.

Type

The type references the timing of payments or contributions. This entry is optional, and I usually put this value as "0" (zero) for simplicity's sake. But you are supposed to put "0" if the contribution or payment will be made at the end of the period (for example, December 31, 2012) and "1" if you make the contribution at the beginning of the year (for example, January 1, 2012) as interest will accrue and investment gains will presumably be made throughout the year if you start earlier rather than later.

Do the calculations both ways to see the difference. For example, if you save $5,000 yearly for 25 years and earn 5% but start at the beginning of the year (type = 1), then the future value is over $250,000, but if you wait until the end of the year to invest (type = 0), then the value grows to $238,635.

ARTICLE CONTINUES BELOW

Why Calculating Future Value Matters

You may want to know what the value of your savings and investments will be worth in the future. In my article Mindless Ways to Save a Million, I illustrated how various account balances could grow over a working lifetime through automatic savings, such as direct deposits, drafts, automated investments, and regular contributions. Specifically, I looked at how the following types of accounts may grow:

  • Regular savings
  • 401(K)
  • IRA (Traditional or Roth)
  • SEP-IRA (IRA for self-employed individuals)
  • HSA (tied to a high-deductible health plan)

You will likely experience varying rates of interest and investment returns on these accounts. For illustration purposes, I calculated future value using interest rates and investment returns that ranged from 1% to 8%. If you make regular deposits to these accounts over 30 years (in amounts ranging from $100 per month to $8,000 per year) and don't cash them out, then you can expect to save more than $1.5 million.

The difficult aspects of this scenario are:

  1. Investing without fail over a period of 30 years
  2. Earning high enough investment returns consistently

Nevertheless, formulas using the @FV function show the potential for future value. So, if you want to know whether saving X amount each year is really worth it, use the future value function to help you make a decision.

Do you use the @FV to calculate possibilities? How has this formula shaped your decisions?

5
Average: 5 (2 votes)
Your rating: None
ShareThis

comments

5 discussions

Add New Comment

CAPTCHA
This test helps prevent automated spam submissions.
Guest's picture

This is a brilliant introductory piece on Future Value, Julie. Thank you. I hope it will interest (pun not intended) more people to learn about this and related points.

I understand that some people get intimidated by the figures and therefore tend to walk away from technical data such as these. This is such a shame as (1) they are critical to understanding and certainly mastering finances; (2) they are actually easy to learn!

They can do this by using spreadsheets on the computer as you have suggested. The alternative is by using a Financial Calculator. The advantage of using the calculator is that everything is already preset and you just need to enter the figures. It’ll take you two or three days (tops!) to learn how to use it, which is faster than learning to use the iPad!

Once you know how to get the figures, you do not have to rely on others. You will develop confidence on money matters and as a bonus, you may even discover calculation errors in books as I have!

Julie Rains's picture

Glad you enjoyed the article. Great tips on using other tools that have the same functions but are presented differently. I like comparing my results with online calculators, which helps me to see what assumptions that designers are using (which may or may not sync with mine).

Guest's picture

Good overview of the Excel functions! And yes, I agree wholeheartedly that calculating future value is an absolute must when thinking about investing.

Guest's picture
cs

Good stuff, but this is useful mainly for those who understand the relevant math AND the financial modeling (assumptions, parameters). The challenge many people face is that they're trying to make good decisions without those tools, and saying to them "go learn compound interest and basic finance" isn't very useful.

There's another problem: doing projections using flat rates is quite do-able with spreadsheets. And it's a very useful exercise. But the world doesn't follow the neat rules and patterns you have in your projections, it's nastily random. What if you want to model a set of real world (random) scenarios, to get more realistic projections? Now you either need a more sophisticated tool (there are very few out there), or you need to be a proficient programmer, with access to a lot of historical data.

Julie Rains's picture

Thanks for your comment. Hope I've helped folks to see a basic concept in terms of future value, and provided them with a useful tool and starting point for evaluations.

As I mentioned in the article, you have to make assumptions about the interest rates, plus you'll experience varying rates of interest and investment returns over time, and consistency is difficult to come by. Having been invested for 25+ years, I know that returns vary each year. But hopefully, seeing the difference in Future Value between say 1% and 8% might help folks make a decision about different possibilities for investing.