# How do I determine how long it will take to pay off my all my loans?

Right now I have a spreadsheet with the loans I have, the interest rates the are each at, and how much I am putting toward each in monthly payments. I then calculate how long each loan will take to pay off individually by using `=NPER(INTEREST_RATE/12,PAYMENT_AMOUNT,CURRENT_LOAN_AMOUNT)`.

This works (to my knowledge), but does not take into consideration that when one of the loans is paid off, I would like to take the money I would have spent on that loan and apply it to the next highest interest loan.

Is there a way to apply that sort of logic easily? Or do I have to just determine when each loan would be paid off and have a separate model for when that happens?

• Try http:powerpay.org which has built into it all the functionality that you want to construct for yourself. – Dilip Sarwate Feb 2 '16 at 21:54
• "Is there a way to apply that sort of logic easily? " ... you may get better / faster answer on superuser.com. Just put an image of the spreadsheet showing your calculations and your ask. Someone should be able to tell you a formulae or macro that can easily be written of. – Dheer Feb 3 '16 at 4:53
• What you are talking about is called payment snowballing. Here is a link to an excel spreadsheet from lifehacker. – Michael Riley - AKA Gunny May 5 '16 at 18:51
• Pay off the highest interest loans first this will help maximise debt repayment. – Andrew Day May 31 '16 at 15:30

# Multi-Loan Debt Payment Time Calculations

### Calculation Recipe

To calculate how long it will take to pay off multiple loans you can perform the following calculations:

1. Determine the monthly available money (Pmax) and the minimum payment of loan 2 (P2min).
2. Determine NPER1, the `NPER()` of loan 1 using the maximum available money minus the minimum payment of loan 2 (Pmax - P2min).
3. Determine FV2min, the `FV()` of making minimum payments on loan 2 for the number of periods it take to pay off loan 1 (NPER1).
4. Determine NPER2, the `NPER()` of loan 2 with the reduced balance (FV2min) using the maximum available payment (Pmax).
5. Calculate NPERTotal, by adding the two (NPER1 + NPER2).

`B9` and `D9` contain the `NPER()` formulas that you referenced and I have highlighted `D2` that contains the `FV()` calculation for Loan 2. The Total Time result is in `B12`. There are also some other helpful calculations, to provide insight into the total debt, but these are not needed for the time calculation. Comment if you need the formulas. 