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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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%
 
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?
 
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%
 
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
 
Back
Top