Functions for Personal Finance
The FV (Future Value) Function
Establishing a personal savings plan is one of the most important financial exercises. For example, a savings plan is critical for establishing financial security for retirement. Many people mistakenly believe that saving for retirement is something you do when you get older. However, the greatest financial gains for your retirement can be achieved if you start saving in the earliest years of your career.
Now that you understand the time value of money, you can see that the more years you can earn interest on your investments and reinvest those earnings, the more money you will have when you retire. Savings plans are also important for other key life events, such as attending college or buying a home. The FV function is a convenient tool that can help you establish savings goals and project the value of your investments over time. Like the PMT function, the FV function requires you to accurately define specific arguments to produce a reliable result. Table 4 provides definitions for each of the arguments in the FV function. Reviewing the time value of money terms in Table 3 before using the FV function is helpful.
| Argument | Definition |
|---|---|
| Rate | This is the rate of return you expect to earn on an investment over time. This rate is usually quoted in annual terms, so you have to divide it by 12 if you are calculating the value of an annuity and making investments monthly. |
| Nper | The argument letters stand for the number of periods. This is the amount of time you are using to measure the value of an investment. The time used to define this argument must be comparable to the Rate argument. For example, if the rate is stated in terms of months, the amount of time used to define this argument must be in months. |
| Pmt | The argument letters stand for payment. This argument is used if you are measuring the value of an annuity investment. The argument is defined with the value of the investment made for each measure of time used to define the Nper argument. For example, if the Nper argument is expressed in terms of months, you must define this argument with the investment value made every month. |
| [Pv] | The argument letters stand for present value. The brackets around the argument indicate that it is not always necessary to define it. Excel assumes zero if the argument is not defined. The argument is used when measuring the value of a one-time investment. This argument and the Pmt argument will be defined if an annuity investment has a beginning balance or includes a beginning one-time lump-sum investment. |
| [Type] | This argument can be defined with either a 1 or a 0. The number 1 is used if investments are made at the beginning of each period to define the Nper argument. A 0 is used if the investments are made at the end of each period. The argument is in brackets because it does not have to be defined if your investments are made at the end of each period. Excel assumes that this argument is 0 if it is not defined. |
For the Personal Budget workbook, we will use the FV
function to project the value of the savings plan in 10 years. We will
type the function directly into the Personal Budget
worksheet for this demonstration. However, you
can use any of the methods demonstrated in this chapter for future
use. The following steps explain how this function is added to the
worksheet:
- Click cell D10 in the Budget Summary worksheet.
- Type an equal sign: =
- Type the letters FV followed by an open parenthesis: (
- Click cell D13. This is the expected rate of return for the investments.
- Type a comma.
- Click cell D12. This is the amount of time the investments are expected to grow.
- Type a comma.
- Type a minus sign (−). All values or cell locations used to define the Pmt argument must be preceded by a minus sign.
- Click cell D7. This change in cash was calculated by
subtracting the total expenses from the net income. We expect to
save this money for the 10-year period this investment is
being measured.
- Type a comma.
- Type a minus sign (−). All values and cell locations used to define the Pv argument must be preceded by a minus sign.
- Click cell D14. Since the savings plan has a current balance, we
use this to define the function's Pv argument. This is equivalent
to starting with a lump-sum investment.
- Type a closing parenthesis ( ) ). There is no
need to define the last argument of the function because we will assume
that the savings in cash achieved in our budget will be invested at the
end of each year of the savings
plan.
- Press the ENTER key. Check that cell D11 is activated.
- Type an equal sign: =
- Click cell D10.
- Type a minus sign (−) and then click cell D9. This subtracts the savings plan from the current savings plan projection.
- Press the ENTER key.
Integrity Check
PMT and FV Functions Produce Negative Results
If the results of the PMT function or FV function are negative, check the Pv or Pmt arguments. Remember that these arguments must be preceded by a minus sign. If the minus sign is omitted, the functions produce a negative output.
Figure 14 shows the results of the FV function. Notice that the current savings plan projection is $25,606. This is $606 higher than the target of $25,000 entered into cell D9, which shows that the current budget is working to achieve the goals of this savings plan. In other words, given the current net income, we are saving enough money to achieve our savings plan goals.
There are two important factors to notice about this plan.
The first factor is that our spending plan allows us to save enough
money to be invested to achieve our target of $25,000. The
second factor is that the expected rate of return
is 3.5%. This is a relatively low expected rate of return and could
be achieved by investing in relatively low-risk investments such as
bonds as opposed to stocks. This rate can be considered good because we
can achieve our savings goals without making high-risk investments that could significantly lose our savings.
Figure 14 Results of the Savings Plan Projections
Skill Refresher: FV Function
- Type an equal sign: =
- Type the letters FV followed by an open parenthesis, or double click the function name from the function list.
- Define the Rate argument with a cell location that contains the expected rate of return for your investment.
- Define the Nper argument with a cell location that contains the amount of time you are measuring the growth of your investment.
- Define the Pmt argument with a
cell location that contains the value of regular investments for an
annuity. Cell locations or values used for this argument must be
preceded by a minus sign.
- Define the [Pv] argument with a
cell location that contains the value of a one-time lump-sum
investment. Cell locations or values used for this argument must be
preceded by a minus sign.
- Define the [Type] argument
with a 1 if annuity investments are made at the beginning of each period
or a 0 if investments are made at the end of each period.
- Type a closing parenthesis: )
- Press the ENTER key.