Amortization Schedules

[Ashash]

Is there a way to create an amortization schedule with non-monthly payments? For example, a lease may have payments due for 6 months out of the year and no payments due in the other 6. I am unable to figure out how to setup the schedule so that interest accrues in the off months and increases principal.

 

[dcardno]

Sure. Start with a pretty typical amortization schedule: cells above the table for principal, interest rate, and (monthly) payment in the months it is made), and columns for

• Date (Month, I imagine)
• Opening balance
• Accrued Interest
• Payment (in the months it is made)
• Closing balance

These will have the normal calculations in them:

• Opening Balance = prior month closing balance (except in "month 1," where it equals loan principal)
• Interest = stated rate * opening balance / 12 (unless you are charging interest on the actual number of days outstanding...)
• Payment = monthly payment, in the months where it is made
  • You can get fancy, and set up a table of which months are payment months, and which aren't and do some sort of lookup on that table for each month, or
  • You can just set the payment equal to the monthly payment for all months in the first year, delete the formula in the months where payment isn't required - then copy that block of formulas down through the amortization period
• Closing balance = Opening balance + Calculated Interest - Payment
  • In some months the payment will reduce the outstanding balance, and in some months it won't.

NOW comes the finance part - you can calculate the required payment to amortize the loan; the HP 12C Solutions Handbook has a nice example, if you can get your hands on one of those. Or you can do the easy thing - select the cell with the closing balance for the date that the load is supposed to be paid out. Use Goal Seek to set that value to zero by changing the value in the "monthly payment" cell.
If you really want to calculate the monthly payment directly, let me know - we can go through it, but Goal Seek is easier, if you don't mind having a requirement for a manual intervention before the schedule is ready for use. I suppose you could easily tie that calculation into the idea of a table of payment and non-payment months, but I haven't really thought about it.

 

[dcardno]

Okay - I got bored, so went back and set it up using a table to control whether a payment was made in a given month (which also includes the option of an extra payment, or partial payment, as shown in the example. The calculation can be set for any number (well, up to twelve) different payments, either fractions or multiples. The required payment will update with changes in principal, rate, or term. I didn't both to update the amortization schedule itself for term, but that's not hard.

Skipped_Payment.xlsx
	A	B	C	D	E	F	G
1	Debt Payments with Skipped / Increased Payments
2
3	Principal Amount	$	100,000.00
4	Face Rate	%	14.0%
5	Monthly Rate	%	1.17%	Skipped Payment Calculations
6	Term	Years	5	PV as Paid	Assumed in 12 Mo Calc	Payment Required
7	Std (12 mo) Payment	$/Month	(2,326.83)	$8.79	$11.14	(2,948.29)
8
9	Payment Schedule			As Paid	Implied in Calculation
10	Initial Advance	Sep-21
11	Payments	Oct-21	10	1.00	1.00
12		Nov-21	11	1.00	1.00
13		Dec-21	12	1.50	1.00
14		Jan-22	1	-	1.00
15		Feb-22	2	0.50	1.00
16		Mar-22	3	-	1.00
17		Apr-22	4	1.00	1.00
18		May-22	5	-	1.00
19		Jun-22	6	1.00	1.00
20		Jul-22	7	1.00	1.00
21		Aug-22	8	1.50	1.00
22		Sep-22	9	1.00	1.00
23
24			Opening Balance	Accrued Interest	Payment	Closing Balance
25		Sep-21				100,000.00
26		Oct-21	100,000.00	1,166.67	(2,948.29)	98,218.38
27		Nov-21	98,218.38	1,145.88	(2,948.29)	96,415.97
28		Dec-21	96,415.97	1,124.85	(4,422.43)	93,118.40
29		Jan-22	93,118.40	1,086.38	-	94,204.78
30		Feb-22	94,204.78	1,099.06	(1,474.14)	93,829.69
31		Mar-22	93,829.69	1,094.68	-	94,924.37
32		Apr-22	94,924.37	1,107.45	(2,948.29)	93,083.53
33		May-22	93,083.53	1,085.97	-	94,169.51
34		Jun-22	94,169.51	1,098.64	(2,948.29)	92,319.86
35		Jul-22	92,319.86	1,077.07	(2,948.29)	90,448.64
36		Aug-22	90,448.64	1,055.23	(4,422.43)	87,081.44
37		Sep-22	87,081.44	1,015.95	(2,948.29)	85,149.11
Sheet1

Cell Formulas
Range	Formula
C5	C5	=C4 / 12
C7	C7	=PMT(C5, C6 * 12, C3)
D7:E7	D7	=NPV($C$5, D11:D22)
F7	F7	=E7 / D7 * C7
C11:C22	C11	=MONTH(B11)
C26:C37	C26	=F25
D26:D37	D26	=C26 * $C$5
E26:E37	E26	=$F$7 * INDEX($D$11:$D$22, MATCH(MONTH(B26), $C$11:$C$22,0))
F26:F37	F26	=SUM(C26:E26)

Skipped_Payment.xlsx
	A	B	C	D	E	F	G
72		Aug-25	32,572.76	380.02	(4,422.43)	28,530.34
73		Sep-25	28,530.34	332.85	(2,948.29)	25,914.91
74		Oct-25	25,914.91	302.34	(2,948.29)	23,268.96
75		Nov-25	23,268.96	271.47	(2,948.29)	20,592.15
76		Dec-25	20,592.15	240.24	(4,422.43)	16,409.96
77		Jan-26	16,409.96	191.45	-	16,601.41
78		Feb-26	16,601.41	193.68	(1,474.14)	15,320.95
79		Mar-26	15,320.95	178.74	-	15,499.69
80		Apr-26	15,499.69	180.83	(2,948.29)	12,732.23
81		May-26	12,732.23	148.54	-	12,880.78
82		Jun-26	12,880.78	150.28	(2,948.29)	10,082.77
83		Jul-26	10,082.77	117.63	(2,948.29)	7,252.11
84		Aug-26	7,252.11	84.61	(4,422.43)	2,914.29
85		Sep-26	2,914.29	34.00	(2,948.29)	0.00
86		Oct-26
Sheet1

Cell Formulas
Range	Formula
C72:C85	C72	=F71
D72:D85	D72	=C72 * $C$5
E72:E85	E72	=$F$7 * INDEX($D$11:$D$22, MATCH(MONTH(B72), $C$11:$C$22,0))
F72:F85	F72	=SUM(C72:E72)