If you use Excel to model businesses, business processes, or business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.
To Order On Line
Order "Spreadsheet Models for Managers, online edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email. 
Order "Spreadsheet Models for Managers, online edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. 
Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. 
To Order by Mail
Make your check payable to Chaco Canyon Consulting, for the amount indicated:

And send it to: Chaco Canyon Consulting 700 Huron Avenue, Suite 19C Cambridge, MA 02138 
To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.
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.
This file contains a large number of small examples illustrating the computation of Present Value and Future Value.
You save $2,000 per year for 35 years at 4% per year compounded annually. You make payments at yearend. 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.
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.
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.
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’s similar to (d), but now, instead of changing the interest rate for the outer call to fv, we change the payment size.
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 constantpayment 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 lumpsum 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 lumpsum payment.
Here we rework (f) using Convolve. This requires that we compute the base response to a singlepoint 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 lumpsum problem, but we supply the default value, 0, anyway.
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 lumpsum 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.
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 nonuniform 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 Periods1. 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.
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, 27Apr2016 04:15:26 EDT
Nesting invocations of worksheet functions can be a bit tricky, because nested function calls are difficult to think about. Sometimes, in developing a spreadsheet model, we can gain clarity by avoiding nesting. That is, while we’re still thinking about how to approach a modeling problem, we intentionally choose to avoid nesting function calls. After we understand the problem better — and only then — we might go back and replace what we’ve done with a more compact version that exploits nesting. In addition to producing forms that are easier to think about, this practice of developing a simpler form first has another benefit. It enables us to examine intermediate values more easily, which enables us to confirm that the calculations we’re performing make sense.
Some feel that building something that you intend to replace is a waste of effort — that it’s far easier to build things in final form from the start. When that approach works, it is faster and more efficient. But when we think we’re likely to make mistakes, the “slower” way is faster.
In years past, we’ve learned that what makes a model dynamic — as opposed to static — can be difficult to grasp. If you have some doubts yourself, and you haven’t yet looked at the reading on Models vs. Tools, we believe that you will find it helpful.