Interest Only calculation

**JimDandy** · 08-23-2011, 01:41 AM

I'm trying to determine the formula for an interest only payment. The values I’m working with are:

Loan Amount=$13,600,000, Interest Rate=5.64% (annual), Amortization term is 30 years although that may or may not really matter, and there are 12 payments a year. I keep getting #Num error even though all my values are numerical. The value in a regular Interest+Principal payment is $941,017 (annually) and the Interest Only payment should be something like $767,040 but I can't seem to figure out how to do it...I've been trying to use the IPMT function.

**JimDandy** · 08-23-2011, 11:31 AM

Ok, I think I've got it. What I failed to account for initially was that with an Interest Only payment, the only comparable value acheivable is the first one as interest is reduced for every month so teh best information for my needs is the interest for the first month. Here is the calculation I used:

=IF(Values_Entered,-IPMT(Interest_Rate,1,Loan_Years,Loan_Amount),"")

Interest_Rate = 5.64
Loan_Years = 30
Loan_Amount = 13,600,000

The result I was looking for was $767,040