let E18 v f A B D E F G H 1 J K L M Sales Tax Rate 6.754 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Transaction ID Amount Sales Tax Total 578 42.00 579 167.00 580 V209.00 an 581 142.00 Venue 582 234.00 583 88.00 584 197.00 585 209.00 586 163.00 587 151.00 588 103.00 589 148.00 590 51.00 Grand Total 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 Statistical Functions Student Loans Formulas + FAN SAHIT ITTHI w E .00.0 JUTH H17 xVfx A C D E F G H 1 K L L M N 1 Basic Loan 35,000.00 1.06% Financial Stress and Forbearance 2 Years into loan hit 6 month pause buttor Interest accrues - no payment made Amount Needed Fee Percent Fee ILoan Amount Annual Interest Rate Term-Years # of monthly Payments Payment 6.20% 10 Balance at 2 years 6 months Interest New Balance 96 Months remaining New Payment Total Cost of the Loan Interest Balance Reduction Loan Balance Paid before pause Paid after Pause New Cost of the Loan Added Cost 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 27 47 48 49 50 51 52 53 54 Period# 0 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 20 26 27 28 29 30 31 32 33 34 35 36 37 38 39 An Statistical functione CC Student Ioans Formulas + 3.1 Calculate the loan fee, amount, and term. a. After analyzing your situation, you conclude you have a financial need of $35,000 (C3). b. FSA will charge you a 1.06% fee ( C4) before turning your case over to the financial institution servicing your loan. C. In cell C5 calculate the amount of the fee. d. In cell C6 calculate the amount of the total loan amount required. e. In C9) calculate the number of monthly payments you will make. 3 3.2 Calculate your monthly payment a. Using the PMT function in cell C10), calculate your monthly payment. b. Place a minus sign in front of the calculation to make the result a positive number. c. Within the PMT function divide the annual interest rate by 12 to reflect a monthly interest rate. 5 3.3 Calculate the total cost of the loan. a. In cell C12), calculate the total of your payments. b. This is the total cost of the loan that you must pay. 2 i 3.4 Calculate the beginning balance as well as the interest and balance reduction elements of each payment. a. The beginning balance of the loan is in cell C6). b. The beginning balance in the amortization table will be in cell E15). C. Set cell E15 equal to C6). d. In cell C16) use the IPMT function to calculate the amount of interest paid in each period. e. In cell D16 use the PPMT function to calculate the amount of loan balance reduced during each period. f. Place a minus sign in front of these calculations to make the results positive numbers. g. Use relative and absolute references correctly so these calculations will autofill down for all 120 months. h. Reuse the formulas in cell cells C16) and D16) to complete the interest and balance reduction calculations for each period. 10 3.5 Calculate the balance after each payment is made. a. In E16) calculate the loan balance after the first payment is made. b. Autofill this function down for next 120 months. C. This shows how your loan balance decreases as you make your payments. + 100% MyEducator Tasks i 3.6 Determine the loan balance after 2 years. a. One service provided by a loan servicer is suspension of payments should you encounter some major financial setback such as job loss. b. In general interest will continue to accrue during this period. c. In this example we've assumed a 6 month pause in payments after 2 years. d. In cell H6 reference cell E39) to reflect your loan balance after 2 years. 2 3.7 Calculate the interest that will accumulate when payments are not made and the resulting balance at the end of the 6 months. a. Using the monthly interest in cell C40) to be paid on this balance, calculate the interest that accrues over the next 6 months. b. Calculate this interest amount in cell H7). C. Note that you will need to multiply the amount in C40 times 6 to account for all 6 months of interest. d. Add this amount to the loan balance in cell H6 so that cell H8) reflects the new loan balance. I 111 2. X i MyEducator Tasks (3.8) Calculate the updated payment amount after the 6 month deferement. a. In H11) use the PMT function to reflect your new monthly payment. b. Place a minus sign in front of the calculation to make the result a positive number. 16 3.9 Calculate what you paid before the pause. a. Place your calculation in cell H13). b. Use the initial monthly payment rate. C. Multiply this rate times the number of payments made before the pause. 2 3.10 Calculate what you paid after the pause. a. Place your calculation in cell H14). b. Use the new monthly payment rate. C. Multiply this rate times the number of payments remaining after the pause. 2. ^ 3.11 Calculate the new total cost of the loan. a. Place your calculation in cell H15). b. The new total cost of the loan is the total amounts paid both before and after the pause. 3.12) Calculate the increase in cost resulting from pausing payments. a. In H17) calculate the cost increase due to taking a pause in your payments. b. This is the difference between the original total cost of the loan and the new total cost of the loan. C. Another hidden cost that we cannot explicitly calculate is that taking such a pause may reduce your credit score, resulting in a higher interest rate should you want to finance a car or house. 2 T Next let E18 v f A B D E F G H 1 J K L M Sales Tax Rate 6.754 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Transaction ID Amount Sales Tax Total 578 42.00 579 167.00 580 V209.00 an 581 142.00 Venue 582 234.00 583 88.00 584 197.00 585 209.00 586 163.00 587 151.00 588 103.00 589 148.00 590 51.00 Grand Total 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 Statistical Functions Student Loans Formulas + FAN SAHIT ITTHI w E .00.0 JUTH H17 xVfx A C D E F G H 1 K L L M N 1 Basic Loan 35,000.00 1.06% Financial Stress and Forbearance 2 Years into loan hit 6 month pause buttor Interest accrues - no payment made Amount Needed Fee Percent Fee ILoan Amount Annual Interest Rate Term-Years # of monthly Payments Payment 6.20% 10 Balance at 2 years 6 months Interest New Balance 96 Months remaining New Payment Total Cost of the Loan Interest Balance Reduction Loan Balance Paid before pause Paid after Pause New Cost of the Loan Added Cost 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 27 47 48 49 50 51 52 53 54 Period# 0 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 20 26 27 28 29 30 31 32 33 34 35 36 37 38 39 An Statistical functione CC Student Ioans Formulas + 3.1 Calculate the loan fee, amount, and term. a. After analyzing your situation, you conclude you have a financial need of $35,000 (C3). b. FSA will charge you a 1.06% fee ( C4) before turning your case over to the financial institution servicing your loan. C. In cell C5 calculate the amount of the fee. d. In cell C6 calculate the amount of the total loan amount required. e. In C9) calculate the number of monthly payments you will make. 3 3.2 Calculate your monthly payment a. Using the PMT function in cell C10), calculate your monthly payment. b. Place a minus sign in front of the calculation to make the result a positive number. c. Within the PMT function divide the annual interest rate by 12 to reflect a monthly interest rate. 5 3.3 Calculate the total cost of the loan. a. In cell C12), calculate the total of your payments. b. This is the total cost of the loan that you must pay. 2 i 3.4 Calculate the beginning balance as well as the interest and balance reduction elements of each payment. a. The beginning balance of the loan is in cell C6). b. The beginning balance in the amortization table will be in cell E15). C. Set cell E15 equal to C6). d. In cell C16) use the IPMT function to calculate the amount of interest paid in each period. e. In cell D16 use the PPMT function to calculate the amount of loan balance reduced during each period. f. Place a minus sign in front of these calculations to make the results positive numbers. g. Use relative and absolute references correctly so these calculations will autofill down for all 120 months. h. Reuse the formulas in cell cells C16) and D16) to complete the interest and balance reduction calculations for each period. 10 3.5 Calculate the balance after each payment is made. a. In E16) calculate the loan balance after the first payment is made. b. Autofill this function down for next 120 months. C. This shows how your loan balance decreases as you make your payments. + 100% MyEducator Tasks i 3.6 Determine the loan balance after 2 years. a. One service provided by a loan servicer is suspension of payments should you encounter some major financial setback such as job loss. b. In general interest will continue to accrue during this period. c. In this example we've assumed a 6 month pause in payments after 2 years. d. In cell H6 reference cell E39) to reflect your loan balance after 2 years. 2 3.7 Calculate the interest that will accumulate when payments are not made and the resulting balance at the end of the 6 months. a. Using the monthly interest in cell C40) to be paid on this balance, calculate the interest that accrues over the next 6 months. b. Calculate this interest amount in cell H7). C. Note that you will need to multiply the amount in C40 times 6 to account for all 6 months of interest. d. Add this amount to the loan balance in cell H6 so that cell H8) reflects the new loan balance. I 111 2. X i MyEducator Tasks (3.8) Calculate the updated payment amount after the 6 month deferement. a. In H11) use the PMT function to reflect your new monthly payment. b. Place a minus sign in front of the calculation to make the result a positive number. 16 3.9 Calculate what you paid before the pause. a. Place your calculation in cell H13). b. Use the initial monthly payment rate. C. Multiply this rate times the number of payments made before the pause. 2 3.10 Calculate what you paid after the pause. a. Place your calculation in cell H14). b. Use the new monthly payment rate. C. Multiply this rate times the number of payments remaining after the pause. 2. ^ 3.11 Calculate the new total cost of the loan. a. Place your calculation in cell H15). b. The new total cost of the loan is the total amounts paid both before and after the pause. 3.12) Calculate the increase in cost resulting from pausing payments. a. In H17) calculate the cost increase due to taking a pause in your payments. b. This is the difference between the original total cost of the loan and the new total cost of the loan. C. Another hidden cost that we cannot explicitly calculate is that taking such a pause may reduce your credit score, resulting in a higher interest rate should you want to finance a car or house. 2 T Next