Formula doesn't match - ?

[Steve]

I'm using this formula to determine total interest due on a bank CD:

=ISPMT(A2/365,A4-A3,1,A1)

A1 = amount of CD

A2 = interest rate

A3 = inception date

A4 = maturity date

Tried one manually, doesn't match the formula -

$10,000 @ 5% from 1/1/05 to 2/1/05

= (.05/365)(31)(10000) = 42.47

The formula comes up with 41.10, which means it must be calculating on
the basis of a 30-day term? But A4-A3 = 31. ???

 

[Peter Aitken]

I'm using this formula to determine total interest due on a bank CD:

=ISPMT(A2/365,A4-A3,1,A1)

A1 = amount of CD

A2 = interest rate

A3 = inception date

A4 = maturity date

Tried one manually, doesn't match the formula -

$10,000 @ 5% from 1/1/05 to 2/1/05

= (.05/365)(31)(10000) = 42.47

The formula comes up with 41.10, which means it must be calculating on
the basis of a 30-day term? But A4-A3 = 31. ???

First of all, use IPMT - the ISPMT function is provided only for
compatibility with Lotus 1-2-3.

Anyway you are using the formula wrong. The second argument must be less
than or equal to the third argument. See the online help.

 

[Steve]

First of all, use IPMT - the ISPMT function is provided only for
compatibility with Lotus 1-2-3.
Anyway you are using the formula wrong. The second argument must be less
than or equal to the third argument. See the online help.

Thanks Peter. Checked out the help file, still don't quite get it,
although I now understand about the second and third arguments.

Thought maybe Per and Nper should be the same in my example, but that
doesn't seem to work -

=IPMT(A2/365,A4-A3,A4-A3,A1)