This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Solving Accounting Principles
Problems Using Excel for Windows
to accompany Accounting Principles
Ninth Edition
Rex A Schildhouse, LCDR, U.S. Navy, Retired, M.B.A.
San Diego Community College District, Miramar Campus, San Diego, CA
Jerry J. Weygandt, PhD, CPA, Arthur Andersen Alumni Professor of Accounting,
University of Wisconsin, Madison, Wisconsin
Donald E. Kieso, PhD, CPA, KPMG Peat Marwick Emeritus Professor of Accountancy,
Northern Illinois University, DeKalb, IL
Terry D. Warfield, PhD, CPA, PricewaterhouseCoopers Research Scholar,
University of Wisconsin, Madison, Wisconsin Chapter 16 ADVANCED EXCEL
Chapter Outline
Average & AverageA
Command Access
Convert
Embedded Formulas Intermediate Formulas
Keystrokes & SHortcuts
Linking Worksheets
Macros Average & AverageA
Within Excel there are two “Average” functions under the “Statistical” category. The first is “Average”
and returns a simple average of an identified range. This formula is shown in operation on the “Average”
data file on the data disk. The function written in cell E2 determines the average number of the category
“Apple” – cell B2. Cell E3 determines the average number of “Apples”, a range from cell B3 through
B11. The formula in cell E4 determines the average number of “Berries”, a range from cell B12 through
B23 while the formula in cell E5 determines the average number of “Cherries” in a range from cell B24
through 33. This formula is simply written as “=AVERAGE(Range).” The range can be a continuous
string of cells in a column or row or can be a range of cells such as columns and rows such as the formula
shown in the range of F10 through M31. Average will not count text strings into its averages.
The second formula is “AverageA”. With this formula you can even account for text strings in your
average such as “True” – a value of 1, “False” – a value of 0, while the occurrences of text strings such as
“N/A” get counted in the denominator of the average equitation but there is no value for them in the
numerator of the equitation. This is a handy tool for the average number of correct answers on a
“True/False” question as shown in column O of the “AverageA” worksheet. Command Access
Excel has numerous command paths to accomplish almost every one of its many commands. The
selection and utilization is up to you. If you are a proficient typist, you may select keyboard commands
while typing and mouse commands while doing graphic tasks. Others will always select the mouse while
some will use the “Alt” (Alternate) key path.
The most common method of accessing Microsoft Excel commands is through the mouse. With this
concept, you would select a word on the menu bar by placing the mouse cursor over it and clicking on it.
A dropdown menu attached to the menu bar would appear with the options available at that point in time
and available in relation to that position on the worksheet. If an item is grayed out or faded, it is not
available for some reason. You would then select the desired option by clicking on it with the mouse. If
the option has an arrowhead pointing to the right or left, that selection has other choices available to it and
selecting it or placing the cursor over that selection will result in an expanded display for that selection.
Once shown, clicking on the desired option will result in its selection. If no selection is found, or you
decide you are in the wrong path, clicking anywhere other than on the dropdown menu will back you out Section 2, Page 99 Page 100 Solving Accounting Principles Problems Using Excel for Windows
without a selection being activated. You may also press and release the “Esc” (Escape) key to cancel the
menu and avoid a selection.
The second methodology of selecting commands it to use keyboard commands. With the use of a
predecessor key such as the “Ctrl” (Control) key or the “Alt” (Alternate) key on the keyboard other
options become available with additional keystrokes. For example, to copy something using keyboard
commands, activate the cell or cells you would like to copy and then press and hold the Ctrl (Control) key
down and then press and release the “C” key. This series of keystrokes is shown throughout the text as
“CtrlC”. The shift key is not hit and it does not matter if the keyboard is locked into upper or lower case
at the time. Once the “C” key is struck, the keys can be released in any order.
An alternative is to use the “Alt” (Alternate) key to access the menu bar. By striking this key you will
see the first item on the menu bar become active. By using the arrow keys to move left and right as well
as up and down, you can prowl the menu bar for your selection. When your selection is located, striking
the “Enter” key will select and activate the command you have chosen.
Regardless of the method you have used to access the command structure of Excel, the “Esc”
(Escape) key will usually back you out of the command path without making a selection. It may take one
or more strikes of the “Esc” key to accomplish the backout if you are several “layers” into a selection
process. Convert
The “Convert” function will accept one value and then convert that value from one measuring standard to
another measuring standard. Tired of trying to remember that 2.2 pounds is roughly 1 kilogram and have
to convert 825 pounds to kilograms for an shipping invoice? Try the “=SUM(CONVERT(825,
"lbm","g")/1000”. One of the requirements for this function to work is that the “Unit Id” must be in
double quotation marks and in the proper case – some upper case, some lower case. For example, while
Excel ignores “=CONVERT(”, “=Convert(”, “=convert(” upper or lower case issues, it will “fault out” if
the formula is written as “=CONVERT(825,“LBM”,g)” since it will not recognize the unit ID of upper
case “LBM” because of the upper case issue. Nor will it recognize the lower “g” without the double
quotation marks.
Utilize the “Help” screen for “Convert Worksheet Function” from Microsoft Excel for more units and
information. This table is a brief listing of the values that Excel can convert from and into:
Unit Id:
Weight:
"g"
"lbm"
"ozm"
Distance:
"ft"
"in"
"m"
"mi"
"Nmi"
"yd"
Time:
"day" Unit Title:
Gram
Pound mass
(avoirdupois)
Ounce mass
(avoirdupois)
Foot
Inch
Meter
Statute mile
Nautical mile
Yard
Day Unit Id:
Temperature:
"C"
"F" Unit Title:
Degree Celsius
Degree
Fahrenheit "K"
Degree Kelvin
Liquid Measurement:
"cup"
Cup
"gal"
Gallon
"l"
Liter
"oz"
Fluid ounce
"pt"
U.S. pint
"qt"
Quart
"tbs"
Tablespoon
"tsp"
Teaspoon Chapter 16, Page 101
"hr"
"mn"
"sec"
"yr" Hour
Minute
Second
Year On the “Convert” data file there is a data matrix of
common convert terms or “Unit Ids.” The values within
column B cannot be utilized as “Look to” values since
they are in quotation marks, the way you are required to
type them into Excel. However, the column is
duplicated in column C without quotation marks. This
allows you to write a formula such as
“=CONVERT(B1,C11,C10)” in cell C1 to convert the
value of 825 contained in cell B1 from pounds as
identified through the value contained in cell C11, to
grams through the value contained in cell C10. Had you
utilized the column B values of the “Convert” data file for these references you would get a fault
indication such as “#N/A” or “#Name”. By looking at the dialog box you would see that Excel is
interpreting the formula as “=CONVERT(B1, ““B12””, ““B10””)” which contains too many double
quotation marks around the values – so the double quotes are removed from column C for your ease of
use. Embedded Formulas
As addressed elsewhere in this book, the prefix of “=SUM(” starts Excel into the “I have to do something
with formulas and functions” mode. As shown elsewhere in this book, Excel will perform functions and
formulas within other functions and formulas. This embedding capability generates a power and
flexibility within Excel that greatly aids the professional. The way to embed a formula or function is
relatively simple. First and foremost, understand the functions and formulas you are utilizing. For
example, if you are using the “Count If” function on an inventory sheet to find the total number of apples,
your results will be incorrect. This is shown on the Embedded Formulas data file where the desire was to
sum the number of apples, berries, and cakes but the “Count If” formula resulting in finding out how
many times the text apples, berries, and cakes each occurred in the data field.
In the Embedded Formula data file the first task is establish a value for inventory by finding the value
of the apples, the berries, and the cakes and then summing those subtotal values. In cells G23 through
G25 and G27 Excel finds the number of each item. The values for these items as individual elements is
contained in cells H23 through H25 and H27. In cells I25 through I25 and I27 the “Sum If” formula
determines the sum total of each element and then Excel multiplies it by the element value contained in
the H cell ranges. For the apples on row 23 the formula is:
=SUMIF($A$2:$A$250,F23,$B$2:$B$250)*H23
This embedded formula is the “Sum If” formula and a multiplication function by adding the “*H23”
at the end of the previously discussed “Sum If” formula. By using the “Look To” capability you can
change the element value of the inventory items and see the results immediately.
Suppose that we wanted to be alerted if the value of any inventory category exceeded $20,000. This
can be accomplished through conditional formatting – covered elsewhere, so utilize “Concatenate”, a
function discussed shortly, and “IF” statements. The formula in I23, broken down into three lines due to Page 102 Solving Accounting Principles Problems Using Excel for Windows
its length, reads like this:
=IF((SUMIF($A$2:$A$250,F33,$B$2:$B$250)*H33) > $I$31,
CONCATENATE("$",FIXED(SUMIF($A$2:$A$250,F33,$B$2:$B$250)*H33,4,0),
" Exceeds target value"),SUMIF ($A$2:$A$250,F33,$B$2:$B$250) *H33)
To understand this function appreciate that Excel looks at the deepest pair of parentheses and then
starts working its way outward. In this case those parenthesis are contained within the “Sum If” function.
So in line 1 of the formula Excel sums the number of directed inventory items, those identified in cell F33
– Apples, and multiplies the summed number by the value in cell H33  $0.12 per element item as
supplied on the data disk. Then Excel is instructed to compare this total value to the value in cell I31 and
determine if the summed value is greater than the value in cell I31. If the summed value is greater than
the value in cell I31, $20,000, as supplied on the data disk, Excel moves to the set of parenthesis inside
the formula – those of the “If” statement. Since the statement is false (9,674 apples at $0.12 each total
$1,160.8800) Excel executes the function contained in the second part of the “If” statement. This is
actually a repeat of the “Sum If” formula summing the number of referenced inventory items and
multiplying that sum by the element cost contained in cell H33.
However, in cell I35 the statement is true. The 15,771 cakes at $1.8700 each total to $29,491.7700
which exceeds the reference value in cell I31. Since this “If” statement is true, Excel executes the first
half of the “If” statement – the concatenate function. This function is set to sum the total number of
referenced inventory items – cakes, and multiply that sum by the cost in cell H35. Then Excel is told to
format this value to 4 decimal places and with commas through the “Fixed” function. Concatenate adds a
dollar sign as text in front of the total value for a presentation of “$29,491.7700”. The concatenate
function adds spaces and a text string saying that this value exceeds the limit. While not real useful in life,
as will be shown in the Intermediate Formulas section to follow, this string of formulas and functions
demonstrates the power Excel can provide if the user has imagination and knowledge.
Notice that the only equal sign in the formula is the one at the beginning of the long formula string.
An equal sign buried within the formula such as in front the “Sum If” or “Concatenate” formulas and
functions will result in an error message from Excel. You can only nest 7 sets of parenthesis within Excel.
However, as shown in the section on Intermediate Formulas, this is only a restriction within a cell, not
within Excel per say and can be easily compensated for.
The actual solution to this problem should have been conditional formatting of the cell or an “If”
statement as shown in cells F40 through J45 where both “If” statements and Conditional Formatting are
used on the values resulting in easier to write and troubleshoot the formulas. Intermediate Formulas
Excel has many built in formulas and functions which can be
found through the Fx icon on the tool or tool bar. Shown here
is the dialog box that is presented when you click on the Fx
icon and then search all functions or the Math and Trig
functions. This dialog box provides an index of most of the
structured Excel formulas and functions. Two very nice
features of this dialog box are (1) the ability to select “All” as a
category and scroll through every function and formula, and (2)
the brief summary of actions and capabilities of the formula or
function in the lower third of the dialog box, shown inside the
rectangle on the screen print. In this screen print the “Sum If”
function is selected. The dialog box tells you that it adds the
cells specified by a given condition or criteria. Exactly what we Chapter 16, Page 103
were seeking in the “Sum If” tasking for the valuation of inventory earlier.
This dialog box can be narrowed down in its search through the selection made in the “Or Select A
Category” window in the upper third of the dialog box. The categories include financial, math & trig,
statistics, lookup and reference, text, and logical. Selecting one of these categories will narrow your
search but may preclude you from seeing a viable option contained under another category.
Formulas and functions in Excel normally start with the equal sign, “=”, which keys Excel into
expecting to accomplish something through a formula or function. From this point, text, data, and
reference information is provided. The simple Excel formula of =B2, a “Look to” formula or function,
placed in cell C1 would simply provide you with the contents of cell B2 being presented again in cell C1
as shown on the Intermediate Formulas data file. This formula can be used through many of the exercise
and problem templates as the dates and values as well as some account titles are available in a “clean
target” or source presentation. Excel will also do “Look to” on text values as shown in cell D2.
When text is incorporated as part of a formula or function within Excel it must be enclosed within
double quotation marks or Excel will product an error message. In the “If” statement, a logic function or
formula, shown in cell D12 in the Intermediate Formulas data file, you will see the word Apple or Apples.
The formula or function, an “If” statement, in cell E12 is looking at cell D12 and responding based on the
value in D12. If the value is singular – “apple”, Excel hopes it’s a Washington State Apple. If the text is
“apples” – plural, Excel hopes they are all Washington State Apples. However, if the text string is neither
“Apple” or “Apples”, Excel tells you that it is not apples. Excel cannot really read, it is comparing text
strings for validity. The formula says if you find the text string of “Apple” respond with the first of the
“true” statements, if the text string is not equal to “Apple” Excel executes it false statement which is
another “If” statement to see if the text string is equal to “Apples.” If Excel finds a matching text string,
Excel responds with the second true statement in the formula, the plural response for “Apples.” If Excel
did not find the text string “Apples” on the “second look”, it responds with the false response which
declares they are not apples.
As addressed elsewhere in this book, there is a nesting limit of 7 levels within Excel. Can Excel
determine what letter of the alphabet you entered and respond based on your input if there are 26 letters to
choose from? Certainly. Enter a letter (A to Z in upper or lower case) in cell D21 and watch the response.
Excel will determine the letter and respond for that letter. To overcome the 7 layer restriction on nesting,
after 6 levels, the formula looks to the next line – hidden by making the text white – for an answer. All of
the lines, 4 in total, look to cell D21 and evaluate the value. If the value exceeds the limits of the first line
on 21, the formula in the first line looks to line 22. If the value in cell D21 matches one of the values in
line 22’s range, line 22’s formula responds, if line 22’s range is exceeded, line 22 looks to line 23, and
then line 23 looks to line 24. This process can continue until you run out of cells in the worksheet. If line
24 has the valid answer, it passes it to line 23 which passes it to line 22 which passes it to line 21 which
displays the valid response as the “False” response to its own “If” statement. The “False for line 21 is
look at line 22. After filling a worksheet with chained references you can reference the next worksheet in
the workbook. After the number of worksheets is exceeded, you have Excel reference a second, then
third, then fourth workbook and so on. So, while there may be a nesting limit within a cell of Excel, there
is no true nesting limit if you “Look To” the next cell, worksheet, or workbook.
Often a formula can be set up in a “template” format for frequent use without new formula structures
being required. Suppose that you did a lot of work with the Present Value formula. Set the formula up as
shown in cells D31 to G35. In cell G32 the present value formula resides and looks to the cells D31
through D35 for information. Anytime that information is changed the formula recalculates the present
value. (Almost) Instant answers to a formula with little typing once the formula is set up.
Occasionally Excel will have problems with formulas that should yield specific results. The
frequently effective solution is to insure that the formatting of the cells is not precluding the formula or
function action. For example, click into cell H38 of the Intermediate formulas data file. Once the cell is Page 104 Solving Accounting Principles Problems Using Excel for Windows
active, click on the Omega symbol (Σ) for quick sum and Excel looks over to column A and generates a
formula of =SUM(A38:G38) while you would have expected Excel to generate a value of 28
(1+2+3+4+5+6+7) the response is only 17. A check of the formula in cell H38 shows a correct formula =SUM(A38:G38), the sum is obviously wrong. Click into cell H39 and click the Omega symbol (Σ) for
quick sum and Excel looks over to column A and generates a formula of =SUM(A39:G39). And the
response is 28, the correct sum of 1+2+3+4+5+6+7. Now click into cell A38. Once the cell is active, left
mouse button down and begin to sweep right on row 38, when cells A38 and B38 are highlighted, the sum
at the bottom, right side of Excel shows “3”, the correct answer. As you sweep right along row 38 the sum
increases to 6, and then 10. When you sweep into the cell containing the value of 5, there is no increase,
there is no increase when you sweep into the cell with the value of 6, but sweeping into the cell with the
value of 7 results in a sum of 17 appearing at the bottom of the screen.
Time to troubleshoot and the focus has been identified. Click into cell D38. Then right click the cell
and read the formatting – it is “General.” Click into cell E38 and check its formatting – it is “Text.” In the
“Format Cells” dialog box Excel tells you that even though a numerical value may appear in this cell, it is
text and will not function as a number or value. The same for cell F38. Reformat the cells as “General” or
a numerically acceptable format – number, currency, or accounting, and then reenter your data and the
sum is correct – as shown in line 39. Excel had the numbers 5 and 6 entered after the cells were formatted
to text so even reformatting the cells to “General” does not change the data – text images of 5 and 6, to
numerical data until the cells are reformatted and the data is reentered.
Scrolling through the “All” formulas and functions category of Excel will reveal many useful
formulas and functions applicable to the exercises and problems of the textbook. However, Excel is a tool
and the results of every Excel formula and function needs to be evaluated for reasonableness. When
working on extensive and intricate data proof the concept with basic data and simple values. For example,
with the “DDB” function to handle doubledeclining depreciation of an asset, verify it with a $10,000
asset, $0.00 salvage value, 5year life at 200%. This should return a value of 40% of $10,000 (100% / 5
year life X 200%) or $4,000 for the first period. If another value is returned, something is wrong with the
formula selection, the data input, or formatting. In this formula, Excel is not expecting 200 for 200%, it is
expecting either “2” for twice straightline or “200%”. Excel also is not expecting commas to be entered
into the values.
Tip: While working on formulas and functions, you can insert a single text character in front of the
equal sign and the formula or function becomes a text string and will not process. This does not reformat
the cell and preclude later mathematical operations. This is a handy “escape” while troubleshooting
problems and situations that may arise during entry. Excel does not want you to leave the cell with a
formula or function it cannot handle. Excel may present you with proposed resolutions and trouble
screens. Just because Excel presented it, does not mean it is correct or acceptable. By inserting a character
in front of the equal sign this can be stopped. As a matter of presentation, if the character is a single or
double quote, Excel will not show that character in the display of the cell data. This makes the single or
double quote presentation neat and clean. You can see many of these inserts through the data files. Keystrokes & Shortcuts
There are many keystrokes that work in a standard Windows format regardless of the program. Some are
unique to the program. Here are some simple and useful keystrokes for Excel:
The keystrokes of CtrlHome (key / keystrokes not type in entry) will take you to cell A1 unless the upper
row(s) or left column(s) frozen through “Freeze Pane.” Then CtrlHome will take you to the upper, left
most cell not frozen. CtrlHome will not take you into frozen columns or rows.
The keystroke of Home will take you to column A of the row with the currently active cell. It will not
take you into frozen columns. Chapter 16, Page 105
Page up will change the screen displayed up one “pane” of cells. If the top row is row 128, page up
will make the bottom row on the new screen 127. If there is not enough rows available because of the
upper or lower limit of Excel, nothing will happen. It will not take you into frozen rows.
Page down will change the screen displayed down one “pane” of cells. If the bottom row is row 128,
page down will make the top row on the new screen 129. If there is not enough rows available because of
the upper or lower limit of Excel, nothing will happen.
Arrow keys will move you one cell up, right, down, or left for each striking, if held, they will scroll
you in the selected direction. If you have edited the data within a cell, the use of the arrow keys will not
let you leave the cell. “Tab” or “Enter” must be struck or you must click into another cell with the mouse.
CtrlA selects all cells on the worksheet.
CtrlB toggles the selected cell or range of cells into and out of bold text mode.
CtrlC copies the selected cell or range of cells.
CtrlD is fill down – starting with the source cell, highlight the range you want filled in and press and
release CtrlD for the fill to occur.
CtrlF brings up the “Find” and “Find and Replace” dialog box.
CtrlG brings up the “Go to” dialog box.
Ctrl H brings up the “Find” and “Find and Replace” dialog box.
CtrlI toggles the selected cell or range of cells into and out of italics text mode.
CtrlK brings up the “Hyper link” dialog box.
CtrlN opens a new worksheet.
CtrlO brings up the “Open Workbook” dialog box.
CtrlP brings up the “Print” dialog box.
CtrlR is fill right– starting with the source cell, highlight the range you want filled in and press and
release CtrlR for the fill to occur.
CtrlS saves the file if it has been saved before, it brings up the “Save” dialog box if this is the first
save operation.
CtrlX is cut the cell or range of cells.
CtrlZ is “Undo.”
Ctrl`  the accent character on usually to the left of the 1/! Key brings up the audit tool bar.
Ctrl1 (The number one) is format cell or cells.
F1 (The number 1) brings up the Help screen.
F5 is refresh.
F7 starts spellcheck.
F12 brings up the “Save As” dialog box. Linking Worksheets
Excel can utilize the “Look to” function as a quick link between worksheets within the same workbook
and link between worksheets in difference workbooks. This is different then a link or hyperlink which
provides the path which opens a target document. If you are working within the same workbook but
different worksheets within Excel simply enter the equal sign into the target cell, select the source
worksheet and click into the source cell and press and release the “Enter” key. This is demonstrated in the
“Linking” data file on the data disk. On the “Links” worksheet, the “=” (equal sign) was entered into cell
A1. Then the Source worksheet was selected and cell A1 was clicked into to make it the source cell and
“Enter” was struck. This returns you to the “Links” worksheet as the function is complete. Then cell A1
as selected and the formula within cell A1 was dragged down through cell A5. The dragging operation is
relational as explained under Absolute References so the cells A1 through A5 of the Links worksheet are
now “children” or “dependents” of the “parent” or “source” cells of the Source worksheet.
Notice the special format and sequence of the linking formula, =Source!A1. This can be typed in
manually if desired but it is quicker and less troublesome to let Excel write the formula. You can utilize Page 106 Solving Accounting Principles Problems Using Excel for Windows
these cells for data within other formulas and you can add functions and formulas to the linking formula.
In cells D4 through D7 additional math actions are added to the linking function.
Linking can also be accomplished through Copy then Paste Special > Paste Link. When done with
this method the references are created as Absolute References by Excel, the absolute reference function
can be inserted on the “Look to” method as well. This allows the “dependent” or “child” cell to be moved,
copied and pasted, or dragged to another location without losing its reference.
When Excel links between two workbooks the formula becomes slightly more complicated. The
formula “=[Book3]Sheet1!$A$1” inserted into a cell in a workbook other than Book3 would look to
Book3 and return the data entered in cell A1. In this formula, the parent and child workbooks are within
the same directory. On your data disk is a “Linking Source” data file, another file, “Linking Dependent”,
looks to this file to attain data. Ensure that both these files are in the same directory and open the
“Linking Dependent” workbook. Upon opening Excel will ask if you want to update the link. Select
“Update” and Excel will “read” the “Linking Source” workbook for changes. It will not open the
“Linking Source” workbook and it does not need to open the workbook to read the linking data. Assume
that the child or dependent file is closed and the source or parent file, “Linking Source” is open and being
edited. The child or dependent will not be updated upon saving the source or parent file. However, when
the child or dependent file, “Linking Dependent”, is opened, Excel will ask if you want it updated and
check for changes if you do.
Tip: You have a big, dependent or child file that is critical and numerous individuals can make
changes to the parent or source file. Upon opening the file you can select not to update the file and, once
open, use File > Save As to save the file as a new name. If you desire you can Copy and Paste Special >
Values the entire worksheet to remove the links and turn all the formulas into hard values. Then reopen
the (real) child or dependent file, update it, and inspect it. If unacceptable changes have been made to the
parent or source data file you have the child saved in a previous, unaffected condition through this
technique.
Excel will allow you to link between directories as long as you can define the path or generate the
path through Windows movements. With the ability to link the worksheets and workbooks you can build
subsidiary ledgers containing accounts receivable and accounts payable data and then link it to the general
ledger of accounts receivable and accounts payable in another directory. The subsidiary ledgers would be
the source or parent with the general ledger being the child or dependent cells. You would not have to
have the general ledger open to make entries into the subsidiary ledgers. But when the dependent or child
workbook, the general ledger file is opened, Excel would ask if you would like to check for changes and
update the file. This capability is demonstrated with the data disk directories “Linked Source” and
“Linked Child.” The Linked Source directory contains a data file titled “Linking Source  Directory.xls”
which is looked to by the child or dependent file titled “Linking Dependent  Directory.xls” in the
“Linking Child” directory. The formula in cell A1 of the “Linking Dependent – Directory.xls” file is long
and specific to how you copied the files to your directory. If the links have difficulty in reestablishing
upon opening, you can reinstate them by opening both files, clicking into cell A1 of the “Linking
Dependent  Directory.xls” entering an equal sign, then clicking into cell A1 of the “Linking Source Directory.xls” data file and striking the Enter key. Now that the formula is in cell A1, remove the absolute
reference dollar signs from the cell reference of “$A$1” to make it a relational “A1” and drag it down 35
or more rows to bring in the populated cell range of the “Linking Source  Directory.xls” data file. The
links are reestablished.
Clue: The cell A1 to A1 relationship is not required. You can link any cell to any cell through this
process.
The “Linking” and “Look to” capabilities makes Excel a powerful tool for data management. There is
a danger created in this manner that the data being utilized to generate values on your worksheets and in Chapter 16, Page 107
your workbooks may not be easily reviewed or visible for review and audit. And, as a professional, you
are responsible for the work you provide, regardless of the validity of the source. Chapter 17 MICROSOFT WORD
Chapter Outline
Word File Storage
Word Upon Opening
Word Tables Word Table Formulas
Excel Tables Into Word Word File Storage
During an initial installation Word will bring in the defaults of the installation process for file storage and
document setting. If the installation is an upgrade installation the installation process will incorporate the
previous file storage and document settings. The location of Word files can be left at “Default” which
may be in the “My Documents and Settings” directory. You can determine the actual storage location of
your will be Word Upon Opening
Word is a complete word processing application and more. Upon opening Word, after registration is
complete, you will be presented with a blank page usually titled “Document 1” by default. The cursor will
be flashing in the upper left corner of the “sheet” waiting for text to be entered and, depending on the
default settings of the installation process or as established by prior users.
To enter text During an initial installation Word will bring in the defaults of the installation process
for file storage and document setting. If the installation is an upgrade installation the installation process
will incorporate the previous file storage and document settings. The location of Word files can be left at
“Default” which may be in the “My Documents and Settings” directory. You can determine the actual
storage location of your will be Word Tables
Word has the ability to generate basic tables on its own through the path Table > Insert. The table inserted
below was created with this function. While it does not have all of the features of Excel, it is a good tool
for the presentation of limited material or materials not found elsewhere and not necessarily needed later.
By following the path Table > Insert > Table and telling Word through the dialog box that you want a
table with 5 rows and 5 columns the result is: Section 2, Page 108 Chapter 17, Page 109 1/A B C D E 2 10 15 20 25 3 11 22 33 44 4 12 144 1728 20736 5 33 60.33 By default, Word will make these columns fill the width of the page. You can select the table by
clicking into the table and then moving your cursor to the upper left corner area. While moving your
cursor in that area, you will see a fourheaded arrow appear. Click on that arrow and you will have
selected the entire table. You can also sweep and highlight the table from one corner to another if desired.
When the table is selected, you will
activate the tab markers in the
ruler/measurement bars at the top and
side of the page. By moving these tab
Microsoft Word Table Tabs in the Ruler
markers with your cursor (select them
by left mouse click and hold the left
button down) you can change the column width and row height by dragging them. You can also change
row height and column width by selecting the row(s) or column(s) by highlighting them, then right
clicking on the highlighted cells and following the path to Table Properties and selecting the Row or
Column tabs and changing the values there.
The borders on the table can be formatted by highlighting the specific cell, cells, or the entire table
and using the Borders icon on the tool bar or right clicking and selecting Borders and Shading. The
borders of row 3 have been removed through this process and the double lines under row 5 were placed
there by the same process. Under this tab, the line weight and characteristics can be modified as desired to
include dashes, dots, and mixes of other symbols. Row 2 was modified in this manner.
The background and text coloring of the cells and their data are controlled as other formatting
features that are in Word. You can select the table, the row, the column, or the cell and invoke the colors
and features through the tool bar icons or by following the path Format > Font while the cells are
highlighted.
Once a table has been inserted you can select a row or column and follow the path Tables > Insert and
increase the number of rows or columns as needed by instructing Word to place the inserted rows or
columns into the existing table above, below, to the right, or to the left of the cursor placement. To insert
a specific number of rows or columns, select/highlight that number of rows or columns and then follow
the path Table > Insert and select rows or columns from the selection options and inform Word if you
want them above, below, to the left, or to the right of the highlighted cells. Rows and columns can be
deleted through the path Table > Delete after the row(s) or column(s) are highlighted.
Tip: The entire row or column need not be selected to insert or delete rows or columns, only cells
representing the number of rows or columns you desire to have Word insert or delete. However, the
technique / habit of highlight all of the cells reinforces what will be inserted or deleted before the action is
invoked. Page 110 Solving Accounting Principles Problems Using Excel for Windows Word Table Formulas
Microsoft Word tables have their own formulas under the path Table > Formulas. It is through this path
that you establish and modify formulas within Word tables. Formulas in Word are not “read” in a formula
or data entry window like Excel. Word table formulas are done similar to Excel formulas in that they use
cell references of columns and rows. The upper left most cell is cell A1, the cell to its right is B1, the cell
below cell A1 is A2 and so on. In a Word table though the formula to add the contents of cells B2, B3,
and B4 is “=SUM(b2,b3,b4)” and the formula to multiply the contents of cells C2, C3, and C4 is
=PRODUCT(d2,d3,d4). Notice that the operation is controlled by the leader – Sum or Product and that
the cell references are separated by commas, not attached to each other by math operatives such as the +
or * symbols. The following table contains the formula =SUM(b2,b3,b4) in cell B5 and it adds the rows 2
through 4 into cell B5, the formula =AVERAGE(c2,c3,c4) in cell C5 and it calculates the average of the
values within cells C2, C3, and C4 into cell C5, and the formula =PRODUCT(d2,d3,d4) in cell D5 which
multiplies the value in cell D2 by the value in cell D3 and the product of that operation by the value in
cell D4 and places the final product in cell D5.
A1
B1
C1
D1
A2
2
3
4
A3
3
4
5
A4
4
5
6
A5
9
4
120
A6
Sum
Average
Product
Word formulas are not “live” like Excel when manual calculation is not invoked. That is, if you had
access to this table and changed the value in cell B2 from 2 to 20, the value shown in cell B5 would not
change.
Note: It is frequently easier to create the table within Excel and then copy and paste it into Word. Excel Tables Into Word
While Excel may be a major tool for accountants, communication of information is the key to success.
Once an Excel worksheet is populated with data its presentation can be adjusted through hidden columns
and rows, paste special, and other tools such as “look to” formulas from a specific area of the worksheet
or a new worksheet to give clean presentation. However, this may not suit the complete requirement and
the constraints of Excel may hinder the presentation. Much of the work presented within this text was
accomplished by entering the data, such as an exercise or problem, into Excel, then highlighting the data,
copying it through the keystrokes CtrlC, clicking the copy icon on the tool bar, or following the path Edit
> Copy. Then the data is pasted into Microsoft Word by
selecting Word, placing the cursor into the appropriate
area, the target zone, and using the keystrokes CtrlV,
clicking the paste icon on the tool bar, or following the
path Edit > Paste. Once placed in this manner, the data is
“static” in nature. If the source within Excel changes, the
data within Word will retain its original value without update.
From this point, the data is normally contained within a table which can be formatted as many other
Word objects can be with borders, fill colors, fonts and sizing. To move this inserted object place your
cursor near the top of the table and a square with a four
headed arrow, as shown in the screen print will appear.
When it does, click on it and the table will be selected.
While the table is selected, it will highlight to emphasize
Positioning blocks or cubes
the fact that it is selected. By right clicking on the Chapter 17, Page 111
highlighted table, you will be presented with a popup menu which has formatting options on it. While it
is highlighted, you will see a new set of “blocks” or “cubes” appear in the ruler bar of Word. These blocks
or cubes, as shown in the screen print, are the margin tools for the table. You can justify the table by
clicking on the Word left, right, center, or fill justify buttons. You can also reposition and resize the table
by dragging these margin tools left or right with your mouse.
Additionally, Excel and Word have the ability to paste the data as “linked” information from Excel
into Word. With this feature, when the source data in Excel is updated, the Word data will be updated
upon the next opening of the file Word file. This feature makes your data dynamic. Word of caution,
while being a “really neat” feature, it may not be appropriate for all situations so its use needs to be
appropriate. To link the data in Word back to the source in Excel, copy the data as previously explained
from Excel, open Word and locate the target area within Word and follow the path Edit > Paste Special,
then select Paste Link from the left side of the popup dialog box and select Microsoft Excel Worksheet
Object in the “As” window. The data, as copied, is pasted into the document and linked back to its parent
or source.
Reading the information below it becomes immediately apparent that its presentation is unclear and
does not convey the complete data as words are chopped and lost.
40,
Shares
000 $1
pur par value
$50 p Amount
,000$12 co rate per
flig
If the cells in5Excel’s source data are reformatted to increase their width, the “trimming” problem is
resolved. But this may not be practical as resizing cells would affect the rest of the parent or source
worksheet presentation. This trimming is because the cells are shown as they are in Excel and numerous
empty cells were copied and pasted, that is, while all of the data has been entered into the cells, the data
exceeded the cell width of the column and the Word page and flowed into the areas of other columns. By
copying and pasting the link with a smaller range of cells, the presentation is:
40,000
shares purchased
$1
par value per share
$50,000
amount contributed to RAS, Inc for the shares
$125
rate per flight hour flown
Once pasted through the linking operation you can select the data in Word and add additional
formatting to it as mentioned earlier without it affecting the source data but that capability is limited. Fill,
borders, and font coloring from the tool bar are the easiest to invoke. This is done below.
40,
000 shares purchased
$1 $50
,000 par value per share
amount contributed to RAS, Inc for the shares $12
5 rate per flight hour flown The copied and linked blocks can be moved around once placed by selecting the matrix of linked cells
and then moving the tab markers on the index markers. Chapter 18 HEWLETTPACKARD HP12C
Chapter Outline
HewlettPackard HP12C Basics
Days Between Dates With The HP12C
Storing Values In The HP12C
Balloon Payments With The HP12C
Time Value Of Money With The HP12C
Present Value Of A $1 With The HP12C Present Value Of An Annuity Of $1 With The
HP12C
Future Value Of An Annuity Of $1 With The
HP12C
Other HP12C Functions HewlettPackard HP12C Basics
HewlettPackard produces the HP12C Programmable Financial Calculator, a calculator for the
professional accountant, financial analysts, and business users. This calculator uses “RPN” or “Reverse
Polish Notation” logic. With this logic as an operating system to add 2 and 2 together and get the result of
4, the keystrokes are:
“2” – The display shows “2”.
“Enter” – The display shows 2.0000. (If the display is set to 4 significant digits.)
“2” – The display shows “2”.
“Plus” – The display shows 4.000. (If the display is set to 4 significant digits.)
This is slightly different than the
“2” – The display shows “2”.
“+” – The display shows 2.0000. (If the display is set to 4 significant digits.)
“2” – The display shows “2”.
“=” – The display shows 4.000. (If the display is set to 4 significant digits.)
of calculators like the BA II+ of Texas Instruments. When mastered, the HP12C can greatly assist
you in your academic endeavors, accounting, financial, and business career. The HP12C has an extensive
manual and many features, only a few will be addressed here. The same basic features will be addressed
for the Texas Instrument BA II+ (Business Analyst II+) in other chapters.
Hint: There is no “=” (equal sign) on the HP12C calculator.
First, the HP12C default for significant digits displayed is four shown as “0.0000”. In most
academic, accounting, financial, and business situations four or five significant digits are appropriate.
And, when the requirement is less, the few extra digits displayed can easily be ignored. They will also be
hidden if the HP12C needs more display room for whole numbers. To set or change the number of
decimal places displayed in the HP12C’s display follow these steps:
1. Turn the HP12C on by pressing and releasing the black “ON” button on the lower left corner of
the key pad. This is a “toggle” switch, pressing it while the HP12C is on will turn the calculator
off. Section 2, Page 112 Chapter 18, Page 113
2. Press and release the yellow “F” (Function) key once. This key is just to the right of the “ON” on
the bottom row.
3. Press and release any of the numerical numbers from “0” through “9”. The display changes to the
requested display immediately. Any values current in the display are redisplayed in the new
format.
4. Now test the calculator, if you chose “4” as the significant digits, dividing 0.025 by 30 is actually
0.000833333….. This is done by the keystrokes “0.025”, “ENTER”, “30”, “÷” and the display
should show “0.0008” when the HP12C is asked for this calculation while set to four significant
digits. The trailing 3s round down and therefore, “off” the display. This complete value to some
significant number of digits is still held in memory, without rounding, for future calculations.
Multiply the display by 100,000 through the keystrokes “100000” and then “X” (Multiply) and
the results should be “83.3333” – demonstrating that the nondisplayed values are not lost.
5. Now divide 0.025 by 26 with the keystrokes “0.025”, “ENTER”, “26”, “÷” and the display
should show “0.0010” when the HP12C is asked for this calculation while set to four significant
digits. The HP12C should return “0.0010” while the actual value is 0.00096154… and it has
been rounded up to 0.0010. Multiply the display by 100,000 through the keystrokes “100000”
and then “X” (Multiply) and the result should be 96.1538 – again demonstrating that the nondisplayed values are not lost.
Second, the HP12C gives you a quick way to support the time value of money calculations in
numerous periodicities as well as annual statements and monthly interest and payments. No adjustments
or setting need be made. These will be discussed in detail later.
Tip: When using the time value of money function on the calculator develop the habit of entering
values in all “fields” from left to right or right to left even if that value is “0” (zero). This should preclude
any old values from entering the equation unexpectedly.
Enter the following data through the keystrokes – enter “60” in the “n” field of the time value of
money register of the HP12C by typing in “60” into the display and pressing and releasing the “n” key in
the upper left corner of the HP12C. Pressing the “ENTER” key is not required since you will not be
using it for a “keyboard” operation. Type in “6.9” and press and release the “ENTER” key, the type in
“12” followed by the “÷” (Division) key. The display should read “0.5750”. Press and release the “i”
(Interest) key to record this value as the period interest. Type in “35000” and press the “PV” (Present
value) key to enter the loan value into the present value field. Type in “0” and press and release the
“PMT” (Payment) field. As a matter of discipline, entering a value, even “0” in the fields or register from
left to right or from left to right will preclude an unintended value from entering the calculation. Type in
“0” and press and release the “FV” (Future value) field. Now press the “PMT” key and the HP12C will
calculate the payment for the loan as “691.3918”.
The HP12C will respect the presentation of cash flow and value flow within the time value of money
(TVM) calculations. If the “PV” (Present value or principle value) is entered as a positive number
indicating cash flows in or value flows in, the “PMT” (Payment) and / or “FV” (Future value” will be
negative indicating cash flows out or value flows out. If “PMT” (Payment) is entered as a positive value
indicating cash flows in or value flows in, then computed “PV” and / or “FV” (Future value) will be
negative indicating cash or value flows in the opposite direction  out. If you wanted true discipline, you
can enter the present value as positive since it was value inflow to you and cash outflow in payments will
be negative. However, if you were the used car salesman and wanted discipline, in the data enter, type in
“35000” and then press the “CHS” (Change sign) key in the fifth column of the first row. This will
convert a positive display to a negative value (or a negative value to a positive value). Rerun the
calculation simply by placing a “35000” (negative) into the present value field and recompute the Page 114 Solving Accounting Principles Problems Using Excel for Windows
payment. This time the HP12C should show “691.3918”, a positive value indicating cash flows or value
flows in since the present value is negative, representing cash flows or value flows out.
Annuities can be paid at the beginning or the end of the payment period To make this change there
are two buttons on the HP12C. The “Beginning” button labeled “BEG” in blue font on the slanted face of
the “7” and the “End” button labeled “END” in blue font on the slant face of the “8”. To change to
“Annuity Due” or “Beginning”, press and release the blue “g” key in the lower left corner of the HP12C
and then press the “BEG” (Beginning / 7) key since you are in the “Blue text options” after pressing and
releasing the blue “g” key. At this time the HP12C will display “BEGIN” in the center, bottom of its
display. To enter an “Ordinary Annuity” where payments are made at the end of the period, press and
release the blue “g” key in the lower left corner of the HP12C and then press the “END” (Ending / 8) key
since you are in the “Blue text options” after pressing and releasing the blue “g” key. At this time the HP12C display of “BEGIN” in the center, bottom of its display will disappear indicating that it is in the
default mode of “Ordinary Annuity.”
Clue: While the HP12C is in the “Annuity Due” mode – “BEGIN” indicating that the annuity
payment is due at the beginning of the period there will be displayed in the center, bottom of the display.
If the HP12C is in the “END” there will be no additional display items as this is the default mode. Days Between Dates With The HP12C
The HewlettPackard HP12C will calculate the days between dates. A very nice function for accounts
receivable and accounts payable aging as well as determining issues such as interest payments made or
received every 30 days. To determine the days between the invoice date of January 24, 2008 and the date
of March 15, 2008:
1. Turn the HP12C on by pressing and releasing the “ON” button on the bottom left corner of the
key pad.
2. Press the blue “g” key in the third column of the bottom row followed by the “M.DY” (“5” /
“M.DY”) key, in the eighth column of the second row from the top. This sets the HP12C to a
date format of “01.242008” for January 24, 2008. Your instruction manual has other date formats
available.
2. Type in the first date, January 24, 2008, as “01.242008” and press “Enter.”
3. Type in the second date of March 15, 2008 as “03.152008”.
4. Press and release the blue “g” key followed by the “▲DYS” (EEX / ▲DYS) in the sixth column
of the second row from the top. This key reads as “Delta in days” with “▲” being an engineering
symbol for “delta” or “Degree of change.” The “▲DYS” is in blue font on the slant face of the
key since you are in the “g” (blue) key board options. The HP12C should respond with “51”
days between these two dates.
You can also calculate the date a note, such as a 120day note, issued January 24, 2008 is due. To do
this:
1. Turn the HP12C on by pressing and releasing the “ON” button on the lower left corner of the
key pad.
2. Press the “g” (blue) key in the third column of the bottom row followed by the “M.DY” (“5” /
“M.DY”) key, in the eighth column of the second row from the top. This sets the HP12C to a
date format of “01.242008” for January 24, 2008. Your instruction manual has other date formats
available.
2. Type in January 24, 2008 as “01.242008” and press “Enter.”
3. Type in “120” into the HP12C. Do not hit the “ENTER” key.
4. Press the “g” (blue) key in the third column of the bottom row followed by the “DATE” (“CHS” /
“DATE”) key, in the sixth column of the top row. The HP12C should respond with “5,23,2008
5” indicating the note will be due on May 23, 2008, which is a Friday as indicated by the trailing
“5”. Chapter 18, Page 115
Tip: Entering the days between dates value as a negative will generate a valid response for a earlier
date. If you wanted a 180day note payable on June 30, 2008, enter “06.302008” and press “ENTER.”
Then enter “120” and press the “CHS” (change sign) key in the sixth column of the top row. The display
will show “120”. Press the blue “g” key followed by the “Date” key (“CHS” / “DATE”) again. The HP12C will respond with “3,02,2008 7” indicating that the note should be issued Sunday, March 2, 2008. Storing Values In The HP12C
A major asset of a professional calculator like the HP12C is the ability to store data. In the example you
are issuing 10 year bonds. The bonds carry a face interest rate of 8% while the market is 7%. Interest is
paid semiannually. The issue consists of 1,500 bonds with face values of $1,000 each. Your challenge is
to identify the present value of the interest payments, the present value of the bonds, and the present value
of the bond issue. The “Store” and “Recall” capability are major tools in this task.
First, determine the face value of the bonds – 1,500 bonds X $1,000 = $1,500,000 by typing in
“1500” and pressing and releasing the “Enter” key. Then type in “1000” and press and release the “X”
(multiply) key. The display will show “1,500,000”. Now use the keystrokes “STO” (Store) near the
bottom of the calculator in the fourth column, followed by pressing and releasing the “1” key. This will
store $1,500,000 into the Storage Register 1.
Now you will address the present value of the bonds.
While the $1,500,000 is still in the display, press and release the “FV” (Future value) key to record
this value into that field.
Type in “0” (zero) and press and release the “PMT” (Payment) button. It is recommended that you
develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or
right to left to ensure previous values do not enter your current calculation. This field will remain at “0”
for this calculation.
Type in “0” (zero) and press and release the “PV” button. It is recommended that you develop a
discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left
to ensure previous values do not enter your current calculation. This will be your “solve” value in a
couple of steps.
Type in the market interest rate as a whole number – “7” then press and release “ENTER” key, enter
“2” followed by the ÷ (division) key. The display will properly state the annual (stated) interest rate a
period of interest rate of 3 ½%. Press and release the “i” (Interest) key to enter the value into the interest
field.
Type in “10”, the life of the bonds in years, then press and release the “ENTER” key followed by “2”
and the “X” (Multiply) key in the right most column of the calculator. The display should read “20”. Press
and release the “n” (Period) key to enter this value into the “Number of periods” or “life” register.
Now press and release the “PV”(Present value) key in the third column of the top row. The HP12C
should respond with “753,848.8266”. This is the present value of the bonds.
Press and release the “STO” button in the fourth column of the bottom row followed by the “2”. You
have now stored that value into the second storage register.
To solve the present value of the interest payments, enter “0” (zero) into the HP12C and press and
release the “FV” (Future value) key.
Press and release the “RCL” (Recall) key, in the fourth column of the bottom row of the key pad,
followed by the “1” key. The value stored in the first memory register, “1,500,000” should appear in the
display. Page 116 Solving Accounting Principles Problems Using Excel for Windows
Press and release the “ENTER” key then enter “8” and press and release the “%” for the face interest
rate in the fifth column of the second row from the top. The display should show “120,000.0000”. Now
enter “0.5” indicating semiannual interest payments. Press and release the “X” (multiply) key on the
right side of the HP12C. The display should show the cash interest payments of “60,000.0000”.
Press and release the “STO” (Store) key and then the “3” key to place this value in the third storage
register.
The “60,000.0000” value should still be in the display. Press and release the “PMT” (Payment) key.
Press and release the “PV” (Present value) key and the HP12C should display “852,744.1981”, the
present value of the interest payments.
The “N” (Periods) and “I/Y” fields were entered in your first calculation for the present value of the
bonds and you have not changed them in this short period of time so reentry was not necessary.
Press and release the “STO” (Store) key followed by the “4” to store this value in the fourth memory
register.
The present value of the bonds is the sum of: “RCL” “2”, “ENTER”, and “753,848.8266” should
appear. Press and release the “RCL” (Recall) key followed by pressing and releasing the “4” key and the
display should read “852,744.1981”.
Press the “+” (Plus) sign to display the sum of these two numbers – “1,606,593.025”. Since your
display was set to four significant digits but the HP12C needed one of those places to display a whole
number it took it away. This value is the present value of the bonds.
While the “1,606,593.025” is in the display, press and release the “STO” (Store) button followed by
the “5” to store this value.
To determine the premium or discount, recall the value in memory register 1 by “RCL” “1” and ADD
these numbers as one is a positive value while the other is a negative value, the premium of “106,593.025” on this bond issue. Store this value in memory register by pressing “STO” followed by “6”.
Now, the bond issue is stored in “1”, the present value of the bonds is stored in “2”, the cash interest
payments are stored in “3”, the present value of the interest payments is stored in “4”, the present value of
the bond issue is stored in “5”, and the premium is stored in “6”.
The memory registers can be “overwritten” or “rewritten” by simply “STO” and the specific registry
number. All the memory registers can be cleared at one time by the keystrokes the yellow “f” key
followed by the “Clear – Reg” key which is in the fifth column and the third row for the top which has
white text of “CLX and orange/yellow text above it labeled “REG” which is under the “Gang title” of
“Clear.” The valid registers are from “0” (zero) through “9” (nine). Balloon Payments With The HP12C
The HewlettPackard HP12C (HP12C) respects cash and value inflows and outflows. If the present
value (PV) (or principle) entered is positive indicating cash inflows or value inflows to you, then the
payment (PMT) or future value (FV) will be an outflow or a negative value. However, for most uses you
realize that you are getting a loan or making a loan and enter the present value (PV) as positive to save
keystrokes and simply read “691.3918” as “$691.39” as your payment or cash outflow. However, assume
that in this loan example, you are buying a $35,000 vehicle, the loan term is 60 months, the interest is
6.9% annually, and you are going to make a balloon payment of $5,000 at the end of the loan with and
payments are made monthly. “N” or number of periods is (5 years X 12 months) “60”, “i” or interest is
“6.9 ENTER 12 ÷” or “0.5750”, “PV” or present value is “35000”, and “PMT” or payment is 0 as a
matter of discipline. Now cash flow representation is important. Since the balloon payment is cash flow
out and the principle is cash flow in, if the principle is positive, then the balloon payment or “FV” – future Chapter 18, Page 117
value, must be negative. If you had entered the 35000 as a negative number representing cash flow or
value flow out, then the balloon payment entered in “FV” must be positive. When “5000” is entered into
the HP12C you will need to press the “CHS” key in the sixth column of the top row of the key pad to
convert the positive value to negative “5000”. Now press and release the “FV” key to enter the negative
“5000” into that field. Now press “PMT” (Payment) the key in the fourth column of the top row. The HP12C should return 621.13716. Without the balloon payment your payment was $691.3918, with the
balloon payment your payment is only $621.3716. Had the balloon payment been put in as a positive
number matching the “PV” or present value field, the payment would be 761.4121 or $761.4121. Which
fails a basic logic evaluation of lower your payment level by the balloon payment. Time Value Of Money With The HP12C
The HewlettPackard HP12C (HP12C) utilizes the same field identities as the accounting and financial
professionals in the calculation of the time values of money. On the top row of keys of the HP12C from
left to right (column 1 through column 5) you are presented with:
“n” for the number of periods for the event. There is a way to turn years into months with the HP12C. In the loan example the term is 5 years. Enter “5” into the HP12C, then press the blue
“g” key followed by the “12X”, which is the “n” key in the upper left corner of the key pad.
By pressing the blue “g” key first you enter a secondary key pad which states “Take the input
value and multiply it by 12” when you press the “n” key. The display will show “60”, the
result of 5 years multiplied by 12 months per year. No additional settings or actions are
required to take advantage of this function. If you press “RCL” (Recall) in the bottom row of
the key pad followed by the “n” key you will see the value of “60”.
“i” is for interest for the event. The HP12C is expecting interest in whole values so 6.9% is
entered as “6.9”. If this is an annual interest rate and interest is compounded monthly you can
enter the “6.9” followed by blue “g” key followed by the “12÷”, which is the “i” key in the
second column of the upper row of the key pad. By pressing the blue “g” key first you enter a
secondary key pad which states “Take the input value and divide it by 12” when you press
the “i” key. The display will show “0.5750”, the result of 6.9(%) divided by 12 months per
year. No additional settings or actions are required to take advantage of this function.
“PV” is the present value field. For this example you will remain with the basic loan and conform
to discipline. Since the cash flow or value is in, it will be positive, so enter 35000 into the
HP12C and press and release the “PV” key in the third column of the top row.
“PMT” is the payment field. Since you are trying to solve for payment and want to ensure every
field’s value, enter “0” (zero) into the HP12C and press the “PMT” key.
“FV” is the future value field. For this example you will retain your balloon payment issue. Since
the “PV” or present or principle value is positive, this must be negative indicating that you
are making the payment, representing cash flows out, at the end of the loan. Enter 5000 into
the HP12C then press and release the “CHS” (Change sign) key in the sixth column of the
top row of the HP12C. The display changes to 5,000. Press and release the “FV” future
value key in the fifth column of the top row.
Now solve for “PMT” or payment by pressing the “PMT” (Payment) button in the fourth column of
the top row of the HP12C. The HP12C should respond with 621.3716 indicating our payment is
$621.3716. Page 118 Solving Accounting Principles Problems Using Excel for Windows Present Value Of A $1 With The HP12C
You need the present value of $1 with 15 periods at 15% interest and no payments, the fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV = 1
When “PV” (Present value) is pressed and released the HP12C should respond with 0.1229 which
can be checked with printed present value table for validity.
To find the present value of $150 with 15 periods at 15% interest and no payments, the fields would
be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV = 150
When “PV” (Present value) is pressed and released the HP12C should respond with 18.4342 which
can be checked with printed present value table for validity by multiplying $150 X 0.1229 = $18.4350
and the difference is a rounding factor.
Future Value Of A $1 With The HP12C
You need the future value of $1 with 15 periods at 15% interest and no payments, the fields would be:
n = 15
i = 15
PV = 1
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “FV” is pressed and released. The response should be 8.1371 which can be checked with
printed future value table for validity.
To find the future value of $150 with 15 periods at 15% interest and no payments, the fields would
be:
n = 15
i = 15
PV = 150
PMT = 0 as a matter of discipline to ensure the values in the HP12C.
FV =0 as a matter of discipline to ensure the values in the HP12C.
When “FV” (Future value) is pressed and released the HP12C response should be 1,220.5592 which
can be checked with printed future value table for validity by multiplying $150 X 8.1371 = $1,220.5650
and the difference is a rounding factor. Chapter 18, Page 119 Present Value Of An Annuity Of $1 With The HP12C
You need the present value of an annuity of $1 with 15 periods at 15% interest and no future value. The
payments are to be made at the end of the period. The fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 1
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “PV” is pressed and released the response should be 5.8474 which can be checked with
printed present value of an annuity table for validity.
To find the present value of an annuity of $150 with 15 periods at 15% interest and no future value.
The payments are to me made at the end of the period. The fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 150
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “PV” is pressed and released the HP12C should respond with 877.1055 which can be
checked with printed present value of an annuity table for validity by multiplying $150 X 5.8474 =
877.1100 and the difference is a rounding factor.
For the present value of an annuity due, change the HP12C into “BEGIN” or “Beginning” mode
(due) by utilizing the keystrokes:
1.
2. 3. Press and release the blue “g” key once. This key is in the third column of the bottom row.
Press and release the blue font “BEG” (7 / BEG) key, this text is shown below the “7” on the
slant face in the top row of the key pad, the seventh column. The blue text on the slant face of the
key is used since you are in the “blue” or “g” keyboard options.
The display should show “BEGIN in the bottom, center of the display. Recomputing the example as an annuity due by pressing the “PV” (Present value) key should result in
a value 1,008.6713.
To change to “ordinary annuity” or an annuity paying at the end of the period:
1.
2. 3. Press and release the blue “g” key once. This key is in the third column of the bottom row.
Press and release the blue font “END” (8 / END) key, this text is shown below the “8” on the
slant face in the top row of the key pad in the eighth column. The blue text on the slant face of
the key is used since you are in the “blue” or “g” keyboard options.
The display of “BEGIN in the bottom, center of the display will disappear as this is the default
mode. Future Value Of An Annuity Of $1 With The HP12C
You need the future value of an annuity of $1 with 15 periods at 15% interest and payments at the end of
the periods – ordinary annuity, the fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C. Page 120 Solving Accounting Principles Problems Using Excel for Windows
PMT = 1
FV = 0 as a matter of discipline to ensure the values in the HP12C.
When “FV” (Future value) key in the fifth column of the top row is pressed and released the HP12C
response should be 47.5804 which can be checked with printed future value of an annuity table for
validity.
To find the future value of an annuity of $150 with 15 periods at 15% interest and payments made at
the end of the periods, the fields would be:
n = 15
i = 15
PV = 0 as a matter of discipline to ensure the values in the HP12C.
PMT = 150
FV = 0
Then press and release the “FV” (Future value) key. The response should be 7,137.0616 which can
be checked with printed future value of an annuity table for validity by multiplying $150 X 47.5804 =
7,137.0616 and the difference is a rounding factor.
For the present value of an annuity due, change the HP12C into “BEGIN” or “Beginning” mode
(due) by utilizing the keystrokes:
1.
2. Press and release the blue “g” key once. This key is in the third column of the bottom row.
Press and release the blue font “BEG” (7 / BEG) key, this text is shown below the “7” on the
slant face in the top row of the key pad, the seventh column. The blue text on the slant face of the
key is used since you are in the “blue” or “g” keyboard options. Recomputing the example as an annuity due by pressing and releasing the “FV” (Future value) key.
The HP12C should respond with a value of “8,207.6209”.
To change to “ordinary annuity” or an annuity paying at the end of the period:
1. Press and release the blue “g” key once. This key is in the third column of the bottom row.
2. Press and release the blue font “END” (8 / END) key, this text is shown below the “8” on the
slant face in the top row of the key pad in the eighth column. The blue text on the slant face of the
key is used since you are in the “blue” or “g” keyboard options.
3. The display of “BEGIN in the bottom, center of the display will disappear as this is the default
mode. Other HP12C Functions
There are other HP12C functions explained in the HewlettPackard HP12C manuals and documents.
Some of these include the determination of the present value of a bond issue through a single math
function, net present value, internal rate of return, and depreciation functions. Chapter 19 TEXAS INSTRUMENTS BA II+
Chapter Outline
Texas Instruments BA II+ Basics
Days Between Dates With The BA II+
Storing Values In The BA II+
Balloon Payments With The BA II+
Time Value Of Money With The BA II+
Present Value Of A $1 With The BA II+ Present Value Of An Annuity Of $1 With The
BA II+
Future Value Of An Annuity Of $1 With The
BA II+
Other BA II+ Functions Texas Instruments BA II+ Basics
The Texas Instruments TI BA II Plus Advanced Business Analyst Calculator is a powerful tool that, if
learned, can greatly assist you in your academic endeavors, accounting, financial, and business career.
The BA II+ has an extensive manual and many features, only a few will be addressed here.
First, the BA II+ default for significant digits displayed is two shown as “0.00”. In most academic,
accounting, financial, and business situations four or five significant digits are appropriate. And, when the
requirement is less, the few extra digits displayed can easily be ignored. They will also be hidden if the
BA II+ needs more display room for whole numbers. To set or change the number of decimal places
displayed in the BA II+’s display follow these steps:
1.
2.
3. 4.
5.
6.
7. 8. Turn the BA II+ on by pressing and releasing the “On/Off” button on the upper right corner of
the key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “Format” key. This is in yellow text above the “.” (period) key in the center
of the bottom row. The yellow text above the key is used since you are in the “2nd” keyboard
option as selected in step 2.
Press and release the “4” key, if four significant digits are desired, or, press any number from 0
through 8.
Press and release the “Enter” key on the first row, second column.
Press “CE/C” on the lower left corner of the keyboard to clear the display and return to normal
operations.
Now test the calculator, if you chose “4” as the significant digits, dividing 0.025 by 30 is actually
0.000833333….. When the BA II+ is asked for this calculation while set to four significant digits
the response should be “0.0008” since the trailing 3s round down and therefore, “off” the display.
This value is still held in memory, without rounding, for future calculations. Multiply the display
by 100,000 and the result should be 83.3333 – demonstrating that the nondisplayed values are
not lost.
Now divide 0.025 by 26. The BA II+ should return “0.0010” while the actual value is
0.00096154 and it has been rounded up to “0.0010”. Multiply the display by 100,000 and the
result should be “96.1538” – again demonstrating that the nondisplayed values are not lost. Section 2, Page 121 Page 122 Solving Accounting Principles Problems Using Excel for Windows
Second, the BA II+ comes defaulted to compute the time value of money with monthly payments and
interest compounding – set for car dealers and real estate agents. In the academic, accounting, financial,
and business world, interest is frequently compounded in daily, monthly, quarterly, semiannual, and
annual terms. To insure that the BA II+ will handle most of these options without getting them confused it
is recommended that you set the payments and compounding periods (P/Y = Payments per year and C/Y
= Compounding periods per year) to “1” so that you can control the payment and compounding periods as
follows:
1.
2.
3. 4.
5.
6.
7. Turn the BA II+ on by pressing and releasing the “On/Off” button on the upper right corner of
the key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “P/Y” key, this text is shown above the “I/Y” key on the third row in the
second column. The yellow text above the key is used since you are in the “2nd” keyboard
options as selected in step 2.
The display should show “P/Y=” and a value such as “12”. Enter “1” and then press the “Enter”
key.
Press the up arrow or down arrow on the first row in the third or fourth column.
Check the value of “C/Y” and insure that it is also “1”. If it is not, enter “1” and then press the
“Enter” key.
Press “Clear” to return to the normal operations mode of the calculator. As a test, you are going to determine the payment for a $35,000 vehicle, the loan term is 60 months,
the interest is 6.9% annually and payments are made monthly. The BA II+ time value of money keys are
the five keys in the third row labeled “N” for number of periods, “I/Y” for the interest rate, “PV” for the
present value, “PMT” for payments, and “FV” for future value.
Tip: When using the time value of money function on the calculator develop the habit of entering
values in all “fields” from left to right or right to left even if that value is “0” (zero). This should preclude
any old values from entering the equation unexpectedly.
For the example loan, enter 60 into the keypad and press and release the “N” key in the first column
of the third row. Then enter 6.9% by typing in 6.9 then press and release the division (÷) key and type in
12 followed by the equal sign. This provides the period interest rate (6.9 / 12) of 0.5750. Enter this into
the interest field by pressing the “I/Y” key in the second key in the third row.
Clue: “I/Y” converts the value input into a percentage so had you entered 6.9%/12 or 0.00575, the
final calculation will be off.
Type in the $35,000 as 35000 and press the “PV” key, the center key in the third row. Since the object
is to compute payment (PMT), type in “0” (zero) and press the “PMT” or payment key, the key in the
fourth column of the third row. This will preclude any residual value from carrying over. Since there is no
balloon payment on this loan, type in “0” (zero) and press the “FV” or future value key. Now press and
release the “CPT” (compute) in the upper left corner of the keyboard. Now press and release the “PMT”
or payment key. The BA II+ should calculate 691.3918 indicating that for only $691.39 a month you can
have that used, minimum option, SUV hidden on the back lot.
Clue: The asterisks appearing above the value of the payment indicates that this value is computed,
not inputted by you.
The payment is negative since the present value or principle was entered as a positive indicating a
cash or value in to you. If you wanted true discipline, you would enter the present value (in this example)
as positive number since it was value inflow to you and cash outflow in payments will be negative.
However, if you were the used car salesman and wanted discipline, in the data enter, type in 35000 and
then press the +/ key just inside the bottom right corner of the keyboard. This will convert a positive Chapter 19, Page 123
display to a negative value (or a negative value to a positive value). Rerun the calculation simply by
placing a 35000 (negative) into the present value field and recompute the payment. This time the BA II+
should show 691.3918, a positive value since the present value is negative and it indicates the payments
will be cash in flows or value inflows.
Annuities can be paid at the beginning or the end of the payment period. To make this change there is
a “Toggle” function – the same keystrokes once will place it in the opposite mode of what it was in. So if
you run this set of keystrokes from the default of “END”, the BA II+ will be switched to “BGN” or
“Beginning” for annuity due. If it was in “BGN” for annuity due, the process will switch it to an ordinary
annuity or one paid at the end of the period. The keystrokes for this toggle on the BA II+ are:
1.
2.
3. 4. 5.
6.
7. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the
key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the
fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options
as selected in step 2.
The display should show “END” or “BGN” to indicate which mode you are currently in. If this is
the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to
cancel the toggle process.
If you want to change from “End” to “Beginning” or from “Beginning”” to “End, press and
release the “2nd” key once, from this intermediate position.
Then press and release the “SET” key once. This key is in the second column of the first row.
The display will change from “END” to “BGN” or from “BGN” to “END.”
Press and release the “C/CE” key to return to the normal operations of the BA II+. Clue: While the BA II+ is in the “Annuity Due” mode – “BGN” indicating that the annuity payment
is due at the beginning of the period there will be displayed near the top and towards the right side of the
display. If the BA II+ is in the “END” there will be no additional display items as this is the default mode. Days Between Dates With The BA II+
The Texas Instrument BA II+ will calculate the days between dates. A very nice function for accounts
receivable and accounts payable aging as well as determining issues such as interest payments made or
received every 30 days. To determine the days between the invoice date of January 24, 2008 and the date
of March 15, 2008:
1.
2.
3. 4. 5. 6. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the
key pad.
Press and release the yellow “2nd” key once. This key is in the first column of the second row.
Press and release the “DATE” key, this text is shown above the “1” key on the key pad. The
yellow text above the key is used since you are in the “2nd” keyboard options as selected in step
2.
The display should show “DT1=” for Date 1. To enter the date of January 24, 2008, enter
“01.2408” and press and release the “ENTER” in the second column of the first row. The display
should show “DT1= 1  24  2008”.
Use the up or down arrows in the third and fourth columns of row one to display the “DT2=”
screen. This screen may contain a date from previous work or a default date. Enter March 15,
2008 as “03.1508” and press and release the “ENTER” key. The display should show “DT2= 3 15 – 2008”.
Use the up and down arrows in the third and fourth columns of row one to display the “DBD=”
screen. This screen may show a value from a previous computation. Press the “CPT” button in Page 124 Solving Accounting Principles Problems Using Excel for Windows
the first column of the first row. The BA II+ should display “DBD= 51.000”, days between dates
is 51.
Hint: Each time a new date is entered the “CPT” process must be run again to generate the correct
number of days between dates.
You can also calculate the date a note, such as a 120day note, issued January 24, 2008 is due. To do
this:
1. Turn the BA II+ on by pressing and releasing the On/Off button on the upper right corner of the
key pad.
2. Press and release the yellow “2nd” key once. This key is in the first column of the second row.
3. Press and release the “DATE” key, this text is shown above the “1” key on the key pad. The
yellow text above the key is used since you are in the “2nd” keyboard options as selected in step
2.
4. The display should show “DT1=” for Date 1. To enter the date of January 24, 2008, enter
“01.2408” and press and release the “ENTER” in the second column of the first row. The display
should show “DT1= 1  24  2008”.
5. Use the up or down arrows in the third and fourth columns of row one to display the “DBD=”
screen. This screen may contain a value from previous work or a default value. Enter “120” and
then press and release the “ENTER” key. The display should show “DBD= 120”.
6. Use the up and down arrows in the third and fourth columns of row one to display the “DT2=”
screen. This screen may show a date from a previous computation or a default date. Press the
“CPT” button in the first column of the first row. The BA II+ should display “DT2= 5 – 23 –
2008”. The date a 120day note issued on January 24, 2008 would be due.
Tip: Entering the “DBD” (Days between dates) value as a negative will generate a valid response for
a earlier date. If you wanted a 180day note payable on June 30, 2008, enter “DT1” as “06.3008” and
“DBD” as “180” and then compute “DT2” to determine that the note should be issued January 02, 2008,
displayed as “Tue = 1022008”. Storing Values In The BA II+
A major asset of a professional calculator like the BA II+ is the ability to store data. In the example you
are issuing 10 year bonds. The bonds carry a face interest rate of 8% while the market is 7%. Interest is
paid semiannually. The issue consists of 1,500 bonds with face values of $1,000 each. Your challenge is
to identify the present value of the interest payments, the present value of the bonds, and the present value
of the bond issue. The “Store” and “Recall” capability are major tools in this task.
First, determine the face value of the bonds – 1,500 bonds X $1,000 = $1,500,000 and use the
keystrokes “STO” (Store) near the bottom of the calculator in the first column, followed by pressing and
releasing the “1” key. This will store $1,500,000 into the Storage Register 1.
Now you will address the present value of the bonds.
While the $1,500,000 is still in the display, press and release the “FV” (Future value) key to record
this value into that field.
Type in “0” (zero) and press and release the “PMT” (Payment) button. It is recommended that you
develop a discipline of entering values in all the TVM (Time value of money) fields from left to right or
right to left to ensure previous values do not enter your current calculation. This field will remain at “0”
for this calculation.
Type in “0” (zero) and press and release the “PV” button. It is recommended that you develop a
discipline of entering values in all the TVM (Time value of money) fields from left to right or right to left
to ensure previous values do not enter your current calculation. This will be your solve value in a couple
of steps. Chapter 19, Page 125
Type in the market interest rate as a whole number – “7” then press and release the ÷ (division) key,
enter “2” and press and release the equal sign to properly state the annual (stated) interest rate a period of
interest rate of 3 ½%. Press and release the “I/Y” (Interest) key to enter the value into the interest field.
Type in “10”, the life of the bonds in years, then press and release the “X” (Multiply) key in the right
most column of the calculator followed “2”, the number of interest periods per year by the “=” (Equal)
sign. The display should read “20”. Press and release the “N” (Period) key to enter this value into the
“Number of periods” or “life” register.
Now press and release the “CPT” (Compute) key in the upper, left corner of the calculator followed
by the “PV”(Present value) key in the center of the TVM row. The BA II+ should respond with “PV= 753,848.8266”. This is the present value of the bonds.
Press and release the “STO” button in the first column near the left, bottom corner followed by the
“2”. You have now stored that value into the second storage register.
To solve the present value of the interest payments, enter “0” (zero) into the BA II+ and press and
release the “FV” (Future value) key.
Press and release the “RCL” (Recall) key, near the left bottom corner of the key pad, followed by the
“1” key. The value stored in the first memory register, “1,500,000” should appear in the display.
Press and release the “X” (Multiply) key followed by the “(“ (Open parenthesis) then press and
release the “8”, the face interest rate, press and release the “%” (Percentage) key, followed by the ÷
(Division) followed by “2” and then the “)” parenthesis. The display will show “0.04”. This is the period
interest rate of the face or stated interest rate (8% / 2). Since you already have “1,500,000” and “X”
hovering in cyberspace from the earlier step, press “=” (Equal) and the BA II+ should respond with
60,000.00. This is your period interest payment in cash.
Press and release the “STO” (Store) key and then the “3” key to place this value in the third storage
register.
The $60,000.0000 value should still be in the display. Press and release the “PMT” (Payment) key.
Press and release the “CPT” key followed by the “PV” (Present value) key and the BA II+ should
display “PV= 852,744.1981”, the present value of the interest payments.
The “N” (Periods) and “I/Y” fields were entered in your first calculation for the present value of the
bonds and you have not changed them in this short period of time so reentry was not necessary.
Press and release the “STO” (Store) key followed by the “4” to store this value in the fourth memory
register.
The present value of the bonds is the sum of: “CE/C” to clear the display and working register to
preclude bring in an unintended value, “RCL” “2”, and “753,848.8266” should appear. Press and release
the “+” (Plus) key followed by “RCL” (Recall) and then the “4” key and the display should read “852,744.1981”.
Press the “=” (Equal) sign to display the sum of these two numbers – “1,606,593.025”. Since your
display was set to four significant digits but the BA II+ needed one of those places to display a whole
number it took it. This value is the present value of the bonds.
While the “1,606,593.025” is in the display, press and release the “STO” (Store) button followed by
the “5” to store this value.
To determine the discount, recall the value in memory register 1 by “RCL” “1” and ADD these
numbers as one is a positive value while the other is a negative value, the premium of “$106,593.0248”
on this bond issue. Store this value in memory register by pressing “STO” followed by “6”. Page 126 Solving Accounting Principles Problems Using Excel for Windows
Now, the bond issue is stored in “1”, the present value of the bonds is stored in “2”, the cash interest
payments are stored in “3”, the present value of the interest payments is stored in “4”, the present value of
the bond issue is stored in “5”, and the premium is stored in “6”.
The memory registers can be “overwritten” or “rewritten” by simply “STO” and the specific registry
number. All the memory registers can be cleared at one time by the keystrokes “2ND” then “MEM” (the
text above the “0” key), the “2ND” again followed by the “CE/C” key. The valid registers are from “0”
(zero) through “9” (nine). Balloon Payments With The BA II+
The Texas Instruments BA II Plus (BA II+) respects cash and value inflows and outflows. If the present
value (PV) (or principle) entered is positive indicating cash inflows or value inflows to you, then the
payment (PMT) or future value (FV) will be an outflow or a negative value. However, for most uses you
realize that you are getting a loan or making a loan and enter the present value (PV) as positive to save
keystrokes and simply read “691.3918” as “$691.39” as your payment or cash outflow. However, assume
that in this loan example, you are buying a $35,000 vehicle, the loan term is 60 months, the interest is
6.9% annually, and you are going to make a balloon payment of $5,000 at the end of the loan with and
payments are made monthly. “N” or number of periods is (5 years X 12 months) 60, “I/Y” or interest is
6.9 /12 months or 0.5750, “PV” or present value is 35000, and “PMT” or payment is 0 as a matter of
discipline. Now cash flow representation is important. Since the balloon payment is cash flow out and the
principle is cash flow in, if the principle is positive, then the balloon payment or “FV” – future value,
must be negative. If you had entered the 35000 as a negative number representing cash flow or value flow
out, then the balloon payment entered in “FV” must be positive. When 5000 is entered into the BA II+
you will need to press the “+/” key near the bottom right corner of the key pad to convert the positive
value to negative 5000. Now press and release the “FV” key to enter the negative 5000 into that field.
Now press “CPT” the key in the first column of the first row and then the “PMT” or payment key. The
BA II+ should return 621.13716. Without the balloon payment your payment was $691.3918, with the
balloon payment your payment is only $621.3716. Had the balloon payment been put in as a positive
number matching the “PV” or present value field, the payment would be 761.4121 or $761.4121. Which
fails a basic logic evaluation of lower your payment level by the balloon payment. Time Value Of Money With The BA II+
The Texas Instrument BA II Plus (BA II+) utilizes the same field identities as the accounting and
financial professionals in the calculation of the time values of money. On the third row of keys of the BA
II+ from left to right (column 1 through column 5) you are presented with:
“N” for the number of periods for the event. If you set your BA II+ up as earlier discussed with “1” in
the “P/Y” and “C/Y” parameters, this value is easy to determine. If the loan is for 5 years and payments as
well as interest is compounded monthly, enter the value of 5 into the BA II+ then press the “X” (times)
key in the right column of the BA II+, then press “12” followed by the “=” (equal sign). The display will
now indicate “60.000” representing the periods of the loan, in this example, months. Now press and
release the “N” or number of periods key in the first column of the second row.
“I/Y” is interest per year and, if the BA II+ has been set up as previously discussed, this is now
interest per period. Since it is interest per period and the interest rate is provided as annually, enter the
interest rate of 6.9 and DO NOT PRESS THE “%” KEY.. Now divide this value by the same factor as
you multiplied the term by – 12. So press and release the “÷” (divide) key followed by the “=” (equal
sign). The display should show 0.5750. Press and release the “I/Y” key to enter the value into the “I” or
interest field. Chapter 19, Page 127
“PV” is the present value field. For this example you will remain with the basic loan and conform to
discipline. Since the cash flow or value is in, it will be positive, so enter 35000 into the BA II+ and press
and release the “PV” key in the third column of row three.
“PMT” is the payment field. Since you are trying to solve for payment and want to ensure every
field’s value, enter “0” (zero) into the BA II+ and press the “PMT” key.
“FV” is the future value field. For this example you will retain your balloon payment issue. Since the
“PV” or present or principle value is positive, this must be negative indicating that you are making the
payment, representing cash flows out, at the end of the loan. Enter 5000 into the BA II+ then press and
release the “+/“ key near the bottom right corner of the BA II+. The display changes to 5,000. Press and
release the “FV” future value key in the fifth column of the third row.
Now solve for “PMT” or payment by pressing the “CPT” (Compute) button in the upper left corner of
the BA II+ followed by pressing and releasing the “PMT” (payment) key and the BA II+ should respond
with 621.3716 indicating our payment is $621.3716. Present Value Of A $1 With The BA II+
You need the present value of $1 with 15 periods at 15% interest and no payments, the fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV = 1
Then “CPT” and “PV.” The response should be 0.1229 which can be checked with printed present
value table for validity.
To find the present value of $150 with 15 periods at 15% interest and no payments, the fields would
be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV = 150
Then “CPT” and “PV.” The response should be 18.4342 which can be checked with printed present
value table for validity by multiplying $150 X 0.1229 = $18.4350 and the difference is a
rounding factor.
Future Value Of A $1 With The BA II+
You need the future value of $1 with 15 periods at 15% interest and no payments, the fields would be:
N = 15
I/Y = 15
PV = 1
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV = 0 as a matter of discipline to ensure the values in the BA II+.
Then “CPT” and “FV.” The response should be 8.1371 which can be checked with printed future
value table for validity.
To find the future value of $150 with 15 periods at 15% interest and no payments, the fields would be:
N = 15
I/Y = 15
PV = 150
PMT = 0 as a matter of discipline to ensure the values in the BA II+.
FV =0 as a matter of discipline to ensure the values in the BA II+. Page 128 Solving Accounting Principles Problems Using Excel for Windows
Then “CPT” and “FV.” The response should be 1,220.5592 which can be checked with printed
future value table for validity by multiplying $150 X 8.1371 = $1,220.5650 and the difference is a
rounding factor. Present Value Of An Annuity of $1 With The BA II+
You need the present value of an annuity of $1 with 15 periods at 15% interest and no future value. The
payments are to be made at the end of the period. The fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 1
FV = 0 as a matter of discipline to ensure the values in the BA II+.
Then “CPT” and “PV.” The response should be 5.8474 which can be checked with printed present
value of an annuity table for validity.
To find the present value of an annuity of $150 with 15 periods at 15% interest and no future value.
The payments are to me made at the end of the period. The fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 150
FV = 0 as a matter of discipline to ensure the values in the BA II+.
Then “CPT” and “PV.” The response should be 877.1055 which can be checked with printed present
value of an annuity table for validity by multiplying $150 X 5.8474 = 877.1100 and the difference is a
rounding factor.
For the present value of an annuity due, change the BA II+ into “BGN” or “Beginning” mode (due)
by utilizing the keystrokes:
1. Press and release the yellow “2nd” key once. This key is in the first column of the second row.
2. Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the
fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options
as selected in step 2.
3. The display should show “END” or “BGN” to indicate which mode you are currently in. If this is
the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to
cancel the toggle process.
4. If you want to change from “End” to “Beginning”, press and release the “2nd” key once, from this
intermediate position.
5. Then press and release the “SET” key once. This key is in the second column of the first row. The
display will change from “END” to “BGN.”
6. Press and release the “C/CE” key to return to the normal operations of the BA II+.
7. You will have a “BGN” text string in the upper right area of the display while in this mode.
Recomputing the example as an annuity due should result in a value 1,008.6713.
The steps of 1 through 6 are “toggles.” Accomplishing them again should result in a “BGN” in step 2
and “END” in step 5. Chapter 19, Page 129 Future Value Of An Annuity Of $1 With The BA II+
You need the future value of an annuity of $1 with 15 periods at 15% interest and payments made at the
end of the periods – ordinary annuity, the fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 1
FV = 0
Then “CPT” and “FV.” The response should be 47.5804 which can be checked with printed future
value of an annuity table for validity.
To find the future value of an annuity of $150 with 15 periods at 15% interest and payments are made
at the end of the periods, the fields would be:
N = 15
I/Y = 15
PV = 0 as a matter of discipline to ensure the values in the BA II+.
PMT = 150
FV = 0
Then “CPT” and “FV.” The response should be 7,137.0616 which can be checked with printed
future value of an annuity table for validity by multiplying $150 X 47.5804 = 7,137.0600 and the
difference is a rounding factor.
For the future value of an annuity due, change the BA II+ into “BGN” or “Beginning” mode (due) by
utilizing the keystrokes:
1. Press and release the yellow “2nd” key once. This key is in the first column of the second row.
2. Press and release the “BGN” key, this text is shown above the “PMT” key on the third row in the
fourth column. The yellow text above the key is used since you are in the “2nd” keyboard options
as selected in step 2.
3. The display should show “END” or “BGN” to indicate which mode you are currently in. If this is
the mode you desire, press and release the “CE/C” key on the lower left corner of the key pad to
cancel the toggle process.
4. If you want to change from “End” to “Beginning”, press and release the “2nd” key once, from this
intermediate position.
5. Then press and release the “SET” key once. This key is in the second column of the first row. The
display will change from “END” to “BGN.”
6. Press and release the “C/CE” key to return to the normal operations of the BA II+.
7. You will have a “BGN” text string in the upper right area of the display while in this mode.
Recomputing the example as an annuity due should result in a value 8,207.6209.
The steps of 1 through 6 are “toggles.” Accomplishing them again should result in a “BGN” in step 2
and “END” in step 5. Other BA II+ Functions
There are other BA II+ functions explained in the Texas Instrument BA II Plus manuals and documents.
Some of these include the determination of the present value of a bond issue through a single math
function, net present value, internal rate of return, depreciation functions, and breakeven points. ...
View
Full
Document
This note was uploaded on 09/19/2010 for the course ACCT 220 taught by Professor Ullmann during the Fall '10 term at University of Nebraska Kearney.
 Fall '10
 Ullmann
 Accounting

Click to edit the document details