{"id":4208,"date":"2020-04-08T02:01:04","date_gmt":"2020-04-08T02:01:04","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/mathforlibscoreq\/?post_type=chapter&#038;p=4208"},"modified":"2021-02-05T23:59:40","modified_gmt":"2021-02-05T23:59:40","slug":"responsible-borrowing","status":"web-only","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/chapter\/responsible-borrowing\/","title":{"raw":"Loan Calculator: Borrowing Responsibly","rendered":"Loan Calculator: Borrowing Responsibly"},"content":{"raw":"<div class=\"textbox learning-objectives\">\r\n<h3>Learning Outcomes<\/h3>\r\n<ul>\r\n \t<li>Calculate loan payments, interests, and balance<\/li>\r\n \t<li>Analyze and compare loans in real-world applications<\/li>\r\n<\/ul>\r\n<\/div>\r\nSaving and borrowing go hand-in-hand. You save for your future, build a safety net against unexpected expenses, and borrow to supply yourself now with the things you wouldn\u2019t otherwise be able to purchase outright, such as a car or a house. Responsible borrowing helps you to build a stable credit history. It signals to lenders that you\u2019ll be a safe candidate for a loan. They\u2019ll be more likely to offer you favorable terms on their money if they feel secure that they\u2019ll receive it back without a hassle. That\u2019s why it is important to create a strong credit presence if you lack one or repair a credit history that has taken a few dings. Responsible borrowing will help you do that.\r\n\r\nAlways do your research or speak with a financial expert when it comes to big decisions with your money. But there are a few steps you can take right away. Learn about your credit score. Limit the amount of debt that you can\u2019t pay back right away, such as credit card debt and student loans. Always make your payments on time. Know how to handle high rate products like retail credit cards, and steer clear of credit that is easy to obtain but hard to pay back like so-called payday loans. Finally, understand the math behind loan products. It will help empower you to make wise decisions and keep you from falling prey to predatory lenders.\r\n\r\nAs you saw earlier in the text, the loan formula is the same as the payout annuity formula.\r\n<div class=\"textbox\">\r\n<h2>Loans Formula<\/h2>\r\n[latex]P_{0}=\\frac{d\\left(1-\\left(1+\\frac{r}{k}\\right)^{-Nk}\\right)}{\\left(\\frac{r}{k}\\right)}[\/latex]\r\n<ul>\r\n \t<li><em>P<sub>0<\/sub><\/em> is the balance in the account at the beginning (the principal, or amount of the loan).<\/li>\r\n \t<li><em>d <\/em> is your loan payment (your monthly payment, annual payment, etc)<\/li>\r\n \t<li><em>r<\/em> is the annual interest rate in decimal form.<\/li>\r\n \t<li><em>k<\/em> is the number of compounding periods in one year.<\/li>\r\n \t<li><em>N<\/em> is the length of the loan, in years.<\/li>\r\n<\/ul>\r\n<\/div>\r\n<div class=\"textbox exercises\">\r\n<h3>Example<\/h3>\r\nYou take out a 30-year, fixed-rate mortgage for $225,000 at 5% interest. What will your monthly payment be? (Note this payment won't take into account fees, insurance, or taxes)\r\n\r\n[reveal-answer q=\"370409\"]Solution[\/reveal-answer]\r\n\r\n[hidden-answer a=\"370409\"]\r\n\r\nUse the loans formula to find the payment by solving it for [latex]d[\/latex]\r\n\r\n[latex]225000=\\frac{d\\left(1-\\left(1+\\frac{.05}{12}\\right)^{-30*12}\\right)}{\\left(\\frac{.05}{12}\\right)}[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Substitute the values you have for [latex]P_{0}, r, k, \\text{ and } N[\/latex]\r\n\r\n[latex]225000\\ast \\frac{.05}{12}=d\\left(1-\\left(1+\\frac{.05}{12}\\right)^{-30*12}\\right)[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Multiply the denominator on both sides.\r\n\r\n[latex]937.5=d(0.77617)[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Simplify both sides\r\n\r\n[latex]d=\\dfrac{937.5}{.77617}=1207.85[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Solve for d. The monthly loan payment will be $1,207.85.\r\n\r\n[\/hidden-answer]\r\n\r\n<\/div>\r\n<h3>Spreadsheet\u00a0Hands-On: Create a Loans Calculator<\/h3>\r\n<h3>Step 1: Create the loans calculator<\/h3>\r\nSince the loans formula is identical to the one for payout annuities, we can use our withdrawal calculator from the annuities worksheet. We need to make some changes though, so we'll make a copy of the annuities worksheet and rename it Loans.\r\n<ol>\r\n \t<li>Right-click on the Annuities tab, move or copy, click the box to copy, then place it at the end of the list of sheets.<\/li>\r\n \t<li>Delete lines 1-21. We won't need the Annuity Calculator.<\/li>\r\n \t<li>Rename line 1 as Loans Calculator.<\/li>\r\n \t<li>Relabel cells A3 and A12 as Payment<\/li>\r\n \t<li>Relabel the description of the interest field in cell C10 as \"total interest paid.\"<\/li>\r\n \t<li>Empty all the non-formula cells. When you are finished, your sheet should look like the image below.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153028\/Loans_01.jpg\"><img class=\"aligncenter size-full wp-image-4336\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153028\/Loans_01.jpg\" alt=\"\" width=\"645\" height=\"404\" \/><\/a><\/li>\r\n<\/ol>\r\nWe will use this sheet a little differently than we did the withdrawal annuity formula. Since we are concerned with paying money back, we'll start at the bottom of the sheet and work up. First, let's test our formulas using the numbers in the example above.\r\n<ol>\r\n \t<li>To find the monthly payment, enter the rate, years, balance, and periods in cells B13, - B16. The payment should automatically populate in cell B18 as in the image below.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153541\/Loans_02.jpg\"><img class=\"aligncenter size-full wp-image-4337\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153541\/Loans_02.jpg\" alt=\"\" width=\"651\" height=\"394\" \/><\/a><\/li>\r\n \t<li>Now, let's test the formulas in the top by entering the information again into cells B3 - B6. This time the balance of approximately 225,000 and the interest should automatically populate cells B8 and B10 as you can see below.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153825\/Loans_03.jpg\"><img class=\"aligncenter size-full wp-image-4338\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153825\/Loans_03.jpg\" alt=\"\" width=\"648\" height=\"397\" \/><\/a><\/li>\r\n \t<li>You can see that a mortgage is expensive! It costs nearly as much in interest as the amount you borrowed. (At this point, you can also test your formula in cell B19 by typing the payment amount in B12 and seeing that 30 years are returned in B19.)<\/li>\r\n<\/ol>\r\n<div class=\"textbox exercises\">\r\n<h3>Example<\/h3>\r\nUse your loan calculator to see how much faster you can pay off your mortgage by making larger than the required monthly minimum payment.\r\n\r\n[reveal-answer q=\"584200\"]Solution[\/reveal-answer]\r\n[hidden-answer a=\"584200\"]\r\n\r\nIncrease the payment amount in cell B12 to see how it affects the number of years left in cell B19.\r\n\r\nFor example, if you increase the monthly payment to $1500, you can pay off your mortgage 10 years early, and paying $1800 a month cuts the loan time in half!\r\n\r\n[\/hidden-answer]\r\n\r\n<\/div>\r\n&nbsp;\r\n<h2>Amortization<\/h2>\r\nAuto loans and mortgages use a process called\u00a0<em>amortization<\/em> to decrease the loan balance while you pay the interest. Recall that the interest rate, such as the 5% rate in our example above, is an annual rate. Divide it by 12 monthly payments to get the portion of the rate payable per month.\r\n\r\n[latex]0.5\/12\\approx 0.004167[\/latex].\r\n\r\nEach month, the monthly rate is multiplied by the amount of principal remaining to calculate the amount of that month's payment that goes to the interest. The remainder is applied to the principal. This continues each month until the loan is paid off. Banks will provide the details of this process in a table called an <em>amortization schedule.\u00a0<\/em>Let's see how this plays out over time by adding an\u00a0amortization schedule to our Loans sheet.\r\n<h3>Step 2: Create an amortization schedule<\/h3>\r\n<ol>\r\n \t<li>Begin by labeling row 22 \"Amortization Schedule\" then label B23 - E23 as Payment, Principal, Interest, and Balance respectively.<\/li>\r\n \t<li>In Cell A24, type 0. In cell A25, type 1. These will represent the payment number. We will start with the zeroth payment, to indicate the starting loan amount.<\/li>\r\n<\/ol>\r\nWe will store the information for our amortization schedule in the first part of the loans calculator, where we use the inputs to the loans formula to compute the loan amount. We'll reference these cells in the schedule we are creating to auto populate them based on the terms of the loan and monthly payment. It's okay that the balance has a little spare change on the end. It won't affect the general trend that we are looking for.\r\n\r\nWe need to handle an important detail as we reference the stored information when building our formulas. Spreadsheets will allow many rows in a column to be filled with a formula that computes subsequent rows based on information in previous ones by automatically changing the reference-cell location as it moves from row to row. We will do this to fill our schedule with these formulas in a moment. To prevent the spreadsheet from changing numbers we don't want it to change, we'll make some formula reference points static. We'll do this by including a $ between the column and row coordinates that point to the monthly payment, rate, and periods.\r\n<ol>\r\n \t<li><span style=\"font-size: 1em\">In cell E24, type = B$8.<\/span><\/li>\r\n \t<li>In cell B25, type = B$3.<\/li>\r\n \t<li>In cell D25, we'll use a formula to calculate the interest portion. Type =E24*(B$4\/B$5).<\/li>\r\n \t<li>In cell C25, we'll take the difference between the amount paid in B25 and the interest portion in D25 to get the amount applied to the principle. Type\u00a0=B25-D25.<\/li>\r\n \t<li>Now, click and drag to highlight cells A25 - E25 and release the mouse. Grab the little square in the lower right hand corner and drag it straight down. You can take the information down as far as you wish. If you drag it all the way down to row 384 on the sheet you can see the loan balance reduce to zero! The image below shows one year of payments. Note how the interest paid per month decreases as the portion paid to the principal increases.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12163226\/Loans_04.jpg\"><img class=\"aligncenter size-full wp-image-4341\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12163226\/Loans_04.jpg\" alt=\"\" width=\"648\" height=\"708\" \/><\/a><\/li>\r\n<\/ol>\r\nNow you are ready to use your loan calculator to answer some questions.\r\n<h3>Step 3: Use the loan calculator<\/h3>\r\n<div class=\"textbox exercises\">\r\n<h3>Example<\/h3>\r\nYou have a credit card with a $5,000 balance at 18% interest and you'd like to pay it off in 3 years. How much will your monthly payment need to be?\r\n\r\n[reveal-answer q=\"563403\"]Solution[\/reveal-answer]\r\n[hidden-answer a=\"563403\"]\r\n\r\n&nbsp;\r\n\r\n[\/hidden-answer]\r\n\r\n<\/div>\r\n<div class=\"textbox key-takeaways\">\r\n<h3>Try it<\/h3>\r\n[ohm_question]6685[\/ohm_question]\r\n\r\n<\/div>\r\n<div class=\"textbox exercises\">\r\n<h3>Example<\/h3>\r\nYou've found a house you want to purchase for $255,000. You can pay 5% down and take a 30 year mortgage for the rest. The average rate on for a 30-year fixed rate mortgage in April of 2020 according to The Federal Home Loan Mortgage Corporation, FreddieMac,[footnote]FreddieMac Mortgage Rates U.S. weekly averages as of 04\/09\/2020. Accessed 04-15-2020 at http:\/\/www.freddiemac.com\/pmms\/[\/footnote] is 3.33%, but rates can be as high as 7% or more, depending on the borrower's credit history.\r\n\r\n(a) How much will your down-payment be?\r\n\r\n(b) How much money will you need to borrow?\r\n\r\n(b) What will your monthly payment be at 7% interest?\r\n\r\n(c) What will your monthly payment be at 3.33% interest?\r\n\r\n[reveal-answer q=\"245116\"]Solution[\/reveal-answer]\r\n[hidden-answer a=\"245116\"]\r\n\r\n(a) $12,750 ([latex](0.05)(255000)=12,750[\/latex])\r\n\r\n(b) $242,250. ([latex]255000 - .05(255000) = 242250[\/latex])\r\n\r\n(c) $1,611.70 (enter the values for Rate=0.033, Years=30, Balance=242250, and Periods=12 in your spreadsheet in cells B13 - B16)\r\n\r\n(d) $1,060.95 (change Rate to 0.033)\r\n\r\n[\/hidden-answer]\r\n\r\n<\/div>\r\n<div class=\"textbox key-takeaways\">\r\n<h3>try it<\/h3>\r\n[ohm_question]6686[\/ohm_question]\r\n\r\n<\/div>\r\n&nbsp;","rendered":"<div class=\"textbox learning-objectives\">\n<h3>Learning Outcomes<\/h3>\n<ul>\n<li>Calculate loan payments, interests, and balance<\/li>\n<li>Analyze and compare loans in real-world applications<\/li>\n<\/ul>\n<\/div>\n<p>Saving and borrowing go hand-in-hand. You save for your future, build a safety net against unexpected expenses, and borrow to supply yourself now with the things you wouldn\u2019t otherwise be able to purchase outright, such as a car or a house. Responsible borrowing helps you to build a stable credit history. It signals to lenders that you\u2019ll be a safe candidate for a loan. They\u2019ll be more likely to offer you favorable terms on their money if they feel secure that they\u2019ll receive it back without a hassle. That\u2019s why it is important to create a strong credit presence if you lack one or repair a credit history that has taken a few dings. Responsible borrowing will help you do that.<\/p>\n<p>Always do your research or speak with a financial expert when it comes to big decisions with your money. But there are a few steps you can take right away. Learn about your credit score. Limit the amount of debt that you can\u2019t pay back right away, such as credit card debt and student loans. Always make your payments on time. Know how to handle high rate products like retail credit cards, and steer clear of credit that is easy to obtain but hard to pay back like so-called payday loans. Finally, understand the math behind loan products. It will help empower you to make wise decisions and keep you from falling prey to predatory lenders.<\/p>\n<p>As you saw earlier in the text, the loan formula is the same as the payout annuity formula.<\/p>\n<div class=\"textbox\">\n<h2>Loans Formula<\/h2>\n<p>[latex]P_{0}=\\frac{d\\left(1-\\left(1+\\frac{r}{k}\\right)^{-Nk}\\right)}{\\left(\\frac{r}{k}\\right)}[\/latex]<\/p>\n<ul>\n<li><em>P<sub>0<\/sub><\/em> is the balance in the account at the beginning (the principal, or amount of the loan).<\/li>\n<li><em>d <\/em> is your loan payment (your monthly payment, annual payment, etc)<\/li>\n<li><em>r<\/em> is the annual interest rate in decimal form.<\/li>\n<li><em>k<\/em> is the number of compounding periods in one year.<\/li>\n<li><em>N<\/em> is the length of the loan, in years.<\/li>\n<\/ul>\n<\/div>\n<div class=\"textbox exercises\">\n<h3>Example<\/h3>\n<p>You take out a 30-year, fixed-rate mortgage for $225,000 at 5% interest. What will your monthly payment be? (Note this payment won&#8217;t take into account fees, insurance, or taxes)<\/p>\n<div class=\"qa-wrapper\" style=\"display: block\"><span class=\"show-answer collapsed\" style=\"cursor: pointer\" data-target=\"q370409\">Solution<\/span><\/p>\n<div id=\"q370409\" class=\"hidden-answer\" style=\"display: none\">\n<p>Use the loans formula to find the payment by solving it for [latex]d[\/latex]<\/p>\n<p>[latex]225000=\\frac{d\\left(1-\\left(1+\\frac{.05}{12}\\right)^{-30*12}\\right)}{\\left(\\frac{.05}{12}\\right)}[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Substitute the values you have for [latex]P_{0}, r, k, \\text{ and } N[\/latex]<\/p>\n<p>[latex]225000\\ast \\frac{.05}{12}=d\\left(1-\\left(1+\\frac{.05}{12}\\right)^{-30*12}\\right)[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Multiply the denominator on both sides.<\/p>\n<p>[latex]937.5=d(0.77617)[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Simplify both sides<\/p>\n<p>[latex]d=\\dfrac{937.5}{.77617}=1207.85[\/latex]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Solve for d. The monthly loan payment will be $1,207.85.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<h3>Spreadsheet\u00a0Hands-On: Create a Loans Calculator<\/h3>\n<h3>Step 1: Create the loans calculator<\/h3>\n<p>Since the loans formula is identical to the one for payout annuities, we can use our withdrawal calculator from the annuities worksheet. We need to make some changes though, so we&#8217;ll make a copy of the annuities worksheet and rename it Loans.<\/p>\n<ol>\n<li>Right-click on the Annuities tab, move or copy, click the box to copy, then place it at the end of the list of sheets.<\/li>\n<li>Delete lines 1-21. We won&#8217;t need the Annuity Calculator.<\/li>\n<li>Rename line 1 as Loans Calculator.<\/li>\n<li>Relabel cells A3 and A12 as Payment<\/li>\n<li>Relabel the description of the interest field in cell C10 as &#8220;total interest paid.&#8221;<\/li>\n<li>Empty all the non-formula cells. When you are finished, your sheet should look like the image below.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153028\/Loans_01.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4336\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153028\/Loans_01.jpg\" alt=\"\" width=\"645\" height=\"404\" \/><\/a><\/li>\n<\/ol>\n<p>We will use this sheet a little differently than we did the withdrawal annuity formula. Since we are concerned with paying money back, we&#8217;ll start at the bottom of the sheet and work up. First, let&#8217;s test our formulas using the numbers in the example above.<\/p>\n<ol>\n<li>To find the monthly payment, enter the rate, years, balance, and periods in cells B13, &#8211; B16. The payment should automatically populate in cell B18 as in the image below.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153541\/Loans_02.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4337\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153541\/Loans_02.jpg\" alt=\"\" width=\"651\" height=\"394\" \/><\/a><\/li>\n<li>Now, let&#8217;s test the formulas in the top by entering the information again into cells B3 &#8211; B6. This time the balance of approximately 225,000 and the interest should automatically populate cells B8 and B10 as you can see below.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153825\/Loans_03.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4338\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12153825\/Loans_03.jpg\" alt=\"\" width=\"648\" height=\"397\" \/><\/a><\/li>\n<li>You can see that a mortgage is expensive! It costs nearly as much in interest as the amount you borrowed. (At this point, you can also test your formula in cell B19 by typing the payment amount in B12 and seeing that 30 years are returned in B19.)<\/li>\n<\/ol>\n<div class=\"textbox exercises\">\n<h3>Example<\/h3>\n<p>Use your loan calculator to see how much faster you can pay off your mortgage by making larger than the required monthly minimum payment.<\/p>\n<div class=\"qa-wrapper\" style=\"display: block\"><span class=\"show-answer collapsed\" style=\"cursor: pointer\" data-target=\"q584200\">Solution<\/span><\/p>\n<div id=\"q584200\" class=\"hidden-answer\" style=\"display: none\">\n<p>Increase the payment amount in cell B12 to see how it affects the number of years left in cell B19.<\/p>\n<p>For example, if you increase the monthly payment to $1500, you can pay off your mortgage 10 years early, and paying $1800 a month cuts the loan time in half!<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p>&nbsp;<\/p>\n<h2>Amortization<\/h2>\n<p>Auto loans and mortgages use a process called\u00a0<em>amortization<\/em> to decrease the loan balance while you pay the interest. Recall that the interest rate, such as the 5% rate in our example above, is an annual rate. Divide it by 12 monthly payments to get the portion of the rate payable per month.<\/p>\n<p>[latex]0.5\/12\\approx 0.004167[\/latex].<\/p>\n<p>Each month, the monthly rate is multiplied by the amount of principal remaining to calculate the amount of that month&#8217;s payment that goes to the interest. The remainder is applied to the principal. This continues each month until the loan is paid off. Banks will provide the details of this process in a table called an <em>amortization schedule.\u00a0<\/em>Let&#8217;s see how this plays out over time by adding an\u00a0amortization schedule to our Loans sheet.<\/p>\n<h3>Step 2: Create an amortization schedule<\/h3>\n<ol>\n<li>Begin by labeling row 22 &#8220;Amortization Schedule&#8221; then label B23 &#8211; E23 as Payment, Principal, Interest, and Balance respectively.<\/li>\n<li>In Cell A24, type 0. In cell A25, type 1. These will represent the payment number. We will start with the zeroth payment, to indicate the starting loan amount.<\/li>\n<\/ol>\n<p>We will store the information for our amortization schedule in the first part of the loans calculator, where we use the inputs to the loans formula to compute the loan amount. We&#8217;ll reference these cells in the schedule we are creating to auto populate them based on the terms of the loan and monthly payment. It&#8217;s okay that the balance has a little spare change on the end. It won&#8217;t affect the general trend that we are looking for.<\/p>\n<p>We need to handle an important detail as we reference the stored information when building our formulas. Spreadsheets will allow many rows in a column to be filled with a formula that computes subsequent rows based on information in previous ones by automatically changing the reference-cell location as it moves from row to row. We will do this to fill our schedule with these formulas in a moment. To prevent the spreadsheet from changing numbers we don&#8217;t want it to change, we&#8217;ll make some formula reference points static. We&#8217;ll do this by including a $ between the column and row coordinates that point to the monthly payment, rate, and periods.<\/p>\n<ol>\n<li><span style=\"font-size: 1em\">In cell E24, type = B$8.<\/span><\/li>\n<li>In cell B25, type = B$3.<\/li>\n<li>In cell D25, we&#8217;ll use a formula to calculate the interest portion. Type =E24*(B$4\/B$5).<\/li>\n<li>In cell C25, we&#8217;ll take the difference between the amount paid in B25 and the interest portion in D25 to get the amount applied to the principle. Type\u00a0=B25-D25.<\/li>\n<li>Now, click and drag to highlight cells A25 &#8211; E25 and release the mouse. Grab the little square in the lower right hand corner and drag it straight down. You can take the information down as far as you wish. If you drag it all the way down to row 384 on the sheet you can see the loan balance reduce to zero! The image below shows one year of payments. Note how the interest paid per month decreases as the portion paid to the principal increases.\u00a0<a href=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12163226\/Loans_04.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4341\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/4685\/2020\/04\/12163226\/Loans_04.jpg\" alt=\"\" width=\"648\" height=\"708\" \/><\/a><\/li>\n<\/ol>\n<p>Now you are ready to use your loan calculator to answer some questions.<\/p>\n<h3>Step 3: Use the loan calculator<\/h3>\n<div class=\"textbox exercises\">\n<h3>Example<\/h3>\n<p>You have a credit card with a $5,000 balance at 18% interest and you&#8217;d like to pay it off in 3 years. How much will your monthly payment need to be?<\/p>\n<div class=\"qa-wrapper\" style=\"display: block\"><span class=\"show-answer collapsed\" style=\"cursor: pointer\" data-target=\"q563403\">Solution<\/span><\/p>\n<div id=\"q563403\" class=\"hidden-answer\" style=\"display: none\">\n<p>&nbsp;<\/p>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"textbox key-takeaways\">\n<h3>Try it<\/h3>\n<p><iframe loading=\"lazy\" id=\"ohm6685\" class=\"resizable\" src=\"https:\/\/ohm.lumenlearning.com\/multiembedq.php?id=6685&theme=oea&iframe_resize_id=ohm6685&show_question_numbers\" width=\"100%\" height=\"150\"><\/iframe><\/p>\n<\/div>\n<div class=\"textbox exercises\">\n<h3>Example<\/h3>\n<p>You&#8217;ve found a house you want to purchase for $255,000. You can pay 5% down and take a 30 year mortgage for the rest. The average rate on for a 30-year fixed rate mortgage in April of 2020 according to The Federal Home Loan Mortgage Corporation, FreddieMac,<a class=\"footnote\" title=\"FreddieMac Mortgage Rates U.S. weekly averages as of 04\/09\/2020. Accessed 04-15-2020 at http:\/\/www.freddiemac.com\/pmms\/\" id=\"return-footnote-4208-1\" href=\"#footnote-4208-1\" aria-label=\"Footnote 1\"><sup class=\"footnote\">[1]<\/sup><\/a> is 3.33%, but rates can be as high as 7% or more, depending on the borrower&#8217;s credit history.<\/p>\n<p>(a) How much will your down-payment be?<\/p>\n<p>(b) How much money will you need to borrow?<\/p>\n<p>(b) What will your monthly payment be at 7% interest?<\/p>\n<p>(c) What will your monthly payment be at 3.33% interest?<\/p>\n<div class=\"qa-wrapper\" style=\"display: block\"><span class=\"show-answer collapsed\" style=\"cursor: pointer\" data-target=\"q245116\">Solution<\/span><\/p>\n<div id=\"q245116\" class=\"hidden-answer\" style=\"display: none\">\n<p>(a) $12,750 ([latex](0.05)(255000)=12,750[\/latex])<\/p>\n<p>(b) $242,250. ([latex]255000 - .05(255000) = 242250[\/latex])<\/p>\n<p>(c) $1,611.70 (enter the values for Rate=0.033, Years=30, Balance=242250, and Periods=12 in your spreadsheet in cells B13 &#8211; B16)<\/p>\n<p>(d) $1,060.95 (change Rate to 0.033)<\/p>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"textbox key-takeaways\">\n<h3>try it<\/h3>\n<p><iframe loading=\"lazy\" id=\"ohm6686\" class=\"resizable\" src=\"https:\/\/ohm.lumenlearning.com\/multiembedq.php?id=6686&theme=oea&iframe_resize_id=ohm6686&show_question_numbers\" width=\"100%\" height=\"150\"><\/iframe><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n\n\t\t\t <section class=\"citations-section\" role=\"contentinfo\">\n\t\t\t <h3>Candela Citations<\/h3>\n\t\t\t\t\t <div>\n\t\t\t\t\t\t <div id=\"citation-list-4208\">\n\t\t\t\t\t\t\t <div class=\"licensing\"><div class=\"license-attribution-dropdown-subheading\">CC licensed content, Original<\/div><ul class=\"citation-list\"><li><strong>Authored by<\/strong>: Deborah Devlin. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em><\/li><li>Screenshot of Excel worksheet. <strong>Authored by<\/strong>: Deborah Devlin. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em><\/li><li>Revision and Adaptation. <strong>Authored by<\/strong>: Deborah Devlin. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/about\/pdm\">Public Domain: No Known Copyright<\/a><\/em><\/li><\/ul><div class=\"license-attribution-dropdown-subheading\">CC licensed content, Shared previously<\/div><ul class=\"citation-list\"><li>Question ID 6685, 6686. <strong>Authored by<\/strong>: David Lippman. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em>. <strong>License Terms<\/strong>: IMathAS Community License CC-BY + GPL<\/li><li>Math in Society. <strong>Authored by<\/strong>: David Lippman. <strong>Provided by<\/strong>: Open Textbook Store, Transition Math Project, and the Open Course Library. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by-sa\/4.0\/\">CC BY-SA: Attribution-ShareAlike<\/a><\/em><\/li><\/ul><\/div>\n\t\t\t\t\t\t <\/div>\n\t\t\t\t\t <\/div>\n\t\t\t <\/section><hr class=\"before-footnotes clear\" \/><div class=\"footnotes\"><ol><li id=\"footnote-4208-1\">FreddieMac Mortgage Rates U.S. weekly averages as of 04\/09\/2020. Accessed 04-15-2020 at http:\/\/www.freddiemac.com\/pmms\/ <a href=\"#return-footnote-4208-1\" class=\"return-footnote\" aria-label=\"Return to footnote 1\">&crarr;<\/a><\/li><\/ol><\/div>","protected":false},"author":25777,"menu_order":12,"template":"","meta":{"_candela_citation":"[{\"type\":\"original\",\"description\":\"\",\"author\":\"Deborah Devlin\",\"organization\":\"\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"\"},{\"type\":\"original\",\"description\":\"Screenshot of Excel worksheet\",\"author\":\"Deborah Devlin\",\"organization\":\"\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"\"},{\"type\":\"cc\",\"description\":\"Question ID 6685, 6686\",\"author\":\"David Lippman\",\"organization\":\"\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"IMathAS Community License CC-BY + GPL\"},{\"type\":\"cc\",\"description\":\"Math in Society\",\"author\":\"David Lippman\",\"organization\":\"Open Textbook Store, Transition Math Project, and the Open Course Library\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by-sa\",\"license_terms\":\"\"},{\"type\":\"original\",\"description\":\"Revision and Adaptation\",\"author\":\"Deborah Devlin\",\"organization\":\"\",\"url\":\"\",\"project\":\"\",\"license\":\"pd\",\"license_terms\":\"\"}]","CANDELA_OUTCOMES_GUID":"","pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-4208","chapter","type-chapter","status-web-only","hentry"],"part":4179,"_links":{"self":[{"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/pressbooks\/v2\/chapters\/4208","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/wp\/v2\/users\/25777"}],"version-history":[{"count":14,"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/pressbooks\/v2\/chapters\/4208\/revisions"}],"predecessor-version":[{"id":4535,"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/pressbooks\/v2\/chapters\/4208\/revisions\/4535"}],"part":[{"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/pressbooks\/v2\/parts\/4179"}],"metadata":[{"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/pressbooks\/v2\/chapters\/4208\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/wp\/v2\/media?parent=4208"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/pressbooks\/v2\/chapter-type?post=4208"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/wp\/v2\/contributor?post=4208"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/slcc-mathforliberalartscorequisite\/wp-json\/wp\/v2\/license?post=4208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}