IPMT Function DOESN'T WORK!

  • Thread starter Thread starter Saar
  • Start date Start date
S

Saar

I have read all the answers, and even the snide remark to
dave on 3/17. Also his answer to it and agree with him.
I did his assignment exactly as laid out. IPMT DOESN'T
WORK PROPERLY. IT DOESN'T PUT INTEREST INTO THE FIRST
MONTH AS IT SHOULD WHEN SETTING PAYMENTS TO THE FRONT. Is
there a MVP out there that can tell me, if not this dave,
how to get this bug to Microsoft? And please no "you
don't know what your doing" things. As a CPA I know
what "should" be happening, but as an interested user of
Excel, I also know what isn't happening.

Thanks

Saar
 
I have read all the answers, and even the snide remark to
dave on 3/17. Also his answer to it and agree with him.
I did his assignment exactly as laid out. IPMT DOESN'T
WORK PROPERLY. IT DOESN'T PUT INTEREST INTO THE FIRST
MONTH AS IT SHOULD WHEN SETTING PAYMENTS TO THE FRONT. Is
there a MVP out there that can tell me, if not this dave,
how to get this bug to Microsoft? And please no "you
don't know what your doing" things. As a CPA I know
what "should" be happening, but as an interested user of
Excel, I also know what isn't happening.
Click to expand...

If you don't want to be treated like an imbecile, don't act like one.

BTW, CPA? BFD! As Arthur Andersen proved, CPAs have been known from time to time
to keep their heads where the sun don't shine.

You may know what 'should' happen, but you don't know what IPMT is doing. In the
case of building up a fund, with the first payment made at the *beginning* of
the first period with no prior balance, IPMT returns the interest accrued as of
the *beginning* of the period. That is,

IPMT(Rate,1,NPer,,-FV,1)

returns the interest accrued on the accumulating balance at the *beginning* of
the given period. As there is no balance prior to the initial payment, there's
no prior balance from which to generate interest at the time of the 1st payment.

For payments made at the end of the period,

IPMT(Rate,1,NPer,,-FV,0)

also returns zero because again there's no balance prior to the first payment,
so again no interest acrued at the time of the 1st payment.

As for a loan of 75,000 with monthly payments over 18 years at 6.5% APR with
payments at the beginning of each period, the monthly payment would be $586.74,
and the interet paid in the first payment is zero, i.e.,

=IPMT(APR/12,1,NPer,-PV,,1)

because IPMT follows custom in which initial loan payments made at the inception
of the loan reduce the face value loan amount by the amount of the initial
payment. If the payment were made at the end of the first period, then there'd
be positive interest, i.e.,

=IPMT(APR/12,1,NPer,-PV,,0)

would return $406.25 (and the loan payment would be $589.92). It's the absence
of a prior balance that leads IPMT to return zero in period 1.

That so, oh ye of more credentials than imagination, it's possible to manipulate
IPMT to give the desired results.

=IPMT(APR/12,m,NPer,PMT(APR/12,NPer,,FV,1),FV-PMT(APR/12,NPer,,FV,1),0)

This gives the intended results of $0.99 for m=1, $24.17 for m=24, $404.06 for
m=216. A little cleverness, a little effort, and you can usually make Excel work
like you want it to.

So, not a bug, nothing to report to M$FT.
 
Wonderful answer and explanation Harlan

: "Saar" wrote...
: >I have read all the answers, and even the snide remark to
: >dave on 3/17. Also his answer to it and agree with him.
: >I did his assignment exactly as laid out. IPMT DOESN'T
: >WORK PROPERLY. IT DOESN'T PUT INTEREST INTO THE FIRST
: >MONTH AS IT SHOULD WHEN SETTING PAYMENTS TO THE FRONT. Is
: >there a MVP out there that can tell me, if not this dave,
: >how to get this bug to Microsoft? And please no "you
: >don't know what your doing" things. As a CPA I know
: >what "should" be happening, but as an interested user of
: >Excel, I also know what isn't happening.
:
: If you don't want to be treated like an imbecile, don't act like one.
:
: BTW, CPA? BFD! As Arthur Andersen proved, CPAs have been known from time to
time
: to keep their heads where the sun don't shine.
:
: You may know what 'should' happen, but you don't know what IPMT is doing. In
the
: case of building up a fund, with the first payment made at the *beginning* of
: the first period with no prior balance, IPMT returns the interest accrued as
of
: the *beginning* of the period. That is,
:
: IPMT(Rate,1,NPer,,-FV,1)
:
: returns the interest accrued on the accumulating balance at the *beginning* of
: the given period. As there is no balance prior to the initial payment, there's
: no prior balance from which to generate interest at the time of the 1st
payment.
:
: For payments made at the end of the period,
:
: IPMT(Rate,1,NPer,,-FV,0)
:
: also returns zero because again there's no balance prior to the first payment,
: so again no interest acrued at the time of the 1st payment.
:
: As for a loan of 75,000 with monthly payments over 18 years at 6.5% APR with
: payments at the beginning of each period, the monthly payment would be
$586.74,
: and the interet paid in the first payment is zero, i.e.,
:
: =IPMT(APR/12,1,NPer,-PV,,1)
:
: because IPMT follows custom in which initial loan payments made at the
inception
: of the loan reduce the face value loan amount by the amount of the initial
: payment. If the payment were made at the end of the first period, then there'd
: be positive interest, i.e.,
:
: =IPMT(APR/12,1,NPer,-PV,,0)
:
: would return $406.25 (and the loan payment would be $589.92). It's the absence
: of a prior balance that leads IPMT to return zero in period 1.
:
: That so, oh ye of more credentials than imagination, it's possible to
manipulate
: IPMT to give the desired results.
:
: =IPMT(APR/12,m,NPer,PMT(APR/12,NPer,,FV,1),FV-PMT(APR/12,NPer,,FV,1),0)
:
: This gives the intended results of $0.99 for m=1, $24.17 for m=24, $404.06 for
: m=216. A little cleverness, a little effort, and you can usually make Excel
work
: like you want it to.
:
: So, not a bug, nothing to report to M$FT.
:
: --
: To top-post is human, to bottom-post and snip is sublime.
 
Hi Saar!

Let me just add to Harlan's great explanation.

Just because your payments are made in advance doesn't mean to say
that your interest is received or paid in advance!

I think that you may also be confusing "points in time" with "periods
of time". Certainly, if payments are made in advance, interest does
accrue during the first period but if compounding is monthly, it will
not be credited until the end of period 1 which is at point in time 2.

You can use Product Support to report bugs but you'll find that
payment is required. If the result is that there is a bug, your
payment will be refunded. But trust us!

--
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.
 
Actually Harlan, you're the one who needs to be treated
like the imbicle you called me.

This assignment, if you had read it, isn't about an
amortization of a loan. It's about putting in payments
and accumulating $75,000. The payment put into the
beginning of the period WILL ABSOLUTELY HAVE EARNED
INTEREST BY THE END OF THE FIRST PERIOD and must be
credited to the account to start the second period payment.

Learn what is going on before jumping to conclusions. So
much for MVP's all jumping on the bandwagon without
knowing what it's all about.

I dare any of you to put the formula through IPMT with a
payment of $182.68, (which is computed through the PMT
function), show an interest rate of 6.5% annually, over 18
years, first payment made at the beginning of the period;
run a spreadsheet showing the accumulation of monies for
that time period, and come up with $75,000 on the 216th
period. With IPMT it WON'T happen. You can't get the
&75,000 unless you put in your own formula and not IPMT.

I took all of your MVP answers to two (2) count them (2)
certified Microsoft Instructors at the local University
and had them run the IPMT function. IT DOESN'T WORK on a
accumulation of money.

Before you call others names perhaps you should rein in
your emotions and check out what is really happening.
 
First, when you argue from authority ("Microsoft Instructors" - as far
as I know, there's no designation with exactly that title), you should
probably make sure they're authorities in the field you're citing.

Now, I'm no financial guru (heck, I'm not even an XL MVP!), but even I
can figure this one out.

Here's a workbook with the relevant calculations:

ftp://ftp.mcgimpsey.com/excel/saar_demo.xls

The key is to realize that when you say "accumulate 75,000 at the end of
18 years", with payments on the first of the month, that's 217 periods,
not 216. The first month has a payment but no interest on the first day.
The 217th period has interest but no payment.

An apology to Harlan and to the XL MVP's is probably in order...
 
Hi JE!

Apart from basic lack of manners implicit in the attack on Harlan's
for his model explanation, our OP fails to see that his end period
problem is one of asking the IPMT function to return the interest
element of the (n+1)th payment. Since there isn't an (n+1)th payment
IPMT has a problem with the question.

IPMT is "about" apportioning payments between principal and interest.
It can't return an answer for the (n+1)th payment which, with payments
in advance, will be after n periods because there is no (n+1)th
payment to apportion.

To calculate the interest earned in the last period (as opposed to the
interest element of the last payment) you need the general algorithm:

=-(FV(Rate,Term,Payment,0,1)-FV(Rate,Term-1,Payment,0,1))-Payment

--
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.
 
JE McGimpsey said:
The key is to realize that when you say "accumulate 75,000 at the
end of 18 years", with payments on the first of the month, that's
217 periods, not 216.
Click to expand...
....

Quibble: it's 216 periods, but the key point is that 216 successive periods
have 216+1 period bounds. That is, periods 1 to 216 start at point in time 0
and go through point in time 216, for 217 points in time. You gotta keep
periods/intervals and bounds/points distinct.
 
Norman Harker said:
Apart from basic lack of manners implicit in the attack on Harlan's
for his model explanation, . . .
Click to expand...

Yes, but I like spirited, all heat & no light discussions!
 
Saar said:
Before you call others names perhaps you should rein in
your emotions and check out what is really happening.
Click to expand...
....

I didn't call you names, I implied that your head is firmly stuck up your
ass. Thanks for confirming the fact.

Live with the fact that you don't understand how IPMT works. It needs a
initial balance (PV <> 0) to generate any interest from period 1. As I
pointed out, this can be handled by feeding the initial payment to IPMT as
its PV and adjusting FV. I guess that's too complicated for you and your
claimed but apparently worthless professional certification.
 
Norman Harker said:
Apart from basic lack of manners implicit in the attack on Harlan's
for his model explanation, our OP fails to see that his end period
problem is one of asking the IPMT function to return the interest
element of the (n+1)th payment. Since there isn't an (n+1)th payment
IPMT has a problem with the question.
Click to expand...

It was obvious the problem was conceptual...

....the OP couldn't conceive that his world-view wasn't the one
implemented in XL's functions.
 
Back to the fence again - he's got 216 fence panels so he's going to need 217
fence posts to hold/bound them.
 
Hi Gord!

But at least you understood when your supplier asked if you were sure
you needed the same number.

The problem here is that OP can't see that IPMT is apportioning
payments and has a problem apportioning a payment that doesn't exist.
Sort of, "What's the load on fence post 217?" (when he only has 216
fence posts). In your case, you might start looking for how to measure
the load on your house wall.

--
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.
 
Hi Ken!

You'd need to do a Tom Sawyer every year and then you wouldn't think
it so much fun.


--
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.
 
Btw Harlan

I want to apologize for CPA's everywhere. To be publicly represented by Saar is
absolutely repulsive and definately a misrepresentation of the intelligance and
demeanor.



: ...
: >Before you call others names perhaps you should rein in
: >your emotions and check out what is really happening.
: ...
:
: I didn't call you names, I implied that your head is firmly stuck up your
: ass. Thanks for confirming the fact.
:
: Live with the fact that you don't understand how IPMT works. It needs a
: initial balance (PV <> 0) to generate any interest from period 1. As I
: pointed out, this can be handled by feeding the initial payment to IPMT as
: its PV and adjusting FV. I guess that's too complicated for you and your
: claimed but apparently worthless professional certification.
:
:
 
Lady Layla said:
I want to apologize for CPA's everywhere. To be publicly
represented by Saar is absolutely repulsive and definately
a misrepresentation of the intelligance and demeanor.
Click to expand...
....

You have nothing for which to apologize. Possession or lack of a particular
certification, by itself, confers neither all-sweeping knowledge nor guilt
by association.
 
Now the serious response.

Saar said:
This assignment, if you had read it, isn't about an
amortization of a loan. It's about putting in payments
and accumulating $75,000. The payment put into the
beginning of the period WILL ABSOLUTELY HAVE EARNED
INTEREST BY THE END OF THE FIRST PERIOD and must be
credited to the account to start the second period payment.
Click to expand...

True, if a payment is made at the beginning of a period, interest should
accrue by the end of that period. However, there's at best some ambiguity
here. Where does it say that the interest accrued for a given period
wouldn't be measured as of the time of that period's payment? If there's no
initial balance (PV arg zero/missing) but payments made at the beginning of
each period, then the interest accrued at the time of the first payment is
correctly zero. If payments are made at the end of each period, then again
with no initial balance the interest accrued in the fist period at the time
of the first payment is also zero.

Given the specs in the other thread in which you failed to respond, the
constant monthly payment was 182.68. Payments were made at the beginning of
each period. The balance accruing interest during the first period is just
the first payment, so interest accrued at the end of the first period is
0.99. What does

IPMT(0.065/12,2,216,,-75000,1)

return? Given one possible set of semantics for the final argument, this
means the interest accrued as of the beginning of the second period is the
same as the interest accrued as of the end of the first period.

If IPMT has a flaw, it's that it doesn't return

IPMT(r,n,n,,-FV,1)*((1+r)^k-1)

for

IPMT(r,n+k,n,,-FV,1) for k > 0. However, I can see why Microsoft made IPMT
return errors when IPMT's second arg falls outside the range [1;NPER].
Learn what is going on before jumping to conclusions. So
much for MVP's all jumping on the bandwagon without
knowing what it's all about.
Click to expand...

Oh, it's not just the MVPs. Many people know how to cope with Excel. You
should try to do so yourself.
I dare any of you to put the formula through IPMT with a
payment of $182.68, (which is computed through the PMT
function), show an interest rate of 6.5% annually, over 18
years, first payment made at the beginning of the period;
run a spreadsheet showing the accumulation of monies for
that time period, and come up with $75,000 on the 216th
period. With IPMT it WON'T happen. You can't get the
&75,000 unless you put in your own formula and not IPMT.
Click to expand...

A challenge!

For my one ease, I'll use defined names i referring to =0.065/12, NPer
referring to =18*12, and FV referring to =75000.

A1:
=ROW()

B1:
=PMT(i,NPer,,-FV,1)

C1:
=IPMT(i,A1,NPer,PMT(i,NPer,,FV,1),FV-PMT(i,NPer,,FV,1),0)

D1:
=SUM(B$1:C1)

Fill A1:D1 down into A2:D216. What do you get in cell D216?

I already gave you the C1 formula above in my initial response to your rant.
A pity you didn't test it before you set out to prove just how obtuse you
can be.
I took all of your MVP answers to two (2) count them (2)
certified Microsoft Instructors at the local University
and had them run the IPMT function. IT DOESN'T WORK on a
accumulation of money.
Click to expand...

Got names? Got any proof? Got any wit?

Ah, but there's that unfortunate presumption that certification equals
knowledge again. How many certified Microsoft Instructors answer newsgroup
postings?

No matter. I refuse to assume that these claimed certified instructors
failed to use IPMT to reach the correct answer for any reason other than the
poor job you very likely did in posing the problem to them.

But it just doesn't matter. All that matters is demonstrated in the formulas
above. Learn from them or not.
 
Back
Top