Need help calculating APR for an adjustable rate loan

[guttal]

Is there a way with Excel to calculate the APR for an adjustbale rate loan? I'm trying to figure out how to calculate the APR for an ARM loan that has a rate set for three years (initial rate) then the rate adjusts to the index plus the margin (2.75+1.25=4.00%) for the remainiing 27 years. Here's the data I'm using to test:

Initial Loan amount: $200,000
Loan fees: $1,500 (includes any prepaid interest)
Loan term: 360 months
Initial interest rate: 6%
Initial term: 36 months
Estimated index rate: 4%
Margin: 2.75%
Index: 1.25%

The APR will be lower than the start rate of 6%. What formula in Excel will ehlp me calullate the APR?

Thank you

 

[Dave Patton]

Excel Workbook
	A	B	C
1	Principal	$200,000
2	Term years	30
3	initial	3
4	subsequent	27
5	Rate
6	initial	6%
7	subsequent	4%
8	Fees	1,500.00
9	Payment	1,008.69
10	Calculated rate	4.5247%
11	Payment calc	1,008.69	0.00
4a

Excel 2003

Cell Formulas
Range	Formula
B10	=RATE(B2*12,-B9,B1-B8,0.044/12)*12
C11	=FV(B7/12,B4*12,-B11,-FV(B6/12,B3*12,-B11,B1))

Welcome to the forum.

If you know the fixed payment amount, try the formula
"calculated rate".

If you need to calculate the payment, consider Goal Seek
with either an amortization chart or a formula like "Payment calc".

I used a guess of a payment of $1,000 and Goal Seek
calculated the required payment amount with the formula in the adjacent cell.

If you built an amortization chart, you could use Excel's function IRR.

 

[guttal]

Thank you Dave, sorry I should have included the payments. When I change the payment you estimated to the payment for a 30 year amortization as requried for the loan the APR in your formula recalculates to the same APR as a 30 year fixed rate loan. It looks like the rate adjustment was not included in the calculation. To calculate the APR I have to use a replayment schedule that includes the payment for the first rate period 36 months and then the second rate period until the loan pays off in the remaining 27 years. The loan structure looks like this:

Payment for the first 36 months @ 6.00% interest is $1,199.10. The balance of the loan at the end of 36 months would be $192,168.20. Interest paid over the 36 mohts would be $35,335.80. Then the interest rate adjusts to match the index plus the margin to a rate of 4.00% which results in a payment of $970.85 for the life of the loan. The APR for this loan including loan fees should be 4.397%. The total payments made for the entire loan period will be $357,722.59 and the total interest paid will be $157,722.59.

I hope this helps and again sorry I did not provide the payments and extra detail you would need to understand my question in my first post.

Thank you for your help.

Greg

 

[Dave Patton]

=IRR(B14:B374,0.004)*12 4.5347%

B14 198,500
B15:B374 payments as cited

 

[guttal]

Sorry to be a pain with this but I'm getting a div/0 error. Seems simple. The formula reads:

=IRR(B14:B374,0.004)*12

Where b14 = the loan amount minus cost of 1,500 198500. B15 through B374 contains 36 payments of 1,199.10 and the reamining payments of 970.85 atart B48 to B374. There are no cells with 0, text or any other data other than the payments. If I reduce the number of cells with payments to b14:b19 I get a #Num error.

Do you see that I have any thing incorrect?

Thank you

 

[Dave Patton]

on my test sheet B14 198,500
The 198,500 is negative

Either the initial amount is negative and other amounts positive or vice versa.

 

[guttal]

Thank you, the formula is calculating a result. The result is 4.508%. I've checked the results against online ARM calulators and the online calulators have a consistant result of 4.397% with the same scenario. Are there any other Excel formulas that might be used that would create a different result to match the 4.397%?

 

[Dave Patton]

You didn't explain how your calculated a different rate. Excel 2003 and Excel 2010 yield the rate that I mentioned.

What version of Excel are you using and what calculators are you using?

What other formulas and functions have you tried?

Excel's NPV with the rate ~ 4.5347% yields a result of -0.00000000370

 

[guttal]

I'm using Excel 2007.

I built an APR formula for fixed rate loans using Excel Rate and the results match all the online calulators I've tested against.

Here's how my fixed rate APR Rate formula works:

Results for APR 5.148%

=RATE(L23*12,-N23,(M23-Q23-O23),H23)*12

L23 = term (years) 30
N23 = Payment $2,270.51
M23 = Loan amount $417,000
Q23 = closing costs $1,077.00
O23= Pre Paid Interest $1,780.94
H23 = Interest rate 5.125%

One of the online ARM APR calulators I've test against is:

http://www.dinkytown.net/java/MortgageAprAdjustable.html

I've also tested against others. All online calulators have the same results.

As common as ARM loans are I would think the formula would be easier to find. It has been a real challenge to figure out how to calculate ARM APR in Excel.

Thank you for your help.

 

[MikeWx]

Greg,

I have a few comments, then a question.

The first 36 payments are in the range B15 through B50, and the remaining 324 payments are in B51 through B374.

At the end of 36 months Note #3 says the balance is $192,168.20 and the interest paid is $35,335.80. Thirty six payments of 1199.10 is $43,167.60, minus the $35,335.80 interest means the principal paid was $7,831.80. Adding the principal paid to the principal balance of $192,186.20 means you started with an initial loan of $200,000. Your 4.508% IRR from Excel starts with an initial loan of $198,500.

When are you paying the loan fee, and how do you enter it in the other ARM calculators you are comparing to Excel?

Mike