Seasonal Principal payments on a term loan [duplicate]

This question already has an answer here:

I need a way to calculate the loan amortization on a 7 year term loan that only has 8 principal and interest payments per year and 4 interest only payments.

marked as duplicate by Dheer, Pete B., mhoran_psprep, Grade 'Eh' Bacon, Nathan LJan 4 '18 at 16:25

• Can you do an amortization schedule in a spreadsheet? How are the payments scheduled (e.g. are the interest-only in 4 consecutive months) I doubt there's a closed-form formula for such a schedule. – D Stanley Jan 3 '18 at 21:31
• Which months are interest only? That will have an impact on the total interest paid, which affects the amortization. – chepner Jan 3 '18 at 23:48
• This question is not an exact duplicate because in each year there are 4 interest-only repayments in this question. In the so-called duplicate question the 4 months accumulate interest. – Chris Degnen Jan 5 '18 at 7:25

The answer for the seven year case is given in the final section.

Taking a simple two-year example to begin with, with interest-only payments in Sep, Oct, Nov & Dec, the calculation could look like this.

Supposing a principal of £100,000 and a monthly interest rate of 1%.

The principal remaining is p and the principal-and-interest payments are d.

s = 100000
r = 0.01

p = s
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)

p = p - (r p - r p)
p = p - (r p - r p)
p = p - (r p - r p)
p = p - (r p - r p)

p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)

p = p - (r p - r p)
p = p - (r p - r p)
p = p - (r p - r p)
p = p - (r p - r p)

Solve[p == 0, d]
{{d -> 6794.459682089437}}

The sixteen principal-and-interest payments should be £6,794.46

Check

p, p, p, p, and p are all the same, as are p, p, p, p, and p.

d = 6794.459682089437

Discounting all cash flows to net present value and summing should equal the principal. True

Equivalently

Omitting the interest-only repayments computes the same value for d.

This requires numbering the steps differently, necessary for the later recurrence calculation. (The step numbers no longer correspond to the month numbers.)

s = 100000
r = 0.01

p = s
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)

p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)
p = p - (d - r p)

Solve[p == 0, d]
{{d -> 6794.459682089437}}

This can be calculated more simply by the standard loan formula.

s = 100000
r = 0.01
n = 16
d = r s/(1 - (1 + r)^-n) = 6794.459682089437

The recurrence equation for the principal remaining p at the end of step x is derived from

p[x + 1] = p[x] (1 + r) - d where p = s giving

p[x] = (d - d (1 + r)^x + r (1 + r)^x s)/r

So the principal remaining in step 8 (August) is

p = 51989.0159847336

p is zero so the total payments are 16 d + 4 r p = 110,790.92

Seven Year Model

The above calculation can be extended to a seven year model like so.

s = 100000
r = 0.01
n = 7 * 8
d = r s/(1 - (1 + r)^-n) = 2340.824395505267

Using the formula for the principal remaining

p[x] = (d - d (1 + r)^x + r (1 + r)^x s)/r

the principal remaining in August each year is

p  = 88890.37077626715
p = 76860.23427430636
p = 63833.32029353705
p = 49727.03913582063
p = 34451.95799271273
p = 17911.23394788499
p = 0

so the total repayments are

7 * 8 d + 4 r (p + p + p + p + p + p) = 144,353.13

If the interest-only repayments were in say Jun, Jul, Aug & Sep the calculation would be

7 * 8 d + 4 r (p + p + p + p + p + p + p) = 145,890.56

Plotting the values for principal remaining in the latter case, i.e.

p, p, p, p, p, p, p, p, p, p, p ... p

Note the repetitions of p and (not shown) p, p, etc. 