Output of file : NPV.DOC contained in archive : POWERWKS.ZIP

NPV - Net Present Value Calculator

1. INTRODUCTION

The net present value of a cash flow (at a given discount
rate) is found by discounting all values in the cash flow back to
the opening period ('now') and calculating their sum, taking into
account the sign of each value. It yields a 'current value' for
the cash flow, allowing for the time value of money (ie. \$1 now
is worth more than \$1 next year).

Net present value is a commonly-used method of evaluating
projects or investments, or of comparing two investments with
different cash flow characteristics. (Another measure is the rate
of return, usually calculated as the discount rate which reduces
the net present value of the cash flow to zero. See the ROR
worksheet for a discussion of rate of return.)

This worksheet will calculate the net present value of a cash
flow at a number of discount rates. You can produce a plot of the
cash flow and cumulative cash flow by period. And you can produce
a plot of the net present value against the discount rate. The
discount rate at which this latter graph crosses the X axis is the
internal rate of return of the cash flow (see the ROR worksheet).

2. USING NPV

2.1 Overview

The main menu of the NPV worksheet offers you seven options
(plus Quit). They are:

Input
This option allows you to enter cash flow values and
discount rates. It presents a sub-menu from which to select
the data you want to input.

Blank
This option blanks out the input and results areas of the
worksheet, after receiving confirmation this is what you
intend. It is useful for clearing out all data before
entering an entirely new cash flow.

Calc
The calc option performs all calculations. You must use
this option whenever you change data or enter new data.

Results
When you select this option, you are presented a sub menu
allowing you to choose between the cash flow and the net
present value areas. The only essential difference is in
the set of column headings the program freezes as you scroll
through the data.

09/09/84 NPV - 1

Graph
This worksheet offers two main graphs. You can plot the
cash flow and cumulative cash flow by period; and you can
plot the net present values against the discount rates.

Print
This option sends the cash flow and net present value data
to your printer, after a pause for paper alignment.

Agenda
If you select this option, and confirm it when requested,
the current worksheet is lost and replaced by the main
worksheet selection agenda. You can then load another
worksheet. Note confirmation is required before this option
proceeds, because it overwrites the worksheet in memory.

2.2 Input Details

Choosing the Input option from the main menu leads to a sub
menu with three options - Cashflow, Periods/Yr, and DiscRate.
Each option allows you to enter part of the data.

The Cashflow option allows you to enter cash flow values by
period. This option places the cell pointer at the top of the
cash flow column and moves it down one cell each time you press
the Enter key. Thus, if you want to enter the entire cash flow,
simply type each number in turn and press the Enter key after each
one. Please note the following:

- A value must be entered for each period covering the
entire duration of the project and starting at period
zero. If the cash flow value for a particular period is
zero, enter zero. Do not leave the cell blank.

- Period zero is now, period one is one period (year,
month, or whatever) hence.

- Cash outflows must be entered as negative numbers; cash
inflows must be entered as positive numbers.

If you don't want to enter the whole cash flow (for example,
if you want to adjust a cash flow you've already entered) you may
use the cursor movement keys to place the cell pointer on any
number you want to change, and re-type it in normal 1-2-3 fashion.
When you press the Enter key, the macro will move the cell pointer
down one row.

The macro which drives this process has no way of knowing
when you've finished entering or changing data. When you have
finished, you must break out of the macro and re-invoke the input
menu. To do this, hold down the Ctrl key and press the Break key.
The CTL flag at the top of the screen will go out, indicating
you've broken out of the macro. Then hold down the Alt key and
press I to invoke the input menu.

09/09/84 NPV - 2

The Periods/Yr option accepts the number of periods that
correspond to a year. If you have annual data, you must enter 1.
If you have monthly data, enter 12. This parameter is used to
convert the discount rates from the annual values you enter to
values applicable to periods. Thus, whatever the periods your
data represents, you always enter discount rates as annual values.

The third choice in the input menu is DiscRate. It allows
you to enter annual discount rates for which the worksheet is to
calculate the net present value. Enter the rates as percentages.
Do not use the % key or convert to a decimal (for example, to
enter 20%, type 20 and press the Enter key). It's a good idea to
include zero in the set of discount rates, because this gives the
straight sum of the cash flow values - a figure you can calculate
independently to check your data entry.

2.3 Graph Details

This worksheet can produce two different graphs on either the
color or monochrome display. Each graph can take the form of a
line graph, a bar chart, or a scatter graph (points without
connecting lines). These are selected by means of a menu with
four choices.

The Cashflow option displays a graph of the cash flow by
period. The form of the graph will be the same as the one that
was last displayed. The NPV option displays a graph of the net
present value against the discount rate. This shows you how the
net present value behaves as future values are discounted more or
less heavily. The discount rate (if any) at which the graph
crosses the X-axis represents the rate which reduces the net
present value of the cash flow to zero. This is known as the
internal rate of return of the cash flow.

The Type option presents a sub-menu from which you can select
the form of the graph. The three choices are Line, Bar, and
Scatter. Each choice will immediately re-display the last graph
in the selected form, then return to the graph menu.

The final choice - Options - allows you to choose the Color
or monochrome (B&W) monitor for the display. Again, each choice
re-displays the last graph on the selected device.

3. EXAMPLES

3.1 A Simple Investment

This example shows how the net present value varies with
discount rate when the cash flow is well-behaved. You have been
given the opportunity to purchase a note for \$15,000. The note
will return \$5,000 at the end of the first year, \$7,000 at the end
of the second year, and \$9,000 at the end of the third year. What
is the net present value of this investment at various discount
rates?

09/09/84 NPV - 3

Keystroke Comments

{ENTER} Select the Input option from the main menu.

P 1 {ENTER} Select the Periods/Yr option from the input menu,
and enter the number of periods per year (the data
is annual)

C Select the Cashflow option from the input menu.
The cell pointer will go to the top of the cash
flow column.

-15000 {ENTER} Enter the value for period zero (now). Since this
is a payment (ie. a cash outflow) its value is
entered as a negative number. When you press the
ENTER key, the cell pointer moves down to the next
cell in the column.

5000 {ENTER} Enter the value returned at the end of the first
year. This is a cash inflow.

7000 {ENTER} Enter the value returned at the end of the second
year.

9000 {ENTER} Enter the value returned at the end of the third
year.

{Ctrl}{Break} To terminate the input loop, hold down the Ctrl
key and press the Break key. The CMD flag at the
top of the screen will go out.

{Alt} I To re-invoke the input menu, hold down the Alt key
and press I.

D Select the DiscRate option to enter the discount
rates, as annual percentages. The cell pointer
will move to the top cell in the discount rate
column.

0 {ENTER} The net present value at zero percent is the sum
of the cash flow items. In this example, it's
easy to check your input by eye, but you may want
to get into the habit of always entering zero in
the discount rate column to give you a total cash
flow, which you could check independently.

5 {ENTER} Enter the remaining discount rates.
10 {ENTER}
15 {ENTER}
20 {ENTER}
25 {ENTER}
30 {ENTER}

{Ctrl}{Break} As for the cash flow column, hold down the Ctrl
key and press Break to terminate the input loop.

09/09/84 NPV - 4

{Alt} I Then hold down the Alt key and press I to re-
invoke the input menu.

Q Return to the main menu

C Select the Calc option in the main menu. The net
present values will be calculated

G Select the Graph option from the main menu.

text {ENTER} Whenever you enter the graph menu, the system will
ask you to enter a second title line for the
graphs. You may leave this line blank, or enter
any short description. To get rid of an existing
description before typing a new one, press the Esc
key.

N This option displays a graph of the net present
value against the discount rates. Press any key
to return to the graph menu.

T L If the graph is not already a line graph, you can
go to the Type sub-menu to specify a line graph.
The graph will be re-displayed. Press any key to
return to the graph menu.

The line graph shows that the net present value of the cash
flow is zero at a discount rate of about 17%. This is the
internal rate of return of the cash flow. You should buy the note
if this rate of return is sufficiently high, bearing in mind all
other factors (such as security).

3.2 An Accelerated Income Project

Many projects in the resources industry are aimed at
accelerating resource recoveries. This leads to increased cash
flows in early years, followed by compensating decreased cash
flows in later years. Thus the cash flow consists of a series of
outflows (the investment), followed by a series of inflows (the
increased cash flows) followed by another series of outflows (the
reduced cash flows). This is not a 'well-behaved' cash flow. The
NPV worksheet can illustrate how the net present value of such a
cash flow varies with discount rate.

You are considering the value of an enhanced recovery
project. The immediate cost of this project is \$600. It will
increase cash flows in the next three years by \$800, \$500, and
\$200 respectively. In the three years after that, however, cash
flows will be reduced by \$200, \$500, and \$800 respectively. You
need to know the net present value of this project at various
rates of return.

09/09/84 NPV - 5

Keystroke Comments

{ENTER} Select the first option - Input - from the main
menu.

P 1 {ENTER} Enter the number of periods per year (the data is
annual).

C Select the Cashflow option from the input menu.
The cell pointer will go to the top cell of the
cash flow column.

-600 {ENTER} Enter the initial investment (in period zero).
The cell pointer will automatically move down to
the next period when you press the Enter key.

800 {ENTER} Enter the increases in cash flow values in the
500 {ENTER} first three years.
200 {ENTER}

-200 {ENTER} Enter the decreases in cash flow values in the
-500 {ENTER} next three years.
-800 {ENTER}

{Ctrl}{Break} When all the cash flow values have been entered,
hold down the Ctrl key and press the Break key to
break out of the macro.

Alt I To re-invoke the input menu, hold down the Alt key
and press I.

D Select the DiscRates option in the input menu to
enter discount rates. The cell pointer will go to
the top cell in the discount rates column.

0 {ENTER} Enter the discount rates, as annual percentages
5 {ENTER} Each time you press the Enter key, the cell
10 {ENTER} pointer will move down to the next cell in the
15 {ENTER} column.
20 {ENTER}
25 {ENTER}
30 {ENTER}
35 {ENTER}
40 {ENTER}
45 {ENTER}
50 {ENTER}
55 {ENTER}
60 {ENTER}
65 {ENTER}
70 {ENTER}
75 {ENTER}
80 {ENTER}
85 {ENTER}
90 {ENTER}
95 {ENTER}
100 {ENTER}

09/09/84 NPV - 6

{Ctrl}{Break} When you have entered all the discount rates, hold
down the Ctrl key and press the Break key to break
out of the input loop.

{Alt} I To re-invoke the input menu, hold down the Alt key
and type I.

Q Return to the main menu.

C Invoke the calculations.

G Select the graph option from the main menu.

text {ENTER} Each time you invoke the graph menu, it asks you
to enter the second title line for the graphs.
You may enter any short description. To remove an
existing description before typing a new one,
press the Esc key.

N Select the NPV option in the graph menu to display
a graph of the net present value against the
annual discount rates. Press any key to return to
the graph menu.

T L Specify the type of graph as a line graph. The
graph will immediately be re-displayed. Press any
key to return to the graph menu.

Q Return to the main menu

Q Exit to 1-2-3 READY mode. To return to the main
menu, hold down the Alt key and type M.

09/09/84 NPV - 7

Menu Tree for NPV Worksheet

Input - Input periods/year, cash flow, and discount rates
. Periods/Yr - Enter number of periods per year
. Cashflow - Enter cash flow values by period
. DiscRate - Enter annual discount rates (%)
. Quit - Return to main menu

Blank - Blank out all input areas
. No - Do not erase data. Return to main menu
. Yes - Erase all input data and results

Calc - Calculate Results

Results - Display Results
. Cashflow - Display cashflow figures and column headings
. . Down - Scroll down one screen
. . Up - Scroll up one screen
. . Quit - Return to results menu
. NPV - Display net present values and column headings
. . Down - Scroll down one screen
. . Up - Scroll up one screen
. . Quit - Return to results menu
. Quit - Return to main menu

Graph - Plot graphs of cashflow and/or results
. Cashflow - Display graph of cashflow and cumulative cashflow
. NPV - Display graph of net present values
. Type - Set type of graph
. . Line - Display a line graph
. . Bar - Display a bar chart
. . Scatter - Display a scatter chart
. Options - Set color or monochrome display option
. . Color - Display graph in color
. . B&W - Display graph in monochrome
. Quit - Return to main menu

Print - Print Results

Agenda - Return to Worksheet Selection Agenda
. No - Do not leave this worksheet. Return to main menu
. Yes - Leave this worksheet. Load the selection agenda

Quit - Exit to 1-2-3 READY mode

09/09/84 NPV - 8

### 3 Responses to “Category : Lotus and other SpreadsheetsArchive   : POWERWKS.ZIPFilename : NPV.DOC”

1. Very nice! Thank you for this wonderful archive. I wonder why I found it only now. Long live the BBS file archives!

2. This is so awesome! 😀 I’d be cool if you could download an entire archive of this at once, though.

3. But one thing that puzzles me is the “mtswslnkmcjklsdlsbdmMICROSOFT” string. There is an article about it here. It is definitely worth a read: http://www.os2museum.com/wp/mtswslnk/