Spreadsheet Models for Managers

Demonstrations for Session 9
Capital Leases I

We have one demonstration (2007+) for this session: Computing Present Value and Future Value. We’ll need a thorough understanding of these concepts when we return to the question of computing the effects of streams of lease events on the three financial statements.

Computing Present Value and Future Value (2007+)
[Sheet: Examples]

This file contains a large number of small examples illustrating the computation of Present Value and Future Value.

(a)

You save $2,000 per year for 35 years at 4% per year compounded annually. You make payments at year-end. How much will you have at the end of 35 years?

This is a simple example involving Future Value. We have a constant stream of payments, and seek its future value. The only tricky thing is the minus sign, which appears because we’re “paying” into the stream. Since payments are made at the end of each period, the fifth argument, type, would be 0. Since it happens that this is the default value, we don’t have to supply it explicitly. However, it’s wise to make a practice of always supplying the optional arguments, even if they’re zero. This shows your intent explicitly, which makes your worksheet more understandable to those who follow you.

(b)

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, but we supply the default value, 0, anyway.

(c)

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, in this case, 1.

Notice that the two payment arguments (the periodic payment, and the lump sum amount) contribute linearly. That is, we can compute their effects separately and sum their results. This is illustrated in the three cells immediately below result C. Also notice that the type argument has no effect on the lump sum amount. This point is illustrated in the cell just above result D.

(d)

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.

(e)

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’s similar to (d), but now, instead of changing the interest rate for the outer call to fv, we change the payment size.

(f)

• Interest over a four-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.

(g)

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.

(h)

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, but we supply the default value, 0, anyway.

(i)

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.

(j)

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.

(k)

• 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, and then multiply by (1 + Rate). The second factor, (1+Rate), is necessary because payments occur at the beginning of the period. 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. In this computation, we require as the second argument of pv the number of periods for which the payment is held. Since the payments occur at the beginning of the period, the expression we use is Periods-1. In this way, for example, the first payment is held for 0 periods, which is correct since it occurs at the beginning of the first period.

(l)

• How much must you 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.

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT