Calculate Fixed Annuity Based on Interest Rate by emg45140

VIEWS: 19 PAGES: 12

More Info
									Example of Relative Cell Referece:
Sale for September 2004
Region            Magazines Books                    Apparel
North                  $1,200               $2,113             $1,250           Relative cell references are basic cell ref
South                  $1,450               $2,450             $1,525           that adjust and change when copied or w
East                   $2,341               $3,200             $1,623
West                   $3,442               $3,800             $2,356
Total                  $8,433              $11,563             $6,754



Find the Sale Total for Apparel?

Example of Absolute Cell Referece:
Commission for September 2004
                 Total Sales Commission(10%)    Commission(15%)                 There are certain situations in which the
Robert               $15,213             $1,521          $2,282                 using AutoFill. Dollar signs are used to h
Dennis               $23,654             $2,365          $3,548                 example, we would like to hold the comm
Mary                 $19,523             $1,952          $2,928
Jennifer             $21,566             $2,157          $3,235

Rate                      10% Find the Commission with 15%?
Rate                      15%




Another Example of Absolute Cell Referece:
Prices after Discount
Product            Price     AfterDiscountA       AfterDiscountB
Polo Shirt               $55               $49.50            $52.25
Jeans                    $42               $37.80            $39.90
Watch                    $85               $76.50            $80.75


Discount A                10%
Discount B                 5% Find the Prices After DiscountB?


Three Dimensional Reference in Formulas:
Having a formula that points to cells or sets of cells in other worksheets is called three-dimensional
referencing.The formula =SUM(Sheet1:Sheet3!B4) adds the contents of the cell B4 in Sheet1, Sheet2,
Sheet3.Rather than just using a range of cells, we are using a range of sheets first and then indicating
which cells we want to use.The exclamation mark separates the worksheet reference(s) from the
cell reference(s).
             $4,624
Example: Total Sale Amount of Magazines in July, August and September (=SUM(Sheet1:Sheet3!B4:B7)
            $30,723
Find the Combined Sale Amount for Books and Apparel in July, August
and September.
$39,129.00
ell references are basic cell references
t and change when copied or when using AutoFill.




 certain situations in which the cell reference must remain the same when copied or when
                                                                                this
oFill. Dollar signs are used to hold a column and/or row reference constant. In the above
we would like to hold the commission rate constant.




Sheet3!B4:B7)
Example of Relative Cell Referece:
Sale for August 2004
Region     Magazines Books      Apparel
North         $1,212     $2,133    $1,260
South         $2,450     $2,350    $1,425
East          $2,541     $3,500    $1,623
West          $4,442     $5,800    $2,756
Total        $10,645 $13,783       $7,064
Example of Relative Cell Referece:
Sale for July 2004
Region     Magazines Books      Apparel
North          $2,212    $2,133    $1,260
South          $2,450    $2,350    $1,425
East           $2,541    $3,500    $1,623
West           $4,442    $5,800    $2,756
Total         $11,645 $13,783      $7,064
Calculating Loan Payment                (assume monthly payment)
                                                                               PMT is a frequently used financial function
Amount borrowed                             $10,000                            It is used to calculate the payment for a loan ba
Annual Interest Rate                          6.75%                            Generally used to calculate fixed rate mortgage
No of Payments per year                           12
Total no of payments                              48

Amount due each payment                     $238.30


Calculating Future Value                (assume monthly payment)

Interest rate                                   12%                            FV is used to analyze long term financial in
No of payments                                    48                           One application could be to use FV for future in
Amount paid                                    -1500
Future Value                                       0

Future value of annuity                  $91,833.91


Using If Function                       (In the "target reached" column, use if function to derive at either "yes" or "no")

Sales Report
          Expected Target Sale              $30,000

Employee Sales        Target Reached?
A            32000    yes
B            25000    no
C            12250    no
D            36500    yes
E            31520    yes
F            29000    no
G            32500    yes
H            33200    yes
I            23000    no
y used financial function.
 the payment for a loan based on constant payments and constant interest rates.
culate fixed rate mortgage payments or monthly payments.




ze long term financial investments.
d be to use FV for future investments based on payments and interest rates




 either "yes" or "no")
Sum of Amount State
Gender        CA       TX      Grand Total
F                 2500   13500      16000
M                 5000    3500       8500
Grand Total       7500   17000      24500
PIVOT TABLE
It is an interactive table that allows one to summarize, analyze, and manipulate data from a large collection of inform
They are called the pivot tables because the fields can be moved or changed the way the data is summarized.
Use instruction sheet to derive at the template


Contributor             Gender    Street                            City             State
Mr. Douglas Viereck     M         4005 West Madison Road            Toledo           TX
Mr. Ken Hodge           M         6 Bayberry Pointe Drive           Topaz            MI
Mr. Gregory Olson       M         1925 Bridge Street                Halfway Corner   MA
Mr. Ray Suchecki        M         Pond Hill Road                    Monroe           VT
Ms. Susan Mouw          F         3408 Gateway Boulevard            Sylvania         TX
Ms. Gretchen Fletcher   F         2819 East 10 Street               Mishawaka        IN
Ms. Doris Reaume        F         82 Mixi Road                      Bootleg          ME
Ms. Shirley Woodruff    F         8408 E. Fletcher Road             Clare            MI
Mr. Wayne Bouwman       M         400 Salmon Street                 Ada              MA
Mr. John Rohrs          M         37 Queue Highway                  Lacota           CO
Ms. Michele Yasenak     F         95 North Bay Boulevard            Jenison          CO
Mr. Ronald Kooienga     M         15365 Old Bedford Trail           Eagle Point      FL
Mr. Donald Bench        M         2874 Western Avenue               Drenthe          WY
Ms. Janice Stapleton    F         2840 Cascade Road                 Zeeland          WV
Ms. Joan Hoffman        F         4090 Division Street NW           Borculo          MA
Mr. Shannon Petree      M         3509 Garfield Avenue              Romulus          NC
Mr. Joe Markovicz       M         1366 36th Street                  Roscommon        MA
Ms. Bridgit Feeney      F         5013 North Cliff Avenue           LaPorte          IN
Ms. Dawn Parker         F         1935 Snow Street SE               Saugatuck        NH
Mr. Carl Seaver         M         5480 Alpine Lane                  Selkirk          VA
Ms. Deborah Wolfe       F         2140 Edgewood Road                Five Lakes       CT
Mr. James Cowan
Ms. Rebecca Van         M         114 Lexington Parkway             Alto             NY
Singel                  M         56 Four Mile Road                 Grand Rapids     NY
Ms. Jennifer Lewis      F         3333 Bradford Farms               Copper Harbor    CA
Mr. Walter Reed         M         150 Hall Road                     Kearsarge        CO
Mr. Toby Stein          M         431 North Phillips Road           South Bend       IN
Ms. Barbara Feldon      F         230 South Phillips Road           South Bend       IN
Mr. Gilbert Scholten
    Donald              M         3915 Hawthorne Avenue             Toledo           TX
MacPherson              F         701 Bagley Street                 Grand Rapids     CT
Ms. Julie Pfeiffer      F         3300 West Russell Street          Maumee           TX
Ms. Curtis Haiar        F         10 Sycamore Street                Grand Rapids     WI
Ms. Jean Brooks         F         44 Tower Lane                     Mattawan         WA
Mr. Janosfi Petofi      M         11456 Marsh Road                  Shelbyville      CA
Ms. Nancy Mills         F         2890 Canyonside Way               Romulus          MI
Ms. Tara Jerentowski    F         109 East Monroe Avenue            Elkhart          IN
Ms. Pam Leonard         F         915 South Creek Drive             Grand Rapids     MI
Mr. Jeffrey Hersha      M         8200 Baldwin Boulevard            Burlington       CA
Mr. Clifford Merritt    M         4004 West 41st Street             Goshen           IN
Mr. Joseph Allen        M         101 South Plains Ave              Omaha            NE
Mrs. Wendy Williams     F         4545 Main Street                  Montpelier       VT
Dr. Mark Bickford       M         P.O. Box 100011                   Boston           MA
Ms. Anne Logan          F         101 South University Ave.         Denver           CO
Rev. Philip Johnson   M   555 Parker Street     Miami        FL
Ms. Mary Beth Mauch   F   P.O. Box 6677         New York     NY
Mr. Peter Burton      M   777 Orchard Ave.      Seattle      WA
Ms. Helen Gooch       F   P.O. Box 43000        Jackson      WY
Mrs. Suzanna Lianos   F   P.O. 1121             Meredith     TX
Mrs. Jane Everritt    F   160 Vittum Hill Rd.   Massapequa   NY
from a large collection of information.
ata is summarized.


                      Letter
            Zip       Sent        Status    Region   Amount
            43617      02/06/03   Parent    East     $  1,000
            49925      12/21/03   Parent    North    $  1,000
            48441      01/05/04   Fan       East     $  1,000
            48161      02/13/04   Parent    East     $  1,000
            43560      02/18/04   Alumna    East     $  1,000
            46544      02/24/04   Fan       East     $  1,000
            49945      03/18/04   Parent    East     $  1,000
            48617      03/27/04   Fan       North    $  1,000
            49301      04/01/04   Fan       East     $  1,000
            49063      04/04/04   Parent    West     $  1,000
            49428      04/11/04   Alumna    West     $  1,000
            49031      04/15/04   Alumna    South    $  2,000
            49464      04/17/04   Unknown   West     $  2,000
            49464      04/25/04   Fan       East     $  2,000
            49464      04/25/04   Alumna    East     $  2,000
            48174      04/26/04   Unknown   South    $  2,000
            48653      05/02/04   Parent    East     $  2,000
            46351      05/03/04   Alumna    East     $  2,000
            49453      05/03/04   Parent    East     $  2,000
            48661      05/09/04   Fan       East     $  2,000
            48446      05/09/04   Fan       East     $  2,000
            49302      05/17/04   Alumna    East     $  2,000
            49505      05/27/04   Alumna    East     $  2,000
            49918      06/08/04   Fan       West     $  2,500
            49942      06/16/04   Fan       West     $  2,500
            46611      07/01/04   Parent    East     $  2,500
            46623      07/03/04   Alumna    East     $  2,500
            43603      07/13/04   Alumna    East     $  2,500
            49571      08/09/04   Parent    East     $  2,500
            43537      08/28/04   Unknown   East     $  2,500
            49509      08/31/04   Fan       North    $  2,500
            49071      09/02/04   Unknown   West     $  2,500
            49344      10/08/04   Fan       West     $  2,500
            48174      10/27/04   Fan       North    $  2,500
            46515      10/31/04   Alumna    East     $  2,500
            49587      11/22/04   Parent    North    $  2,500
            49029      11/25/04   Alumna    West     $  2,500
            46526      12/12/04   Alumna    East     $  2,500
            68101      09/15/04   Parent    West     $  2,500
            05602      09/15/04   Alumna    East     $  2,500
            02134      09/24/04   Fan       East     $  5,000
            80210      09/26/04   Alumna    West     $  5,000
33010   09/26/04   Parent    South   $    5,000
10015   09/27/04   Alumna    East    $    5,000
98117   10/05/04   Parent    West    $    5,000
83001   10/06/04   Unknown   West    $   10,000
03253   10/10/04   Fan       East    $   10,000
11758   10/13/04   Fan       East    $   10,000

								
To top