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" <(E-Mail Removed)> wrote in message
news:49630AF3-0F89-469E-9609-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:49630AF3-0F89-469E-9609-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:52EE912E-2EC3-4939-AB16-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:49630AF3-0F89-469E-9609-(E-Mail Removed)...
>> >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
<RobertGo...@discussions.microsoft.com> 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.