How to calculate multiple debt payments with additional lump sum and varying additional monthly payments

Question

I'm attempting to create a calculator that I can use to try different debt repayment scenarios. If I have multiple debts with different rates I'm wondering what algorithm to use to figure out how long it will take to repay and how much interest will be saved.

I have figured out how to calculate these numbers with EXCEL and NPER and CUMIPMT for adding blank amounts to all of them, but I'd like to figure out how to apply it to highest interest rate debt first.

I'm wondering if I just need to calculate the principal and interest payment on a per month basis as if it was a new loan each month to figure this out.

To simply even if I have just one loan, say a mortgage at 3.75% that currently has 300k left on it, my minimum monthly payment is 1435, say I put 60k on it in 4 months, and then in 6 months I start adding another 1200 to the payment for a year... how do I calculate the new payoff date and interest saved over just the minimum payment.

Answer 1

Answering your simplified question, here is a calculation to show the process. Note payments are made here straightaway at the beginning of the month. The method works by discounting everything to present value.

Note the summations in the above calculation can be converted to a convenient formula for hand-calculations:

s = (d ((1+r)^(1-m)-(1+r)^-n))/r

where

s is the present value principal
d is the periodic payment
m is the initial payment month (0 for straightaway, 1 for end of 1st period)
n is the number of periods
r is the periodic interest rate

(Computer algebra by Mathematica.)

Answer 2

Check out this chart here: http://mortgagevista.com/#m=2&a=345000&b=3.75&c=30y&d=1/2010&e=100&f=9/2016&g=60000&h=7/2016&G&J&M&P&oa&n&o&p&q&x

You left out some of the key details for your scenario, so in the linked example chart I made some assumptions for you ($345K loan with a 30yr term that originated in 1/2010). In my example if you paid $60K down in 7/2016 and also started making extra payments of $100 each month ($1200/year) starting 9/2016 the loan would be all the way paid off in 1/2032 and over the life of the loan you would save around $74,000 of interest.

Update the chart with the specific details of your loan and you should get an accurate representation just how much interest is saved and when the loan will be paid off in full.