contentsturing.une.edu.au/~math120/economics/120prac.pdf · to know all the details before getting...
TRANSCRIPT
CONTENTS i
Contents
1 Getting Started, Entering Data and Formula 1
2 Drawing Graphs 4
3 Using Solver to Find Zeros, Minima and Maxima of Functions 10
4 Using Matrix Commands in Excel 13
5 Solving Systems of Linear Equations by Using Excel 15
6 Using Solver for Linear Programming Problems 18
7 Using Financial Functions in Excel 23
1
1 Getting Started, Entering Data and Formula
1. General Advice
In this class you are to learn to enter data e!ciently into cells and to enter
formulae into cells.
You may have never used Excel before, but the important thing is to get
started. You might like to play with the Excel screen , the various bars,
and have a try on how to save and open Excel workbooks. You don’t have
to know all the details before getting your hands on the machine. You’ll get
yourself familiarized with them when you do the practice problems in this class
and later on.
2. Entering Data into Cells.
Each cell is named by its location in the worksheet. For example, B2 refers to
the cell at column B and row 2. A100 refers to the cell at column A and row
100.
If you want to enter something into a specific cell, you should move the cursor
to that cell, click the left button on the mouse, and then type what you wanted
followed by pressing the [Enter] key.
Task 1: Enter your name into cell B2.
Enter your student number into cell D2.
Enter the degree you are doing into cell F2.
Enter MATH120, PC1 into cell H2.
In many practical problems, for example, when you want to draw graphs of a
function in later practical classes, you will need to enter a set of values into a
range of cells. If these values are evenly spaced, then you can use the Autofill
in Excel to enter them in the following e!cient way.
Suppose you want to enter the values 1, 2, 3, . . . , 99, 100 into the cells from A1
to A100 (which is often denoted by A1 : A100), then you can use the following
4 steps:
(a) Enter 1 in cell A1 and 2 in cell A2.
(b) Use the mouse to highlight these two cells.
(c) Move the cursor to the bottom right corner of the shaded area so that its
shape changes from an arrow to a cross.
(d) Hold down the left button on your mouse and drag it down to cell A100
so that the block of cells A1:A100 is highlighted. Release the left button
on the mouse. Then the values 1, 2, 3, . . . , 99, 100 should appear in the
cells A1:A100.
2
If you want the even numbers 2, 4, . . . , 200 entered, enter 2 in cell A1 and 4 in
cell A2 then use the same 4-step procedure.
Task 2: Enter 0, 0.1, 0.2, . . . , 0.9, 1, 1.1, 1.2 into cells B6:B18.
Enter !10,!9, . . . ,!1, 0, 1, . . . , 9, 10 into cells E6:E26.
3. Entering Formulae into Cells.
Suppose you want to find the values of the function y = x2 for the x values
!10,!9, . . . , 9, 10. Now that you have already entered these values in cells
E6:E26, you can enter just this function and Excel will find the y values for
you. Excel uses the following symbols for the operations:
a + b " a + b a! b" a! b
a# b " a $ b a/b" a/b
ab " a!b
Therefore, 3x2 + 5x + 6 becomes 3 $ x!2 + 5 $ x + 6
(x + 2)2 + 4x becomes (x + 2)!2 + 5 $ x
Note that we use the bracket ( ) to make the operations inside it take prece-
dence over all other operations.
Now let us enter x into cell E5 to denote that the values in cells E6:E26 are
the di"erent x values. Let us also enter y = x!2 into cell F5 to denote that
we want the corresponding y values to be given in cells F6:F26.
To find the y value for x taking the value -10 in cell E6, we enter the formula
= (E6)!2 into cell F6 (Note: Don’t forget the = sign). When we press [Enter]
we should have 100 in cell F6, that is the y value when x = E6 (which means
when x takes the value in cell E6). If we now change the value !10 in cell
E6 to !11, then the value in cell F6 changes automatically to 121, which is
(!11)2.
Now let us use Autofil:
(a) Use the mouse to highlight the cell F6.
(b) Move the cursor to the bottom right corner of the shaded area so that its
shape changes from an arrow to a cross.
(c) Hold down the left button on the mouse and drag it down to cell F26,
and then release the left bottom on the mouse. We should now have the
corresponding y values in cells F6:F26.
Task 3: For the x values in cells E6:E26, find the values of the
function y = 5x + x2 and put them in cells F6:F26.
For the x values in cells B6:B18, find the values of y =
(x + 1)2 + x and put them in cells C6:C18.
3
Task 4: (a) The population of a certain city at time t (mea-
sured in years from 1990) is given by the formula P =
50000e0.05t. Calculate the population in each year be-
tween 2001 and 2010.
(b)A company finds that the number y of dollars per
week that it must spend on advertising in order to sell
x units of its product is given by y = 2000 ln ( 400500"x).
Make a table to show the advertising expenditure needed
in each case when the units to be sold are in the range
between 200 and 230 units.
This is the end of practical class 1. To submit online, go to the homepage of
math120 (http://turing.une.edu.au/%math120/) or refer to guidance to sub-
mission online for math120.
To print, click File & Print
Select the right printer and click OK on the dialog box.
4
2 Drawing Graphs
1. General Advice
In the last practical class you have learned how to enter data and formulae
into cells. This allows you to use Autofil to enter a set of values of the
independent variable x and then find the corresponding values of a function,
such as y = x2 + 5x + 6. In this class, we are to learn how to draw the graphs
of functions using Excel. The importance in applications of such a skill is
obvious.
2. Drawing the Graph of One Function
Suppose we have the function y = x2 + 1 and we want to draw the graph of
this function for the range of x between !1 and 1. Then what we need to do
is the following:
(a) choose several values of x between !1 and 1, say
!1,!0.9,!0.8, . . . , 0, 0.1, . . . , 1.
(Note: we choose the values that space evenly so that we can use Autofil
to enter them easily).
(b) Find the corresponding values of the function y = x2 + 1.
(c) Draw the graph by Excel.
Steps 1 and 2 can be done using what we learned in the last practical class;
while we need to learn to do step 3.
Therefore, we can enter x into, say, cell A1, and then use Autofil to enter
the values !1,!0.9, . . . , 1 into cells A2:A22. Next we enter y = x!2 + 1 into
B1 (to denote what the values in the cells below stand for), and then enter
= (A2)!2 + 1 into B2 to obtain 2 and use Autofil to find the values of the
function in cells B3:B22.
Now we are ready to draw the graph using the Chart Wizard in Excel.
To obtain a chart:
(a) Go to the menu bar and click Insert & Chart, the Chart Wizard -
step 1 will appear (Fig 2.1).
(b) In Chart type, select Line, in Chart sub-type, select the first one, as
highlighted in Fig 2.1. After that click Next > go to step 2.
(Tips: Use the “Press and Hold to View Sample” button to see the
preview of the chart if one of the data in the sheet is selected as displayed
in Fig 2.2)
5
Figure 2.1: The Chart Wizard dialog box
Figure 2.2: The preview of the chart
6
Figure 2.3: Data range
(c) In the data range tab, enter the range of cells A1:B22, choose Columns
in the entry Series in (because our data are in columns A and B) as in
Fig 2.3.
(d) Then click the Series tab to setup the series displayed in this chart. We
don’t need to display the graph for x, so in Series list select x and click
Remove button to remove it. In Category (X) axis labels, choose
the value of the x (A2:A22) as the x axis labels. Then the result should
look like that in Fig 2.4. After all of these is done, click Next > and go
to step 3.
(e) In the Titles tab, under Chart Title, we can enter “the graph of the
function y=x!2+1”. In Axis Titles, we can enter “x, the independent
variable” in Category (X) axis, and enter “y, the dependent variable”
in Value (Y) axis as in Fig 2.5. Then go to the Legend tab, choose
Right under Placement of the legend as shown in Fig 2.5. Then, click
Next > to the final step.
(f) In this step, we can choose where to put this chart. Set the value as
displayed in Fig 2.6 (chart will be displayed as an object in Sheet 1)
which is also the default choice. Finally, click Finish button to obtain
our graph. Put the cursor in the chart containing the graph, keep the left
button of the mouse pressed, you can drag the chart to wherever you like
in your worksheet. You can also change the size of the chart by putting
7
Figure 2.4: Choose right series (delete unnecessary series) and category axis labels
8
Figure 2.5: Setup the title, category, value and legend label
Figure 2.6: Chart location
9
the cursor on the black boxes on the frame of the chart, keeping the left
button of the mouse pressed and dragging. Fig 2.7 shows a proper chart
display in the worksheet.
Figure 2.7: The chart in worksheet
In step (2), we can also choose other chart type such as XY (Scatter).
This will depend on the data.
3. Drawing the Graphs of Several Functions
Very often we want to draw the graphs of several functions on the same chart
to compare them.
Suppose now we want to compare y = x2 + 1 with y = x3 + 1 and y = x4 + 1
over the same x range, i.e. from x = !1 to x = 1. Then we need first to enter
the y values for y = x3 +1 into the cells C2 : C22 and then that for y = x4 +1
into D2 : D22. After that we use the Chart Wizard much as before, except
that in the Range dialog box, we enter the range of cells A2 : D22 because
we used all these cells for our data.
Task: Open a new workbook, and draw the graphs of y =
x2 + 1, y = x3 + 1, y = x4 + 1 for the x range from !1
to 1.
Save your worksheet and submit it online. This finishes
your prac class.
10
3 Using Solver to Find Zeros, Minima and Max-
ima of Functions
1. General Advice and Background.
In practical problems, we often need to find the zeros, or minima, or maxima
of a given function. For example, in break-even analysis, we want to find the
zero of the profit function. We also often need to find the minima of the costs,
or the maxima of the profits for various activities. We have learned to use
Excel to draw the graphs of many functions, and the graphs can be used to
find the zeros and minima and maxima. However, this is not enough if we want
sharp accuracy for the solutions. In this class, we learn how to use Solver to
achieve this goal.
2. Using Solver to Find the Zeros of a Function.
Suppose we have the function y = x3!6x2 +11x!5.9 and we want to find the
zeros of this function. It helps if we know this is a cubic function and there
are at most three zeros. But we don’t have much idea about where the zeros
are. To use Solver e!ciently, we need first to have a reasonably good initial
guess of the solutions. To achieve this, let us check whether the range of x
between !10 and 10 contains any zeros of the function. We can enter the x
values !10,!9, . . . ,!1, 0, 1, . . . , 9, 10 into A2 : A22 using Autofil, and then
enter the formula = (A2)!3 ! 6 $ (A2)!2 + 11 $ (A2) ! 5.9 into B2 and then
use Autofil to find the values of the function in cells B2 : B22.
Inspecting the values in B2 : B22, we observe that the zeros seem to lie in the
x range from 0 to 4.
(Tips: Have some di!culties in finding the zero points? You can use chart to
facilitate the observation. Go to Prac2: Drawing Graphs)
Now we change the x values in A2 : A22, to 0, 0.2, . . . , 3.8, 4. Then the y
values in B2 : B22 change automatically to their new corresponding values.
Now we see that there should be 3 zeros, one near x = 1, one near x = 2
and one near x = 3. If you like, draw a graph of the curve using the Chart
Wizard.
We are ready to use Solver now. We follow the four steps listed below.
(a) Click Tools & Solver The Solver Parameters box now appears (Fig.
3.1).
(b) At the box Set Target Cell, we enter B7, then click the Value of
option and enter 0 and then at the By Changing Cells box, we enter
A7. These steps mean we want to achieve 0 in B7 by changing the value
in A7.
11
Figure 3.1: solver parameters dialog box
(c) Now we click the box Solve and find that the values in A7 and B7 become
0.953319833120002 and 8.34443628860981E-07 respectively. This means
if we take x = 0.953319833120002, then y = 8.34443628860981x10-7.
Therefore, x = 0.953319833120002 is a very good approximation of a
zero of the function.
The Solver Results box now appears and we click the Keep Solver
Solution option so that the new values in A7 and B7 will be kept.
Figure 3.2: Solver results dialog box
Task 1: Find the zeros near x = 2 and x = 3.
(d) Using Solver to Find the Maxima or Minima of a Function.
Checking the values in A2:A22 and B2:B22 we see that in the x range
from 0 to 3, there is a maximum value for y which occurs near x = 1.4.
To use Solver to find the actual value of the maximum, we follow the 3
steps used in finding the zeros above, with the following changes: In Step
2, we enter B9 in the Set Target Cell and click Max instead of Value
of. In the box By changing Cells, we enter A9.
At the end, we should have 1.4226497129766 and 0.48490017945975 in A9
and B9 respectively. This means the maximum is y = 0.48490017945975
which occurs at x = 1.4226497129766.
12
Task 2: Find the maximum and minimum and the zeros of the
function y = 2x3+x2!2x+1 in the range !10 ' x ' 10.
Save your result and submit it online.
This finishes the class.
13
4 Using Matrix Commands in Excel
1. General Advice and Background
Matrices are used in many practical problems. For example, when you produce
a diagram, most of the time, matrices appear in the diagram. More impor-
tantly, many calculations are most easily performed by using matrices. In this
class, we will learn to enter and to perform various operations of matrices in
Excel. We will use these skills to some practical problems in the next class. It
is important that you know the basic properties of matrices in order to deal
with them in Excel.
2. Entering and Naming Matrices
Before entering the elements of any matrix, you should always enter the Ma-
trix Name, such as A, B, etc. You may also want to enter the Description
of the matrix, like Coe!cient Matrix, Product of A and B, etc.
Let
P =
!1 0.5
1 !0.5
", Q =
!2 !1
1 4
"
To enter P , we can begin by entering the name in cell B3 and the elements in
the block of cells B4 : C5. To name the block of cells as matrix P , we do the
following:
(a) Highlight B4 : C5 with the mouse.
(b) Go to the Menu Bar then click Insert&Name&Define
(c) Enter P at the Names in Workbook box and click OK.
Task 1: Enter the matrix Q above.
3. Matrix Operations
We need to know the following rules on matrix operations:
A + B or A!B can be performed if A and B are of the same size.
AB can be performed if the number of columns in A is the same as
the number of rows in B.
The transpose of A can always be performed.
The determinant of A is defined only is A is a square matrix.
The inverse of A is defined only if A is a square matrix with nonzero
determinant.
As the matrices P and Q are of the same size, we know P +Q can be performed.
To do this in Excel, we use the following steps.
14
(a) Enter P and Q (which have been done already). In the cells above those
which will contain the answer we enter a description and the name of this
answer. For example, we can enter “sum of P and Q” as the description,
and enter “RS” as the name.
(b) Use the mouse to highlight a 2# 2 block of cells (as P +Q will be a 2# 2
matrix) such as B9 : C10 in which the answer is to be stored.
(c) Enter the Excel function
= P + Q
followed by pressing [Enter] + [Shift] + [Ctrl] (this notation means we
press the three keys simultaneously).
(d) Name the new matrix RS with the sequence of commands Insert, Name,
Define, etc. You should also enter a description besides RS, such as “sum
of P and Q”, to help you remember what RS stands for.
Note: You may like to name P +Q as R, but you will find this is not allowed
as R is reserved in Excel for other use.
For the other operations, you follow the same steps with the appropriate
changes. The Excel function for
A!B is = A!B
AB is = MMULT(A,B)
AT (Transpose of A) is = TRANSPOSE(A)
A"1 (Inverse of A) is = MINVERSE(A)
det(A) (Determinant of A) is = MDETERM(A)
Note that you need to know the size of the resulting matrix in Step (b) before
you perform the operation in Step (c). Remember, det(A) is a number, not a
matrix (or, one can say it is a 1# 1 matrix).
Task 2: Find P !Q, PQ, P T , Q"1, det(P ) and PT where
T =
!1 2 3
4 5 6
".
15
5 Solving Systems of Linear Equations by Using
Excel
1. General Advice and Background
Many models that are used in Accounting, Economics and Finance consist of
a set of linear simultaneous equations. An essential problem about such equa-
tions is to solve them simultaneously. There are two simple ways in Excel to
solve certain (but not all) such equations. One involves matrix calculations,
and the other uses Solver in Excel. To be able to use these methods, the
system of such linear equations should have the property that the number
of equations is the same as the number of unknowns, and the coef-
ficient matrix has nonzero determinant (i.e., it has an inverse). If these
conditions are not met, there might be no solution or infinitely many solutions,
and we need methods from mathematics to find this out. In this class, we are
to learn to use matrix calculations to solve those linear systems that meet the
above conditions. We will learn to use Solver in the next practical class.
2. Using Matrix Calculations to Solve Linear Systems
A typical system of linear simultaneous equations can be written in the matrix
form
AX = B
where A is a given n # n square matrix, B is a given column matrix, and X
is a column matrix whose entries are the unknowns.
For example, the following system of linear equations
2x! 3y + 4z = 13
x + y + 2z = 4
3x + 5y ! z = !4
can be written as#
$2 !3 4
1 1 2
3 5 !1
%
&
#
$x
y
z
%
& =
#
$13
4
!4
%
& ,
i.e. AX = B with
A =
#
$2 !3 4
1 1 2
3 5 !1
%
& , X =
#
$x
y
z
%
& , B =
#
$13
4
!4
%
& .
If A"1 exists, then multiplying A"1 from the left to both sides of AX = B one
obtains
X = A"1B
16
In other words, the solutions are given by the product of the matrices A"1 and
B.
Now let us use these matrix calculations in Excel to solve the above example
system, following the steps below.
(a) Enter matrices A and B.
(b) Find A"1 and name it IA.
(c) Find A"1B by performing the multiplication of IA and B and name it
X.
Following figure gives an example of the print out for this problem and its
solutions.
Task 1: Use the above method to solve the system of
linear equations
x + 2y + 3z = 10
2x + 5y + 7z = 23
3x + 7y + 8z = 31
Task 2: Find a matrix X such that#
$2 1 3
1 2 !1
!1 1 1
%
&X =
#
$2 14
!3 4
0 2
%
&
17
Task 3: The interaction between two industries P and Q that form a
hypothetical economy is given in the following diagram:
Industry P Industry Q Consumer Total
Demands Output
Industry P 46 342 72 460
Industry Q 322 114 134 570
Labour Inputs 92 114
(a) Determine the input-output matrix A;
(b) Find the output matrix if the consumer demands change to 129
for P and 213 for Q;
(c) Find the new labour requirements for the two industries.
18
6 Using Solver for Linear Programming Problems
1. General Advice
Linear Programming (LP) problems arise from various practical situations. If
the number of variable in a LP problem is large, then it is very time consuming
to solve it by hand. Excel’s Solver is a clever tool to handle LP problems.
In this class, we are to learn to use Solver to solve LP problems. A simple
variant of the method can be used to solve many systems of linear equations
which we learned to solve in the last class by using matrix calculations.
2. Using Solver for LP Problems
To explain how the Solver tool in Excel is used to solve LP problems, we shall
use the following example.
Suppose our LP problem is:
Maximize P = 4x + 12y
subject to1
4y ' 10
1
6x +
1
3y ' 30
x ( 0
y ( 0
To produce a table for the solution of this LP problem, we start by entering
the data of the problem. We can do this in the following (but clearly not the
Unique) way.
EnterMaximize P = 4x + 12y in A3
x in B4
y in B5
Constraints in A6
(1/4)y <= 10 in A7
(1/6)x + (1/3)y <= 30 in A8
x >= 0 in A9
y >= 0 in A10
Now all the information in the LP problem are entered.
Next we enter 0 in C4 to indicate that we use x = 0 as an initial guess for the
solution of x. We then name the cell C4 as x by using Insert, Name, Define
etc.
We enter 0 in C5 to indicate that we use y = 0 as an initial guess and then
name C5 as y.
19
Now we enter the formula = 4 $ x + 12 $ y in C3, and hit [Enter]. Then 0
appears in C3. This means that with the initial guess x = 0, y = 0 the formula
in C3 takes value 0. We now name the cell C3 as P , to be maximized later.
In C7, we enter = (1/4) $ y which is the left hand side of the constraint in A7.
When we hit [Enter], it becomes 0.
In C8, we enter = (1/6) $ x + (1/3) $ y and then hit [Enter].
In C9, we enter = x and then hit [Enter].
In C10, we enter = y and then hit [Enter].
We are now ready to use Solver. We want to maximize the value in cell C3
(named P ) by varying the values in cell C4 (named x) and cell C5 (named y)
under the constraints that the value in C7 ' 10, the value in C8 ' 30, the
value in C9 ( 0 and the value in C10 ( 0. To use Solver, we do the following:
(a) Click Tools & Solver
The Solver Parameters box now appears.
(b) At Set Target Cell, we enter P (or C3), at Equal to, we choose Max,
at By Changing Cells, we enter x, y (or C4, C5). These steps mean
we want to maximize the value of P (in C3) by changing the values of x
and y (in C4 and C5).
To enter the constraints at Subject to the Constraints, we click Add,
and the Add Constraint box appears.
In the Cell Reference text box we enter C7 which contains the formula for
the left hand side of the constraint 14y ' 10. Then we choose <= in the center
20
box and enter 10 in the right box. So the first constraint has been entered into
Solver. Now we click Add to obtain a new Add Constraint box and enter
C8, <=, 30 into the appropriate boxes.
Now click Add to obtain a further Add Constraint box and enter C9, >=, 0.
Then click Add and enter C10, >=, 0 into the new Add Constraint box.
Now that we have entered all the constraints into Solver, we click OK and
we are back to the Solver Parameters box.
We now click Solve and the Solver Results box appears (Fig. 6.1).
We choose Keep Solver Solution and then click OK. Then our worksheet
looks like what is shown in Fig. 6.2.
This tells us the maximum P = 880 is achieved at x = 100 and y = 40.
21
Figure 6.1: Solver results box
Figure 6.2: Results in our worksheet
Let us now see how the above procedures can be modified to solve systems of
linear equations. We consider the linear system
2x! 3y + 4z = 13
x + y + 2z = 4
3x + 5y ! z = !4
which was solved by using matrix calculations in the last class.
This time we view the problem as an LP problem:
Make P = 2x! 3y + 4z equal to 13
subject to the constraints
x + y + 2z = 4
3x + 5y ! z = !4
Then we can use the same procedures as in the maximization problem above
except that now at Equal to in the Solver Parameters box, we choose Value
of instead Max, and we enter 13 at the Value of box (see Fig. 6.3). Fig. 6.4
shows the worksheet at the end. It shows the solutions are x = 1, y = !1, z =
2.
22
Figure 6.3: The constraints in linear equations
Figure 6.4: The constraints in linear equations
Task: Solve the following LP problems:
(a) Minimize C = 500x + 700y subject to
x ( 0
y ( 0
25x + 12.5y ( 92500
2x + 4y ( 16000
0.5x + 1.5y ( 5000
(b) Maximize P = 20x + 30y subject to
x ( 0
y ( 0
0.7x + 0.5y ' 1500
0.3x + 0.5y ' 1000
(c) Find the solutions to
'(
)
2x + y + 3z = 1
x + 2y ! z = 2
!x + y + z = 3
23
7 Using Financial Functions in Excel
1. General Advice
For some of the frequently used Financial Functions, we can use the Function
Wizard in Excel to perform calculations. In this class, we are to learn how to
use FV (the future value function). You may like to learn to use the various
other Financial Functions in Excel by reading Chapter 5 of John Shannon,
“Excel for Business Mathematics”, John Wiley & Sons, 1996.
2. Using the FV Function
If one makes a deposit of P with an interest rate per compounding period of
i for n compounding periods, the Future Value S is given by
S = P (1 + i)n.
If P is a loan instead of a deposit, then S would be the amount of money one
owes to the bank. This gives one situation that one needs to find the future
value of S.
Another situation is when one makes a series of Regular Payments R at the
end of each of n compounding periods (an Ordinary Annuity), with interest
rate i (per compounding period). The Future Value S is now given by
S = R
!(1 + i)n ! 1
i
".
Let us see how to use the FV function in Excel by the following two examples.
Example 1. Mr Brown had a loan of $2000 from the bank, at the interest
rate of 12% per compounding period, for 12 compounding periods. Produce a
tabel showing the particulars of the loan and the amount Mr Brown owes to
the bank at the end of the loan.
Solution. We can enter “Present Value” into A1, 2000 into B1 to indicate
the loan is $2000. We then enter “ Interest Rate” into A3 and 0.12 in B3,
enter “Time Period” into A5 and 12 into B5. Finally we enter “Future Value”
into A7 and we need to find this future value and enter it to B7. We use the
FV function now as follows.
(a) Move the cursor to B7 and click to highlight it.
(b) Click Insert & Function, in Category, select Financial as shown in
Fig. 7.1.
(c) Click OK> to obtain the next dialog box and enter
24
Figure 7.1: Insert function
Figure 7.2: Function arguments
at rate (the interest rate) 0.12,
at nper (the number of periods) 12,
at pmt (payment) 0, or simply ignore it,
at pv (the present value) 2000.
at type (means payment type) 0, or simply ignore it.
Now you should have !7791.951985 appearing in the Value box at the
lower left corner of the Function Arguments box. The minus sign
means this is the amount one needs to pay the bank.
(d) Click OK at the lower right corner of the box, and ($7791.951985) should
appear in B7. (Note, you might need to widen column B so that there is
enough space for ($7791.951985)). If Mr Brown makes a deposit of $2000
instead of borrowing $2000, we should then enter !2000 at the pv box
25
Figure 7.3: Function arguments
to indicate this amount of money was paid to the bank, and the future
value should be 7791.951985, which is the amount Mr Brown will receive.
Example 2 Mr Brown’s salary is paid as a regular payment of $250
to his bank account, at the end of each compounding period, with an
interest rate of 12% (per compounding period). Produce a table showing
the particulars of the deposit and the amount Mr Brown will receive at
the end of 12 compounding periods.
Solution:
We can enter “Regular Payments” in A1, 250 in B1
interest rate in A3, 0.12 in B3
Time periods in A5, 12 in B5
Future Value in A7.
Then we follow the 4 steps in example 1 except that in step (c), we enter
!250 at pmt (payment) while ignore pv.
At the end, we should have $6033.28 in B7.
Task: For his prize, Mr. Brown can choose to receive either (i) a $25000
cheque or (ii) a plan of regular deposits of $3200 to his bank ac-
count at the end of each of the following 10 years. Suppose that
the interest rate is 8% per annum, the compounding period is 12
months, and Mr. Brown would deposit the $25000 if he chooses
this option. Find which option gives Mr. Brown more money after
10 years, and produce a table for him.