Dragging formulas in excel 2002, using the IPMT function.

[clara]

I can't seem to copy the formula down to the other cells when I use th
Ipmt function, to calculate interest per month

 

[Niek Otten]

What data? What formula? What actions to copy? What did you expect to get?
What did you get instead?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Norman Harker]

Hi Clara!

IPMT syntax is:

=IPMT(rate,per,nper,pv,fv,type)

To copy this down to get interest for each month? First all of the
arguments *except per* need to be absolute referenced.

You can approach determination of per in two ways:

Either:
Provide a helper column with the per number and refer relatively to
the cell containing the "current" per number.
Or:
Calculate the per number using ROW(current)-ROW(period_zero) where the
ROW(period_zero) is absolutely referenced.

Example with period zero in Row 12
=ROW(A12)-ROW($A$12)

Alternatively, if preparing an amortization schedule, the interest
payment can be found by multiplying the previous balance by the rate
per period. Incidentally, deducting the interest from the payment
gives you the principal repaid that period.

Sample amortization schedules that implement these calculations are
available on direct request to email below.

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