Amortization Schedule

"Crossposted" to mrexcel.com/forum. My response from there.

Originally Posted by AEvans190

I am trying to make a spreadsheet that has a amortization schedule for a loan. The details on the loan.
$729,000
8%
15 years
Start date 12/1/14

However for the first 6 months it will be an interest only payment, (12/1/14-6/1/15 interest only payments)

After that regular payments will be made, (interest and principle)

One more thing, a balloon payment will be made 18 months after the start date to recapture the principle that was not paid for the first 6 months. (6/1/16 balloon payment to pay principle that was deferred during the interest only payments)

Downloand AEvans.xls (click here) [1]. Ignore any box.net preview errors. In summary:

	A	B	C	D	E	F	G	H	I
1	loan	729,000.00		Pmt#	Date	Pmt	Int	Prin	Bal
2	total term	180	months		12/1/2014				729,000.00
3	annl rate	8.00%		1	1/1/2015	4,860.00	4,860.00	0.00	729,000.00
4	int-only term	6	months	2	2/1/2015	4,860.00	4,860.00	0.00	729,000.00
5	int-only pmt	4,860.00		3	3/1/2015	4,860.00	4,860.00	0.00	729,000.00
6	unpaid principal	12,852.77		4	4/1/2015	4,860.00	4,860.00	0.00	729,000.00
7	recap pmt#	18	months	5	5/1/2015	4,860.00	4,860.00	0.00	729,000.00
8	reg pmt	6,976.29		6	6/1/2015	4,860.00	4,860.00	0.00	729,000.00
9				7	7/1/2015	6,976.29	4,860.00	2,116.29	726,883.71
19				17	5/1/2016	6,976.29	4,714.61	2,261.68	704,929.11
20				18	6/1/2016	19,829.06	4,699.53	15,129.53	689,799.58
21				19	7/1/2016	6,976.29	4,598.66	2,377.63	687,421.95
181				179	11/1/2029	6,976.29	92.09	6,884.20	6,928.80
182				180	12/1/2029	6,974.99	46.19	6,928.80	0.00

Please Login or Register  to view this content.

The additional midterm payment (B6, misnamed the "balloon" payment) is the amount of principal that would have been paid during the first 6 months of a normally-amortized loan.

The interest-only payment (B5) is the interest charged on the true outstanding balance (original loan, B1) during the first 6 months. That differs from the amount of interest that would have been paid during the first 6 months of a normally-amortized loan.

The regular payment (B8) after the first 6 months is the amount that reduces the normally-amortized original loan (B1) minus the additional midterm payment (B6) with payment #18 (B7) discounted back to the beginning of the regular-payment term (payment #7, B4+1) to zero over the remaining 174 months.

That's a mouthful. Let me know if you need further explanation.

The complexity of the formulas in F3 and G3 attempt to ensure that the final loan balance is exactly zero [2] and that the sum of the displayed final interest and principal payments equals the displayed final payment.


-----
[1] https://app.box.com/s/mtk89v41gf3b1r97n94b

[2] Exactly zero within the limitations of 64-bit binary floating-point arithmetic.

Originally Posted by AEvans190

Just wondering why there was a difference with the two solutions.

rcm's amortization schedule is incorrect, IMHO, at least for a "normally-amortized" loan.

rcm assumes a fixed amount of principal paid per month (column E). That plus interest (column F) would result in a variable monthly payment, which rcm does not include in the amortization schedule.

Most "closed-end" loans with a fixed interest rate have fixed monthly payments, paying variable amounts of principal and interest per month. That is the method of repayment that Excel financial functions like PMT, PV and FV assume.

One other difference is subject to interpretation. You wrote that the "start date" is 12/1/2014. I interpreted that to mean: that is when the loan funds are disbursed, and monthly payments start one month later on 1/1/2015.

rcm assumes that monthly payments start on 12/1/2014.

Which is it?

Originally Posted by AEvans190

Also principal and interest payments start 6/1/15. So only 5 months of interest only payments. I am sure it is a easy change

Funny, that was one of the "missteps" that I posted in the mrexcel.com discussion.

Yes, it is a simple tweak: simply change B4 to 5 instead of 6.

[EDIT] Nitpick.... Since B7 is payment number ("recap pmt#"), C7 really should be empty, not "months". It makes no real difference. But it might be a point of confusion if you say the first payment is really on 12/1/2014 (my question above).