6

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
  • 1
    "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
  • 4
    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
1

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).

Example Spreadsheet

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.

example debt calculation

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.