Jump to content


Photo

Loan Amortization Tables and IPMT function

CUMIPMT PMT number format tables DATE IF

  • Please log in to reply
7 replies to this topic

#1 Tom Frank

Tom Frank

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 06 October 2016 - 03:29 PM

I have been attempting to build a loan amortization table as part of a larger solution, to create a debt  reduction 'snowball' plan, paying down high interest debts and then once balance reaches 0 you apply those funds to the next highest cost liability. A very helpful tool for those who have multiple liabilities.

 

My problem comes in when amortizing longer term liabilities, my loan amortization do not match the ones from the loan vendors.

I believe the issue may have to do that the banks like to compound interest more frequently, more % for them, than what I have laid out in my excel sheet.

 

Since the IPMT function uses the period of the loan ( Payment #) I am at a loss as to how to accurately model a loan that has DAILY interest but monthly payments.

 

Has anyone else encountered this problem?

 

Also appreciate any other ideas, end gola would be to build a sheet that sorts all liabilities by Interest cost, then once balance reaches 0 apply payments as extra to next highest cost liability.

Thanks

Tom F.

 

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 06 October 2016 - 06:21 PM

Hi Tom,

 

You should be able to calculate daily interest with monthly payments by using the CUMIPMT function. It's functionally identical to the IPMT function, but with the added ability to return the cumulative amount paid between any two periods.

 

You could use CUMIPMT to calculate a 365-period loan (or however many days the loan is for) and then use its arguments to return the cumulative values for each month.

 

For example, to get the cumulative daily interest paid in the first 30 days of a 1 year loan for $100,000, you could use the following formula:

=CUMIPMT(3%/365,365,100000,1,30,0)

 

Unfortunately this method won't apply very accurately to a loan that spans several years because years can have an inconsistent number of days, making accurately calculating the interest rate per period very difficult. The only way I can see to create an accurate calculation of daily interest is to know exactly how many days (periods) the loan covers and what the interest rate is for each period.

 

You could calculate this assuming that years are 365 days long, but I think it's unlikely that your bank is doing this. It may be worth querying the interest payments with your loan vendor as they may be able to supply you with the number of periods and interest rates that they are using.

 

It's also possible that rounding may be a factor - they may be rounding up the interest rate for each period, for example.

 

You can find our video lesson explaining the PMT function in: Expert Skills Lesson 3-3 Create an Excel PMT function using the Insert Function Dialog.

For rounding, see the video lesson: Essential Skills Lesson 4-3: Format numbers using built-in number formats.

 

You may also be interested to look at some other threads from a user creating amortization schedules, which you can find here and here.

 

I'm sorry I wasn't able to provide an 'instant fix', but I hope this gives you a starting point to work from.

 

Please feel free to reply, and if you are able to find a way to calculate the daily interest rate for the loan I can provide assistance with integrating the CUMIPMT function to calculate the interest.


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 Tom Frank

Tom Frank

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 06 October 2016 - 07:54 PM

Jonathan,

Thanks for the thoughts, I think you are on to something.

Would it be possible to count days between payment dates, since loans like U.S. based 30 year mortgage loans are payable on the SAME day every month.  Then use this # of days count to calculate the interest accrued since last payment?

Thanks

Tom



#4 Tom Frank

Tom Frank

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 07 October 2016 - 08:24 PM

OK so I trade the CumInt function to know avail, I went from understating the interest costs to overstating them.

Just did a quick run thru without to much  editing.

I feel like I am on the right track but just missing some key insight.

The interest costs would have to be the daily interest between the last due date and the next due date, it would of course adjust in reality,for WHEN the payments were actually received.

 

Not Sure How to attach my sample file?

Tom



#5 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 09 October 2016 - 01:50 PM

Hi Tom,

 

I have done some research into this and have discovered some very important key facts regarding the number of days per year used when calculating interest.

 

As I mentioned in my earlier post, 'true' years have an inconsistent number of days, leading to inconsistent results when calculating interest. For this reason, interest is always calculated using a fixed number of days per year (and usually per month).

 

Unfortunately, there is no 'one true' standard that is used worldwide, so different banks may use different numbers of days per year. The most common standard is 30 days per month and 360 days per year so this is a good starting point, but some loans may be calculated using different day count conventions.

 

I have attached a copy of your workbook with the Interest Pmt column modified to calculate daily interest assuming 360 days per year:

 

Attached File  Sample Loan Amort Daily.xlsx   39.2KB   58 downloads

Amortization.png

 

You'll notice that I have also placed your data within a Table to improve readability of both the data and formulas.

If you're not familiar with Tables, you can see how they work in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

If you'd rather not have the data in a Table, you can see how to remove it in the video lesson: Expert Skills Lesson 1-12 Format a table using table styles and convert a table into a range.

 

I also spotted an error in one of the formulas in column M, which I have corrected.

 

I hope this is closer to what you needed, but please feel free to reply if you have any more questions. You can find instructions for attaching files here.


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.


#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 12 October 2016 - 04:25 PM

Just an update to this - we've been contacted by a consultant who's pointed out some flaws in the above solution.

 

The solution provided above recalculates the interest based on the present value after each payment to account for extra payments. Unfortunately, this has the side effect of producing lower interest payments for later periods, which is not the case for most loans.

 

Unfortunately, accurately calculating interest in this way while accounting for additional payments is beyond the capabilities of Excel's built-in functions (such as IPMT and CUMIPMT). You would need to use more complex formulas to accurately calculate these numbers, and such calculations are outside my personal expertise and beyond the scope of what we can offer on these forums. We're able to answer any question relating to Excel and its features, but very advanced financial calculations such as this may be better directed at a financial expert.

 

IPMT and CUMIPMT are still useful for calculating interest when you don't need to account for extra payments, but they do not account for any rounding that your loan issuer may be applying so their results are unlikely to match the actual payments exactly and should usually only be used as an estimate.


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.


#7 Tom Frank

Tom Frank

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 13 October 2016 - 05:05 PM

Jonathan,

I am getting closer to what I would like to accomplish.

Daily interest is Balance * Rate /365 * days between payment dates.:

 

This gets me closer to current balance reflected on recent statement at least the loan is paying of at last payment of term, the trick may be the gap between close date of loan and first payment date which results in odd interest period, and any extra payments made previously.

 

Please take a look at payment date and use of DATE function.

 

My final goal would be to create an input page for multiple loans which would feed this data to multiple tables and then based upon interest rates apply additional payments to highest cost loan until  that loan reached a

0 balance and then apply sum of those payments to next highest loan.

 

I know how to use Boolean circuit of 'IF then' to stop payment to one loan but not sure how to create only as many amortization tables as there are loans on inputs pages?

Thanks for your help!

Tom



#8 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 15 October 2016 - 04:25 PM

Hi Tom,

 

Regarding the DATE function, I'm not really sure what you're trying to do from your post - can you attach a copy of an example?

You can find a video lesson explaining the DATE function in: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

It sounds like you're trying to model an automatic process that will cycle through each of your loans, automatically create amortization tables for them and automatically apply payments to them. This is something that really goes outside what a spreadsheet is designed to do and would be more suited to a computer program that can look at the available data and make appropriate decisions.

 

It sounds like you have already managed to calculate the appropriate data, so it might be easier to make the appropriate decisions and apply the payments manually rather than trying to develop a complex system that can process everything automatically.

 

The IF function will allow you to define logical operations, but it isn't designed for complex chains of logic.

The video lesson explaining how to use the IF function can be found in: Expert Skills Lesson 3-5 Use the IF logic function.

 

I hope this is some help, but feel free to reply as always. If you reply with an example of your DATE functions I will take a look and see if I can find the solution.


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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users