7.3 Methods for Solving Time Value of Money Problems

Example 1: Future Value of a Single Payment or Lump Sum

Let’s start with a simple example that will provide you with most of the skills needed to perform TVM functions involving a single lump sum payment with a financial calculator.

Suppose that you have $1,000 and that you deposit this in a savings account earning 3% annually for a period of four years. You will naturally be interested in knowing how much money you will have in your account at the end of this four-year time period (assuming you make no other deposits and withdraw no cash).

To answer this question, you will need to work with factors of $1,000, the present value (PV); four periods or years, represented by N; and the 3% interest rate, or I/Y. Make sure that the calculator register information is cleared, or you may end up with numbers from previous uses that will interfere with the solution. The register-clearing process will depend on what type of calculator you are using, but for the TI BA II Plus™ Professional calculator, clearing can be accomplished by pressing the keys 2ND and FV [CLR TVM].

Once you have cleared any old data, you can enter the values in the appropriate key areas: 4 for N, 3 for I/Y, and 1000 for PV. Now you have entered enough information to calculate the future value. Continue by pressing the CPT (compute) key, followed by the FV key. The answer you end up with should be displayed as 1,125.51 (see Table 7.3).

Important Notes for Using a Calculator and the Cash Flow Sign Convention

Please note that the PV was entered as negative $1,000 (or -$1000). This is because most financial calculators (and spreadsheets) follow something called the cash flow sign convention, which is a way for calculators and spreadsheets to keep the relative direction of the cash flow straight. Positive numbers are used to represent cash inflows, and negative numbers should always be used for cash outflows.

In this example, the $1,000 is an investment that requires a cash outflow. For this reason, -1000 is entered as the present value, as you will be essentially handing this $1,000 to a bank or to someone else to initiate the transaction. Conversely, the future value represents a cash inflow in four years’ time. This is why the calculator generates a positive 1,125.51 as the end result of this calculation.

Had you entered the present value of $1,000 as a positive number, there would have been no real concern, but the ending future value answer would have been returned expressed as a negative number. This would be correct had you borrowed $1,000 today (cash inflow) and agreed to repay $1,125.51 (cash outflow) four years from now. Also, it is important that you do not change the sign of any input value by using the - (minus) key). For example, on the TI BA II Plus™ Professional, you must use the +|- key instead of the minus key. If you enter 1000 and then hit the +|- key, you will get a negative 1,000 amount showing in the calculator display.

An important feature of most financial calculators is that it is possible to change any of the variables in a problem without needing to reenter all of the other data. For example, suppose that we wanted to find out the future value in our bank account if we left the money from our previous example invested for 20 years instead of 4. Before clearing any of the data, simply enter 20 for N and then press the CPT key and then the FV key. After this is done, all other inputs will remain the same, and you will arrive at an answer of $1,806.11.

Example 2: Present Value of Lump Sums

Solving for the present value (discounted value) of a lump sum is the exact opposite of solving for a future value. Once again, if we enter a negative value for the FV, then the calculated PV will be a positive amount.

Taking the reverse of what we did in our example of future value above, we can enter -1,125.51 for FV, 3 for I/Y, and 4 for N. Hit the CPT and PV keys in succession, and you should arrive at a displayed answer of 1,000.

An important constant within the time value of money framework is that the present value will always be less than the future value unless the interest rate is negative. It is important to keep this in mind because it can help you spot incorrect answers that may arise from errors with your input.

Example 3: Calculating the Number of Periods

There will be times when you will know both the value of the money you have now and how much money you will need to have at some unknown point in the future. If you also know the interest rate your money will be earning for the foreseeable future, then you can solve for N, or the exact amount of time periods that it will take for the present value of your money to grow into the future value that you will require for your eventual use.

Now, suppose that you have $100 today and you would like to know how long it will take for you to be able to purchase a product that costs $133.82.

After making sure your calculator is clear, you will enter 5 for I/Y, -100 for PV, and 133.82 for FV. Now press CPT N, and you will see that it will take 5.97 years for your money to grow to the desired amount of $133.82.

Again, an important thing to note when using a financial calculator to solve TVM problems is that you must enter your numbers according to the cash flow sign convention discussed above. If you do not make either the PV or the FV a negative number (with the other being a positive number), then you will end up getting an error message on the screen instead of the answer to the problem. The reason for this is that if both numbers you enter for the PV and FV are positive, the calculator will operate under the assumption that you are receiving a financial benefit without making any cash outlay as an initial investment. If you get such an error message in your calculations, you can simply press the CE/C key. This will clear the error, and you can reenter your data correctly by changing the sign of either PV or FV (but not both of these, of course).

Example 4: Solving for the Interest Rate

Solving for an interest rate is a common TVM problem that can be easily addressed with a financial calculator. Let’s return to our earlier example, but in this case, we know that we have $1,000 at the present time and that we will need to have a total of $1,125.51 four years from now. Let’s also say that the only way we can add to the current value of our savings is through interest income. We will not be able to make any further deposits in addition to our initial $1,000 account balance.

What interest rate should we be sure to get on our savings account in order to have a total savings account value of $1,125.51 four years from now?

Once again, clear the calculator, and then enter 4 for N, -1,000 for PV, and 1,125.51 for FV. Then, press the CPT and I/Y keys and you will find that you need to earn an average 3% interest per year in order to grow your savings balance to the desired amount of $1,125.51. Again, if you end up with an error message, you probably failed to follow the sign convention relating to cash inflow and outflow that we discussed earlier. To correct this, you will need to clear the calculator and reenter the information correctly.

After you believe you are done and have arrived at a final answer, always make sure you give it a quick review. You can ask yourself questions such as “Does this make any sense?” “How does this compare to other answers I have arrived at?” or “Is this logical based on everything I know about the scenario?” Knowing how to go about such a review will require you to understand the concepts you are attempting to apply and what you are trying to make the calculator do. Further, it is critical to understand the relationships among the different inputs and variables of the problem. If you do not fully understand these relationships, you may end up with an incorrect answer. In the end, it is important to realize that any calculator is simply a tool. It will only do what you direct it to do and has no idea what your objective is or what it is that you really wish to accomplish.

Future Value (FV)

The Future Value function in Excel is also referred to as FV and can be used to calculate the value of a single lump sum amount carried to any point in the future. The FV function syntax is similar to that of the other four basic time-value functions and has the following inputs (referred to as arguments), similar to the functions listed above:

Lump sum problems do not involve payments, so the value of Pmt in such calculations is 0. Another argument, Type, refers to the timing of a payment and carries a default value of the end of the period, which is the most common timing (as opposed to the beginning of a period). This may be ignored in our current example, which means the default value of the end of the period will be used.

The spreadsheet in Figure 7.3 shows two examples of using the FV function in Excel to calculate the future value of $100 in five years at 5% interest.

In cell E1, the FV function references the values in cells B1 through B4 for each of the arguments. When a user begins to type a function into a spreadsheet, Excel provides helpful information in the form of on-screen tips showing the argument inputs that are required to complete the function. In our spreadsheet example, as the FV formula is being typed into cell E2, a banner showing the arguments necessary to complete the function appears directly below, hovering over cell E3.

Figure 7.3 Using the FV Function in Excel

Cells E1 and E2 show how the FV function appears in the spreadsheet as it is typed in with the required arguments. Cell E4 shows the calculated answer for cell E1 after hitting the enter key. Once the enter key is pressed, the hint banner hovering over cell E3 will disappear. The second example of the FV function in our example spreadsheet is in cell E6. Here, the actual numerical values are used in the FV function equation rather than cell references. The method in cell E8 is referred to as hard coding. In general, it is preferable to use the cell reference method, as this allows for copying formulas and provides the user with increased flexibility in accounting for changes to input data. This ability to accept cell references in formulas is one of the greatest strengths of Excel as a spreadsheet tool.

Determining Future Value When Other Variables Are Known. You have $2,000 invested in a money market account that is expected to earn 4% annually. What will be the total value in the account in five years?

Note: Be sure to follow the sign conventions. In this case, the PV should be entered as a negative value.

Note: In Excel, interest and growth rates must be entered as percentages, not as whole integers. So, 4 percent must be entered as 4% or 0.04—not 4, as you would enter in a financial calculator.

Note: It is always assumed that if not specifically stated, the compounding period of any given interest rate is annual, or based on years.

Note: The Excel command used to calculate future value is as follows:

=FV(rate, nper, pmt, [pv], [type])

You may simply type the values for the arguments in the above formula. Another option is to use the Excel insert function option. If you decide on this second method, below are several screenshots of dialog boxes you will encounter and will be required to complete.

1. First, go to Formulas in the upper menu bar, and select the Insert Function option. When you do so, a dialog box will appear that looks like what you see in Figure 7.4.

   Figure 7.4 Dialog Box to Insert FV Function

   This dialog box allows you to either search for a function or select a function that has been used recently. In this example, you can search for FV by typing this in the search box and selecting Go, or you can simply choose FV from the list of most recently used functions (as shown here with the highlighted FV option).

2. Once you select FV and click the OK button, a new dialog box will appear for you to enter the necessary details. See Figure 7.5.

   Figure 7.5 New Dialog Box for FV Function Arguments

   Figure 7.6 shows the completed data input for the variables, referred to here as “function arguments.” Note that cell addresses are used in this example. This allows the spreadsheet to still be useful if you decide to change any of the variables. You may also type values directly into the Function Arguments dialog box, but if you do this and you have to change any of your inputs later, you will have to reenter the new information. Using cell addresses is always a preferable method of entering the function argument data.

   Figure 7.6 Completed Data Entry Menu for FV Function Arguments

Additional notes:

1. The Pmt argument or variable can be ignored in this instance, or you can enter a placeholder value of zero. This example shows a blank or ignored entry, but either option may be used in problems such as this where the information is not relevant.
2. The Type argument does not apply to this problem. Type refers to the timing of cash flows and is usually used in multiple payment or annuity problems to indicate whether payments or deposits are made at the beginning of periods or at the end. In single lump sum problems, this is not relevant information, and the Type argument box is left empty.
3. When you use cell addresses as function argument inputs, the numerical values within the cells are displayed off to the right. This helps you ensure that you are identifying the correct cells in your function. The final answer generated by the function is also displayed for your preliminary review.

Once you are satisfied with the result, hit the OK button, and the dialog box will disappear, with only the final numerical result appearing in the cell where you have set up the function.

The FV of this present value has been calculated as approximately $2,433.31.

Present Value (PV)

We have covered the idea that present value is the opposite of future value. As an example, in the spreadsheet shown in Figure 7.3, we calculated that the future value of $100 five years from now at a 5% interest rate would be $127.63. By reversing this process, we can safely state that $127.63 received five years from now with a 5% interest (or discount) rate would have a value of just $100 today. Thus, $100 is its present value. In Excel, the PV function is used to determine present value (see Figure 7.7).

Figure 7.7 Using the PV Function in Excel

The formula in cell E1 uses cell references in a similar fashion to our FV example spreadsheet above. Also similar to our earlier example is the hard-coded formula for this calculation, which is shown in cell E6. In both cases, the answers we arrive at using the PV function are identical, but once again, using cell references is preferred over hard coding if possible.

=PV(rate, nper, pmt, [fv], [type])

Periods of Time

The following discussion will show you how to use Excel to determine the amount of time a given present value will need to grow into a specified future value when the interest or growth rate is known.

You want to be able to contribute $25,000 to your child’s first year of college tuition and related expenses. You currently have $15,000 in a tuition savings account that is earning 6% interest every year. How long will it take for this account grow into the targeted amount of $25,000, assuming no additional deposits or withdrawals are made?

Notes:

1. As with our other examples, interest and growth rates must be entered as percentages, not as whole integers. So, 6 percent must be entered as 6% or 0.06—not 6, as you would enter in a financial calculator.
2. The present value needs to be entered as a negative value in accordance with the sign convention covered earlier.
3. The Excel command used to calculate the amount of time, or number of periods, is this:

=NPER (rate, pmt, pv, [fv], [type])

As with our FV and PV examples, you may simply type the values of the arguments in the above formula, or we can again use the Insert Function option in Excel. If you do so, you will need to work with the various dialog boxes after you select Insert Function.

1. First, go to Formulas in the upper menu bar, and select the Insert Function option. When you do so, the Insert Function dialog box will appear (see Figure 7.11).

   Figure 7.11 Dialog Box to Insert NPER Function

   As discussed in our previous examples on FV and PV, this menu allows you to either search for a function or select a function that has been used recently. In this example, you can search for NPER by typing this into the search box and selecting Go, or you can simply choose NPER from the list of most recently used functions.

2. Once you have highlighted NPER, click the OK button, and a new dialog box will appear for you to enter the necessary details. As in our previous examples, it will look like Figure 7.12.

Figure 7.12 New Dialog Box for NPER Function Arguments

Figure 7.13 shows the completed Function Arguments dialog box. Note that once again, we are using cell addresses in this example.

Figure 7.13 Completed Dialog Box for NPER Function Arguments

As in the previous function examples, values are shown off to the right of the data input area, and our final answer of approximately 8.77 is displayed at the bottom. Also, once again, the Pmt and Type boxes are not relevant to this single lump sum example.

Review your answer, and once you are satisfied with the result, click the OK button. The dialog box will disappear, with only the final numerical result appearing in the cell where you have set up the function.

The amount of time required for the desired growth to occur is calculated as approximately 8.77 years.

Interest or Growth Rate

You can also use Excel to determine the required growth rate when the present value, future value, and total number of required periods are known.

Let’s discuss a similar example to the one we used to calculate periods of time. You still want to help your child with their first year of college tuition and related expenses, and you still have a starting amount of $15,000, but you have not yet decided which savings plan to use.

Instead, the information you now have is that your child is just under 10 years old and will begin college at age 18. For simplicity’s sake, let’s say that you have eight and a half years until you will need to meet your total savings target of $25,000. What rate of interest will you need to grow your saved money from $15,000 to $25,000 in this time, again with no other deposits or withdrawals?

Note: The present value needs to be entered as a negative value.

Interest Rate (RATE)

Note: The Excel command used to calculate interest or growth rate is as follows:

=RATE(nper, pmt, pv, [fv], [type], [guess])

As with our other TVM function examples, you may simply type the values for the arguments into the above formula. We also again have the same alternative to use the Insert Function option in Excel. If you choose this option, you will again see the Insert Function dialog box after you click the Insert Function button.

1. First, go to Formulas in the upper menu bar, and select the Insert Function option. When you do so, the Insert Function dialog box will appear (see Figure 7.14).

   Figure 7.14 Dialog Box to Insert RATE Function
2. This time, find and highlight RATE, and click the OK button once you have done so. The Function Arguments dialog box will look like Figure 7.15.

   Figure 7.15 New Dialog Box for RATE Function Arguments

   Once we complete the input, again using cell addresses for the required argument values, we will see what is shown in Figure 7.16.

   Figure 7.16 Completed Dialog Box for RATE Function Arguments

   As in our other examples, cell values are shown as numerical values off to the right, and our answer of approximately 0.0619, or 6.19%, is shown at the bottom of the dialog box.

   This answer also can be checked from a logic point of view because of the similar example we worked through when calculating periods of time. Our present value and future value are the same as in that example, and our time period is now 8.5 years, which is just under the result we arrived at (8.77 years) in the periods example.

   So, if we are now working with a slightly shorter time frame for the savings to grow from $15,000 into $25,000, then we would expect to have a slightly greater growth rate. That is exactly how the answer turns out, as the calculated required interest rate of approximately 6.19% is just slightly greater than the growth rate of 6% used in the previous example. So, based on this, it looks like our answer here passes a simple “sanity check” review.