Variable-Rate-Level-Payment Mortgage

[theinc1]

Hi,
I'm trying to construct an amortization schedule but since this is not a regular schedule I am having some trouble with it.

Here's what I am working on:
15-year loan
years 1-2 have one rate
years 3-4 different rate
years 5-6 different rate
years 7-15 different rate

I can do a regular schedule with no problem but I am having a hard time with this one. Also, can someone please explain how would the payments be leveled if there are different interest rates involved. Another thing, I'm also having a hard time working with IPMT as I have never used that function before.

 

[shg]

Welcome to the board.

You could make an amortization schedule with the different interest rates, and then use Solver to calculate the constant payment that liquidates the loan at 15 years.

 

[theinc1]

Do you mean make an amortization schedule for each rate?
Sorry if I sound kind of new but I have never done a schedule that has more than one interest rate so all of this is new to me.

 

[meldoc]

Hello

This may help, see the link.

http://www.youtube.com/user/ExcelIsFun#p/search/1/NjWDp-tw6y0

 

[shg]

Like this:

       --A-- ------B------ -C-- -----D----- ----E----
   1   Month               Rate     Pmt              
   2      1                  5%  $1,719.21           
   3     36                  6%                      
   4     60                  7%                      
   5     84                  8%                      
   6                                                 
   7    Pmt       Amt      Rate     Int       Prin   
   8      1   $200,000.00    5%    $833.33   $885.88 
   9      2   $199,114.12    5%    $829.64   $889.57 
  10      3   $198,224.55    5%    $825.94   $893.28 
  11      4   $197,331.27    5%    $822.21   $897.00 
  12      5   $196,434.27    5%    $818.48   $900.74 
  13      6   $195,533.53    5%    $814.72   $904.49 
  14      7   $194,629.04    5%    $810.95   $908.26 
  15      8   $193,720.79    5%    $807.17   $912.04 
  16      9   $192,808.74    5%    $803.37   $915.84 
  17     10   $191,892.90    5%    $799.55   $919.66 
  18     11   $190,973.24    5%    $795.72   $923.49 
  19     12   $190,049.75    5%    $791.87   $927.34 
  20     13   $189,122.41    5%    $788.01   $931.20

The Rate column is a lookup into the table above. D2 was solved to force the balance to 0 after the 180th payment.

 

[theinc1]

Would it be the same level payment for the whole loan or every rate would have a different payment?
That part has me confused

 

[shg]

Would it be the same level payment for the whole loan or every rate would have a different payment?

The same.

See http://www.box.net/shared/0uqibhpejr