Need help calculating APR for an adjustable rate loan

guttal

New Member
Joined
Nov 18, 2011
Messages
18
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Excel Workbook
ABC
1Principal$200,000
2Term years30
3initial3
4subsequent27
5Rate
6initial6%
7subsequent4%
8Fees1,500.00
9Payment1,008.69
10Calculated rate4.5247%
11Payment calc1,008.690.00
4a
Excel 2003
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
=IRR(B14:B374,0.004)*12 4.5347%

B14 198,500
B15:B374 payments as cited
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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%?
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top