Amortize in non periodic payments

[Nikos Koutantos]

I am trying to find a way to amortize a loan with non periodic payments,
that is, if I have two payments per year, they are not semiannual, but one
made in 6th month and the other in 9th (no payment due in 12th or 3rd month)
with either equal or unequal payments. I know the PMT function works for
periodic equal payments, but is there a formula, or some other way to get
this?

TIA
Nikos

 

[Harlan Grove]

I am trying to find a way to amortize a loan with non periodic payments,
that is, if I have two payments per year, they are not semiannual, but
one made in 6th month and the other in 9th (no payment due in 12th or 3rd
month) with either equal or unequal payments. I know the PMT function
works for periodic equal payments, but is there a formula, or some other
way to get this?

Amortization is just splitting loan payments into principal and interest
pieces, and the interest piece is always the periodic effective interest
rate times the outstanding principal balance. The remainder of the payment
is the principal. If the payment is less than rate times balance, you have
what's called 'negative amrotization', which means that the principal
portion of the payment is negative, so the outstanding principal balance
increases. There are some circumstances in which negative amortization isn't
allowed, but a separate accrued interest due balance is used to the same
effect.

So, if your *effective* annual interest rate were 6% and you made your first
payment at 6 months, the interest and principal portions would be

Intererst: =StartingBalance*(1.06^0.5-1)

Principal: =Payment-Interest

EndingBalance: =StartingBalance-Principal

If the original loan amount were $5,000, then a portion of an amortization
table for irregular payments could look like

Mon_StartBal_Payment_Interest_Princpl_EndBal
_0____________________________________5,000.00
_6__5,000.00__200.00__147.82___52.18__4,947.82
_9__4,947.82__100.00___72.60___27.40__4,920.42
18__4,920.42__200.00__219.80__-19.80__4,940.22
21__4,940.22__300.00___72.49__227.51__4,712.71
24__4,712.71__500.00___69.15__430.85__4,281.86
27__4,281.86__500.00___62.83__437.17__3,844.69
33__3,844.69__800.00__113.66__686.34__3,158.35
42__3,158.35__300.00__141.09__158.91__2,999.44

where, e.g., the 18 month figures are calculated as

Interest: =4290.42*(1.06^((18-9)/12)-1)

Principal: =200.00-219.80

EndingBalance: =4920.42+19.80

 

[Nikos Koutantos]

Thanks for your reply Harlan, however, it has got me a little puzzled.

Negative amortization was not at all in my mind, it is very well understood
that when the payment is less than interest due, remaining principal balance
increases.

What I am trying to come up with, is a way to calculate payments for a loan
with unequal payment periods. What I have in mind is to calculate the first
payment, say for 9 months, using the PMT function, and also the interest
payment, using the IPMT function, for that same first payment, and the
difference between these would be the principal portion of the payment which
would decrease the remaining principal balance, and this new balance would
be calculated for the next payment, say for 3 months, again with the PMT and
IPMT functions, and so on with remaining payments. This, of course, will
result in unequal payment amounts. Any ideas if we can calculate this with
equal payment amounts?

In your example, what is the total loan period used for your calculations?
And the formula you use to calculate interest, isn't it what the IPMT
function calculates? However, if I use the IPMT function with your example,
I get for the first payment after six months interest of 150, not 147.82.

 

[Guest]

Nikos,
Here is how I have my loan ammortized. In addition to your loan information
columns, create three columns titled last paid, next paid and days. Put your
loan payment dates in the two columns then insert a function caled days360 in
the third column. This function will calculate the number of days between
those payment dates. Then in another column insert this formula:
=principle X rate/360 * days 360 and this will be the amount of interest
that accrues between payments. In your balance column insert this formula: =
(principle+interest)-pmt. Copy these formulas down your columns where you
principle for the next payment will be equal to the balance of your last
payment.
Hope that helps.