help with IPMT formulas for advance payments on a loan amortization schedule

[a2424]

hi,

I am having issues with IPMT formulas to calculate the interest for advance payments, for the last payment in the 24 month example. In the attached spreadsheet (example I have taken from the internet) i have an amortization schedule with a residual value.

When cell D9 is set to END - arrears payments then my formula in column O (IPMT arrears) works perfectly for each payment month, (cell O10 = cell F10), however when i switch cell D9 to BEGINNING - advance payments then my formula for the last month does not work (cell M36 is showing number error, because my per is greater than the nper ( I think )), every other line in the formula works M13 - M35 however its the last month which errors. Is there a simple correction to what the formula should be for cell M 36?

many thanks in advance for your help

[Estevaoba]

I guess the PERIOD argument should be 1, not 2 for the first period.
That results zero interest on first payment, since it is paid on beginning of period.

[hrlngrv]

Semantics: when loan payments are made at the BEGINNING of each period, the first such loan payment is an immediate reduction of the loan amount. THERE SHOULD BE NO INTEREST PAID. That is, if the loan amount if 200,000.00, you'd receive it on 1 Jan 2017, AND you make an 8,153.59 loan payment on 1 Jan 2017, you're immediately paying down the loan and have incurred NO INTEREST.

Anyway, the cause of the #NUM! error in M36 is =IPMT($D$5/12,C36+1,$D$6,-$D$4,$D$10,$E$9). C36+1 = 25, while D6 = 24. IPMT doesn't allow interest payments after the end of the loan term.

Getting back to the semantic point above, while the balance upon which interest would be incurred in month 1 would be D13-E13, that interest would be paid with the 2nd loan payment at the beginning of month 2, not at the beginning of month 1. In other words, your amortization table is wrong in month 1 for Beginning.

The amortization table you're using is meant for variable interest rates, which is why it recalculates the loan payment in each row in E13:E372 for the months remaining. Perfectly reasonable for that, but that scenario invariably calls for a residual value of 0. For positive residual value AND payments at the Beginning of each period, you have to make adjustments to produce exact amortization table results.

Also, for Beginning repayment type, the residual value is realized at the END of the loan period, so in your example at the END of 24 months. Thus the balance after the 24th payment at the BEGINNING of the 24th month should be 14,936.52. That incurs interest between the beginning and end of month 24, and balance at the beginning of month 24 plus interest incurred during month 24 produce 15K residual value at the END of month 24. One implication of this is that Excel's documentation is wrong for PMT and IPMT. This should not come as a shock as it's not the only error, though MSFT's apparent indifference to correcting them is impressive.

I've added simple amortization tables with fixed interest rates in 2 additional worksheets. I've also made a fair number of changes in your Example worksheet. The Beginning worksheet shows 2 amortization tables. The 1st producing the residual value immediately after the final payment (so at the beginning of month 24, which is effectively the end of month 23 considering either a second before or after midnight). The 2nd producing the residual value at the end of the 24th month. The 2nd uses the standard PMT(...,1) value. The 1st requires an adjustment: =PMT(i,m,-P,F*(1+i),1), where i is the effective monthly interest rate, m the number of months, P the original nominal loan amount, F the residual value. The adjusted formula pays off less principal as of the last payment at the beginning of month 24. Combining that with variable interest rates requires adjusting the -P term too.

[a2424]

Thank you for your long and comprehensive response, i have learned a lot from your explanation.

great job!!

[joeu2004]

when loan payments are made at the BEGINNING of each period, the first such loan payment is an immediate reduction of the loan amount. THERE SHOULD BE NO INTEREST PAID

That is certainly what the programmer of IPMT thought. But that is wrong. It is a defect in IPMT for type=1.

Interest is always charged at the end of the period, based on the balance at the beginning.

The only difference between beginning and ending payments is what the balance at the beginning is.

To demonstrate, use the FV function to calculate the remaining balance after the first payment.

	D	E
4	200,000.00	Loan
5	5.10%	Annual rate
6	24	#Payments
7	8,153.59	Pmt
8
9	1	Pmt type
10	15,000.00	Final balance
11
12	192,661.76	Remaining balance
13	7,338.24	Principal
14	815.35	Interest

Please Login or Register  to view this content.

If there were zero interest for the first period, the remaining balance would 200,000.00 - 8,153.59, which is 191,846.41, because the entire payment would allocated to principal.

But obviously, that is not the case.

Instead, for the first period, the beginning balance is 200,000.00 - 8,153.59, which is 191,846.41, instead of 200,000.00.

At the end of the period, the interest is 191,846.41 * 5.10%/12, which is 815.35.

The interest is added back to the loan balance, so the remaining balance becomes 191,846.41 + 815.35, which is 192,661.76.

[a2424]

now i am confused

[joeu2004]

now i am confused

I can certainly understand why. I will post a correct amortization schedules in the next hour. Please look back.

But the important take-aways are: IPMT and PPMT are flawed for type=1; and you do not need either function to construct an amortization table.

Not surprisingly, CUMIPMT and CUMPRINC have the same flaw for type=1. I can provide correct alternatives, if you need them.

[a2424]

that would be amazing if you could post correct schedules!
also, yes, that is my next step to look at doing cumipmt and cumprinc for advance payment schedules (similar to before with a residual value at the end).
my end result would be a report where by i can select the number of remaining months and it would calculate the cumipmt and cumprinc for a set number of months within the amortization schedule

[joeu2004]

that would be amazing if you could post correct schedules! also, yes, that is my next step to look at doing cumipmt and cumprinc

The attached Excel file has a bare-bones amortization schedule for beginning and ending payments.

Since I am having trouble with this site's API, I will keep my comments brief and unstructured. Sorry.

-----

First, I have a bit of a terminology problem. Previously, I wrote: ``Interest is always charged at the end of the period, based on the balance at the beginning. The only difference between beginning and ending payments is what the balance at the beginning is``.

That is correct, as I meant it. But in the amortization schedule, we want the "beginning balance" to be "balance before payment". That way, we know what is owed if we pay off the loan at the beginning of that period.

I hope it is clear enough if I say: ``The only difference between beginning and ending payments is what the balance at the beginning is for the purpose of calculating interest``.

You have the correct formulas in your Excel file.

For beginning payments, interest is (B8-C8)*$B$3/12, where B8 is beginning balance, C8 is the payment, and B3 is the annual rate.

For ending payments, interest is H8*$B$3/12, where H8 is beginning balance.

-----

For ending payments, the payment is PMT(B3/12,B4,-B1,B2,0), where B4 is number of payments, B1 is loan (or lease) amount, B2 is "residual value" (aka "balloon payment", "final balance").

For beginning payments, the payment is PMT(B3/12,B4,-B1,B2,1). Note that the only difference is the payment "type" parameter.


-----

You might notice that for each period, the amount of principal and the ending balance are the same for beginning and ending payments.

That is correct. The only difference between the two payment types is the amount of interest and the different payment amount to account for the difference in interest.

----

Finally, you will find alternative calculations for CUMIPMT and CUMPRINC after the amortization schedule.

Given a start and end period number, the cumulative principal is FV(B3/12,A36-1,C8,-B1,1) - FV(B3/12,B36,C8,-B1,1) for beginning payments. Change type=1 to type=0 for ending payments. But they should be the same.

The cumulative interest is the cumulative payments minus the cumulative principal, to wit: (B36-A36+1)*C8 - E36.

-----

I apologize for the brevity and incompleteness of this explanation. It seems to me that you have the skills to understand the formulas in the Excel file attachment.

If you need a better explanation, do not hesitate to ask.

[a2424]

WOW TOP CLASS RESPONSE, this is absolutely amazing - this answers everything i need to know, then I can apply the same method to quarterly payments as well.

thank you so much

your explanation is so clear

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[hrlngrv]

That is certainly what the programmer of IPMT thought. But that is wrong. . . . Interest is always charged at the end of the period, based on the balance at the beginning.

I'm up for arguing semantics. Especially when it comes to interest rate theory.

Note first that when payments are made at the BEGINNING of each period, so the 1st payment would be made at time 0, the interest accrued at that point is . . . wait for it . . . 0. Thus, the interest paid with the 1st payment is, in fact, zero, and the 1st payment is entirely principal. Thus, PMT(i,n,-P,0,1) = PMT(i,n-1,PMT(i,n,-P,F,1)-P,0,0). That is, immediately after the 1st payment at inception, the payment stream becomes identical to n-1 payments of the same amount at the END of each period for a loan amount of the nominal loan amount less the 1st payment. This dovetails nicely when adding in a residual value, F.

PMT(i,n,-P,F,1) = PMT(i,n-1,PMT(i,n,-P,F,1)-P,F/(1+i),0)

Note the /(1+i) term. Why is it needed? Because ALL Excel's annuity functions realize the residual value as of the end of the loan term, meaning the END of the final payment period. This for PMT(i,n,-P,F,1) and PMT(i,n,-P,F,0), F is realized at the END of n periods. That specific detail is where Excel's documentation is inconsistent with annuity functions' implementation. In the identity above, PMT(i,n,-P,F,1) gives F at the end of n months. PMT(i,n-1,PMT(i,n,-P,F,1)-P,x,0) would give F at the end of n-1 months, but that'd be off by 1 month, so one needs the PV of F at 1 month, thus F/(1+i).

Anyway, PMT(i,n,-P,F,x) = IPMT(i,t,n,-P,F,x) + PPMT(i,t,n,-P,F,x) for all integers t = 1 to n and x = 0 or 1. Also, t means ordinal payment number, NOT point in time. Thus, for t for x = 0 refers to the same point in time as t+1 for x = 1, leading to the notion that point in time = t - x. Thus the 1st payment for Beginning is at t - x = 1 - 1 = 0, while the 1st payment for End is at t - x = 1 - 0 = 1. Implying that PPMT(i,1,n,-P,F,1) = PMT(i,n,-P,F,1), so IPMT(i,1,n,-P,F,1) = 0 since that is at point in time t - x = 1 - 1 = 0, a time at which NO INTEREST HAS BEEN INCURRED.

Sure looks to me like Excel gets that correct.

Note: the distinction is essential. The 2nd argument to IPMT and PPMT is NOT point in time. Rather, it's the ordinal payment number. The corresponding point in time for any 2nd argument to IPMT and PPMT is one month earlier for Beginning than End payment types.

. . . If there were zero interest for the first period, the remaining balance would 200,000.00 - 8,153.59, which is 191,846.41, because the entire payment would allocated to principal.

But obviously, that is not the case. . . .

But that most definitely is the case at TIME ZERO with the 1st payment made at the BEGINNING of that 1st period. Again because the 1 as 2nd argument to IPMT and PPMT when the 6th argument is 1 means TIME ZERO, not time 1.

. . . Instead, for the first period, the beginning balance is 200,000.00 - 8,153.59, which is 191,846.41, instead of 200,000.00.

At the end of the period, the interest is 191,846.41 * 5.10%/12, which is 815.35.

The interest is added back to the loan balance, so the remaining balance becomes 191,846.41 + 815.35, which is 192,661.76.

192,661.76 is the balance immediately before the 2nd payment, but 815.35 is the interest paid with the 2nd payment, thus IPMT(i,2,n,-P,F,1) if one is cognizant of the precise definition of IMPT's (and PPMT's) 2nd argument.

You are assuming IPMT's/PPMT's 2nd argument is something it isn't both by implementation and documentation. Maybe there's financial texts or calculators which have functions labeled IPMT and PPMT, and maybe their corresponding arguments do refer to point in time rather than ordinal payment number, but that ain't Excel. I'd note that A LOT of other texts, spreadsheet programs (Lotus 1-2-3 and Quattro Pro), and other financial software do not bother with sign convention as Excel does. Paying attention to actual conventions used is a necessity with financial functions.