How do I create a formula to calculate the average percentage rat

[LD]

How do i create a formula in Excel to calculate the average percentage rate
of a 30 year financing period
For example Loan amount 135,000 montly payment amount 684.03 number of
payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57. The
first 74 payments the monthly payment amount will be 742.53 and for the
remaining payments of 286 the monthly note will be 684.03

 

[N Harkawat]

for the first situation the formula is
=RATE(360,-684.03,135000-1770.57)*12

For second situation where the PMT changes after 74th month the Rate
function would not work.

however if copied from cell A2 thru A74 $742.53 and from A75 thru A361
$684.03 and on cell a1 ,$-135000
Then on cell B1 type the following:
=IRR(A1:A361,3%/12)*12

That should give you your average rate of 4.74%

Its cheaper taking the first option of 4.61%

 

[LD]

When this calculation is put into a program called easylender a mortgage
program a annual percentage rate of 4.859% appears how do I create a formula
in excel to match this rate?

 

[N Harkawat]

On cell A1 change the value of -135,000 to -133,229.43 (prepaid finance
charge $1,770.57)
That should give you 4.859%

 

[LD]

when entering this formula the result is #num! what am I doing wrong?

 

[N Harkawat]

Are you entering on cell a1 number as negative 133229.43 and the rest from
A2 thru A361 as positive
because I sure do get the correct result