please show work as well thank you (formulas)
Aid (FSA). Complete each task by inserting the appropriate function, referencing the appropriate cells in the task data, in the specified cells. (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.2) Calculate your monthly payment a. Using the PMT function in cell C10 calculate your monthly payment. 3.2) Calculate your monthly payment a. Using the PMT function in cell (10) 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. a. In cell 012), calculate the total of your payments. b. This is the total cost of the loan that you must pay. A 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 formular in cell cells C16 and D16 to complete the interest and balance reduction Accalculations for each period. Com 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. 2 G H Amount Needed Fee Percent Financial Stress and Forbearance 2 Years into loan hit 6 month pause button Interest accrues - no payment made Fee Loan Amount Annual Interest Rate Term-Years Fof monthly Payments Payment Basic Loan $35,000.00 1.06% $371.00 $35,371.00 6.20% 10 120 $740,561.40 Balance at 2 years 6 months interest New Balance Months remaining New Payment 96 Total Cost of the Loan $2,333.23 Periods Interest Balance Reduction Loan Balance Paid before pause Paid after Pause New Cost of the Loan 0 1 2 3 4 Added Cost S 6 7 8 9 10 11 12 13 1 14 15 16 17 18 19 20 3 4 5 6 7 30 21 22 23 Formulas Studenti Aid (FSA). Complete each task by inserting the appropriate function, referencing the appropriate cells in the task data, in the specified cells. (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.2) Calculate your monthly payment a. Using the PMT function in cell C10 calculate your monthly payment. 3.2) Calculate your monthly payment a. Using the PMT function in cell (10) 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. a. In cell 012), calculate the total of your payments. b. This is the total cost of the loan that you must pay. A 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 formular in cell cells C16 and D16 to complete the interest and balance reduction Accalculations for each period. Com 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. 2 G H Amount Needed Fee Percent Financial Stress and Forbearance 2 Years into loan hit 6 month pause button Interest accrues - no payment made Fee Loan Amount Annual Interest Rate Term-Years Fof monthly Payments Payment Basic Loan $35,000.00 1.06% $371.00 $35,371.00 6.20% 10 120 $740,561.40 Balance at 2 years 6 months interest New Balance Months remaining New Payment 96 Total Cost of the Loan $2,333.23 Periods Interest Balance Reduction Loan Balance Paid before pause Paid after Pause New Cost of the Loan 0 1 2 3 4 Added Cost S 6 7 8 9 10 11 12 13 1 14 15 16 17 18 19 20 3 4 5 6 7 30 21 22 23 Formulas Studenti
