## Calculate the cumulative interest payments for a loan with a balloon payment

Oct 18

Written by: Charles Flock
10/18/2011 8:10 PM

11 Financial Calculations that you can’t do in EXCEL - Part 8 of 11
8. Calculate the cumulative interest payments for a loan with a balloon payment.
I know that there is a CUMIPMT function in EXCEL. But there is no parameter for the future value, even though there is one for IPMT. The same is true for CUMPRINC and PPMT. Assume that we have following loan information:

 rate 0.004166667 nper 120 pv -10,000,000.00 fv 7,392,447.56 type 0 pmt 58,459.00

There is no way to enter these parameters into the CUMIPMT function, because fv is not one of the parameters. If we enter the CUMIPMT, this is what we get.

The formula in B7 is =CUMIPMT(B1,B2,-B3,B4,B5,B6). And why does the pv value in this function have to positive? That’s certainly not the case in the IPMT function.
Here’s what the cumulative interest payment for periods 1 through 12 as calculated in SQL Server.
SELECT SUM(amt_int_pay)
FROM wct.PMTSCHED(
10000000   --pv
,58459      --pmt
,120        --numpmts
,7392447.56 --fv
,0          --float
)
WHERE num_pmt BETWEEN 1 and 12
This produces the following result.
----------------------
495317.309191749

(1 row(s) affected)
Here is another example of doing this calculation in SQL Server.
SELECT wct.CUMLIPMT(
10000000         --PV
,'2011-10-01'     --Loan Date
,.05              --Annual Rate
,'2011-11-01'     --First Pay Date
,120              --Number of Payments
,12               --Payments per year
,1                --Start Period
,12               --End Period
,360              --Days in Year
,7392447.56       --FV
,'U'              --Interest Rule
) as CUMIPMT
This produces the following result.
CUMIPMT
----------------------

495317.364478636

 Previous: Calculate the PRICE of a bond with a monthly coupon
 Next: Calculate the interest payment amount on a pay-in-advance loan

Tags:
Categories:

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < June 2023 >
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678
Monthly
Go