Document Sample

```					                                      aa8d3046-8481-4743-94e2-e248a3081a24.xls

Amortization Table
u To use the table, change the values in the Inital Data section of the worksheet.
u To print the table, choose Print from the File menu. The print area is set to A1:G77.
u If you increase the term of the loan or the number of payments, you will need to add more payment periods
to the table. Select cells A75:G77, then drag the Fill handle (+) into the cells below the table.
u Most formulas on this worksheet are contained in defined names. To see the names and formulas,
choose Name from the Insert menu, and then choose Define. Select a name from the list
('Amortization Table'!Interest, for example).

Initial Data
LOAN DATA                                                            TABLE DATA
Loan amount:          \$10,000.00                             Table starts at date:
Annual interest rate:   6.00%                                    or at payment number: 1
Term in years:    5
Payments per year:      12
First payment due:     7/1/2004
PERIODIC PAYMENT
Entered payment:                              The table uses the calculated periodic payment amount,
Calculated payment:              \$193.33         unless you enter a value for "Entered payment."
CALCULATIONS
Use payment of:                \$193.33                 Beginning balance at payment 1:         \$10,000.00
1st payment in table: 1                               Cumulative interest prior to payment 1:          \$0.00

Table
Payment                 Beginning                                             Ending       Cumulative
No.            Date                  Balance           Interest         Principal         Balance       Interest
1           7/1/2004               10,000.00           50.00            143.33           9,856.67        50.00
2           8/1/2004                9,856.67           49.28            144.04           9,712.63        99.28
3           9/1/2004                9,712.63           48.56            144.76           9,567.86       147.85
4          10/1/2004                9,567.86           47.84            145.49           9,422.37       195.69
5          11/1/2004                9,422.37           47.11            146.22           9,276.16       242.80
6          12/1/2004                9,276.16           46.38            146.95           9,129.21       289.18
7           1/1/2005                9,129.21           45.65            147.68           8,981.53       334.82
8           2/1/2005                8,981.53           44.91            148.42           8,833.11       379.73
9           3/1/2005                8,833.11           44.17            149.16           8,683.95       423.90
10           4/1/2005                8,683.95           43.42            149.91           8,534.04       467.32
11           5/1/2005                8,534.04           42.67            150.66           8,383.38       509.99
12           6/1/2005                8,383.38           41.92            151.41           8,231.97       551.90
13           7/1/2005                8,231.97           41.16            152.17           8,079.80       593.06
14           8/1/2005                8,079.80           40.40            152.93           7,926.87       633.46
15           9/1/2005                7,926.87           39.63            153.69           7,773.18       673.10
16          10/1/2005                7,773.18           38.87            154.46           7,618.72       711.96
17          11/1/2005                7,618.72           38.09            155.23           7,463.48       750.06
18          12/1/2005                7,463.48           37.32            156.01           7,307.47       787.37
19           1/1/2006                7,307.47           36.54            156.79           7,150.68       823.91
20           2/1/2006                7,150.68           35.75            157.57           6,993.11       859.67
21           3/1/2006                6,993.11           34.97            158.36           6,834.74       894.63
22           4/1/2006                6,834.74           34.17            159.15           6,675.59       928.80
23           5/1/2006                6,675.59           33.38            159.95           6,515.64       962.18
24           6/1/2006                6,515.64           32.58            160.75           6,354.89       994.76
25           7/1/2006                6,354.89           31.77            161.55           6,193.33      1,026.54
26           8/1/2006                6,193.33           30.97            162.36           6,030.97      1,057.50

Page 1
aa8d3046-8481-4743-94e2-e248a3081a24.xls
Payment     Beginning                              Ending     Cumulative
No.      Date      Balance      Interest     Principal   Balance     Interest
27     9/1/2006    6,030.97      30.15        163.17     5,867.80    1,087.66
28    10/1/2006    5,867.80      29.34        163.99     5,703.81    1,117.00
29    11/1/2006    5,703.81      28.52        164.81     5,539.00    1,145.51
30    12/1/2006    5,539.00      27.70        165.63     5,373.37    1,173.21
31     1/1/2007    5,373.37      26.87        166.46     5,206.91    1,200.08
32     2/1/2007    5,206.91      26.03        167.29     5,039.61    1,226.11
33     3/1/2007    5,039.61      25.20        168.13     4,871.48    1,251.31
34     4/1/2007    4,871.48      24.36        168.97     4,702.51    1,275.67
35     5/1/2007    4,702.51      23.51        169.82     4,532.70    1,299.18
36     6/1/2007    4,532.70      22.66        170.66     4,362.03    1,321.84
37     7/1/2007    4,362.03      21.81        171.52     4,190.52    1,343.65
38     8/1/2007    4,190.52      20.95        172.38     4,018.14    1,364.61
39     9/1/2007    4,018.14      20.09        173.24     3,844.90    1,384.70
40    10/1/2007    3,844.90      19.22        174.10     3,670.80    1,403.92
41    11/1/2007    3,670.80      18.35        174.97     3,495.83    1,422.27
42    12/1/2007    3,495.83      17.48        175.85     3,319.98    1,439.75
43     1/1/2008    3,319.98      16.60        176.73     3,143.25    1,456.35
44     2/1/2008    3,143.25      15.72        177.61     2,965.64    1,472.07
45     3/1/2008    2,965.64      14.83        178.50     2,787.14    1,486.90
46     4/1/2008    2,787.14      13.94        179.39     2,607.75    1,500.83
47     5/1/2008    2,607.75      13.04        180.29     2,427.46    1,513.87
48     6/1/2008    2,427.46      12.14        181.19     2,246.27    1,526.01
49     7/1/2008    2,246.27      11.23        182.10     2,064.17    1,537.24
50     8/1/2008    2,064.17      10.32        183.01     1,881.16    1,547.56
51     9/1/2008    1,881.16       9.41        183.92     1,697.24    1,556.97
52    10/1/2008    1,697.24       8.49        184.84     1,512.40    1,565.45
53    11/1/2008    1,512.40       7.56        185.77     1,326.63    1,573.02
54    12/1/2008    1,326.63       6.63        186.69     1,139.94    1,579.65
55     1/1/2009    1,139.94       5.70        187.63      952.31     1,585.35
56     2/1/2009     952.31        4.76        188.57      763.74     1,590.11
57     3/1/2009     763.74        3.82        189.51      574.23     1,593.93
58     4/1/2009     574.23        2.87        190.46      383.78     1,596.80
59     5/1/2009     383.78        1.92        191.41      192.37     1,598.72
60     6/1/2009     192.37        0.96        192.37       0.00      1,599.68

Page 2
aa8d3046-8481-4743-94e2-e248a3081a24.xls
Payment   Beginning                              Ending    Cumulative
No.     Date     Balance      Interest     Principal   Balance    Interest

Page 3
aa8d3046-8481-4743-94e2-e248a3081a24.xls
Payment   Beginning                              Ending    Cumulative
No.     Date     Balance      Interest     Principal   Balance    Interest

Page 4
aa8d3046-8481-4743-94e2-e248a3081a24.xls
Payment   Beginning                              Ending    Cumulative
No.     Date     Balance      Interest     Principal   Balance    Interest

Page 5
aa8d3046-8481-4743-94e2-e248a3081a24.xls
Payment   Beginning                              Ending    Cumulative
No.     Date     Balance      Interest     Principal   Balance    Interest

Page 6
aa8d3046-8481-4743-94e2-e248a3081a24.xls
Payment   Beginning                              Ending    Cumulative
No.     Date     Balance      Interest     Principal   Balance    Interest

Page 7
Worksheet Functions

Worksheet Function Examples
This worksheet contains sample formulas you can use to complete common spreadsheet tasks.
Cells containing formulas are yellow. To view a sample formula, select the cell to display the formula
in the formula bar. Or, press CTRL+` to switch between displaying values and displaying
containing the function, then click the Function Wizard button on the Standard toolbar.

Summing, Counting, and Offsetting
Use the new COUNTIF and SUMIF functions to total data that meets specified criteria.            Sample List
The formulas in the following table use the sample data located in cells G10:J16.                   Name           Salary   Dept.
Functions                  Using                        Using                 Using
and Operators            Cell References              Defined Names             OFFSET()      Nancy Davolio      16,000           9
COUNTIF()                                      2                              2          2      Andrew Fuller      25,000           3
SUMIF()                                      145                            145        145      Michael Suyama     28,500           3
SUM(IF())                                      2                              2          2      Janice Leverling   30,000       12
SUM(IF())                                    145                            145        145      Linda Callahan     50,000           9
SUM(IF(IF()))                                126                            126        126      Johnathan King     50,000       12

Indexing, Matching, and Looking Up Values
By setting the new fourth argument for the VLOOKUP function (Range_Lookup) to FALSE,
you can look up an exact value in a list. The formulas in the following table use the sample
data located in cells G10:J16.

Functions                   Using                         Using                 Using
and Operators            Cell References               Defined Names             OFFSET()
VLOOKUP()                               50,000                           50,000
MATCH()                                      6                                6
INDEX()                                 30,000                           30,000
INDEX(MATCH())                          50,000                           50,000        50,000

Manipulating Text
Use the LEFT, MID, and RIGHT functions to extract characters from a text string.

Page 8
Worksheet Functions

Use the new CONCATENATE function to combine separate text strings.
Sample_Text:     He was out at first base.

Functions                  Using                         Using
and Operators           Cell References               Defined Names
LEFT()              He was out                  He was out
MID()               at                          at
RIGHT()             first base.                 first base.
CONCATENATE()       He was out at first base.   He was out at first base.
Concatenation (&)   He was out at first base.   He was out at first base.

Date Functions
Start Date                         9/10/1990
End Date                          10/20/1992

Number of months between above dates:           25

Number of days between above dates:             771

Number of years between above dates:            2

Time Functions

Summing Time
Start Time          End Time                    Elapsed Time
8:15 AM             5:30 PM                     9:15

Accumulating Time Over 24 Hours
9:00
12:30
16:05
22:00

Page 9
Worksheet Functions

Total (in decimal):                    59.58
Total ([h]:mm:ss):                  59:35:00

Converting Time in h:mm Format to Decimal Format
17.83                   17:50

Converting Time in Decimal Format to h:mm Format
4:30:00                      4.5

Forecasting with the TREND, LINEST, and FORECAST Functions.
Creating a Best Fit Line Using TREND()                                         Creating a Best Fit Line Using LINEST()

Predicted Y                                           Predicted Y
Month                Sales                    TREND()                   Month        Sales          LINEST()      Slope
1      4200                                  4543             1        4200                  4543 1122.86
2      6100                                  5666             2        6100                  5666
3      7300                                  6789             3        7300                  6789
4      7300                                  7911             4        7300                  7911
5      8700                                  9034             5        8700                  9034
6     10500                                 10157             6       10500                 10157

Predicting Values Using TREND()                                                Predicting Values Using FORECAST()
Month                 Sales                                              Month         Sales
1                    4200                                          1             4200
2                    6100                                          2             6100
3                    7300                                          3             7300
4                    7300                                          4             7300
5                    8700                                          5             8700
6                   10500                                          6            10500
7                   11280 Prediction                               7            11280 Prediction
8                   12403 Prediction                               8            12220 Prediction
9                   13526 Prediction                               9            13384 Prediction

Page 10
Worksheet Functions

Qty Sold

19
129
234
199
126
45

Page 11
Worksheet Functions

Page 12
Worksheet Functions

Y Intercept
3420

Page 13
Control Structures

Control Structures
This sheet contains macros written in the Microsoft Excel 4.0 macro language. For more
information about a function, select the cell containing the function, then click the
Function Wizard button on the Standard toolbar.

Guidelines for running each macro appear with each description. To run a macro,
choose the Macro command from the Tools menu.

The Visual Basic equivalent for the macro is identified in the Visual Basic Procedure
Name column. To view the Visual Basic equivalent, select the VBControl Structures tab.

Visual Basic
Microsoft Excel 4.0 Macro                                                                     Procedure Name
Test For Multiple Conditions                                                                  VB_Multiple_Condition_Testing
Illustrates branching using IF, ELSE, and ELSE.IF functions.
Enters text to the right of the active cell, indicating the positive, negative,
or zero value of the active cell.
> Run this macro from the first cell of a continuous column of positive
and negative numbers.
=WHILE(NOT(ISBLANK(ACTIVE.CELL())))
=IF(ACTIVE.CELL()>0)
=      FORMULA("Positive",OFFSET(ACTIVE.CELL(),0,1))
=ELSE.IF(ACTIVE.CELL()<0)
=      FORMULA("Negative",OFFSET(ACTIVE.CELL(),0,1))
=ELSE()
=      FORMULA("Zero",OFFSET(ACTIVE.CELL(),0,1))
=END.IF()
=SELECT(OFFSET(ACTIVE.CELL(),1,0))
=NEXT()
=RETURN()

For Next Loop                                                                                 VB_For_Next_Array
Enters a predefined series of numbers into the range \$A\$1:\$D\$4 on
the active worksheet.
> Run this macro from a blank worksheet.
=SET.NAME("List",{1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16})
=FOR("counter1",1,ROWS(List))

Page 14
Control Structures

=   FOR("counter2",1,COLUMNS(List))
=       SET.NAME("index_value",INDEX(List,counter1,counter2))
=       FORMULA(index_value,OFFSET(!A1,counter1-1,counter2-1))
=   NEXT()
=NEXT()
=RETURN()

While Next Loop                                                                         VB_While_Next_UsingSelect
Applies italic and bold formatting to all cells in a single column that have            VB_Do_Until
values greater than 33.
> Run this macro from the first cell in a column that contains the numbers 0 to 100.
=WHILE(NOT(ISBLANK(ACTIVE.CELL())))
=     IF(ACTIVE.CELL()>33)
=         FORMAT.FONT(,,TRUE,TRUE)
=     END.IF()
=     SELECT(OFFSET(ACTIVE.CELL(),1,0))
=NEXT()
=RETURN()

Main Procedure (Subroutine Sample)                                                      VB_Main_Procedure
Calls the macro Subroutine, passing it the current time. Then displays
the time returned by Subroutine.
> Run this macro from any worksheet.
EndTime=Subroutine(NOW())                                                               Cell formatting, for international:
=RETURN()

Subroutine (Subroutine Sample)                                                          VB_Main_Function
Displays a message showing the time received from Main_Procedure, then
returns the current time to Main_Procedure when the user chooses the OK button.
> This macro runs automatically when you run Main_Procedure.
=ARGUMENT("passed_value")
=RETURN(NOW())

Page 15

This sheet contains macros written in the Microsoft Excel 4.0 macro language. For more
information about a function, select the cell containing the function, then click the Function
Wizard button on the Standard toolbar.
Guidelines for running each macro appear with each description. To run a macro, choose
the Macro command from the Tools menu.
The Visual Basic equivalent for the macro is identified in the Visual Basic Procedure Name
column. To view the Visual Basic equivalent, select the VBAdvanced tab.

sample procedures that do not have Microsoft Excel 4.0 macro language
equivalents, including several OLE 2 examples that work with Word 6.0 for Windows.

Visual Basic
Microsoft Excel 4.0 Macro                                                                        Procedure Name
Custom Function: Distance Calculation                                                            VB_Distance
Returns the distance between two points, given the x and y coordinates of both.
> To use this custom function, select a blank cell on a worksheet, and click the
Function Wizard button. Select Samples Collection from the Function Category list
and choose the Finish button. Then enter values for all arguments and press ENTER.
=RESULT(1)
=ARGUMENT("First_X",1)
=ARGUMENT("First_Y",1)
=ARGUMENT("Second_X",1)
=ARGUMENT("Second_Y",1)
distance_calc=SQRT((Second_X-First_X)^2+(Second_Y-First_Y)^2)
=RETURN(distance_calc)

Branch Example: VB Code <=> XLM Code.                                                            VB_Branch_Example
Calls the VB_Branch_Example procedure located in the VBAdvanced module.
> Run this macro from any sheet.
=IF(ALERT("Currently running XLM code. Call VB procedure?",1)=TRUE)
=END.IF()
=RETURN()

Page 16

MS Query Fetch                                                                                VB_MS_Query_Fetch
Starts Microsoft Query, performs the query, then returns the data to Microsoft Excel.
For information about using Microsoft Query, see the Microsoft Query User's Guide .
> To run this macro, you must install Microsoft Query and the dBASE driver using
Microsoft Excel Setup. The sample file EMPLOYEE.DBF is installed when you install
Microsoft Query.
> Run this macro from any sheet. A new output sheet is inserted by the macro.
(The following function calls the VB_GetWindowsDirectory function in the VBAdvanced module which retrieves the name of the Windows
module, which retrieves the name of the Windows directory, and appends the
Microsoft Query path to it. This is used to refer to Query files.)
=EXEC(MSQueryPath&"msquery.exe")
chan=INITIATE("MSQuery","System")
(The dBASE Files data source is the default data source that is available when you
install the dBASE driver during Setup.)
=EXECUTE(chan,"[logon('dBase Files')]")
=EXECUTE(chan,"[open('select * from "&MSQueryPath&"employee.dbf where CITY='London'')]")
nr=REQUEST(chan,"numrows")
nc=REQUEST(chan,"numcols")
=WORKBOOK.INSERT(1)
=EXECUTE(chan,"[fetch('excel','"&GET.WORKBOOK(38)&"','r1c1:r"&nr&"c"&nc&"','all')]")
=EXECUTE(chan,"[exit()]")
=TERMINATE(chan)
=WORKBOOK.NAME(,">> Query Results <<")
=RETURN()

API Call GetPrivateProfileString                                                              VB_API_Call_GetPrivateProfileString
Calls the kernal to retrieve the name of the last file opened (which is stored
in the EXCEL5.INI file), then displays the name in a message box.
> Run this macro from any sheet.
=RETURN()

API Call Play WAV File                                                                        VB_API_Call_Play_WAV_File
Plays the WAV sound file specified by the CALL function (DING.WAV).
> Run this macro from any sheet, then listen! Requires a sound driver to work.
=SET.NAME("sound",REGISTER("MMSYSTEM","sndPlaySound","ACH","Play","lpszSoundName,wFlags"))

Page 17

=CALL(sound,"ding.wav",1)
=RETURN()

Page 18

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 24 posted: 5/2/2012 language: English pages: 18