How do I calcualte APR on Variable Rate Mortgages?

[Guest]

I am trying to compute the APR for Variable Rate Mortgages. The data I have
is the following:
Loan Type: 5/1 ARM
Amount:$100,000
Index: 4.3
Margin: 2.75%
Rate: 6.125%
Term: 30 Years

Is there any way using Excel formulas that I can calculate this?????
Thanks

 

[Niek Otten]

In answer to a former question, Norman Harker wrote this:

Hi John!

Assuming the persona of the borrower:


Deduct initial setup costs from the amount of the loan
Deduct (eg) monthly charges from the negatively signed loan


Example


I borrow 100000 and pay set up costs of 2% of the loan. I also pay $30
per month account service charge. The loan is over 10 years with first
repayment 1 month after draw down of loan. The quoted APR is 6%.


First the repayments:
A1:
=PMT(6%/12,10*12,100000,0,0)
Returns: -1110.20501941652


Then the APR:
B1:
=RATE(10*12,A1-30,100000-(100000*2%),0,0,0)*12
Returns: 7.04634008500682%


Or as one formula:
=RATE(10*12,PMT(6%/12,10*12,100000,0,0)-30,100000-(100000*2%),0,0,0)*1
2
Returns: 7.04634008500682%