Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Need help calculating APR for an adjustable rate loan
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2011
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need help calculating APR for an adjustable rate loan

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,739
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    4a
    ABC
    1Principal$200,000
    2Term years30
    3  initial3
    4  subsequent27
    5Rate
    6  initial6%
    7  subsequent4%
    8Fees1,500.00
    9Payment1,008.69
    10Calculated rate4.5247%
    11Payment calc1,008.690.00
    Excel 2003

    Worksheet Formulas
    CellFormula
    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 by Dave Patton; Nov 19th, 2011 at 02:40 AM.

  3. #3
    New Member
    Join Date
    Nov 2011
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    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 by guttal; Nov 19th, 2011 at 12:04 PM. Reason: Correct spelling and clarify numbers used on loan example

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,739
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

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

    B14 198,500
    B15:B374 payments as cited

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,739
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    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.

  7. #7
    New Member
    Join Date
    Nov 2011
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    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%?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,739
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    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 by Dave Patton; Nov 20th, 2011 at 12:23 AM.

  9. #9
    New Member
    Join Date
    Nov 2011
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    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/Mortga...djustable.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.

  10. #10
    Board Regular
    Join Date
    Jan 2010
    Location
    Bentonville, AR
    Posts
    436
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help calculating APR for an adjustable rate loan

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •