Hi Evan,

You should be able to achieve a 'variable' payment rate by moving your **PMT** function into the *Scheduled Payment* column instead of calculating the payments once at the top of the worksheet. You can then recalculate the scheduled payments for the number of periods remaining based on the current balance of the loan. The formula would look something like:

**=-PMT($D$3/$D$5,$D$8-(A17-1),C17)**

Of course, if regular extra payments are being made the loan may be paid before the projected number of periods. This is probably exactly what you want to happen, but it can cause errors in the formulas since they are expecting a fixed number of periods (in this case 60). To prevent errors from appearing on the workbook, I've added an IFERROR function:

**=IFERROR(-PMT($D$3/$D$5,$D$8-(A17-1),C17),0)**

The PMT function is covered in depth in the video lesson: Expert Skills Lesson 3-3: Use the formula palette and the PMT function.

The IFERROR function is covered in: Expert Skills Lesson 3-23: Use an IFERROR function to suppress error messages.

You're also using absolute references quite extensively (eg. $D$3). If you'd like to review the lesson on these, see: Essential Skills Lesson 3-12: Understand absolute and relative cell references.

I have attached a copy of your spreadsheet with the new formulas applied.

**extrapayments.xlsx** **37.82KB**
178 downloads

As you can see, the *Scheduled Payment* amount now reduces every time an additional payment is made. This can still result in a negative final period value if extra payments are made every month, so I have kept the IF formula in place to prevent this.

As usual, please feel free to reply if you need any more assistance with this.