Results 1 to 5 of 5
Thread: CUMPRINC formula (2000 sr3)

20041115, 23:05 #1
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 234
 Thanks
 6
 Thanked 0 Times in 0 Posts
CUMPRINC formula (2000 sr3)
Have a worksheet where I want to show an ending balance after specified months of payments. I found the CUMPRINC formula which returns the principle paid. I subtract that from the PV to give me the result I want...However, we know payments are rarely computed to be only two decimals, so rounding must occur. for example, $11000 @ 10% for 5 years is a payment of $233.7174919. However for practical purposes, the persons payment would be $233.72. Then, at the end of the loan, the final payment would be adjusted to account for the approximate $.002508104 in over payment the person was paying each month.
Ok now to my problem: the CUMPRINC formula assumes the person is actually making the EXACT payment of $233.7174919 and returns a value that, for that period is incorrect because the person in fact is making a slightly different payment. Since the period I'm calculating is not the final loan period, the adjustment doesn't come into play yet. I realize that this doesn't amount to a large amount, but the documents I'm preparing must be EXACT for legal purposes.
any ideas, work arounds, or am I mixed up?

20041116, 00:14 #2
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: CUMPRINC formula (2000 sr3)
It depends on how exact you are trying to be....
For example, the calculation you cited ($11,000 loan at 10% over 5 = 233.72/month) is "inaccurate," to the extent of a $0.0025 overpayment every month for a total of $0.0025 * 5 * 12 ~ $0.15. This is by far the lesser inaccuracy in the calculation, however, since it assumes that payments are made every twelfth of the year. In fact, onetwelfth of a year of 365 days is 30.42 days. Since payments are always credited at a particular day, each payment will be received either 0.42 days "in advance" or 0.58 "late" (and this is ignoring the longmonth / shortmonth difference  this is just the effect of crediting payments or debiting advances an integer number of days apart). At inception, this inaccuracy is worth: $11,000 * 10% / 365 = $3.01 for a full day (and prorata for the long or short days identified above). These differences will be much more than the 15 cent overpayment.
If this level of inaccuracy is a problem, then your only choice is to:
<UL><LI> Set up an amortization schedule that accurately reflects the number of days between scheduled payments
<LI> Adjust for any payment dates that fall on weekends or statutory holidays
<LI> Calculate interest due to the payment date, rounded to two decimal places. If not stipulated in the loan agreement, the usual practice is to use conventional rounding, with $0.005 rounded up to the nearest multiple of one cent  but other conventions are possible
<LI> Apply the balance of any payments to the outstanding principal for calculation of the interest in the subsequent period
<LI> Note that your example does not explicitly adjust for compounding effects  with payments made monthly the interest is compounded on every payment date, so the effective interest rate is (1 + 12%/12)^12  1 = 12.68% This may be what you have in mind, but it should be explicit in the loan agreement if you are taking this degree of care in the calculation
<LI> Consider how you want to handle leapyears. I have seen (not commonly) loan agreements that stipulate that daily interest is calculated as the principal balance * face rate * the number of days outstanding / number of days in the year. Our lawyers told us that this implied a different calculation for days of 365 and 366 days.
<LI> Determine the ending balance at the time you wish, based on the calculations above.[/list]In practice, so long as payments are made on the first business day of each month (or at least, on the same day each month) I have never seen a banker or lawyer worry very much about (only) getting onetwelfth of a year's interest even when a loan has been outstanding for 31 days, nor have I ever seen anyone worry about the rounding differences either in calculation of payments to the nearest penny, or the accumulation of those minor differences over the term of the loan  and that includes dealing with loans in the multimillion dollar range.

20041116, 01:04 #3
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 234
 Thanks
 6
 Thanked 0 Times in 0 Posts
Re: CUMPRINC formula (2000 sr3)
Thanks for the lesson.
I think you're right in that there seems to be generally accepted assumptions made when computing such things, and that the few cents one may be off is going to be ok...certainly difficult to challange in a court to any monetary gain.
thanks,

20041117, 01:05 #4
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: CUMPRINC formula (2000 sr3)
<hr>Thanks for the lesson.<hr>I didn't mean to be snotty about it  I hope it didn't come across that way....
If you are required to produce a loan agreement (or similar document) that stipulates the outstanding balance at the end of the term, given an initial advance amount, an interest rate, and the periodic payment amount, the value returned by CUMPRINC will be close enough that you can justify it as a reasonable calculation based on the (equally reasonable) assumptions that payments are made at equal intervals throughout the year, and so on  the potential difference from calculating the exact number of days between payments and applying them on the due date or the first business day following will be greater than the roundoff error from using CUMPRINC. If your calculation of the ending balance is different than mine by some small amount then the difference is perforce in the calculation of interest  and in this case your interest calculation was reasonable (and I presume that it matches the requirements in the loan documentation).
On the other hand, if you need to stipulate the interest expense for each period it is nice if <font color=blue>advance  total payments + total interest = ending balance.</font color=blue> If you are in this situation then CUMPRINC may be a problem because of the rounding assumption you have identified  but if you ARE faced with that, you already have to have an amortization schedule ready  whether it calculates to the day, or just determines monthly interest as <font color=blue>outstanding balance * face rate / 12.</font color=blue> Since you have an amortization schedule handy, it is just as easy to use it (with calculations appropriately rounded...) to determine the ending balance.

20041117, 14:12 #5
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 234
 Thanks
 6
 Thanked 0 Times in 0 Posts
Re: CUMPRINC formula (2000 sr3)
no, you did not come across snotty, I meant "lesson" in the most positive way. I enjoy detailed explanations because it helps me with the complete understanding. I appreciate the time & though you put into helping me. And on a big scale, am grateful for Woody's lounge and its participants for helping me so many times over the years. The answers given have made me the office smart guy on such matters, but I really can't take much credit.
Thanks again,