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?

Improve this question
4
  • Try http:powerpay.org which has built into it all the functionality that you want to construct for yourself.
    – Dilip Sarwate
    Commented Feb 2, 2016 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
    Commented Feb 3, 2016 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
    Commented May 5, 2016 at 18:51
  • Pay off the highest interest loans first this will help maximise debt repayment.
    – Andrew Day
    Commented May 31, 2016 at 15:30

1 Answer 1

Reset to default
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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .