Variable Payment Calculator

**Openjim** · 09-29-2011, 11:27 AM

Hi, I am trying to create a table for variable repayments for a fixed loan amount that updates the remaining monthly payment amounts based on what you paid in the previous month.

e.g.
loan of 1000 repaid over 20 months
I want a column showing what each payment would be each month (50) but if only 25 is paid in one month (entered in adjacent column) the remaining payment amounts to change to reflect this. I want the extra 25 spread out over all the remaining payments, not just put on the next one.

I would also like the converse to be true, if an overpayment is made the payments each month afterwards should be correspondingly lower.

I've tried to do this with a few fomulas but nothing I do can get this to work, I either end up repaying early or too much.

Can anyone help?

**alansidman** · 09-29-2011, 11:51 AM

Will the attached work for you?

Alan

**Openjim** · 09-29-2011, 12:05 PM

Hi Alan,

Yes that does work!

And it does more than I was asking for too, I shall get this integrated into my spreadsheet pronto.

Thanks!

**ViPro** · 09-25-2014, 10:32 PM

I tried this out using my own mock numbers but I don't know if what I am seeing makes sense.

I have a loan of 62000, an interest rate of 6.7%. I would like to pay it in 6 years, or 72 months. I took the spreadsheet and made the following changes:

1. C3 to 72
2. A5 I did =(A4+1), then copied this down the column to create about 75 rows
3. Copied the 20th row in your sheet to the rows below so I ended up with another 55 or so rows, all autocalculating

The sched Payment column is all messed up, even before I started, showing negative values and all kinds of messy stuff. I'm guessing it's something I did and didn't realize?

Also the Interest column leaves me suspicious. APR at 6.7% so that's 0.558% monthly. interest on a 62000 amount should then be say, 345 or so, but the first Interest cell in the set of rows says only $57 or so.

So I'm confused, did my adding all my extra rows mess this up? I'm using Excel 2013 if that affects this.

Thank you.

**protonLeah** · 09-25-2014, 10:36 PM

ViPro,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**wootenhuber361** · 03-08-2021, 05:45 AM

Set the "Loan Date" and "First Payment Due" so that the time between them equals one full period, as set in "Payment Frequency," if you want to match other calculators. For instance, if the "Loan Date" is May 15th and the "Payment Frequency" is "Monthly," the "First Payment Due" should be June 15th, IF you want a traditional interest calculation. Regard,