1. ## 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?
Jeff

3. ## 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.

5. ## Re: Adjustable Rate Mortgage vs Fixed (2000)

Anybody else out there?

6. ## 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. ## Re: Adjustable Rate Mortgage vs Fixed (2000)

Thanks. Think maybe you might send it along?

8. ## 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. ## 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. ## 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. ## 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%)