# arm loan

#### jbl

##### New Member
I am trying to find the payment amount for an arm loan. This is a \$200,000, five year interest only loan, with a 7% interest rate, amoritized for 30 years. THe payment for years 1-5 is interest only so it does not affect the principle. The payment for years 6 and 7 will be \$1414, leaving a principle balance of \$193,660. In year 8 the rate goes up by 5%. My example said the payment should be \$2094, but I get \$2069.
Here is my formula:

=PMT((B1+C1)/12,23*12,D1)
B1=7%
C1=5%
D1=\$193660

Can anyone tell me where my error is?

Thanks!

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### hatman

##### Well-known Member
As far as I can tell, you didn't do anything wrong. I confirmed your numbers with a modified Amortization Table

If you copy this down to 276 payments (23 years at 12 payments per year) it converges within a ridiculously SMALL number. Are you sure that the difference is the monthly Loan Insurance? Though that seems like an awful small number to be that. IS there some other Loan Fee that gets added onto each monthly payment? That's the only thing I can think of...

#### Seti

##### Well-known Member
Are you sure that it said 2,094 and not 2,049?

If it said 2,049, then it is a timing thing assuming that the 1st payment in year 8 is due at the start of year 8 rather than at the end of the month.

#### hatman

##### Well-known Member
Along the lines of what Seti points out, another possibility is that the Amortization table has a SHIFT in it, where the payment is applied in the beginning of the FOLLOWING month... this causes the standard PMT() and FY() functions to break down, and the principle actually goes above 200,000 to start with. I made this converge within a couple of cents at the end of 23 years, though I calculated 2093.75, rather than 2094 (rounding error?).

A point for further discussion: This method of shifting the paymnet would need to commence at the end of the 7th year... if it started at the end of the 5th year, then the expecte dpayment in years 6 and 7 would be 1423 instead of 1414... and then years 8 though 30 would be 2092.93...

here is my sample anyway:

Replies
8
Views
677
Replies
4
Views
822
Replies
8
Views
1K
Replies
1
Views
925
Replies
0
Views
1K

1,191,689
Messages
5,988,035
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back