5/11 FA 7.6 Building an Amortization Spreadsheet

Last updated about 3 years ago

32 Nsɛmmisa

Hyɛ no nsow a efi ɔkyerɛwfo no hɔ:

Akwankyerɛ

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 about 3 years ago

32 Nsɛmmisa

Hyɛ no nsow a efi ɔkyerɛwfo no hɔ:

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

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

Akwankyerɛ

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

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.

null

Ɛhia

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

Learn It 1

VIDEO: What is Amortization?

Watch this short video clip to understand the basic terminology and process of amortizing a loan.

Ɛhia

What two components make up your monthly loan payment?

Ɛhia

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

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.

Ɛhia

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.

Ɛhia

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

Ɛhia

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?

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.

Ɛhia

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

Ɛhia

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.

Ɛhia

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.

Ɛhia

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?

Ɛhia

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

APPLICATION: Compare Loan Payments by Building a Spreadsheet

Ɛhia

Sandra’s friend Kat is in her first year of college and has $1,000 in federal direct subsidized loans, $4,500 in federal direct unsubsidized loans at 3.73% APR. (Use for questions 29-32)

Ɛhia

5/11 FA 7.6 Building an Amortization Spreadsheet

5/11 FA 7.6 Building an Amortization Spreadsheet

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?

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.

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

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?

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.

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.