loan amortization table with uneven payments

[mjantzen]

I need some help. Does anyone know how to create an amortization table where the interest and principal payments are based on number of units shipped and the shipment schedule instead of even payments for a set number of months.

 

[mark.wagner]

shouldn't be a problem. Your payments will be variable, based on the qty shipped. You calc the principal reduction as a % of the total volume as you can see in the formula bar below.

Book2
	A	B	C	D	E	F	G	H
1
2				LoanAmount	10,000
3				Rate	8%
4				TotalUnitstoShip	50,000
5
6
7
8
9	Month	UnitsShipped	BeginningBal	Interest	Principal	TotalPayment	EndBalance
10	1	1,000.00	10,000.00	66.67	200.00	266.67	9,800.00
11	2	2,000.00	9,800.00	65.33	400.00	465.33	9,400.00
12	3	3,000.00	9,400.00	62.67	600.00	662.67	8,800.00
13	4	10,000.00	8,800.00	58.67	2,000.00	2,058.67	6,800.00
14	5	12,000.00	6,800.00	45.33	2,400.00	2,445.33	4,400.00
15	6	15,000.00	4,400.00	29.33	3,000.00	3,029.33	1,400.00
16	7	4,000.00	1,400.00	9.33	800.00	809.33	600.00
17	8	2,000.00	600.00	4.00	400.00	404.00	200.00
18	9	500.00	200.00	1.33	100.00	101.33	100.00
19	10	500.00	100.00	0.67	100.00	100.67	0.00
20
21		50000		343.33	10,000.00	10,343.33
22
23
24
Sheet1

 

[Andrew Poulsom]

Something like this?

Book8
	A	B	C	D	E	F
1	Rate	7%
2	Loan	10000.00	31/12/05
3	Date	Principal	Interest	Total	Balance	Shipments
4	31/12/05				10,000.00
5	30/06/06	-1,600.00	-347.12	-1,947.12	8,400.00	400
6	31/12/06	-2,000.00	-296.42	-2,296.42	6,400.00	500
7	30/06/07	-1,200.00	-222.16	-1,422.16	5,200.00	300
8	31/12/07	-2,400.00	-183.50	-2,583.50	2,800.00	600
9	30/06/08	-2,000.00	-97.73	-2,097.73	800.00	500
10	31/12/08	-800.00	-28.23	-828.23	0.00	200
11		-10,000.00	-1,175.16	-11,175.16		2,500
Sheet1