C12 fx A D E F H I J T 2 3 Financial Stress and Forbearance 2 Years into loan hit 6 month pause button Interest accrues - no payment made 4 5 6 7 Amount Needed Fee Percent Fee Loan Amount Annual Interest Rate Term-Years # of monthly Payments Payment Basic Loan $35,000.00 1.06% $371.00 $35,371.00 6.20% 10 120 $396.25 Balance at 2 years 6 months Interest New Balance 8 9 10 11 96 Months remaining New Payment Total Cost of the Loan Period# Interest Balance Reduction Loan Balance $35,371.00 Paid before pause Paid after Pause New Cost of the Loan 0 12 13 14 15 16 17 18 19 1 2 Added Cost 3 4 20 5 21 22 23 24 25 26 27 28 29 30 31 32 33 6 7 8 9 10 11 12 13 14 15 16 17 18 19 34 Student Loans Statistical Functions Formulas + 3.1 Calculate the loan fee, amount, and i 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. 3.3 Calculate the total cost of the loan. i a. In cell C12), calculate the total of your payments. b. This is the total cost of the loan that you must pay. 2 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 06. 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. d. In cell H6 reference cell E39 to i 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 040 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. 2 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. i b. Place a minus sign in front of the calculation to make the result a positive number 6 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. 11 2 C12 fx A D E F H I J T 2 3 Financial Stress and Forbearance 2 Years into loan hit 6 month pause button Interest accrues - no payment made 4 5 6 7 Amount Needed Fee Percent Fee Loan Amount Annual Interest Rate Term-Years # of monthly Payments Payment Basic Loan $35,000.00 1.06% $371.00 $35,371.00 6.20% 10 120 $396.25 Balance at 2 years 6 months Interest New Balance 8 9 10 11 96 Months remaining New Payment Total Cost of the Loan Period# Interest Balance Reduction Loan Balance $35,371.00 Paid before pause Paid after Pause New Cost of the Loan 0 12 13 14 15 16 17 18 19 1 2 Added Cost 3 4 20 5 21 22 23 24 25 26 27 28 29 30 31 32 33 6 7 8 9 10 11 12 13 14 15 16 17 18 19 34 Student Loans Statistical Functions Formulas + 3.1 Calculate the loan fee, amount, and i 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. 3.3 Calculate the total cost of the loan. i a. In cell C12), calculate the total of your payments. b. This is the total cost of the loan that you must pay. 2 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 06. 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. d. In cell H6 reference cell E39 to i 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 040 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. 2 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. i b. Place a minus sign in front of the calculation to make the result a positive number 6 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. 11 2