Excel IPMT - strange results?

[Wilburn]

First off, I am using Excel 2002 and am attempting to create a workshee
to calculate the balance and interest rate for an investment.
It all works fine except for the fact that the IPMT formula I am usin
to calculate the interest seems to be misbehaving.
I am using the following information :
cell B3 - Investment Goal : $75,000
cell B4 - Interest rate : 6.50%
cell B5 - Number of years : 18
cell B6 - Payments per year : 12 (monthly)
cell B7 - Total number of payments : 214 (18 * 12)
cell B9 - Monthly payment : $183.67 (using the PMT formula)

To calculate the first interest amount I am using the IPMT function, i
the following way :

syntax : IPMT(rate,per,nper,pv,fv,type)

=-IPMT($B$4/12,E2,$B$7,F2)
My explanation of the formula is the following :
rate : $B$4/12 (Rate divided by number of periods per year)
per : E2 (E2 being a column showing the current period number)
nper : $B$7 (total number of payments (18 years * 12 months)
pv : F2 (F2 being a column that adds the interest and the balance, t
get a total balance)

I am not sure if I am using the formula correctly because the followin
situation shows :
The balance increases monthly, which is good.
The interest however seems to first increase, then decrease, while wit
an increasing balance and a constant interest rate, the interest rat
should (in my opinion) constantly increase just like the balance.

Am I right? Am I doing something wrong/using the formula the wrong way
Is there more information necessary?
Any hints/tips are greatly appreciated.

PS. I checked the help file from Excel on this formula but I get th
faint impression that the example used is incorrect.
(for example, it talks about annual payments yet divides the interes
rate by 12 in the IPMT formula, as if the payments are monthly

 

[Norman Harker]

Hi Wilburn!

Use:
=-IPMT($B$4/12,E2,$B$7,0,$B$3,0)

You had the current balance in F2 in the PV and nothing in the FV.

But there's a lot easier way!

E2:E217
1 through to 216
F2
=F1+$B$8+G2 [calculates the accumulating balance nb F1 should be
blank]
Copied down
G2
=F1*($B$4/12)
Copied down

The beauty of these babies is that they are self-checking; F217 should
show a future value of $75,000.00 and (totally by chance) it does.
--
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.