Payment calculation w/ back interest

K

Keeb

I was wondering how I can use the PMT function (or similar) to take into
account unpaid/back interest. There is no interest on the interest so I
can't capitilize it. Any suggestions on how to setup a formula so that I can
figure out a monthly payment for a fixed term? Thanks in advance :)
 
J

joeu2004

Keeb said:
I was wondering how I can use the PMT function (or similar)
to take into account unpaid/back interest. There is no interest
on the interest so I can't capitilize it. Any suggestions on how
to setup a formula so that I can figure out a monthly payment
for a fixed term?
Click to expand...

This seems a little too simple to solve, so I am probably
misunderstanding the problem. Word to the wise: concrete
numerical examples are always better than abstract questions.
They help to ensure that you get a correct solution.

"No interest on the interest"!? I'm surprised. In effect, that
is a free loan, especially if you delay all payments until the
termination date :).

But if that truly is the case, if you have $1200 in unpaid
interest and you must pay it back in 12 months, the monthly
payment is simply $100 = $1200/12.

If, in addition, you have a regular loan with an outstanding
balance of $12000 that must be repaid in 12 months at a
nominal annual interest rate of 12%, the monthly payment
is $1066.19 = pmt(12%/12, 12, -12000).

The combined monthly payment is simply $1166.19 =
$1066.19 + $100.
 
K

Keeb

Sorry, I should have added a couple of things:

1) There is an unpaid principal.

2) Any payments I make now will go towards paying the back interest first.
Regular interest still accrues on the principal until the back interest is
paid off.

So lets assume the principal is $9300 and the back interest is $2300. I'm
in the position of dealing with the lender such that I can choose my
repayment schedule (within reason I suppose) Only thing I can't change is
the interest rate.

The problem I have in trying to devise a formula is that new interest is
accruing while I pay the old back interest which leads to more new(back)
interest. I know that eventually the payments will be what they would have
been from the start had there been no back interest in the first place and I
make a sufficiently large enough payment to start with.

So given a specific monthly payment I want to find out how long it will
take for the old back interest and the new accrued interest to reach $0

Ideally I would want that monthly payment to be close to what I would like
to pay on the loan from that point forward. In other words I want to feed my
loan payment amount into the back interest calculation to see how much more
time it takes to pay off the loan.
 
F

Fred Smith

I read this more than once, but still came to the same conclusion. Surely the
new principal is $11,600. Just feed $11,600 as the present value to PMT, and it
will calculate the payment which will pay off the loan in the time requested. Or
use NPER to calculate the term given a payment amount.
 
J

joeu2004

Fred Smith said:
I read this more than once, but still came to the same
conclusion. Surely the new principal is $11,600. Just
feed $11,600 as the present value to PMT
Click to expand...

Wouldn't that effective charge interest on the back-interest?
(Yes.)
 
J

joeu2004

Keeb said:
2) Any payments I make now will go towards paying the
back interest first. Regular interest still accrues on the
principal until the back interest is paid off.
Click to expand...

Okay, that changes things somewhat. My previous response
assumed that you would pay off the back-interest and the
outstanding loan over the same period of time.
lets assume the principal is $9300 and the back interest is
$2300. I'm in the position of dealing with the lender such
that I can choose my repayment schedule (within reason I
suppose) Only thing I can't change is the interest rate.
Click to expand...

If it takes N months to pay off the back-interest, the balance
of the loan will grow to FV(intr/12, N, -9300), where "intr"
is the nominal annual interest rate.
I know that eventually the payments will be what they
would have been from the start had there been no back
interest in the first place [...].

So given a specific monthly payment I want to find out how
long it will take for the old back interest and the new accrued
interest to reach $0
Click to expand...

Try this:

A1: original loan payment
A2: nominal annual interest rate
A3: =roundup(2300/A1,0) 'number of months to pay back-interest
A4: 2300/A3 'payment to pay back-interest
A4: =fv(A2/12, A3, -9300) 'new balance of loan after A3 months
A5: =nper(A2/12, A1, -A4) 'number of months to pay back loan
'with original payment
A7: =A3+A5 'total time to pay back everything

I broke it into components so that you can play what-if
games. For example, replace A4 with a preferred payment,
and replace A3 with =roundup(2300/A4,0). Or replace A3
with a preferred number of months to pay back-interest.
Or replace A5 with =pmt(A2/12,A6,-A4), where A6 contains
a preferred number of months to pay back the loan balance.

Alternatively, of course you could combine all the arithmetic
into one formula.
 
J

joeu2004

Fred Smith said:
I read this more than once, but still came to the same
conclusion. Surely the new principal is $11,600. Just feed
$11,600 as the present value to PMT
Click to expand...

Also, doesn't that presume that the back-interest and outstanding
balance are paid off little by little at the same time? (Yes.)

The OP specifically said that the back-interest should be paid
off before starting (again) to pay off the loan balance.

By the way, that explains why the lender is willing not to
charge interest on the back-interest. He is earning more
interest while the loan balance remains unpaid. In fact, in
deference to that fact, I meant to suggest in my 2nd response
that the OP might want to play with what-if games to find a
strategy that minimizes total interest. Obviously, paying off
the back-interest in one lump sum does that. But I presume
there is a trade-off between reducing the back-interest
payback time and the back-interest payback amount. Another
what-if strategy might be to use larger-than-original payments
to pay back the new outstanding loan balance -- again,
trading off cash-flow demand against short time and less
total interest.
 
J

joeu2004

Errata....

A1: original loan payment
A2: nominal annual interest rate
A3: =roundup(2300/A1,0) 'number of months to pay back-interest
A4: 2300/A3 'payment to pay back-interest
A5: =fv(A2/12, A3, -9300) 'new balance of loan after A3 months
A6: =nper(A2/12, A1, -A5) 'number of months to pay back loan
'with original payment
A8: =A3+A6 'total time to pay back everything

And I should have noted that A3 assumes that you want the
back-interest payment to be close (but probably less than)
the original loan payment.
Or replace A5 with =pmt(A2/12,A6,-A4), where A6 contains
a preferred number of months to pay back the loan balance.
Click to expand...

"Replace A6 with =pmt(A2/12,A7,-A5), where A7 ...".
 
Top