Functions for Personal Finance

This section details how loans work and the different parts of the PMT function. Pay attention to Table 2.6, which shows the arguments (or inputs) of the PMT function.

The FV (Future Value) Function

Establishing a personal savings plan is one of the most important financial exercises you can do. For example, a savings plan is critical for establishing financial security for your retirement years. 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 have an understanding of 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 going to 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. Similar to the PMT function, the FV function requires you to accurately define specific arguments in order to produce a reliable result. Table 4 Arguments for the FV Function provides definitions for each of the arguments in the FV function. It is helpful to review the time value of money terms in Table 3 Key Terms for Time Value of Money Concepts before using the FV function.

 

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 by 12 if you are calculating the value of an annuity making investments on a monthly basis.
Nper The argument letters stand for number of periods. This is the amount of time you are using to measure the value of an investment. The amount of 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 that is 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 that is 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. Both 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 used 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.


Table 4 Arguments for the FV Function

 

With respect to 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:

  1. Click cell D10 in the Budget Summary worksheet.
  2. Type an equal sign: =
  3. Type the letters FV followed by an open parenthesis: (
  4. Click cell D13. This is the expected rate of return for the investments.
  5. Type a comma.
  6. Click cell D12. This is the amount of time the investments are expected to grow.
  7. Type a comma.
  8. Type a minus sign (). All values or cell locations used to define the Pmt argument must be preceded by a minus sign.
  9. Click cell D7. This is the change in cash that was calculated by subtracting the total expenses from the net income. We are expecting to save this amount of money for the 10-year period this investment is being measured.
  10. Type a comma.
  11. Type a minus sign (). All values and cell locations used to define the Pv argument must be preceded by a minus sign.
  12. Click cell D14. Since the savings plan has a current balance, we use this to define the Pv argument of the function. This is equivalent to starting with a lump-sum investment.
  13. 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.
  14. Press the ENTER key. Check that cell D11 is activated.
  15. Type an equal sign: =
  16. Click cell D10.
  17. Type a minus sign () and then click cell D9. This subtracts the savings plan from the current savings plan projection.
  18. 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 Results of the Savings Plan Projections 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 with regard to this plan. The first factor is that our spending plan allows us to save enough money so that it can 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 having to make high-risk investments that could result in a significant loss of our savings.

 

Results of the Savings Plan Projections

Figure 14 Results of the Savings Plan Projections

 

Skill Refresher: FV Function

  1. Type an equal sign: =
  2. Type the letters FV followed by an open parenthesis, or double click the function name from the function list.
  3. Define the Rate argument with a cell location that contains the expected rate of return for your investment.
  4. Define the Nper argument with a cell location that contains the amount of time you are measuring the growth of your investment.
  5. 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.
  6. 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.
  7. 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.
  8. Type a closing parenthesis: )
  9. Press the ENTER key.