Question

Imagine that you will have $55,000 in student loan debt when you graduate. The annual interest rate on the loan is 6.5%. The loan is amortized over 10 years, with payments due monthly. If you forgo Starbucks and opt to bring your coffee to work, you can make a payment of $250 in addition to your required minimum payment every third month, beginning with month 3. You want to determine how much interest you will save if you make this additional payment every third month.

Create an amortization table and determine the total amount of interest that you will pay if you do not make any additional payments. Hard code this amount in cell C14. Then calculate the amount of interest that you will save over the life of your loan as well as the total number of months in which you will not have to make payments if you opt make the additional $250 payment every third month.

Your table should require only 6 inputs: N, I, PV, FV, the additional payment amount every Nth month, and the Nth month (=3). Hard code the cells in green. Use formulas and cell references in the yellow cells.

Answer 1

Loan Amount (P)= $55,000

Interest Rate (i)= 6.50% anually= (6.5/12)% monthly= 0.542% monthly

Loan Period= 10 Years, Due paid Monthly, hence No. of periods (n)= 10*12= 120

Monthly Payment= {P * i(1+i)^n}/{((1+i)^n)-1}= $624.51. Also inexcel we can use the function =PMT(i,n,P)

In Amortization, some of the monthly payments go to interest payment while the rest is used to reduce the principal loan amount. As time passes the interest payment reduces and the reduction in principal loan increases.

Case 1: When we don't make Additional Payment:

The formula used in the below excel file- For Monthly Payment- =PMT(i,n,P)

Column wise

Starting Balance- from B6- =IF(F5>0,F5,0), this formula is then referenced down till the last, for B5- =A2. For the n month it is the ending balance for n-1 th mont

Monthly Payment- =$D$2, use of $ sign before the Column and Row number freezes or hard code the cell value when we drag the formula

Interest Payment from Month 1)- =B5*($B$2/12), this formula is dragged till the end for reference. Note that cell B2 is hard coded in the formula. This is nothing but Interest= Principal* interest per month.

Principal Payment (from month 1)- =C5-D5, this is then dragged down. This is the extra of the monthly payment left after interest payment, helping in reducing the principal amount

So the Total Interest Paid is the sum of all cells in Column D from Row 5 to 124 (formula =sum(D5:D124)

Total Interest Paid= $19,941.66

Case 2: When we make Additional Payments:

Excel has been updated with new formula where for every third month starting from Month 3, the Additional payment value in cell E2 is added to Monthly Payment column cell values.

Total Interest Paid in this scenario is $16,636.81

Note that for the last month (month 102) Monthly payment was only $310.91 since the Monthly payment was greater than the remaining loan amount. So, for 102nd month, we just have to pay the remaining principal and the interest accrued on that principal.

Comparing Case 1 with 2, we can see that a student can save an amount of $3,304.85 ($$19,941.66-$16,636.81) if he/she can make an additional payment of $250 every 3rd month.

Also, if he/she can make the additional payment, the loan gets completed in 102 months compared to that of 120 months as in Case 1. This results in the save of 18 months, i.e. 1 year 6 months where he/she doesn't have to make payment.