Loans We talked about loans a bit when talking about Credit Cards and Mortgages. Let's establish some vocabulary terms related to loans that will be helpful for this project. The principal is the amount of the loan. You make payments to reduce the principal and pay back the initial amount borrowed plus interest. An interest rate is generally an annual rate. If interest needs to be calculated more frequently than once a year (annually), such as monthly, then a monthly rate would need to be determined from the annual rate. The interest rate is calculated on the remaining principal for each calculation period (such as yearly, or monthly, etc.) 1. Suppose Little Jimmy borrows $100,000 in loans. He is able to get these loans with a 6% interest rate. We'll assume Jimmy is able to save $1,500 each month to put toward an annual payment for his loan. Calculate (by hand) Jimmy's loan payment information for each year until it is paid off by filling in the table below. a. How much would he have paid in total? b. How much would he have paid in interest? Give two ways of calculating this. (For example, one from adding numbers from the table, and one from using your answer in part (a)). c. In this example, we assumed that interest and payments were done annually. However, usually loans are charged interest and have payments made monthly. Explain what we would have to do differently if we were going to calculate in the above scenario on a monthly basis. Note that you do NOT have to do these calculations, simply explain what would need to be done differently; you can do some calculations as an example in your explanation, but you do not need to do all (or even many) of these calculations. 4 2. As agroup, you should create a spreadsheet (you can use Excel or you can use Google Sheets) which will do the calculations from problem 1 for you. In 3 separate cells you should be able to enter: e The initial amount borrowed e The interest rate e The payment amount The spreadsheet should be able to automatically calculate everything else from those cells where this information is entered. a. Create a spreadsheet for the table of problem 1 of your packet. Meaning, you should be able to enter \"$100,000,\" "6%," and \"$1,500\" into the spreadsheet and have the rest of the table information calculated from those three cells where you entered that information. There is nothing you need to record here (in the packet) for your work. b. As noted in 1(c), normally loans are calculated on a monthly basis. Create a space in your spreadsheet to calculate the payments for 1 year. How much is paid in interest in the first year? Is this more or less than the amount of interest in the first year of the annual calculations? c. Explain how we could have known that the total interest paid monthly for the first year would be less than the interest calculated for the first year in the annual scenario. d. Let's return to the annual scenario. Saving $1,500 monthly could be difficult, and often payments are not determined by how much can be saved, but instead by how much time it takes to pay off the loan. Do guess & check by changing the payment amount in your spreadsheet to determine (to the nearest dollar) how much the annual payment should be in order to pay off the loan in 15 years. Record the payment information below and write a short description of your guess and check process in determining this payment amount. Annual Payment: Monthly amount to save: E17 X B D F Month Interest Charged Amount Owed (Start of Month) Monthly Payment paining Principal (End of Month) 1 $500.00 $100,000.00 $1,500.00 $99,000.00 2 $495.00 $99,000.00 $1,500.00 $97,995.00 3 $489.98 $97,995.00 $1,500.00 $96,984.98 4 $484.92 $96,984.98 $1,500.00 $95,969.90 5 $479.85 $95,969.90 $1,500.00 $94,949.75 6 $474.75 $94,949.75 $1,500.00 $93,924.50 7 $469.62 $93,924.50 $1,500.00 $92,894.12 8 $464.47 $92,894.12 $1,500.00 $91,858.59 10 $459.29 $91,858.59 $1,500.00 $90,817.88 10 $454.09 $90,817.88 $1,500.00 $89,771.97 12 11 $448.86 $89,771.97 $1,500.00 $88, 720.83 13 12 $443.60 $88,720.83 $1,500.00 $87,664.44 14 15 16 + 17 18 19 20 21 22 23 25 Inputs Loan Table Monthly Calculations eady Accessibility: Good to go