Results 1 to 11 of 11

20061123, 14:58 #1
 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 30year mortgage with a 5year adjustable rate mortgage over the first 8 years of eac loan. Specifically, assume loan amount is $500,000, 30year 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 sidebyside 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 timeframe. Any help?
Thanks in advance.
Jeff

20061123, 15:05 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Adjustable Rate Mortgage vs Fixed (2000)
You can download a free ARM Loan Calculator for Excel.

20061123, 16:08 #3
 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.

20061123, 16:22 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Adjustable Rate Mortgage vs Fixed (2000)
Sorry, can't help you with that.

20061124, 00:57 #5
 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?

20061124, 01:33 #6
 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

20061124, 01:54 #7
 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?

20061124, 02:28 #8
 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

20061125, 13:14 #9
 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

20061125, 15:17 #10
 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.

20061126, 01:01 #11
 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$31)/$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$31)/$B$3))*$K$3
(A12$J$2*$B$31)
Is the number of periods past 5 years
(A12$J$2*$B$31)/$B$3
Is the number of years past 5 years
INT((A12$J$2*$B$31)/$B$3)
Is the number of complete years past 5 years
MAX(0,INT((A12$J$2*$B$31)/$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$31)/$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