I have an annuity plan for let's say 5 years with 60 monthly installments
with a financing amount of 10.000 ‚¬ and 10 % interest rate. The payment plan
starts in March 2007. Is it somehow possible to calculate with a formula, how
much interest I will have to pay just for the periode from March till
December in the first year? That means only for 9 month.

Thanks in advance for your answers!
Robert

Robert,

Array enter (enter using Ctrl-Shift-Enter) the formula

=SUM(IPMT(10%/12,ROW(INDIRECT("1:9")),60,-10000,0))

The "1:9" is used to tell the function to sum the interest payments for periods 1 to 9. Next year,
you would use
10:21 for those twelve months...

HTH,
Bernie
MS Excel MVP


"Robert Golja" wrote in message
...
I have an annuity plan for let's say 5 years with 60 monthly installments
with a financing amount of 10.000 ? and 10 % interest rate. The payment plan
starts in March 2007. Is it somehow possible to calculate with a formula, how
much interest I will have to pay just for the periode from March till
December in the first year? That means only for 9 month.

Thanks in advance for your answers!
Robert

Hi Bernie,

thanks a lot for your answer. when I enter the proposed formula in a cell I
get "83" as a result whereby the correct sum should be somewhere around 766.
Did I do something wrong?

best regards,
Robert

"Bernie Deitrick" wrote:

Robert,

Array enter (enter using Ctrl-Shift-Enter) the formula

=SUM(IPMT(10%/12,ROW(INDIRECT("1:9")),60,-10000,0))

The "1:9" is used to tell the function to sum the interest payments for periods 1 to 9. Next year,
you would use
10:21 for those twelve months...

HTH,
Bernie
MS Excel MVP


"Robert Golja" wrote in message
...
I have an annuity plan for let's say 5 years with 60 monthly installments
with a financing amount of 10.000 ? and 10 % interest rate. The payment plan
starts in March 2007. Is it somehow possible to calculate with a formula, how
much interest I will have to pay just for the periode from March till
December in the first year? That means only for 9 month.

Thanks in advance for your answers!
Robert

Robert,

Yes, you did something wrong. You need to use Ctrl-Shift-Enter instead of just Enter. Excel will
but curly braces around the formula if you do this correctly.

{=SUM(IPMT(10%/12,ROW(INDIRECT("1:9")),60,-10000,0))}

DO NOT enter the formula with the {}'s.

The other thing you can do is use

=IPMT(10%/12,ROW(A1),60,-10000,0)

in a cell, and copy down for a total of 9 rows, then use the SUM function on those cells. That way,
you will see what each month's interest payment is.

HTH,
Bernie
MS Excel MVP


"Robert Golja" wrote in message
...
Hi Bernie,

thanks a lot for your answer. when I enter the proposed formula in a cell I
get "83" as a result whereby the correct sum should be somewhere around 766.
Did I do something wrong?

best regards,
Robert

"Bernie Deitrick" wrote:

Robert,

Array enter (enter using Ctrl-Shift-Enter) the formula

=SUM(IPMT(10%/12,ROW(INDIRECT("1:9")),60,-10000,0))

The "1:9" is used to tell the function to sum the interest payments for periods 1 to 9. Next
year,
you would use
10:21 for those twelve months...

HTH,
Bernie
MS Excel MVP


"Robert Golja" wrote in message
...
I have an annuity plan for let's say 5 years with 60 monthly installments
with a financing amount of 10.000 ? and 10 % interest rate. The payment plan
starts in March 2007. Is it somehow possible to calculate with a formula, how
much interest I will have to pay just for the periode from March till
December in the first year? That means only for 9 month.

Thanks in advance for your answers!
Robert

On Jul 5, 3:36Â*am, Robert Golja
wrote:
I have an annuity plan for let's say 5 years with 60 monthly installments
with a financing amount of 10.000 ‚¬ and 10 % interest rate. The payment plan
starts in March 2007. Is it somehow possible to calculate with a formula, how
much interest I will have to pay just for the periode from March till
December in the first year? That means only for 9 month.

=CUMIPMT(10%/12, 60, 10000, 1, 9, 0)

If you get a #NAME error, look at the CUMIPMT help page for how to
install the Analysis ToolPak add-in.

If the ATP is not available to you, the following will compute the
total interest for the first 9 months, but not for any arbitrary
period:

=9*PMT(10%/12, 60, -10000) - (10000 - FV(10%/12, 9, PMT(10%/12, 60,
-10000),-10000))

Note: These formulas assume that monthly interest is computed by 10%/
12; that is, that interest compounds monthly.

PS: It is not difficult to modify the second formula to handle an
arbitrary period. But I think it is not worth over-complicating it
since I think (hope) it is unlikely that you will use it.