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

 

[guttal]

Hi Mike, your comments are correct. The actual loan amount is $200,000.

Uing Excel formula Rate you subtract the loan fees that are included in APR from the loan amount. In this case the actual loan amount is $200,000 minus loan fee of $1,500 with a net loan amount of $198,500.

The Internet calulators have you enter fess as a percentage or dollar amount in seperate fields. They also use the net loan amount to calulate APR. So the fee is collected upfront.

I don't know why I can not understand this formula. To calulate APR for a fixed rate loan is easy. The only difference with an ARM APR is there are two interest periods with principal and interest payments instead of one interest period with a fixed rate loan. Both fixed and ARM loans use a fully amortizing loan payment with principal and interest.

I hope this helps. Let me know if you have any other questions.

Thank you,

Greg

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1	Principal	$200,000.00
2		Initial	Subsequent
3	Term	3	27
4	Rate	6%	4%
5	Payment	1,199.10	970.85
6	Fees	1,500.00
7	NPV		0.00
8	FV		0.36
9				Effective
10	IRR	4.5347%	0.3779%	4.6301%
11
12	Num	-198,500.00	Rate	Interest	Principal	$200,000.00
13	1	1,199.10	6%	1,000.00	199.10	199,800.90
4aa

Excel 2003

Cell Formulas
Range	Formula
C7	=NPV(B10/12,B13:B372)+B12
C8	=FV(C4/12,C3*12,-C5,-FV(B4/12,B3*12,-B5,B1))
C10	=IRR(B12:B372,0.004)
B10	=IRR(B12:B372,0.004)*12
B12	=-B1+B6
D10	=(1+C10)^12-1
D13	=ROUND(C13/12*F12,2)
F12	=B1
F13	=F12-E13
E13	=B13-D13

Hello Greg

Please explain how you calculated different rates on your original example.

For some purposes, the IRR is appropriate.
In some countries, the Effective rate is appropriate.

For what country are you preparing the calculation?
How is the APR defined?

Ensure that your Excel includes all patches and updates and possibly try the formulas with Excel 2003 and/or Excel 2010.

 

[guttal]

Thank you Dave. I have to work through you spreadsheet to see who this works.

Thank you again.

Greg

 

[guttal]

Hi Dave, I setup your spreadsheet and worked through the formulas. I understand the process of calculating the total cost of interest for each of the two rates for the periods of time defined. The end result as to the cost of money (effective rate) in your example is 4.6301%. Using the online calculators with the exact same interest cost over the life of the loan is identical; the upfront cost is identical $1500. The online calculators calculate the cost of money as 4.397%.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
With every number matching I cannot understand why there is a different cost of money except the way the cost between the two rates is averaged differently. Is there a different formula to calculate the average cost of money that would result in acheiving the 4.397% in this example?<o></o>

Thank you,

Greg

 

[MikeWx]

I tried using Excel's Goal Seek with the formula for APR in Reg Z, Appendix J (http://www.fdic.gov/regulations/laws/rules/6500-1950.html#fdic6500appendixjtopart226), but it calculated the same value as the IRR (4.5347), and not the 4.397 rate the online calculators come up with. The online calculators appear to use a common Java program to calculate the APR, but I haven't found a reference that told what formula is used.