• Originally published: 25/06/2018 14:01
Last version published: 04/03/2019 07:43
Publication number: ELQ-41065-4
View all versions & Certificate

# Loan Pay Back Optimizer - Excel Model

Enter an extra payment allocation and see how this effects your total repayment across multiple loans.

Description
Use for up to 12 separate loans.

At a high level, it is not that difficult to do this on your own. You can simply figure out the loan with the most interest to be re-paid and/or the loan with the highest interest rate, apply the extra payment allocation to that and when its done move to the next.

However, it is really hard to know how much money you will save based on a given amount of extra principal paid each month as well as the total amount of days it saves you from being in debt compared to sticking with the original amortization schedules and not putting any extra principal into the debt payments.

This model assumes that you have extra money to put on the payments each month.

There are two ranking styles you can use to determine how the allocation is optimized. They include ranking by interest rate and ranking by total interest owed. The reason both are used as an option for the user is because the highest interest rate might not always be the best to pay back first. If you have loans with varying terms and total owed, it might save you more money to pay back the biggest loan first even if it has a lower interest rate.

So, the model can show you both final results of the two ranking styles. From preliminary testing, when I used the model to optimize based on interest rate, it saved less money in the long run but paid back all the loans faster. When optimizing for highest interest owed, the model projected more savings than with the interest rate ranking but took longer to fully pay back all the loans.

The model starts off with you entering the current balances owed of all the loans at present, the interest rates, the remaining years, and the payments per year.

You then enter the month that you want the extra payment allocation to take effect and the model does the rest. It automatically flows that extra payment amount through all the loans in order of the ranking style picked until everything is paid back.

This Best Practice includes
1 Excel template and 1 tutorial video

Discuss

## Further information

### Objectives

Measure cash savings and debt repayment optimization effects with extra principal payment.

### Use it if

Principal and interest loans.

### Don't use it if

When snowballing (using the debt service of previous loans to pay back newer ones).

See all

## Any questions on Loan Pay Back Optimizer - Excel Model?

The user community and author are here to help. Go ahead!