Amortization Table with Early Payments

[Curtis A Lager]

Through an Amortization Table, my goal is to display an amount that may vary per month, as an “Extra Payment” [Column C18], that way I can show how it affects paying off the mortgage sooner, particularly paying less interest. I used an Amortization Table already prepared via another source “Richmore.com” and couldn’t get my question answered. I also went through the 17 MrExcel Message Board “Amortization” references and either I missed it or I couldn’t find the answer. If I may, the data structure is:

C2 = $136,963.00 (pv)
C3 = 4.375% ((interest rate)
C4 = 12 (payments per year)
C5 = +C3/C4 (rate) (0.003645833)
C6 = 30 (number of years)
C7 = +C4*C6 (nper) (360)
C8 = PMT(C5,C7,C2) (($863.84)
C9 = 7/1/2004 (First Payment Date)

Column Headings A18 through F18
A18 = Date; B18 = Period; C18 = Principal; D18 = Interest; E18 = Balance

Note: {Column C18 Extra Payment} does not exist yet with my current data as listed below)

Data starts at B19 as 0 and F19 =pv ($136,963.00 beginning balance) and then remaining data continues at A20

A20 =IF(C9="",DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),C9)
A21 =DATE(YEAR(A20),MONTH(A20)+1,1)

B20 =+B19+1 (B19 was 0)
C20 =PPMT(rate,B20,nper,pv)
D20 =payment - C20
E20 =E19 + C20
Column C18 data will be entered in manually, but I would start out with $201.56 as I have already been paying that extra amount for my current mortgage. I didn’t know how to handle the formula in the Principal, Interest and Balance columns. Any help would be greatly appreciated.

 

[Chitosunday]

Did i interpret it right that 201.56 was paid in advance on start 0 day. Then your adjusted principal is
Adjusted pv is =$136,963.00-201.56
Use this as your original principal

 

[Andrew Poulsom]

Like this?

BOOK2
	A	B	C	D	E	F	G	H	I
1		pv	$136,963.00
2		interestrate	4.375%
3		paymentsperyear	12
4		rate	0.003645833
5		numberofyears	30
6		nper	360
7		payment	$683.84
8		FirstPaymentDate	07/01/04
9		Extrapayment	$201.56
10	Date	Period	Principal	Interest	Balance	Extra	Principal	Interest	Balance
11					$136,963.00				$136,963.00
12	07/01/04	1	-$184.49	-$499.34	$136,778.51	$201.56	-$386.05	-$499.34	$136,576.95
13	08/01/04	2	-$185.16	-$498.67	$136,593.34	$201.56	-$387.46	-$497.94	$136,189.49
14	09/01/04	3	-$185.84	-$498.00	$136,407.50	$201.56	-$388.87	-$496.52	$135,800.62
15	10/01/04	4	-$186.52	-$497.32	$136,220.99	$201.56	-$390.29	-$495.11	$135,410.33
16	11/01/04	5	-$187.20	-$496.64	$136,033.79	$201.56	-$391.71	-$493.68	$135,018.61
Sheet1

Formulas:

F12 - =$C$9
G12 - =-($C$7+F12)-H12
H12 - =-I11*$C$4
I12 - =I11+G12

copied down.

You can start the extra payment when you want - just remove the formulas.

 

[LagerCurtis]

Andrew Poulsom Thank you very much. Sorry that I haven't responded sooner; I didn't attack the situation until recently and then I could not get into MrExcel; I had to register. Anyway, your example works fine. I've also added to the left of the Balance, a column for Insurance & taxes which go along with the mortgage payment. Thanks again for your professionalism. Curtis....