RETIRING STUDENT LOAN DEBT ( Using excel formula and spreadsheet)

You have just started a new job with a significant increase in salary above what you were earning when you originally negotiated your student loan repayment. The salary increase affords you the opportunity of increasing your monthly loan payments, thereby allowing you to retire the debt sooner than originally planned. You have six years remaining in the original payback plan on a loan of $55,000, with an interest rate of 2.4% and a monthly payment of $566.74. With your new salary, you can afford a monthly payment of $672. You also will be eligible for end-of-year bonuses.

PART 1: Use the concepts and techniques that you have learned throughout the semester to create a worksheet containing a loan calculator and an amortization schedule. Determine how soon you can retire the student loan debt with the new higher monthly payments. Assuming that you earn yearly bonuses of $6,000 at the end of each year, when could you pay off the remaining balance on your student loan if you kept the lower monthly payment?

PART 2: Would you opt for increasing your monthly payment, or using any earned bonus money to retire the debt early? Explain the reason for your choice.

Considerations:

Assume this is a fixed rate loan. Most loans do not penalize for pre-payment. In order to arrive at your answers, you will need to determine the following:

1. Number of years for the original loan. There is a financial function that will help you obtain this information. To locate it, select the Formulas tab, click on Insert Function in the Function Library section. In the pop-up, type a brief description of what you want to do in the box provided, and then click Go. Once you select the appropriate function, specify the required parameters.
2. Amortization schedule of the original loan. You will need to provide the Principal and Interest portion for each period (month). Follow the same steps specified in A above to locate the necessary functions. Please note that, as time goes on, the principal amount paid in each period (month) will increase while the interest amount paid will decrease. The principal portion plus the interest portion will always equal the total amount paid on each period (month).
3. Amount of loan still owed. You can obtain this from the amortization table of the original loan and the information provided of the current period (month) of the loan.
4. Period (month) on which you will complete paying the loan, based on the new payment amount. You may either develop an adjusted amortization schedule for the remaining payments, or find a function that provides the number of periods based on the remaining amount in the loan, the fixed interest rate, and the new monthly payments. Follow the same steps specified in A above to locate any functions you may need.
5. Apply the annual bonus to the loan payments. Please follow prior steps to determine the impact of using your annual bonus to accelerate the payment of your loan.