2

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 L Jan 4 '18 at 16:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    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
  • 1
    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
  • 1
    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
1

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[0] = s
p[1] = p[0] - (d - r p[0])
p[2] = p[1] - (d - r p[1])
p[3] = p[2] - (d - r p[2])
p[4] = p[3] - (d - r p[3])
p[5] = p[4] - (d - r p[4])
p[6] = p[5] - (d - r p[5])
p[7] = p[6] - (d - r p[6])
p[8] = p[7] - (d - r p[7])

p[9] = p[8] - (r p[8] - r p[8])
p[10] = p[9] - (r p[9] - r p[9])
p[11] = p[10] - (r p[10] - r p[10])
p[12] = p[11] - (r p[11] - r p[11])

p[13] = p[12] - (d - r p[12])
p[14] = p[13] - (d - r p[13])
p[15] = p[14] - (d - r p[14])
p[16] = p[15] - (d - r p[15])
p[17] = p[16] - (d - r p[16])
p[18] = p[17] - (d - r p[17])
p[19] = p[18] - (d - r p[18])
p[20] = p[19] - (d - r p[19])

p[21] = p[20] - (r p[20] - r p[20])
p[22] = p[21] - (r p[21] - r p[21])
p[23] = p[22] - (r p[22] - r p[22])
p[24] = p[23] - (r p[23] - r p[23])

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

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

Check

p[8], p[9], p[10], p[11], and p[12] are all the same, as are p[20], p[21], p[22], p[23], and p[24].

d = 6794.459682089437

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

enter image description here

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[0] = s
p[1] = p[0] - (d - r p[0])
p[2] = p[1] - (d - r p[1])
p[3] = p[2] - (d - r p[2])
p[4] = p[3] - (d - r p[3])
p[5] = p[4] - (d - r p[4])
p[6] = p[5] - (d - r p[5])
p[7] = p[6] - (d - r p[6])
p[8] = p[7] - (d - r p[7])

p[9] = p[8] - (d - r p[8])
p[10] = p[9] - (d - r p[9])
p[11] = p[10] - (d - r p[10])
p[12] = p[11] - (d - r p[11])
p[13] = p[12] - (d - r p[12])
p[14] = p[13] - (d - r p[13])
p[15] = p[14] - (d - r p[14])
p[16] = p[15] - (d - r p[15])

Solve[p[16] == 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[0] = 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[8] = 51989.0159847336

p[16] is zero so the total payments are 16 d + 4 r p[8] = 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[8]  = 88890.37077626715
p[16] = 76860.23427430636
p[24] = 63833.32029353705
p[32] = 49727.03913582063
p[40] = 34451.95799271273
p[48] = 17911.23394788499
p[56] = 0

so the total repayments are

7 * 8 d + 4 r (p[8] + p[16] + p[24] + p[32] + p[40] + p[48]) = 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[5] + p[13] + p[21] + p[29] + p[37] + p[45] + p[53]) = 145,890.56

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

p[0], p[1], p[2], p[3], p[4], p[5], p[5], p[5], p[5], p[5], p[6] ... p[56]

Note the repetitions of p[5] and (not shown) p[13], p[21], etc.

enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.