Jump to content


Photo

Extra Payments Causing Negative Ending Balance Value

formulas PMT IF IFERROR

  • Please log in to reply
4 replies to this topic

#1 davisec52

davisec52

    Member

  • Members
  • PipPip
  • 11 posts

Posted 07 April 2014 - 03:17 PM

Hi.  I'm building a loan calculator, and although it works fine for the standard, scheduled payments, when I factor in extra payments, it gives a negative value in the ending balance as I don't know how to get the calculator to "prorate" the final payment.  I've uploaded the spread sheet and included two images to better explain the problem.  I'll also mention that the spreadsheet has two columns for extra payments--one for regular extra payments that can be populated automatically and second column for one-off payments.  Each payment for that column must be entered manually.

 

Many thanks again for help and advice.

 

Evan

 

PS: Please expand the images to view the issue I'm trying to explain.

Attached Images

  • endingbal.png
  • endingbal2.png

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 07 April 2014 - 04:00 PM

Hi Evan,

 

I can see two ways that you could achieve this.  One is to re-calculate the Scheduled Payment value after your extra payments, effectively deducting the extra payments from the loan amount.  This would require you to re-structure your spreadsheet so that a variable payment rate is possible.  Your current structure has a fixed scheduled monthly payment, so I'm assuming that you don't want to do this.

 

The alternative is to use the IF function to reduce the value of the final payment if it would exceed the value of the loan.  You can do this by changing the formula in column I to:

=IF((D54-H54+F54+G54)>C54,C54,(D54-H54+F54+G54))

 

This checks whether the Principal calculation will result in a higher value than the remaining balance of the loan.  If it does, it simply sets the final payment to the balance remaining.

 

If you're not familiar with the IF function, you can find a detailed explanation of it in the video lesson: Expert Skills Lesson 3-5: Use the IF logic function.

 

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

 

Attached File  extrapayments.xlsx   36.82KB   198 downloads

dataschedule.png

 

As you can see, the final balance now always comes to zero.

 

If you need any more assistance with this, please feel free to reply.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 davisec52

davisec52

    Member

  • Members
  • PipPip
  • 11 posts

Posted 07 April 2014 - 05:45 PM

Jonathan,

 

Got it, thank you.  I've just recently completed the lessons, but it is taking a me a bit to actually learn how to think with the material. I will take some time to rework the spreadsheet.  I would also appreciate knowing how to setup a variable payment rate as you mentioned above; that would probably simplify the spreadsheet.  Could you refer me to the lessons I should review to accomplish that?

 

Evan



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 07 April 2014 - 08:20 PM

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. 

 

Attached File  extrapayments.xlsx   37.82KB   178 downloads

scheduledpayments.png

 

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.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 davisec52

davisec52

    Member

  • Members
  • PipPip
  • 11 posts

Posted 08 April 2014 - 01:46 PM

Thank you once again.  This helps a great deal--not only in helping with a solution but also helps me see how to work with, or manipulate the various parts of a spreadsheet to solve a problem.  I will probably be back with more questions.

 

Evan







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users