CUMIPMT with balloon payment

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

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Paul" wrote in message
om...
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

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Norman Harker" wrote in message
...
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

Excel and Word Function Lists (Classifications, Syntax and
Arguments)
available free to good homes.
"Paul" wrote in message
om...
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

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

"Norman Harker" wrote in message ...
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

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" wrote in message ...
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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Norman Harker" wrote in message
...
Hi Paul!
--SNIP--
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.

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Paul" wrote in message
om...
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" wrote in message
...
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

Excel and Word Function Lists (Classifications, Syntax and
Arguments)
available free to good homes.
"Norman Harker" wrote in message
...
Hi Paul!
--SNIP--
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.