Login     Register

        Contact Us     Search

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

Oct 18

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

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:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service