Docstoc

Microsoft Excel Spreadsheet for Interest Calculation - PDF

Document Sample
Microsoft Excel Spreadsheet for Interest Calculation - PDF Powered By Docstoc
					                                                           Go To Lesson Activity Home Page
                                                            http://www.powertolearn.com/teachers/lesson_activities/index.shtml




Title: Interest Calculation and Spreadsheets

Grade Ranges:
___K-4
_X_5-8
_X_9-12

Subject Tag:
Math: Algebra
Family and Consumer Sciences
Computers: General

Synopsis:
Students will use an appropriate spreadsheet program to calculate interest and
accumulated value of a sample savings plan. The first computer-based session uses a set
principle and compounding period to calculate total value of an investment at the end of
that period. The second lesson expands the concepts with the addition of annual
contributions to the principle value for ensuing years of investment return. Upon
completion of the lesson, students should have a firm grasp on the concepts of interest
calculation with a set principle and an expanding principle over time as well as a general
understanding of how to use spreadsheets to calculate interest formulas.

This lesson is developed around the features of Microsoft Excel. The plan refers to the
use of a “spreadsheet program” that utilizes features of Excel. Other appropriate
spreadsheet programs may be used, but it is suggested that you do additional research
into the functions, limitations, and especially calculation formulas of any other
spreadsheet program.

Keywords:
interest, principle, rate, time, interest formulas, spreadsheets, Microsoft Excel, algebraic
interest formula

Body:
This lesson should take approximately three days. Time references in “days” refer to
classes structured on a traditional 45 to 50 minute time frame, although this lesson can
also be completed with a block schedule.

The first day should be split into two sessions:
       1. Introduce the formula for interest calculation, and give students the
           terminology to understand interest, principle, rate, and time in the standard
           interest formula I = PRT.
       2. In the computer lab, introduce the basic features and functionality of Excel.

Note: If you are unsure of how to use Excel, meet with your school’s technology
specialist to learn about the following features: Cell formatting (currency and basic




                                                                                                        1
number), calculation formulas, insertion/deletion of rows and columns in a spreadsheet,
filling formulas or values across rows or down columns.

The second and third days should be spent in the computer lab. You will need to provide
students with specific directives on how to create their spreadsheets. Assessment for this
lesson depends upon student-generated spreadsheets, so students must print out their
work to receive credit.

Day 1:

Start by explaining the formula for interest (I = PRT ). Provide several real-world
examples, and show how interest is calculated based on a starting principle investment,
an interest rate, and a time period. Have students complete a few examples on their own.

Next, introduce the concept that principle grows over time as interest is added to the
original principle. Thus, a new principle value is used for each time period of
investment. Ask students to make conclusions on the effect of changing interest rates,
and provide several examples of how variable interest rates can significantly impact
investment values over extended periods of time.

After spending roughly 20 minutes on these ideas, instruct the class to take the next five
minutes to reassemble in your school’s computer lab.

For the remaining 20 minutes of class, have all students open Excel and examine the
program’s basics. Start by showing students how to format cells for text, numbers,
currency, and percentages. Next, show students how to insert and delete rows and
columns. Also, show students how to use the edit fill down and edit fill right
features. Have them insert text, numbers, percentages, and money values in the cells and
play with some of the features you have shown them. You may choose to have your
students save their work or simply discard it; what they create this first day should not be
used for assessment.

Day 2:

Have students construct their first spreadsheet assignment. Here is how to set up the
spreadsheets:
    1. The first row should contain all of the information you would expect for any
       assignment that will be handed in: name, date, class period, etc.
    2. The second row should contain text headers for each column that will be used in
       the formulaic evaluation process: time, principle, rate, interest.
    3. Tell students to create their spreadsheets to calculate interest for 20 years and
       number the rows 1 to 20 in the cells in the time column.
    4. Have students format the cells in the principle and interest columns for currency.
    5. Have them format the cells in the rate column for percentage.




                                                                                             2
     6. Next, students should select the first cell in the interest column, and insert the
        appropriate formula to determine interest. If the first cell in the interest column is
        D4, for example, the formula for I = PRT would be “=A4*B4*C4.”
     7. With the formula in place, have students use the edit fill down feature to
        translate the formula across all cells in the column.
     8. Have students create three separate areas on their spreadsheets and experiment
        with three different principle and interest rate values. Once students have
        finished, they should print their work and hand in a copy to you.

Sample spreadsheet from Day 2:
 John Doe, 3rd Period Algebra I, Mr. Smith, 2/25/02

 Time        Principle     Rate        Interest
         1       $500.00     7.50%          $37.50
         2       $500.00     7.50%          $75.00
         3       $500.00     7.50%         $112.50
         4       $500.00     7.50%         $150.00
         5       $500.00     7.50%         $187.50
         6       $500.00     7.50%         $225.00
         7       $500.00     7.50%         $262.50
         8       $500.00     7.50%         $300.00
         9       $500.00     7.50%         $337.50
        10       $500.00     7.50%         $375.00
        11       $500.00     7.50%         $412.50
        12       $500.00     7.50%         $450.00
        13       $500.00     7.50%         $487.50
        14       $500.00     7.50%         $525.00
        15       $500.00     7.50%         $562.50
        16       $500.00     7.50%         $600.00
        17       $500.00     7.50%         $637.50
        18       $500.00     7.50%         $675.00
        19       $500.00     7.50%         $712.50
        20       $500.00     7.50%         $750.00
* This sample spreadsheet is also provided in the accompanying teacher download.

Day 3:

Have students construct their second spreadsheet assignment in a similar fashion to how
they did on day 2. But this time, have students add an extra column called “ending
value” or some other appropriate label. This represents the balance of their investment
after the accrued interest is added to what they initially invested.

The formula for the first cell of that column (now column E, cell E4) would be
“=B4+(B4*C4)” if you want students to examine the process in the “truest” mathematical
sense. Some students may need to use a simpler formula, and you may decide to use the
formula “=B4+D4” because it is probably easier for students to see in the realm of
spreadsheets. Explain why the first formula is accurate, even though B4+D4 may seem
more accurate to students based on the spreadsheet's layout.

Have students begin with a simple and easy principle value. Because students will
calculate interest only for a single year in each row of this spreadsheet, time will always
equal one, so the value for T can now be omitted from the equation. Make sure that
students know how and why the formula for interest changes in this spreadsheet.



                                                                                              3
Show how to copy the value from the ending value cell in the previous row to update the
new principle each year. At the end of the 20-year period, have students calculate the
total interest gained over the time period based on your suggested initial principle and
interest rate. Have students print their spreadsheets.

Sample spreadsheet 1 from Day 3:
John Doe, 3rd Period Algebra I, Mr. Smith, 2/26/02

Time       Principle      Rate       Interest         Ending value
      1       $1,200.00    7.50%           $90.00       $1,290.00
      2       $1,290.00    7.50%           $96.75       $1,386.75
      3       $1,386.75    7.50%          $104.01       $1,490.76
      4       $1,490.76    7.50%          $111.81       $1,602.56
      5       $1,602.56    7.50%          $120.19       $1,722.76
      6       $1,722.76    7.50%          $129.21       $1,851.96
      7       $1,851.96    7.50%          $138.90       $1,990.86
      8       $1,990.86    7.50%          $149.31       $2,140.17
      9       $2,140.17    7.50%          $160.51       $2,300.69
    10        $2,300.69    7.50%          $172.55       $2,473.24
    11        $2,473.24    7.50%          $185.49       $2,658.73
    12        $2,658.73    7.50%          $199.40       $2,858.14
    13        $2,858.14    7.50%          $214.36       $3,072.50
    14        $3,072.50    7.50%          $230.44       $3,302.93
    15        $3,302.93    7.50%          $247.72       $3,550.65
    16        $3,550.65    7.50%          $266.30       $3,816.95
    17        $3,816.95    7.50%          $286.27       $4,103.22
    18        $4,103.22    7.50%          $307.74       $4,410.96
    19        $4,410.96    7.50%          $330.82       $4,741.79
    20        $4,741.79    7.50%          $355.63       $5,097.42
Total                                   $3,897.42
* This sample spreadsheet is also provided in the accompanying teacher download.

Instruct students to copy all information from the cells (like those in the example above)
and paste it into another spreadsheet.

Have students insert a new column between the time and principle columns and title this
column “Annual Contribution.” Without providing any advice, have students determine
what they would consider to be a reasonable annual contribution to a savings or
investment plan, and enter this amount in all cells in this column. If you wish, encourage
interested students to calculate a formula that translates into increasing contributions as
the years ensue.

Now, tell students to use what they know about spreadsheets and what they know about
interest to add the annual contribution value to the principle value for each year. When
they are finished, have students print their spreadsheets.

Sample spreadsheet 2 from Day 3:
John Doe, 3rd Period Algebra I, Mr. Smith, 2/25/02

Time       Annual Contribution     Principal         Rate     Interest       Ending Value
       1             $1,200.00        $1,200.00      7.50%          $90.00          $1,290.00
       2             $1,260.00        $2,550.00      7.50%         $191.25          $2,741.25
       3             $1,323.00        $4,064.25      7.50%         $304.82          $4,369.07
       4             $1,389.15        $5,758.22      7.50%         $431.87          $6,190.09
       5             $1,458.61        $7,648.69      7.50%         $573.65          $8,222.34
       6             $1,531.54        $9,753.88      7.50%         $731.54        $10,485.42
       7             $1,608.11       $12,093.54      7.50%         $907.02        $13,000.55
       8             $1,688.52       $14,689.07      7.50%       $1,101.68        $15,790.75




                                                                                                4
       9        $1,772.95   $17,563.70   7.50%    $1,317.28   $18,880.98
     10         $1,861.59   $20,742.57   7.50%    $1,555.69   $22,298.27
     11         $1,954.67   $24,252.94   7.50%    $1,818.97   $26,071.91
     12         $2,052.41   $28,124.32   7.50%    $2,109.32   $30,233.64
     13         $2,155.03   $32,388.67   7.50%    $2,429.15   $34,817.82
     14         $2,262.78   $37,080.60   7.50%    $2,781.04   $39,861.64
     15         $2,375.92   $42,237.56   7.50%    $3,167.82   $45,405.38
     16         $2,494.71   $47,900.09   7.50%    $3,592.51   $51,492.60
     17         $2,619.45   $54,112.05   7.50%    $4,058.40   $58,170.45
     18         $2,750.42   $60,920.87   7.50%    $4,569.07   $65,489.94
     19         $2,887.94   $68,377.88   7.50%    $5,128.34   $73,506.22
     20         $3,032.34   $76,538.56   7.50%    $5,740.39   $82,278.96
Totals         $39,679.14                        $42,599.81
* This sample spreadsheet is also provided in the accompanying teacher download.

Optional final step: Have students analyze in short essay form changes in interest return
when they alter interest rates in their spreadsheets.

Related Links:

Microsoft Excel Tutorial
http://www.bcschools.net/staff/ExcelHelp.htm
This site offers a good online MS Excel tutorial from Bay City Public Schools in
Michigan.

Introduction to Microsoft Excel 97
http://www.unt.edu/training/Excel97/
This is another quality and easy-to-understand MS Excel 97 tutorial published by the
University of North Texas.

Computer Software: Information about Microsoft Excel
http://www.computerhope.com/excel.htm
Of most interest is the “Excel formulas” section of this site; also provides good
information about Excel 2000 and 2002. Ideas should be intuitively translatable to
Macintosh or PC and to older versions of Excel.

Ask Mr. Excel
http://www.mrexcel.com/
This site provides FAQ and message board pages for help with Excel.

Microsoft Excel Homepage
http://www.microsoft.com/office/excel/default.asp
This is the homepage for Microsoft Excel. This site lets you “see Excel 2002 in action”
and “test-drive Office XP and Excel 2002.”

Features:
___Contains special education tips
___Quick Activity (less than 30 minutes; story starter)
___Requires Internet access for students to complete




                                                                                            5
Objectives:
  1. Better understanding of basic mathematical formulas
  2. Better understanding of interest calculation
  3. Increased familiarity with the terms interest, accrual, compounding, principle
  4. Integrate computer skills into standard curriculum objectives

Standards:
NY: Mathematics 2: Students use number sense and numeration to develop an
understanding of the multiple uses of numbers in the real world, the use of numbers to
communicate mathematically, and the use of numbers in the development of
mathematical ideas. 3: Students use mathematical operations and relationships among
them to understand mathematics. 7: Students use patterns and functions to develop
mathematical power, appreciate the true beauty of mathematics, and construct
generalizations that describe patterns simply and efficiently.

NYC: M1e. Interprets percent as part of 100 and as a means of comparing quantities of
different sizes or changing sizes. M3a. Discovers, describes, and generalizes patterns,
including linear, exponential, and simple quadratic relationships, i.e., those of the form
f(n)=n2 or f(n)=cn2, for constant c, including A=sˇr2, and represents them with variables
and expressions. M3c. Analyzes tables, graphs, and rules to determine functional
relationships. M5a. Formulation: The student participates in the formulation of problems.
M5b. Implementation: The student makes the basic choices involved in planning and
carrying out a solution. M6b. Knows and uses the correct order of operations for
arithmetic computations. M6f. Uses equations, formulas, and simple algebraic notation
appropriately. M7a. Uses mathematical language and representations with appropriate
accuracy, including numerical tables and equations, simple algebraic equations and
formulas, charts, graphs, and diagrams.

CT: Learning Resource and Information Technology: 5. Application. Students will
use appropriate technologies to create written, visual, oral and multimedia products to
communicate ideas, information or conclusions to others. Mathematics: 1. Number
Sense. Students will use numbers to count, measure, compare, order, scale, locate and
label, and use a variety of numerical representations to present, interpret, communicate
and connect various kinds of numerical information. 2. Operations. Students will add,
subtract, multiply and divide with whole numbers, fractions, decimals and integers, and
develop strategies for selecting the appropriate computational and operational methods
for solving problems. 4. Ratios, Proportions and Percents. Students will use ratios,
proportions and percents to represent relationships between quantities and measures and
solve problems involving ratios, proportions and percents. 9. Algebra and Functions.
Students will use algebraic skills and concepts, including functions, to describe real-
world phenomena symbolically and graphically, and to model quantitative change.
Technology Education: 4. Problem Solving/Research and Development. Students will
recognize technology as the result of a creative act, and will be able to apply disciplined
problem-solving strategies to enhance invention and innovation.




                                                                                              6
NJ: Cross-Content Workplace Readiness: 1: All students will develop career planning
and workplace readiness skills. 2: All students will use technology, information and
other tools. 3: All students will use critical thinking, decision-making, and problem-
solving skills. Mathematics: 4.1: All Students Will Develop The Ability To Pose And
Solve Mathematical Problems In Mathematics, Other Disciplines, And Everyday
Experiences. 4.3: All Students Will Connect Mathematics To Other Learning By
Understanding The Interrelationships Of Mathematical Ideas And The Roles That
Mathematics And Mathematical Modeling Play In Other Disciplines And In Life. 4.5:
All Students Will Regularly And Routinely Use Calculators, Computers, Manipulatives,
And Other Mathematical Tools To Enhance Mathematical Thinking, Understanding, And
Power. 4.6: All Students Will Develop Number Sense And An Ability To Represent
Numbers In A Variety Of Forms And Use Numbers In Diverse Situations. 4.8: All
Students Will Understand, Select, And Apply Various Methods Of Performing Numerical
Operations. 4.13: All Students Will Develop An Understanding Of Algebraic Concepts
And Processes And Will Use Them To Represent And Analyze Relationships Among
Variable Quantities And To Solve Problems.

Prerequisite Skills:
   1. Students must have algebraic reasoning ability significant enough to provide them
      with a basic understanding of variable equations.
   2. Students should have some familiarity with the basics of how to operate a
      computer, print to a networked printer in your school’s computer lab (if
      applicable) and what is involved with a spreadsheet program.

Time Required:
Three 45-minute lessons

Technology and Materials Needed:
   1. Computers, preferred ratio of 1:1 computers to students, but 1:2 is possible

Assessment Criteria:
   1. Grading of spreadsheets — you should design your own rubric.
   2. Students can generate a short essay description of their findings of interest return
      gain or loss based on slight variations of interest rates and attach to spreadsheets.
   3. Follow-up quiz or test with questions related to using computer spreadsheets

Recommended Lesson Plan Review Date:

Review Comments:
Check Web sites.




                                                                                              7
Interest Calculation and Spreadsheets: Examples


Sample spreadsheet from Day 2:
John Doe, 3rd Period Algebra I, Mr. Smith, 2/25/02

Time        Principle      Rate       Interest
        1       $500.00      7.50%         $37.50
        2       $500.00      7.50%         $75.00
        3       $500.00      7.50%        $112.50
        4       $500.00      7.50%        $150.00
        5       $500.00      7.50%        $187.50
        6       $500.00      7.50%        $225.00
        7       $500.00      7.50%        $262.50
        8       $500.00      7.50%        $300.00
        9       $500.00      7.50%        $337.50
       10       $500.00      7.50%        $375.00
       11       $500.00      7.50%        $412.50
       12       $500.00      7.50%        $450.00
       13       $500.00      7.50%        $487.50
       14       $500.00      7.50%        $525.00
       15       $500.00      7.50%        $562.50
       16       $500.00      7.50%        $600.00
       17       $500.00      7.50%        $637.50
       18       $500.00      7.50%        $675.00
       19       $500.00      7.50%        $712.50
       20       $500.00      7.50%        $750.00




Sample spreadsheet 1 from Day 3:
John Doe, 3rd Period Algebra I, Mr. Smith, 2/26/02

Time        Principle      Rate      Interest        Ending value
      1        $1,200.00    7.50%          $90.00      $1,290.00
      2        $1,290.00    7.50%          $96.75      $1,386.75
      3        $1,386.75    7.50%         $104.01      $1,490.76
      4        $1,490.76    7.50%         $111.81      $1,602.56
      5        $1,602.56    7.50%         $120.19      $1,722.76
      6        $1,722.76    7.50%         $129.21      $1,851.96
      7        $1,851.96    7.50%         $138.90      $1,990.86
      8        $1,990.86    7.50%         $149.31      $2,140.17
      9        $2,140.17    7.50%         $160.51      $2,300.69
    10         $2,300.69    7.50%         $172.55      $2,473.24
    11         $2,473.24    7.50%         $185.49      $2,658.73
    12         $2,658.73    7.50%         $199.40      $2,858.14
    13         $2,858.14    7.50%         $214.36      $3,072.50
    14         $3,072.50    7.50%         $230.44      $3,302.93
    15         $3,302.93    7.50%         $247.72      $3,550.65
    16         $3,550.65    7.50%         $266.30      $3,816.95
    17         $3,816.95    7.50%         $286.27      $4,103.22
    18         $4,103.22    7.50%         $307.74      $4,410.96
    19         $4,410.96    7.50%         $330.82      $4,741.79
    20         $4,741.79    7.50%         $355.63      $5,097.42
Total                                   $3,897.42
Sample spreadsheet 2 from Day 3:
John Doe, 3rd Period Algebra I, Mr. Smith, 2/25/02

Time       Annual Contribution     Principal         Rate    Interest       Ending Value
       1             $1,200.00        $1,200.00      7.50%         $90.00          $1,290.00
       2             $1,260.00        $2,550.00      7.50%        $191.25          $2,741.25
       3             $1,323.00        $4,064.25      7.50%        $304.82          $4,369.07
       4             $1,389.15        $5,758.22      7.50%        $431.87          $6,190.09
       5             $1,458.61        $7,648.69      7.50%        $573.65          $8,222.34
       6             $1,531.54        $9,753.88      7.50%        $731.54        $10,485.42
       7             $1,608.11       $12,093.54      7.50%        $907.02        $13,000.55
       8             $1,688.52       $14,689.07      7.50%      $1,101.68        $15,790.75
       9             $1,772.95       $17,563.70      7.50%      $1,317.28        $18,880.98
     10              $1,861.59       $20,742.57      7.50%      $1,555.69        $22,298.27
     11              $1,954.67       $24,252.94      7.50%      $1,818.97        $26,071.91
     12              $2,052.41       $28,124.32      7.50%      $2,109.32        $30,233.64
     13              $2,155.03       $32,388.67      7.50%      $2,429.15        $34,817.82
     14              $2,262.78       $37,080.60      7.50%      $2,781.04        $39,861.64
     15              $2,375.92       $42,237.56      7.50%      $3,167.82        $45,405.38
     16              $2,494.71       $47,900.09      7.50%      $3,592.51        $51,492.60
     17              $2,619.45       $54,112.05      7.50%      $4,058.40        $58,170.45
     18              $2,750.42       $60,920.87      7.50%      $4,569.07        $65,489.94
     19              $2,887.94       $68,377.88      7.50%      $5,128.34        $73,506.22
     20              $3,032.34       $76,538.56      7.50%      $5,740.39        $82,278.96
Totals              $39,679.14                                 $42,599.81

				
DOCUMENT INFO
Description: Microsoft Excel Spreadsheet for Interest Calculation document sample