IPMT Function

  • Thread starter Thread starter dave
  • Start date Start date
D

dave

I am relatively new to excel. I am however, trying to find
out the payment I would need to make on a monthly basis
over 18 years at 6.5% interest in order to have a college
fund balance of &75,000 for my children when they reach
college age.

I am trying buy using the PMT formula. It gives me a
payment amount of $182.68. I was trying to do a spread
sheet presentation for my wife using the IPMT function to
calculate the principal amount and interest earned in each
of the 216 periods.

When I fill in the blanks for the wizard with all of the
above it gives me a total amount of interest in the first
period of $0.00. The second period is shows $0.99 which
is what should be in the first period. It appears that
every period is off by one (shoved down). Therefore the
final period (216) shows an accumulation of the 215
interest and the formula shows a final balance of
$74,595.94 not the $75,000.

Can anyone help me with what I may have done wrong?

If you need more detailed information I will gladly supply
it.

Thanks in advance
 
First, A1 through A216 hold the payment numbers 1-216.

I used this formula in column B to get the monthly contribution from you:

=PMT(0.065/12,216,0,-75000,0)

and this in column C to get the interest earnings:

=IPMT(0.065/12,A1,216,0,75000,0)

My ending total is 75,000.

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
With rate=6.5%, and fv=75,000 and nper = 18 years then PMT is given by
=PMT(rate/12,nper*12,0,fv,1)
This give $182.68 Note the final 1 in the formula - we are making payments
at the start of the month.
Make table
Month interest FV
0 0 182.68 (payment made a
start of month 1)
1 =182.68*6.5/12 =0.99 =int+pmt+fv = 0.99+182.68+182.68 =
366.35 (value of saving at end of second month)
2 =366.35*6.5/12 =1.98 =1.98+182.68+366.35 = 551.02
copy the formulas down to month 216
216 $404.06 75,182.68

Why use IPMT?

Bernard
 
Hi Dave!

No need for IPMT for a schedule

Interest each period is Previous Balance * 6.5%/12

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

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I must really be doing something wrong then. I have used
all formulas given me and I come up with a total of
payments of $39,458.88 (182.68 * 12) and a total of
interest of $35350.48 for a grand total of $74809.36.
That's not $75,000.

Why use Ipmt. my assignment from the class as listed in
the original message said I had to.

Any one?

Dave
 
Dave, your problem is you need to add one more month's interest to the total

For period 1, your contributions are $182.68 and interest is $0. But all
these figures are at the *start* of the period (as signified by the last
parameter in the functions).

So when you get down to the 216th period, the interest calculated is what
will be credited at the start of the 216th period. You need to add to this
the interest for the period. When you take your total, after period 216, of
$74,595.94, add one month's interest (74595.94 * 6.5% / 12 = 404.06), you
get exactly $75,000.00.

As everyone else has pointed out, it's far easier to use PMT. Then your
accumulated interest is $75,000 - pmt * 216.

By the way, it's not a good idea to lead this group astray. You will get
answers, and very quickly I've found, if you're honest with the group. In
this post you said "Why use Ipmt. my assignment from the class as listed
in the original message said I had to." But the original message said
nothing about a class assignment, it said "I was trying to do a spread sheet
presentation for my wife".
 
I didn't lead anyone astray. I copied the quotes from
my assignment sheet as given me, including the children,
and the spreadsheet "for my wife" as given in the
assignment. I personally am not married, just that the
assignment was "putting us in a real life situation". And
no on has been able to tell me what to do with IPMT in
order to get the first period to have .99 interest in it
rather than .00. A bug in IPMT? I think so.
 
You're not listening, dave. There is no bug in IPMT.

Your first problem is that, as you've been told more than once, no one in "a
real life situation" would use IPMT. They would calculate the PMT, multiply
pmt*term to get the contributions, and calculate fv-pmt*term to get the
interest earned. So if you truly want "a real life situation", the answer
is: don't use IPMT.

But you don't want that answer. You were told by your prof to use IPMT.
That's why I admonished you to be honest with the group. If we know what
we're dealing with, you get the answer you're looking for much more quickly.
Anyone in a real life situation would be most happy with the answer "use
PMT, not IPMT". Someone who is doing an assignment doesn't want to know the
most effective way, they want a solution which incorporates the
requirements.

When you use PMT to calculate the future value of deposits, you are making
the deposits at the beginning of the period. IPMT is calculating interest on
the same day as the deposit. Of course, after the first deposit, the
interest is zero. It's not until you get to the second period (one month
later) that you get credited with any interest. So when per=2, IPMT will
give you the interest for the first month.

It may not be the answer you want, it may not be intuitive, but that's the
way IPMT works.
 
Back
Top