How can I calculate a due interest of annuities for a certain peri

[Robert Golja]

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

 

[Bernie Deitrick]

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]

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]

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

 

[joeu2004]

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.