how to calculate student loan repayments in excel

Have a look towards the bottom of the spreadsheet and you will notice that with the extra repayment of $80 per month, we will have paid off our loan 30 months early (period 331). You will notice that for these two formulae, we have used the dollar signs for absolute references to lock in the numbers from our data table for when we fill down. So, if the balance at the beginning of the month is less than the monthly payment amount (B6) then make $0 extra repayment, otherwise, use the extra repayment amount as we set up in B11. Always check with a professional financial advisor before making financial decisions.

You should double-check your results with your bank or professional financial advisor. It does not take into account bank fees.ĭisclaimer: This tutorial is for informational purposes only and does not constitute as professional advice. I’ll start with the same information from the PMT exercise, so if you haven’t done principal and interest calculations, do those first.Īll results are based on the assumption that the interest rate remains fixed throughout the entire loan term and that interest is calculated monthly and payments made monthly. Create Your Own Amortization Table in Excel Of course, making loan calculations can be done easily using free online loan calculators, but that’s not as fun as doing it yourself in Excel and this way you can link your information into your budget to run some dynamic financial scenarios. Photo by rawpixel on Unsplash.Ĭheck out the PMT tutorial before starting this one to create the initial data table.

How to calculate student loan repayments in excel