Go to questions covering topic below

We are assuming that the reader is familiar with spreadsheet basics and they will not be described here.

In economic analysis spreadsheets may be helpful for a number of purposes:

- Constructing tables of cash flows
- Using annuity functions to calculate P, F, A, n, or i.
- Using a block function to find the present worth or internal rate of return for a table of cash flows
- Making graphs for analysis and presentations
- Calculating “what-if” for various problem variables.

**Spreadsheet annuity
functions**

There are five annuity functions that you may be using:

To find the equivalent P | –PV(i,n,A,F,Type) |

To find the equivalent A | –PMT(i,n,P,F,Type) |

To find the equivalent F | –FV(i,n,A,P,Type) |

To find n | NPER(i,A,P,F,Type) |

To find i | RATE(n,A,P,F,Type,guess) |

Where:

i = interest

n = number of periods

A = Annual Value (or Worth)

P = Present Value (or Worth)

F = Future Value (or Worth)

Type: 0 or omitted means calculations are at the end of the period;

Guess is an initial starting point for a possible interest rate.

You only enter the values needed to permit the calculation. If you know the interest, number of periods, and the future value, that is sufficient. For example. i=5%, n=10, and F=$20,000.00, you would set it up in the spreadsheet:

Interest | .05 |

Periods | 10 |

Annual Value | $20,000.00 |

Present Value | –PV(i,n,A,F,Type) |

Cell entry: | =-PV(B1,B2,B3) |

Answer | $154,434.70 |

If you copy this table and paste it in an empty spreadsheet, starting in cell A1, B5 will show the answer we have provided in B6. Notice that a minus sign is required to yield a positive answer. This is a convention for these annuity functions. This is true for PV, PMT, and FV, the value is negative for positive parameter values. Also you will see that the interest is represented as a decimal however Excel will allow you to designate a cell to be a “percent” in which case it knows to use the decimal equivalent.

Now look at another function, PMT, which means payment, but is what we refer to as the Annual Value.

Interest | .05 |

Periods | 10 |

Future Value | $20,000.00 |

Annual Value (payment) | –PMT(i,n,P,F,Type) |

Cell entry: | =-PMT(B1,B2,,B3) |

Answer | $1,590.09 |

Notice that there are two commas (,,) together. The function expected to see the value for P between the commas, but it is only necessary to provide one of the two parameters P or F. Similarly for PV provide A or F and for FV provide A or P.

Notice that this tells us that at 5% interest, by depositing $1590.09 each year for 10 years we will have $20,000 at the end.

Once you have set up these functions you can try different values. Change the value for interest; what happens?

Now consider the RATE function.

Periods | 10 |

Annual Value | $1500.00 |

Future Value | $20,000.00 |

Interest (RATE) | RATE(n,A,P,F,Type,guess) |

Cell entry: | =RATE(B2,-B3,,B4) |

Answer | 6% |

Here again there is a sign convention; the annual value must have the opposite sign from PV or FV to get the expected interest.

Excel provides help for these functions. Enter the beginning
of a function, for example “=FV(“ including the first parenthesis and Excel
will show the parameters needed. To get moer help click on the ** f_{x}**
icon and Excel will guide you through completing the function entry. If you
want more help this last window has a “Help with this function” button which
provides a full explanation and detailed directions for use.

**Spreadsheet block
functions**

There are two Excel functions that work on a series or block of values. They are NPV(i,values) (Net Present Value) and IRR(values, guess) (Internal Rate of Return).

To use these, it is necessary to have a table of the data. Here is an example for NPV: The function use cash flows that occur at the end of each period thus an flow at the beginning of the first year must be handled separately.

Year | 0 | 1 | 2 | 3 |

Cash flow | -50000 | 8000 | 18000 | 35000 |

Interest | 0.01 | |||

Net Present Value | NPV(i,values) | |||

Cell entry | =NPV(B3,C2:E2)+B2 | |||

Answer | $9,536.78 |

Again copy and paste this table into an empty spreadsheet beginning in the A1 cell. It assumes an interest rate of 10% as shown in the third row. As before, when you paste this into the spreadsheet, the cell entry value is immediately calculated and will show the number in the Answer row. Note that to account for the flow at year 0, cell B2 must be excluded from the NPV function and then added in.

We will cover the IRR function in a later chapter.

**Using spreadsheets for
basic graphing**

Excel provides a Wizard to help create graphs. However to begin, you must first have a table of values to plot. It demonstrate we will use the example above where we calculated a PMT. We will examine how the payment varies as the interest rate changes and then plot this behavior. Here is the table from before:

Interest | .05 |

Periods | 10 |

Future Value | $20,000.00 |

Annual Value (payment) | –PMT(i,n,P,F,Type) |

Cell entry: | =-PMT(B1,B2,,B3) |

Answer | $1,590.09 |

Remember that this tells us that at 5% interest, by depositing $1590.09 each year for 10 years we will have $20,000 at the end. How would the annual payment (deposit) have to change if the interest rate changed? Take this table:

Interest | .05 |

Periods | 10 |

Future Value | $20,000.00 |

Annual Value (payment) | –PMT(i,n,P,F,Type) |

Cell entry: | =-PMT(B1,B2,,B3) |

Answer | $1,590.09 |

Now we create a new table which provides a group of interest rates but still uses the same PMT function.

Periods | 10 | 10 | |

Future Value | $20,000.00 | $20,000.00 | |

Interest rate | Payment | Payment | |

.04 | $1,665.82 | =-PMT(A5,B1,,B2) | ‘=-PMT(A5,B1,,B2) |

.05 | $1,590.09 | =-PMT(A6,B1,,B2) | |

.075 | $1,413.72 | =-PMT(A7,B1,,B2) | |

.1 | $1,254.91 | =-PMT(A8,B1,,B2) | |

.2 | $770.46 | =-PMT(A9,B1,,B2) |

Notice that the number of periods and the Future Value are constant. Now we have a table which shows the way the payment varies with the interest rate. What does that look like on a graph? To see, in your spreadsheet, select the cells from A4 to B9 (where it says Interest rate to where you see $770.46). Then click on the menu item Insert and select Chart. The Chart Wizard opens. Since you want to plot Payment vs. Interest rate, you want the XY (scatter) chart. As you will notice, you may display just the points or a variety of ways to connect them. Select one that shows a scatter chart with data points connected by smoothed lines. Follow the directions in the Wizard to its finish. At one point you will see a window called Chart Options; on the Titles tab you can enter titles for your X and Y axes. You will get something like this:

Once again, you may play with the values and the chart options. Explore these capabilities. Then go to the questions for this topic.

Return to Spreadsheets for Economic Analysis

Return to More Interest Formulas Tutorials Menu

Question 1.

This is a modified question from earlier in this chapter.

Suppose that $30,000 is borrowed today at 13% interest. The loan is to be repaid by uniform monthly payments for five years, beginning one year from now. Calculate the annual payment using a spreadsheet.

Choose an answer by clicking on one of the letters below, or click on "Review topic" if needed.

A A = ($710.79)

B A = $682.79

C F = $55,273.06.

D A = $682.59

Question 2.

With a 9% interest rate, find the Net Present Worth for the following cash flows:

Year | Amount |

0 | ($55,000.00) |

1 | $25,000.00 |

2 | ($3,000.00) |

3 | $18,000.00 |

4 | $30,000.00 |

A NPV = $516.33

B NPV = $562.80

C NPV = $106,066.86

D NPV = ($52,509.00)

Question 3.

For the cash flows below, which are possible in a start-up business, complete the table and create a chart showing the Cumulative Net vs Month. The first two lines have been filled.

Month | Revenue | Expenses | Net | Cumulative net |

0 | 0 | $10,000 | ($10,000) | ($10,000) |

1 | 0 | $10,000 | ($10,000) | ($20,000) |

2 | 0 | $10,000 | ||

3 | 0 | $20,000 | ||

4 | 0 | $20,000 | ||

5 | $15,000 | $20,000 | ||

6 | $25,000 | $20,000 | ||

7 | $35,000 | $25,000 | ||

8 | $50,000 | $37,500 | ||

9 | $75,000 | $47,500 | ||

10 | $95,000 | $60,000 | ||

11 | $120,000 | $67,500 | ||

12 | $135,000 | $77,500 | ||

13 | $155,000 | $87,500 | ||

14 | $175,000 | $107,500 | ||

15 | $215,000 | $117,500 | ||

16 | $235,000 | $135,000 |

Select the correct chart from the following:

**A**.

**B**.

**C**.

**D**.