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

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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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

"N Harkawat" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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
"LD" wrote in message
...
when entering this formula the result is #num! what am I doing wrong?

"N Harkawat" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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