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