arm loan

[jbl]

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!

 

[hatman]

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

Book1
	A	B	C	D	E
1	Payment#	Principle	Interest	PaymentAmt
2		200000
3	1	199752.6667	1166.666667	1414
4	2	199503.8906	1165.223889	1414
5	3	199253.6633	1163.772695	1414
6	4	199001.9763	1162.313036	1414
7	5	198748.8211	1160.844862	1414
8	6	198494.1893	1159.368123	1414
9	7	198238.072	1157.882771	1414
10	8	197980.4608	1156.388754	1414
11	9	197721.3468	1154.886021	1414
12	10	197460.7213	1153.374523	1414
13	11	197198.5755	1151.854208	1414
14	12	196934.9006	1150.325024	1414
15	13	196669.6875	1148.78692	1414
16	14	196402.9273	1147.239844	1414
17	15	196134.6111	1145.683743	1414	PAYMENT
18	16	195864.7296	1144.118565	1414	$2,069.26
19	17	195593.2739	1142.544256	1414
20	18	195320.2347	1140.960764	1414
21	19	195045.6027	1139.368036	1414
22	20	194769.3687	1137.766016	1414
23	21	194491.5234	1136.154651	1414
24	22	194212.0573	1134.533886	1414
25	23	193930.9609	1132.903667	1414
26	24	193648.22	1131.263939	1414
27	1	193515.4502	1936.482249	$2,069.26	ChangeInterestHere
28	2	193381.3478	1935.154502	2069.256926
Sheet1

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]

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]

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:

Book1
	A	B	C	D	E
1	Payment#	Principle	Interest	PaymentAmt
2		200000
3	1	201166.6667	1166.666667	1414
4	2	200926.1389	1173.472222	1414
5	3	200684.208	1172.069144	1414
6	4	200440.8659	1170.65788	1414
7	5	200196.1043	1169.238384	1414
8	6	199949.9149	1167.810608	1414
9	7	199702.2894	1166.374504	1414
10	8	199453.2194	1164.930022	1414
11	9	199202.6965	1163.477113	1414
12	10	198950.7123	1162.01573	1414
13	11	198697.2581	1160.545822	1414
14	12	198442.3254	1159.067339	1414
15	13	198185.9057	1157.580232	1414
16	14	197927.9901	1156.08445	1414
17	15	197668.5701	1154.579942	1414	PAYMENT
18	16	197407.6367	1153.066659	1414	$2,086.53
19	17	197145.1813	1151.544548	1414
20	18	196881.1948	1150.013557	1414
21	19	196615.6685	1148.473636	1414
22	20	196348.5932	1146.924733	1414
23	21	196079.96	1145.366794	1414
24	22	195809.7598	1143.799767	1414
25	23	195537.9833	1142.223599	1414
26	24	195264.6216	1140.638236	1414
27	1	195803.2678	1952.646216	$2,093.75	ChangeInterestHere
28	2	195667.5505	1958.032678	2093.75
Sheet1