9.2 Unequal Payments Using a Financial Calculator or Microsoft Excel

Using a Financial Calculator

A financial calculator provides utilities to simplify the analysis of uneven mixed cash streams (see Table 9.8).

Earlier, we explored the future value of a seven-year mixed stream, with $2,000 being saved each year, plus an additional $10,000 in year 4 and an additional $3,000 in year 6. All cash flows and balances earn 7% per year compounded annually, and the payments are made at the start of each year. We proved that this result totals approximately $35,062.26. We begin by clearing all memory functions and then entering each cash flow as follows:

At this point, we have found the net present value of this uneven stream of payments. You will recall, however, that we are not trying to calculate present values; we are looking for future values. The TI BA II Plus™ Professional calculator does not have a similar function for future value. This means that either we can find the future value for each payment in the stream and combine them, or we can take the net present value we just calculated and easily project it forward using the following keystrokes. Note the net present value solution in Step 20 above. We will use that and then use the simpler of the two approaches to calculate future value (see Table 9.9).

This is consistent with the solution we found earlier, with a difference of one cent due to rounding error.

We may also use the calculator to solve for the present value of a mixed cash stream. Earlier in this chapter, we asked how much money you would need today to fund the following five annual withdrawals, with each withdrawal made at the end of the year, beginning one year from now, and all remaining money earning 6% compounded annually:

We determined these withdrawals to have a total present value of $72,753.30. Here is an approach to a solution using a financial calculator. In this example, we will store all cash flows in the calculator and perform an operation on them as a whole (see Table 9.11). Because we will use the NPV function (to be explored in more detail in a later chapter), we enter our starting point as 0 because we do not withdraw any cash until one year after we begin.

This result, you will remember, was calculated earlier in the chapter by the formula approach.

Using Microsoft Excel

Several of the exhibits already in this chapter have been prepared with Microsoft Excel. While full mastery of Excel requires extensive study and practice, enough basics can be learned in two or three hours to provide the user with the ability to quickly and conveniently solve problems, including extensive financial applications. Potential employers and internship hosts have come to expect basic Excel knowledge, something to which you are exposed in college.

We will demonstrate the same two problems using Excel rather than a calculator:

1. The future value of a mixed cash stream for a seven-year investment
2. The present value of a mixed cash stream of five withdrawals that you wish to make from a fund to be established today

Beginning with the future value problem, we created a simple matrix to lay out the mixed stream of future cash flows, starting on the first day of each year, with all funds earning 7% throughout. Our goal is to determine how much money you will have saved at the end of this seven-year period.

Table 9.12 repeats the data from earlier in the chaper for your convenience.

Figure 9.2 is an Excel matrix that parallels Table 9.12 above.

Figure 9.2 Compound Interest Example

We begin by entering the cash flow as shown in Figure 9.2. The assumed interest rate is 7%. The interest on the balance is calculated as the amount invested at the start of the year multiplied by the assumed interest rate. The cumulative cash flows of each year are calculated as follows: for year 1, the amount invested plus the interest on the balance; for years 2 through 7, the amount invested plus the interest on the balance plus the previous year’s running balance. By adding up the amount invested and the interest on the balance, you should arrive at a total of $35,062.27.

We can use Excel formulas to solve time value of money problems. For example, if we wanted to find the present value of the amount invested at 7% over the seven-year time period, we could use the NPV function in Excel. The dialog box for this function (Rate, Value 1, Value2) is shown in Figure 9.3.

Figure 9.3 Dialog Box for NPV Function, Problem 1

The function argument Rate is the interest rate; Value1, Value2, and so on are the cash flows; and “Formula result” is the answer.

We can apply the NPV function to our problem as shown in Figure 9.4.

Figure 9.4 Applying the NPV Function, Problem 1

Please note that the Rate cell value (C1) and the Value1 cell range (C3:I3) will vary depending on how you set up your spreadsheet.

The non-Excel version of the problem, using an assumed interest rate of 7%, produces the same result.

We conclude with the second problem addressed earlier in this chapter: finding the present value of an uneven stream of payments. We can use Excel’s NPV function to solve this problem as well (see Figure 9.5).

Figure 9.5 Dialog Box for NPV Function, Problem 2

Again, Rate is the interest rate; Value1, Value 2, and so on are the cash flows; and “Formula result” is the answer.

Let us apply the NPV function to our problem, as shown in Figure 9.6 and Figure 9.7.

Figure 9.6 Applying the NPV Function, Problem 2: Excel Data

Figure 9.7 Applying the NPV Function, Problem 2: Function Argument

Please note that the Rate cell value (B3) and the Value1 cell range (C2:G2) will vary depending on how you set up your spreadsheet.

The non-Excel version of the problem produces the same result: an NPV of $72,753.49.