CUMIPMT with balloon payment

[Paul]

Hi,

I want to use CUMPRINC and CUMIPMT in an amortization for a loan that
includes a balloon payment. CUMPRINC handles this perfectly when I
subtract the balloon amount (fv) from the principal (pv), like so:

CUMPRINC(rate, nper, pv-fv, start_period, end_period, type)

However, when I try the same thing with CUMIPMT it generates incorrect
values. That is, when compared with a running total of values
generated by IPMT, the CUMIPMT values are always less.

For example, the following returns -100:

=IPMT(0.1,1,10,1000,-100,0)

However, the following returns -90:

=CUMIPMT(0.1,10,900,1,1,0)

Any ideas?

Paul

 

[Norman Harker]

Hi Paul!

With:
=IPMT(0.1,1,10,1000,-100,0)

You are paying interest on 1000 in period 1
1000*.1 = 100
Sign change needed to follow sign convention used by Excel


With:
=CUMIPMT(0.1,10,900,1,1,0)

You are only paying interest on 900 in period 1

To get the interest payments with a balloon loan using CUMIPMT use:

=CUMIPMT(rate,nper,pv,start_period,end_period,type)+(fv*rate)

By deducting the balloon amount from the PV you are making a
conceptual error of completely ignoring the balloon.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Paul!

Formula given was for a single payment for a series of payments it's:

=CUMIPMT(rate,nper,pv,start_period,end_period,type)-(fv*rate*(end_peri
od-start_period+1))

The thing about balloon loans is that the payment comprises interest
only. Accordingly the cumulative interest on the balloon element is
the number of payments counted multiplied by the interest on the
balloon for one period.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Paul]

No, the signs are correct. The IPMT formula below returns -100, as it should.

 

[Paul]

I appreciate your responses, but unfortunately your formulas don't
work. The values they return still do not jibe with summing the IPMT
results.

Paul

 

[Norman Harker]

Hi Paul!

Sorry but I say they do!

Using IPMT:
=IPMT(10%,1,10,100000,-50000,0)
Returns: -10000
=IPMT(10%,2,10,100000,-50000,0)
Returns: -9686.27302558744
=IPMT(10%,3,10,100000,-50000,0)
Returns: -9341.17335373363

Sum: -29027.4463793211

My CUMIPMT approach using:
=CUMIPMT(rate,nper,pv,start_period,end_period,type)-(fv*rate*end_perio
d-start_period+1)

=CUMIPMT(10%,10,50000,1,1,0)-(50000*10%*1-1+1)
Returns: -10000
=CUMIPMT(10%,10,50000,1,2,0)-(50000*10%*2-1+1)
Returns: -19686.2730255874
=CUMIPMT(10%,10,50000,1,3,0)-(50000*10%*(3-1+1))
Returns: -29027.4463793211

That checks out for a single period, for periods 1&2, and periods
1,2&3

And:
=CUMIPMT(10%,10,50000,2,3,0)-(50000*10%*(3-2+1))
Returns: -19027.4463793211

Which checks out with the IPMT return for periods 2 and 3.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.