Download lab spreadsheet discounting-lab.xlsx (OR 2016 version). The spreadsheet has nine worksheets. The things you want to take away from this lab are:

  1. how to use power function in Excel
  2. how to construct simple discounting schedule in excel
  3. how to use Excel's NPV function to calculate net present value of a series of costs and benefits.
  4. how to use Excel's IRR function.
  5. how to chart a series of costs and benefits

Background Excel Functions

NPV(rate,value1,[value2,…)] (net present value of a series of payments over time)

First, learn about the POWER function

We will need the Excel function for raising a number to a power (base to an exponent). The syntax is power(base, exponent). What formula, for example, would give us the value of 202?

\begin{equation} =power(20,2) \end{equation}

TASK 1. On worksheet POWER, construct a simple table of what the numbers 0 to 9are when raised to the powers of 0 through 9.


Simple Compound Interest

Compound interest calculation. Suppose we have 100 initially and invest it at 5% interest compounded annually. How much do we have over each of the next ten years?

Discounting 101

1. For our first example, let's reprise the simple case we discussed in class. You are offered a payment of $10 now, 25 in three years, or 200 in ten years; what is your best choice. As we saw in class, the 200 strikes us intuitively as the best, but let's work it out.

What equation does the trick for us?

PV = FV / (1+R)^n^

Three Successive Payments

Next, let's look at our example of three successive payments: what is the present value of a payment of $5 for each of the next three years? The basic rule is that we add the present values of each of the payments together to get the total present value of the stream of payments.


New Car or Used?

Again, reprising an example we have already seen.

Costs and Benefits

OK, so finally, we can apply all of this to a project that has a projected series of annual costs and benefits. After you have calculated and compared NPV for each scenario, make a chart that nicely shows how they compare. Do a short (a few sentences) writeup and make a chart that shows the costs/benefits stream to show the work and show off your skills (at discounting and at presentation).

Comparing Discount Rates

What if we have two projects and their respective streams of costs and benefits and we want to compare them under different assumed discount rates? Try it on this next worksheet. Chart the results. Comment.