Wise Bread Picks
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.
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:
- Investing without fail over a period of 30 years
- 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?
