Microsoft Office Tutorials and References
In Depth Information
Income statement scenarios
of periods, and present value of the loan principal. Goal Seek will allow the user
to select one of the three inputs, such that it will result in a payment of $1000 per
period. It is a limited tool in that it will permit only a single variable to be changed to
arrive at the goal. Thus, it is not possible to vary interest rate and number of periods
and present value simultaneously.
In the next section we will examine two examples that demonstrate the power
and the pitfalls of Goal Seek. The ﬁrst example is relatively simple and relates to the
calculation of Padcha’s loan, in particular the PMT function. The second example
is a more complex application related to Padcha’s problem of accumulating interest
in years 1 and 2, and it utilizes the CUMIPMT cell function. Although the PMT
function is similar to the CUMIPMT function, the application of Goal Seek to the
latter cell function is somewhat problematic.
9.5.1 Example 1—Goal Seek Applied to the PMT Cell
Consider the mortgage example we introduced in the Scenarios section. Imagine
that Padcha has determined the yacht that she will purchase, the Queen of Malacca,
along with its price, $240,000. The broker for the yacht has agreed to ﬁnance at an
interest rate of 7%; he is anxious to sell the Queen of Malacca due to some rather
unfortunate history of the yacht’s previous owners—pirates and gun runners. He is
not concerned with the term of the loan as long as he gets an agreement to purchase.
Padcha sees an opportunity to set a loan payment and determine the term that will
be implied given the broker’s interest rate and the principal of the loan. She decides
that $5000 per month is a very manageable loan sum for her. Exhibit 9.19 shows the
Goal Seek dialogue box for Padcha’s problem. There are three entries:
1. Set cell entry is the cell that she will set as a goal— Monthly Pmt , C16.
2. To value is the value she selects for the Set cell —$5000.
3. By changing cell is the cell where changes will be permitted— Number of periods
(months) , C11.