# Category : Lotus and other Spreadsheets

Archive : POWERWKS.ZIP

Filename : NPV.DOC

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.

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

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

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

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/