How do I calcualte APR on Variable Rate Mortgages?

C

Caren F

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
 
N

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%
 

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