Calculate the interest payment amount on a pay-in-advance loan
Oct
18
Written by:
Charles Flock
10/18/2011 8:10 PM
11 Financial Calculations that you can’t do in EXCEL - Part 9 of 11
9. Calculate the interest payment amount on a pay-in-advance loan
Enter the following data in a worksheet.
Enter this formula in cell B6: =PMT(B1,B2,B3,B4,B5). Enter this formula in C6: =PMT(C1,C2,C3,C4,C5). You should get the following result.
This represents a very simple interest-only loan structure for 36 months. The monthly payment in column B represents a payment made at the end of the month. The monthly payment in column C represents a payment made at the beginning of the month. The monthly payment in Column B is equal to the monthly payment in column B divided by 1 plus the rate.
Since we know that this is an interest only loan, the monthly payment and the interest payment should be the same. Let’s add another row to the worksheet to keep track of the period.
Enter the following formula in B8: =IPMT(B1,B7,B2,B3,B4,B5). Enter the following formula in C8: =IPMT(C1,C7,C2,C3,C4,C5). You should get the following result.
As you can see, C8 has returned a value of zero. Even though we know that no principal is included in the monthly payment (since the fv is equal and opposite in value to the pv), the EXCEL IPMT calculation for the first period is zero.
Let’s do one more thing. In B9 enter =PV(B1,B2-B7,B6,B4,B5) and in C9 enter =PV(C1,C2-C7,C6,C4,C5). Put the label PV in A9. You should get the following result.
EXCEL has correctly calculated that the PV has not changed, which should mean that there has been no principal payment. In B10 enter =PPMT(B1,B7,B2,B3,B4,B5) and in C10 enter =PPMT(C1,C7,C2,C3,C4,C5). You should get the following result.
So, we can see that EXCEL has recorded the first payment as a reduction in principal. Even though we know that it is interest since the entire loan balance is paid off at the end. The only thing that should have happened is that the interest payments should have been discounted from the end of the period to the beginning of the period, just like payment amounts.
Here’s how we can get that result in SQL Server.
SELECT amt_int_pay
FROM WCT.PMTSCHED(100000,497.51,36,100000,0)
WHERE num_pmt = 1
This produces the following result.
amt_int_pay
----------------------
497.51
We should admit ahead of time that the XLeratorDB IPMT function returns exactly the same results as the EXCEL IPMT function. But, we are in good company as Google Docs and OpenOffice do too.