Log inSign up for FREE
Library

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
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
1
How much of Sharif’s third payment goes toward principal?_______ How much toward interest?_______
Required
1
What percentage of his first payment goes to paying off his principal? _______ What percentage of the third payment?_______
Required
1
What is the relationship between the Payment, Interest, and Principal in each row?
_______ =_______+_______
Required
1

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.
Required
1

What two components make up your monthly loan payment?

Required
1
What is paid FIRST from your monthly loan payment?_______
Required
4
Complete the next line of the table from the introduction for the fourth loan payment breakdown. Be sure when calculating interest you use the remaining balance after month three and the monthly interest rate, not yearly.

_______ Payment
_______ Interest
_______ Principal
_______ Remaining Balance
Required
1

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.
Required
45

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
1

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

Required
1

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.
Required
1
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
1

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

Required
2
By the time Sharif pays off his entire loan…
How much interest will he have paid?_______
How much will the car have cost him in total?_______
Required
4
Notice that the Remaining Balance becomes negative after payment 36. Calculate the correct values and complete the last line of the amortization table.

_______ Payment
_______ Interest
_______ Principal
_______ Remaining Balance
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
1

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
1

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
1

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
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
1

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

Required
0

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.

Required
1
How long will it take Zeke to pay off his cards if he doesn’t use it any more and pays just the minimum payments?_______
Required
1
By the time Zeke pays off his entire balance,
a. How much interest will he have paid?_______
b. What will his college purchases have cost him in total?_______
Required
1
Zeke wonders how long will it take to pay off the credit card balance if instead he increases his monthly payments to $150? In the second tab, make a copy of your amortization table using $150 as the payment amount. How long will it take to pay off if Zeke ups his monthly payments to $150?_______
Required
1
How much would he save on interest with the $150 payments?_______
Required
1

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?

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
30

Create your own amortization spreadsheet for Sandra where she can change the loan term to see how it affects her loan and the payment amount will automatically be calculated. You can make a copy of this template or add a new tab to your existing amortization spreadsheet from Level 1. Paste the link to the spreadsheet below. Be sure your sharing permissions allow your teacher to view it.

Required
1
How much will Sandra’s monthly payment be on the 10-year plan? _______ On the 25-year plan?_______
Required
1
By the time Sandra pays off her entire balance, how much interest will she have paid for each plan?
10-Year:$_______
25-Year:$_______
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)
Required
1
Which loans will begin accruing interest while Kat is in school?_______
Required
1

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?

Required
1

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.

Required
1

Kat wants to see if she can afford to pay the interest that accrues while in school so that her balance doesn’t increase by graduation. Using the compound interest formula, she calculates that each month, this would be $14 in interest per month and it would save her $145 over four years. What are some other reasons that Kat might want to consider starting to pay her interest right away?