Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Adjustable Rate Mortgage vs Fixed (2000)

    First of all, happy Thanksgiving to all!

    Problem: I am trying to compare a fixed rate 30-year mortgage with a 5-year adjustable rate mortgage over the first 8 years of eac loan. Specifically, assume loan amount is $500,000, 30-year fixed rate of interest is 5.5%. Assume further I also have a proposal for an adjustable rate mortgage that is fixed for the first 5 years at 5.15%, but thereafter, can increase in the 6th year by up to 2%, and 1% per year thereafter. Therefore, assuming a worse case scenario, the interest rate for year 6 would be 7.15%, year 7 would be 8.15%, and year 8, 9.15%. I am struggling with coming up with a side-by-side comparison of the two proposals that would give me in essence what the effective rate on the adjustable rate mortgage is over an 8 year time-frame. Any help?
    Thanks in advance.
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    You can download a free ARM Loan Calculator for Excel.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    Thanks, Hans, that looks like a very useful tool. However, I was looking for something (program or formula) that would calculate the "effective" rate of the adjustable mortgage so as to compare with the fixed.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    Sorry, can't help you with that.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    Anybody else out there?

  6. #6
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    I've only done an effective interest calculation on fixed rate loan.

    Basically I had a column which had the principal amount (as a negative) & the monthly repayments (as a positive). I then applied the IRR function to calculate the interest rate. As this was a monthly rate of return I then multiplied this by 12.

    Regards,

    Matthew

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    Thanks. Think maybe you might send it along?

  8. #8
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    Attached is a cut down version of my calculator. Its not pretty, but it has been working for my firm for the last 8 years. I have shaded the column that does the IRR calculation.

    I hope it helps.

    Regards,

    Matthew

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    How about setting up an amortization schedule then calculating the "effective interest rate" based on the "average payment" over the period.

    This also compares the amount of interest and principal paid.

    Steve

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    Thanks to all. Steve, would you mind explaining the formula in the column that has the adjustable rate?
    Thanks.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adjustable Rate Mortgage vs Fixed (2000)

    =$I$3+(A12>$J$2*$B$3)*$J$3+MAX(0,INT((A12-$J$2*$B$3-1)/$B$3))*$K$3

    I$3 is the starting rate (= 5.15%)

    (A12>$J$2*$B$3)*$J$3
    If A12 (period) is > $J$2*$B$3 (5*12 = 5 years) then add $J$3 (2%)

    Last segment is:
    MAX(0,INT((A12-$J$2*$B$3-1)/$B$3))*$K$3

    (A12-$J$2*$B$3-1)
    Is the number of periods past 5 years

    (A12-$J$2*$B$3-1)/$B$3
    Is the number of years past 5 years

    INT((A12-$J$2*$B$3-1)/$B$3)
    Is the number of complete years past 5 years

    MAX(0,INT((A12-$J$2*$B$3-1)/$B$3))
    Since this number may be negative, and we only care about positive, we take the max with 0

    MAX(0,INT((A12-$J$2*$B$3-1)/$B$3))*$K$3
    The number of complete years past 5 years is multiplied by $k$3 (1%)

    Thus we start with 5.15%, add 2% if >5 years, and then add 1% for each year past that.

    Steve

Posting Permissions

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