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

L

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

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%
 
L

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

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%
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top