Annual Percentage Rate

[Guest]

I am trying to calculate annual percentage rate for a mortgage estimate. The
Formula works as long as I don't use a rate above 5.5% and term in months
above 310. I need to calculate using 360 month term.
Also I need to combine 1st and 2nd lien loan info into a single APR. Anyone
know how to creat this;

Here is the formula I Have been using:
=RATE(B4,PMT(ROUND(B2/12,6),B4,B1,0,0),B1-B3,0,0,0)*12
Data is as follows:
B1 = 110410 (Loan Amt)
B2 = 6.5% (Interest Rate)
B3 = 3360 (Estimated Closing Costs)
B4 = 360 (Term)

 

[Bernie Deitrick]

sts,

It would help the function converge if you offer it an initial guess:

=RATE(B4,PMT(ROUND(B2/12,6),B4,B1,0,0),B1-B3,0,0,B2)*12

HTH,
Bernie
MS Excel MVP