5/11 FA 7.6 Building an Amortization Spreadsheet

Last updated over 2 years ago

32 questions

Note from the author:

OBJECTIVES & STANDARDS
Math Objectives
Create an amortization table using recursive formulas
Implement absolute references within recursive formulas
Perform error analysis on amortization calculations
Common Core Math Standards
Link to all CCSS Math
CCSS.HSF.IF.A.3
CCSS.HSF.IF.B.4
CCSS.HSF.IF.C.7
CCSS.HSF.BF.A.1.A
CCSS.HSF.BF.A.1.B
Personal Finance Objectives
Understand the impact of modifying monthly payments on a loan
Use amortization tables to compare different loan products with different features
Understand the impact of longer loan terms on total interest accrued on a loan
Jump$tart Standards
Spending
1d: Evaluate the advantages of using budgeting tools, such as spreadsheets or apps
3b: Analyze the cost and features of three competing products or services
Managing Credit
1b: Compare the cost of borrowing $1,000 using consumer credit options that differ in rates and fees
3c: Compare monthly mortgage payments for loans that differ in repayment period, amount borrowed, and interest rate
5c: Estimate total interest on various student loans based on interest rates and repayment plans

Instructions

OBJECTIVES & STANDARDS
Math Objectives
Create an amortization table using recursive formulas
Implement absolute references within recursive formulas
Perform error analysis on amortization calculations
Common Core Math Standards
Link to all CCSS Math
CCSS.HSF.IF.A.3
CCSS.HSF.IF.B.4
CCSS.HSF.IF.C.7
CCSS.HSF.BF.A.1.A
CCSS.HSF.BF.A.1.B
Personal Finance Objectives
Understand the impact of modifying monthly payments on a loan
Use amortization tables to compare different loan products with different features
Understand the impact of longer loan terms on total interest accrued on a loan
Jump$tart Standards
Spending
1d: Evaluate the advantages of using budgeting tools, such as spreadsheets or apps
3b: Analyze the cost and features of three competing products or services
Managing Credit
1b: Compare the cost of borrowing $1,000 using consumer credit options that differ in rates and fees
3c: Compare monthly mortgage payments for loans that differ in repayment period, amount borrowed, and interest rate
5c: Estimate total interest on various student loans based on interest rates and repayment plans

Untitled Section

Learn It 1

Practice It

Learn It 2

APPLICATION: Compare Loan Payments by Building a Spreadsheet

5/11 FA 7.6 Building an Amortization Spreadsheet

Last updated over 2 years ago

32 questions

Note from the author:

OBJECTIVES & STANDARDS
Math Objectives
Create an amortization table using recursive formulas
Implement absolute references within recursive formulas
Perform error analysis on amortization calculations
Common Core Math Standards
Link to all CCSS Math
CCSS.HSF.IF.A.3
CCSS.HSF.IF.B.4
CCSS.HSF.IF.C.7
CCSS.HSF.BF.A.1.A
CCSS.HSF.BF.A.1.B
Personal Finance Objectives
Understand the impact of modifying monthly payments on a loan
Use amortization tables to compare different loan products with different features
Understand the impact of longer loan terms on total interest accrued on a loan
Jump$tart Standards
Spending
1d: Evaluate the advantages of using budgeting tools, such as spreadsheets or apps
3b: Analyze the cost and features of three competing products or services
Managing Credit
1b: Compare the cost of borrowing $1,000 using consumer credit options that differ in rates and fees
3c: Compare monthly mortgage payments for loans that differ in repayment period, amount borrowed, and interest rate
5c: Estimate total interest on various student loans based on interest rates and repayment plans

Instructions

OBJECTIVES & STANDARDS
Math Objectives
Create an amortization table using recursive formulas
Implement absolute references within recursive formulas
Perform error analysis on amortization calculations
Common Core Math Standards
Link to all CCSS Math
CCSS.HSF.IF.A.3
CCSS.HSF.IF.B.4
CCSS.HSF.IF.C.7
CCSS.HSF.BF.A.1.A
CCSS.HSF.BF.A.1.B
Personal Finance Objectives
Understand the impact of modifying monthly payments on a loan
Use amortization tables to compare different loan products with different features
Understand the impact of longer loan terms on total interest accrued on a loan
Jump$tart Standards
Spending
1d: Evaluate the advantages of using budgeting tools, such as spreadsheets or apps
3b: Analyze the cost and features of three competing products or services
Managing Credit
1b: Compare the cost of borrowing $1,000 using consumer credit options that differ in rates and fees
3c: Compare monthly mortgage payments for loans that differ in repayment period, amount borrowed, and interest rate
5c: Estimate total interest on various student loans based on interest rates and repayment plans

Untitled Section

CALCULATE: Amortizing by Hand
Sharif aced his interview and just landed his first full-time job. Unfortunately, his workplace is a 30-minute drive outside of the town, and the buses don’t run there, so he needs to buy a car. He’s found a used car that will meet his needs for $13,500. He has $1,000 for a down payment, and will pay the rest with an auto loan. With a 3.5% APR and 3-year term, he calculates his monthly loan payment to be $366.28. Answer the following questions about Sharif’s car purchase.

Required

How much of Sharif’s third payment goes toward principal?_______  How much toward interest?_______ 

Required

Learn It 1

VIDEO: What is Amortization?
Watch this short video clip to understand the basic terminology and process of amortizing a loan.

Required

What two components make up your monthly loan payment?

Required

What is paid FIRST from your monthly loan payment?_______ 

Required

VIDEO: Learn to Create an Amortization Spreadsheet
A helpful tool when creating an amortization table is a spreadsheet. This will allow you to complete repetitive calculations more efficiently and save you having to write each value individually. Watch the video on creating a custom amortization table in Google Sheets.  Follow along and pause the video as needed.

Required

Make a copy of this template to create your own version of this amortization spreadsheet and paste a link to the spreadsheet below. Be sure your sharing permissions allow your teacher to view it.
*You will use this same template and add tabs at the bottom,
45 points total:
10 Learn it 1,
15 Practice It,
20 Learn it 2.

Required

What does the “$” symbol do when it is on a cell reference in a formula (e.g. $J$4)

Required

Practice It

ACTIVITY: Using an Amortization Table
An amortization table is useful in that it shows you the whole life of the loan and allows you to look at different pieces of information. First, expand your table for Sharif’s loan, and then answer the questions.

Required

Extend your table for the full 3 year life of the loan. After making 18 payments, half of the loan term, how much remaining principal balance does he have left?_______ 

Required

Explain why this does not necessarily line up with half the initial balance.

Required

Learn It 2

VIDEO:  Learn How to Customize Your Amortization Spreadsheet
There are a few other adjustments you can make to customize your spreadsheet and add some analysis. Watch the video on customizing your table in Google Sheets.  Follow along and pause the video as needed.

Required

Update your own version of your amortization table to include the cumulative interest, graph, and clear label features.
Be sure your sharing permissions allow your teacher to view it.

Required

Suppose Sharif receives a tax refund and contributes an additional $500 on his 10th payment. What will be the general impact on Sharif’s amortization values by making this single larger payment?

Required

APPLICATION: Compare Loan Payments by Building a Spreadsheet

Level 1:
Paying Off a Credit Card
Zeke racked up $4000 in credit card debt before graduating college. Now, he has his first job, and after budgeting for his other needs, he wants to pay down his credit card debt. His card has a 16.44% APR and a minimum monthly payment of $75. He’s trying to decide if he should pay $75 monthly or some other amount. Use an amortization table to help him answer his questions.
(Note: Technically as the balance decreases,  your minimum monthly payment would also decrease)

Required

Level 2:
Budgeting for Student Loans
Sandra has a $31,000 balance of Federal Direct student loan debt she took out while obtaining her 4-year degree at an interest rate of 3.73%. She has an offer for a job making $40,000, and is now trying to decide what repayment plan to choose for her student loans. The two repayment plans she is considering are the standard plan with a term of 10 years and the extended plan with a payoff term of 25 years.

Required

5/11 FA 7.6 Building an Amortization Spreadsheet

5/11 FA 7.6 Building an Amortization Spreadsheet

What two components make up your monthly loan payment?

What does the “$” symbol do when it is on a cell reference in a formula (e.g. $J$4)

Explain why this does not necessarily line up with half the initial balance.

Update your own version of your amortization table to include the cumulative interest, graph, and clear label features.
Be sure your sharing permissions allow your teacher to view it.

Suppose Sharif receives a tax refund and contributes an additional $500 on his 10th payment. What will be the general impact on Sharif’s amortization values by making this single larger payment?

Would it be easy to tell how much Sharif still owes on his loan after 30 months? Why or why not?

What two components make up your monthly loan payment?

Why do you think it is helpful to have an amortization table for your loan?

What does the “$” symbol do when it is on a cell reference in a formula (e.g. $J$4)

A recursive sequence is a sequence in which terms are defined using one or more previous terms. What value in this amortization calculation is a basis for a recursive sequence?

Explain why this does not necessarily line up with half the initial balance.

Update your own version of your amortization table to include the cumulative interest, graph, and clear label features.
Be sure your sharing permissions allow your teacher to view it.

Suppose Sharif receives a tax refund and contributes an additional $500 on his 10th payment. What will be the general impact on Sharif’s amortization values by making this single larger payment?

How can the graph help you to understand Sharif’s loan balance and interest in a better way than the table of values?

What amounts will be fixed in this scenario? Which amounts may vary?

Make a copy of this template, create your own amortization spreadsheet for Zeke that doesn’t have a specific term and where he will vary the payment amounts. Re-paste the link to the spreadsheet below. Be sure your sharing permissions allow your teacher to view it.

Adjust the payment amount and see how it changes the time to pay off the loan. Approximately how large would his payments have to be to pay off the card in exactly 2 years?

Using the compound interest formula provided and the fact that student loan interest compounds daily, how much would she owe on her loans when she graduates after 4 years of college including any interest?

Plug this value in your spreadsheet to find out what her monthly payment would need to be under the standard repayment plan for just these loans if she took out no more.

Would it be easy to tell how much Sharif still owes on his loan after 30 months? Why or why not?

Why do you think it is helpful to have an amortization table for your loan?

A recursive sequence is a sequence in which terms are defined using one or more previous terms. What value in this amortization calculation is a basis for a recursive sequence?

How can the graph help you to understand Sharif’s loan balance and interest in a better way than the table of values?

What amounts will be fixed in this scenario? Which amounts may vary?

Make a copy of this template, create your own amortization spreadsheet for Zeke that doesn’t have a specific term and where he will vary the payment amounts. Re-paste the link to the spreadsheet below. Be sure your sharing permissions allow your teacher to view it.

Adjust the payment amount and see how it changes the time to pay off the loan. Approximately how large would his payments have to be to pay off the card in exactly 2 years?

Using the compound interest formula provided and the fact that student loan interest compounds daily, how much would she owe on her loans when she graduates after 4 years of college including any interest?

Plug this value in your spreadsheet to find out what her monthly payment would need to be under the standard repayment plan for just these loans if she took out no more.

5/11 FA 7.6 Building an Amortization Spreadsheet

5/11 FA 7.6 Building an Amortization Spreadsheet

What two components make up your monthly loan payment?

What does the “$” symbol do when it is on a cell reference in a formula (e.g. $J$4)

Explain why this does not necessarily line up with half the initial balance.

Update your own version of your amortization table to include the cumulative interest, graph, and clear label features. Be sure your sharing permissions allow your teacher to view it.

Suppose Sharif receives a tax refund and contributes an additional $500 on his 10th payment. What will be the general impact on Sharif’s amortization values by making this single larger payment?

Would it be easy to tell how much Sharif still owes on his loan after 30 months? Why or why not?

What two components make up your monthly loan payment?

Why do you think it is helpful to have an amortization table for your loan?

What does the “$” symbol do when it is on a cell reference in a formula (e.g. $J$4)

A recursive sequence is a sequence in which terms are defined using one or more previous terms. What value in this amortization calculation is a basis for a recursive sequence?

Explain why this does not necessarily line up with half the initial balance.

Update your own version of your amortization table to include the cumulative interest, graph, and clear label features. Be sure your sharing permissions allow your teacher to view it.

Suppose Sharif receives a tax refund and contributes an additional $500 on his 10th payment. What will be the general impact on Sharif’s amortization values by making this single larger payment?

How can the graph help you to understand Sharif’s loan balance and interest in a better way than the table of values?

What amounts will be fixed in this scenario? Which amounts may vary?

Make a copy of this template, create your own amortization spreadsheet for Zeke that doesn’t have a specific term and where he will vary the payment amounts. Re-paste the link to the spreadsheet below. Be sure your sharing permissions allow your teacher to view it.

Adjust the payment amount and see how it changes the time to pay off the loan. Approximately how large would his payments have to be to pay off the card in exactly 2 years?

Using the compound interest formula provided and the fact that student loan interest compounds daily, how much would she owe on her loans when she graduates after 4 years of college including any interest?

Plug this value in your spreadsheet to find out what her monthly payment would need to be under the standard repayment plan for just these loans if she took out no more.

Would it be easy to tell how much Sharif still owes on his loan after 30 months? Why or why not?

Why do you think it is helpful to have an amortization table for your loan?

A recursive sequence is a sequence in which terms are defined using one or more previous terms. What value in this amortization calculation is a basis for a recursive sequence?

How can the graph help you to understand Sharif’s loan balance and interest in a better way than the table of values?

What amounts will be fixed in this scenario? Which amounts may vary?

Make a copy of this template, create your own amortization spreadsheet for Zeke that doesn’t have a specific term and where he will vary the payment amounts. Re-paste the link to the spreadsheet below. Be sure your sharing permissions allow your teacher to view it.

Adjust the payment amount and see how it changes the time to pay off the loan. Approximately how large would his payments have to be to pay off the card in exactly 2 years?

Using the compound interest formula provided and the fact that student loan interest compounds daily, how much would she owe on her loans when she graduates after 4 years of college including any interest?

Plug this value in your spreadsheet to find out what her monthly payment would need to be under the standard repayment plan for just these loans if she took out no more.

Update your own version of your amortization table to include the cumulative interest, graph, and clear label features.
Be sure your sharing permissions allow your teacher to view it.

Update your own version of your amortization table to include the cumulative interest, graph, and clear label features.
Be sure your sharing permissions allow your teacher to view it.