IPMT HELP

In EXCEL IPMT function help example 1
A2=10% annual interest, A3=1 period, A4=years of

loan, A5=8000 present value of loan
=IPMT(A2/12,A3*3,A4,A5) Interest due in the first

month for a loan with the terms above (-22.41)

I could not understand why the period is

multiplied by 3 and no of years was not

multiplied by 12 to get the interest of first

month , for me it should be :
=IPMT(A2/12,A3,A4*12,A5)  which yields (-66.67)

any explanation pls.
Musaduq S. Younis
aba_yousif@yahoo.com

The way I read this function is there is no indication of years or months.  A3 answers which period to use in the calculation, and A4 gives the total number of periods. The help example is answering the question of what the interest payment is for the 3rd period of a 3 period loan. That could be 3 months, or 3 years. However, the interest rate is being based on a monthly rate (annual rate divided by 12), so the example appears geared toward a three month loan.

dear Omar it is clearly a mistake, if you read the help text "Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper. " applying this will lead to IPMT(A2/12,A3,A4*12,A5) which yields (-66.67) for the interest due in the first month instead of the quoted example "=IPMT(A2/12, A3*3, A4, A5) Interest due in the first month for a loan with the terms above (-22.41) " even the last explanation suggest that "Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments." where on earth it is multiplied by 12 ?

this should be elevated to the Microsoft Excel team.

Musaduq S. Younis

aba_yousif@yahoo.com

The help examples give the answers I would expect, and I find the text consistent with that.

There is nothing in this function that requires a period to equal one month. It works easily as well with days, weeks, months, quarterly, semi-annual or annual calculations. All that matters is you make sure the interest rate is the rate for the period you are using.

I believe you are allowing yourself to be confused by the concept of a period being related in some way to years or months. A period is agnostic. It could be one day, or it could be 10 years, or any repeated length of time in between. All that matters in this particular function is that the interest rate is the amount per the period of time you are using. If it isn't, you have to convert it. Since the most common period is monthly, and the most common interest rate is annual, you often need to convert the interest rate to match the period length.

This function has been around for decades. I was able to get the identical answer in Open Office Calc, and also using IPAYMT in Quattro Pro 9. I think you might consider that a simple inconsistency that you believe you have found would have been discovered long before now.