Principles of Finance

7.3Methods for Solving Time Value of Money Problems

Principles of Finance7.3 Methods for Solving Time Value of Money Problems

Learning Outcomes

By the end of this section, you will be able to:

• Explain how future dollar amounts are calculated.
• Explain how present dollar amounts are calculated.
• Describe how discount rates are calculated.
• Describe how growth rates are calculated.
• Illustrate how periods of time for specified growth are calculated.
• Use a financial calculator and Excel to solve TVM problems.

We can determine future value by using any of four methods: (1) mathematical equations, (2) calculators with financial functions, (3) spreadsheets, and (4) FVIF tables. With the advent and wide acceptance and use of financial calculators and spreadsheet software, FVIF (and other such time value of money tables and factors) have become obsolete, and we will not discuss them in this text. Nevertheless, they are often still published in other finance textbooks and are also available on the internet to use if you so choose.

Using Timelines to Organize TVM Information

A useful tool for conceptualizing present value and future value problems is a timeline. A timeline is a visual, linear representation of periods and cash flows over a set amount of time. Each timeline shows today at the left and a desired ending, or future point (maturity date), at the right.

Now, let us take an example of a future value problem that has a time frame of five years. Before we begin to solve for any answers, it would be a good approach to lay out a timeline like that shown in Table 7.1:

 Year 0(Today) 1 2 3 4 5
Table 7.1

The timeline provides a visual reference for us and puts the problem into perspective.

Now, let’s say that we are interested in knowing what today’s balance of $100 in our saving account, earning 5% annually, will be worth at the end of each of the next five years. Using the future value formula 7.14 that we covered earlier, we would arrive at the following values:$105 at the end of year one, $110.25 at the end of year two,$115.76 at the end of year three, $121.55 at the end of year four, and$127.63 at the end of year five.

With the numerical information, the timeline (at a 5% interest or growth rate) would look like Table 7.2:

Year 0 1 2 3 4 5
$100.00$105.00 $110.25$115.76 $121.55$127.63
Table 7.2

Using timelines to lay out TVM problems becomes more and more valuable as problems become more complex. You should get into the habit of using a timeline to set up these problems prior to using the equation, a calculator, or a spreadsheet to help minimize input errors. Now we will move on to the different methods available that will help you solve specific TVM problems. These are the financial calculator and the Excel spreadsheet.

Using a Financial Calculator to Solve TVM Problems

An extremely popular method of solving TVM problems is through the use of a financial calculator. Financial calculators such as the Texas Instruments BAII Plus™ Professional will typically have five keys that represent the critical variables used in most common TVM problems: N, I/Y, PV, FV, and PMT. These represent the following:

$N: Number of PeriodsI/Y: Interest Rate (Interest per Year)PV: Present Value of a Lump SumV: Future Value of a Lump SumPMT: PaymentN: Number of PeriodsI/Y: Interest Rate (Interest per Year)PV: Present Value of a Lump SumV: Future Value of a Lump SumPMT: Payment$
7.15

These are the only keys on a financial calculator that are necessary to solve TVM problems involving a single payment or lump sum.

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).

Step Description Enter Display
1 Clear calculator register CE/C   0.00
2 Enter present value (as a negative integer) 1000 +|- PV PV =  -1,000.00
3 Enter interest rate 3 I/Y I/Y = 3.00
4 Enter time periods 4 N N = 4.00
5 Indicate no payments or deposits 0 PMT PMT = 0.00
6 Compute future value CPT FV FV = 1,125.51
Table 7.3 Calculator Steps for Finding the Future Value of a Single Payment or Lump Sum1

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.

Think It Through

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.

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?

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 before 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 period, again with no other deposits or withdrawals? Using Excel to Solve TVM Problems Excel spreadsheets can be excellent tools to use when solving time value of money problems. There are dozens of financial functions available in Excel, but a student who can use a few of these functions can solve almost any TVM problem. Special functions that relate to TVM calculations are as follows: Future Value (FV) 7.16 Present Value (PV) 7.17 Number of Periods (NPER) 7.18 Interest Rate (RATE) 7.19 Excel also includes a function called Payment (PMT) that is used in calculations involving multiple payments or deposits (annuities). These will be covered in Time Value of Money II: Equal Multiple Payments. 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: Rate: Interest Rate 7.20 Nper: Number of Periods 7.21 Pmt: Payment 7.22 PV: Present Value 7.23 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? $Present Value (PV)=(2,000.00)Present Value (PV)=(2,000.00)$ 7.24 Note: Be sure to follow the sign conventions. In this case, the PV should be entered as a negative value. $Interest Rate (Rate or I/Y)=4%Interest Rate (Rate or I/Y)=4%$ 7.25 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. 7.26 Note: It is always assumed that if not specifically stated, the compounding period of any given interest rate is annual, or based on years. $Future Value (FV)=2,433.31Future Value (FV)=2,433.31$ 7.27 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.

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. Think It Through Determining Present Value When Other Variables Are Known You have just won a second-prize lottery jackpot that will pay a single total lump sum of$50,000 five years from now. You are interested in knowing how much value this would have in today’s dollars, assuming a 5% interest rate.

7.28

Notes:

1. If you wish for the present value amount to be positive, the future value you enter here should be a negative value.
2. In Excel, interest and growth rates must be entered as percentages, not as whole integers. So, 5 percent must be entered as 5% or 0.05—not 5, as you would enter in a financial calculator.
3. It is always assumed that if not specifically stated, the compounding period of any given interest rate is annual, or based on years.
4. The Excel command used to calculate present value is as shown here:
=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? 7.29 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? 7.30 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.

Footnotes

• 1The specific financial calculator in these examples is the Texas Instruments BA II Plus™ Professional model, but you can use other financial calculators for these types of calculations.