We use cookies to enhance your experience on our website. By continuing to use our website, you are agreeing to our use of cookies. You can change your cookie settings at any time. Find out more
masthead
 

Spreadsheets for economic analysis

More Interest Formulas

Spreadsheets for Economic Analysis

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:

  1. Constructing tables of cash flows
  2. Using annuity functions to calculate P, F, A, n, or i.
  3. Using a block function to find the present worth or internal rate of return for a table of cash flows
  4. Making graphs for analysis and presentations
  5. 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;

1 means calculations are at the beginning 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 fx 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.

More Interest Formulas

Spreadsheets for Economic Analysis

Question 1

Question 2

Question 3

Return to Spreadsheets for Economic Analysis

Return to More Interest Formulas Tutorials Menu

Return to 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

Review topic

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)

Review topic

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.

Review topic



Legal Notice | Privacy Policy | Cookie Policy
Please send comments or suggestions about this Website to custserv.us@oup.com        
cover