Jump to content


Photo

Auto Population of Payment Dates for Loan Calculator

formulas DATE ROUND

  • Please log in to reply
4 replies to this topic

#1 davisec52

davisec52

    Member

  • Members
  • PipPip
  • 11 posts

Posted 06 April 2014 - 03:58 PM

Hi all,

 

I have created a standard loan amortization calculator very much like the one exampled in the lessons.  However, below the calculator, I am creating a range that will autopopulate data over a 30 year period for the following columns: Payment Number, Payment Date, Starting Balance, Scheduled Payment, Interest, Principal, and Ending Balance.

I am so far able create the range by adding the formulas to the columns and using Excel's iterative capability to populate data for all columns except the Payment Date Column.  Whereas I can get the spreadsheet to calculate all other information, when I make a change to the Payment Date, the dates corresponding to the individual payment periods do not update.

Anyone have any suggestions on how to use the date formulas to correct this?

Thanks,
Evan

 

 

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 06 April 2014 - 09:47 PM

Hi Evan,

 

It is hard to tell without seeing the spreadsheet in question, but it sounds like you just want a formula that allows you to automatically fill down dates.  If you wanted each row to represent the day after the previous row, you could simply add 1 to the day in the previous row.  (eg =A2+1).

 

This works because of the way that dates are stored as 'serial numbers' within Excel.  For a full explanation of date serial numbers, see the video lesson Expert Skills Lesson 3-7: Understand date serial numbers.

 

Alternatively, you can use the DATE function to create a formula similar to: =DATE(YEAR(B2),MONTH(B2),DAY(B2)+1)

For more detailed instructions on using the DATE function, see the video lesson: Expert Skills Lesson 3-11: Use the DATE function to offset days, months and years.

 

Either of these formulas should allow Excel to automatically fill down any date interval that you require.

 

I have attached an example spreadsheet showing both formulas in action.

 

Attached File  dates.xlsx   9.27KB   207 downloads

dates.png

 

Please feel free to reply if you need any more assistance with this.  If you'd like to attach your spreadsheet, you can find the Attach Files options by clicking More Reply Options when replying.


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 - 04:15 AM

Thank you very much, that did the trick. I do have several other questions, and so I'll go one at a time.  I have also uploaded the spread.  The first question is how to reduce the last extra payment so that the last line gives 0.00 instead of a negative amount.  I have applied conditional formatting to hide all lines coming after the loan payoff.

 

 

Evan

 

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 07 April 2014 - 09:18 AM

Hi Evan,

 

We usually ask that you create a new thread for each question so that they can be found more easily.  No problem this once, but it would be great if you could post new threads for any future questions.

 

The reason you're seeing a "negative zero" value is that your formulas are returning values that have more than 2 decimal places.  You're seeing ($0.00), but the real value is ($0.00000000000034).  The formatting that you've used hides the decimal places after the first two, but Excel still knows that they are there in the background and treats the number as negative.

 

There are several ways that you could solve this, but I suggest using the ROUND function to round the results of your formulas to 2 decimal places.  Your formula will then look something like:

=ROUND(C76-(D76-H76+F76+G76),2)

 

...of course, rounding up to 2 decimal places will slightly change the results of your calculations. Best practice is to apply rounding wherever the values are first calculated, so you might prefer to leave this formula alone and apply the rounding to the other calculations (eg. Scheduled Payment).

 

For more on decimal places in Excel and how to solve 'penny rounding' problems like this one, see the video lesson: Essential Skills Lesson 4-3: Format numbers using built-in number formats.

 

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

 

Attached File  pennyrounding.xlsx   16.89KB   187 downloads

pennyrounding.png

 

Once again, please let me know if you need any more help with this issue.


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 07 April 2014 - 02:52 PM

Jonathan,

 

Thank you again!  I'll review the lessons and use the rounding function in the future.  My question had to do with the negative value in the ending payments range when you make extra payments.  I will open a new thread for the new question where I'll provide an image.

 

Evan







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users