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

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%




--
Kind regards,

Niek Otten


"Caren F" <Caren (E-Mail Removed)> wrote in message
news:A4A90337-C318-49F8-AF93-(E-Mail Removed)...
>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
>