You put aside $10,000 now at 4% per year compounded annually. How much will you have in 35 years?

This is the future value of a lump sum payment made now. The third argument, which is used for the size of the periodic payment, is 0, because there is no periodic payment. The lump sum, with a minus sign as in part (a), is supplied as the fourth argument. Since there is no payment stream, we don't have to supply the fifth argument (the type), which determines whether payments occur at the beginning or end of each period. Since there are no periodic payments, it doesn't matter what the fifth argument is.

You're saving for retirement. You've found an investment vehicle that pays 4% compounded annually. You now have $10,000. If you add $2,000 at the beginning of every year, what will be the value of your savings in 35 years?

Now we have both a lump sum initial payment, and periodic payments made at the beginning of each period. To indicate payment type, we must supply the fifth argument, a 1.

How much will you have if, in years 21 through 35, the interest rate is 6%?

A new layer of trickiness — the interest rate changes. To compute the future value in this situation, we proceed in two steps. First we compute the future value of the stream out to the point where the interest rate changes. Then we take that as a lump sum argument to a new stream that takes it from there.

The inner call to fv computes the future value of the first segment at the point where the interest rate changes.

How much will you have if, in years 21 through 35, you add $2,500 per year but the interest rate remains at 4%?

We can use an approach for this one that is similar to (d), but now, instead of changing the interest rate for the outer call to fv, we change the payment size.

• Interest over a 4-year period is 4% (compounded annually)
• Payments are 2,500; 2,500; 3,200; 4,200, made at the beginning of each period.
• How much is available at the end of year 4?

This is just a more complex version of (e), and we could use the approach of (e) to do it, but it's clumsy. A better way is to decompose the payment stream into a sequence of constant-payment streams of different lengths. The first stream is four payments of 2500. The second is two of 700, and the third is one of 1000. We compute the future values of each of these streams, and total them.

Alternatively, we could compute the future values of four lump-sum payments and total those. We show this approach just below the first. It uses the names PaymentStream and FuturePeriods to make the computation a bit more convenient, but each of the four cells is just computing the future value of a single lump-sum payment.

Here we re-work (f) using Convolve. This requires that we compute the base response to a single-point event, and convolve that with the actual input stream. The base response is called CompoundInterest, and the actual input is called PaymentStream. This example is more of a curiosity — we simply want to point it out for those of you who might be interested.

Now we turn our attention to present value. You're saving for retirement. You've found an investment vehicle that pays 4% compounded annually. Your goal is $500,000. How much must you invest now to reach your goal in 35 years?

This is a straightforward application of pv. The fifth argument, payment type, is irrelevant because this is a lump-sum problem.

How much do you need if, in years 21 through 35, the interest rate is 6%?

Here we explore how to handle varying interest rates. First we propagate the value back in time to year 20, then propagate it back the rest of the way to the present. The first segment is the inner call to pv That result is used as a lump-sum argument to the outer call.

You win $1M in the lottery. Assuming that the interest rate is 4%, would you rather have $50K each year for 20 years or $750K in a lump sum right now?

This example is of some topical interest. If you examine the conditions of the prizes for lotteries, you can actually determine which choice to make using pv.

• Interest over a 4 year period is 4% (compounded annually)
• Payments are 2,500; 2,500; 3,200; 4,200, at the beginning of each period.
• What is the present value of this income stream?

We compute this result in two ways, and show that they're the same. First we just divide NonUniformPayments (a stream) by CompoundInterest, defined above. This gives the present value of each of the non-uniform payments. The total is to the right.

Second, we compute the present value of each payment, considered as a lump sum.

• How much do you have to save each month in a savings account that pays 4% per year compounded monthly to reach a goal of $100 in 10 months?
• What is the future value of that payment stream?
• What's the present value of that future value?
• How much would you have to pay each month to repay a loan of that amount if the interest rate is 4% per year, computed monthly?

The first part of this one reverses what we have been doing: it computes the payment, using the pmt function. This would be very difficult to do without Excel or a table.

In the second part, we verify that this payment does indeed produce the goal of $100.

In the third part, we compute the present value of the stream. This is straightforward.

In the last part we verify that the meaning of the payment is the same for both the payer and payee. We have recast the statement of the problem in different terms, but the arithmetic yields the same results. Often two problems are very different, but the computations we actually do to solve them are identical.