IPMT formula help

[CY078]

Hi All

I've used the below reference for the "Interest Only" formula (full link IPMT function in Excel - calculate interest payment on a loan)

My parameters are:

Loan Amount $	630,000​	Pv
Annual Interest Rate	3.19%​	Rate
Life Loan (in years)	30​	Per
Number of Payments per Year	12​
Total Number of Payments	360​	Nper

Which works out to be

=IMPT(3.19%/12,30,360,$630,000,0) (FYI: Divide by 12 as that is the amount of interest payments per year .. ie: One a month)

In which I get an answer of $1,591.01

However when I plug it into a "online calculator" I get the result of $1,675.

So my questions are:
- Why do i get a difference to the online calculator ?
- What have I done wrong ?

Thoughts .... thanks

 

[Dave Patton]

Please post your information with the forum's tool named XL2BB.

I do not know how you calculated the amount you cite.

The result seems to be 1674.75

Why don't you build an amortization schedule and check the calculation?

T202311a.xlsm
	A	B	C	D
1	Loan Amount $	$630,000.00		$1,674.75
2	Annual Interest Rate	3.19%
3	Life Loan (in years)	30
4	Number of Payments per Year	12
5	Total Number of Payments	360
3a

Cell Formulas
Range	Formula
D1	D1	=IPMT(B2/12,1,B3*B4,-B1,0)

 

[Dave Patton]

Do you want the interest for the first month or the 30th month?

 

[CY078]

Please post your information with the forum's tool named XL2BB.

I'm on a work lappy and it doesn't let me

I do not know how you calculated the amount you cite.

By using the below formula.

View attachment 101826

The result seems to be 1674.75

This worked with the formula you provided (=IPMT(B2/12,1,B3*B4,-B1,0) and is consistent with all other inputs when comparing to a mortgage calculator.

Thanks for your help

FYI: I still don't understand what I did wrong with my original formula though

 

[Dave Patton]

Your information
"=IMPT(3.19%/12,30,360,$630,000,0) (FYI: Divide by 12 as that is the amount of interest payments per year .. ie: One a month)"

You are calculating the interest for the 30th payment.

"Do you want the interest for the first month or the 30th month?"

You did not answer the question.


Interest for the first month is 1674.75
Interest for the 30th 1591.01