Document Sample

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

Shared By:

Categories:

Tags:
Microsoft Excel, Excel Spreadsheet, Microsoft Excel Spreadsheet, how to, Microsoft Office, Microsoft Excel spreadsheets, Microsoft Access, spreadsheet software, MS Excel, Pivot Table

Stats:

views: | 81 |

posted: | 7/21/2010 |

language: | English |

pages: | 9 |

Description:
Microsoft Excel Spreadsheet for Interest Calculation document sample

OTHER DOCS BY ktr11791

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.