Quarterly payment calculation question

[DLL]

I need to create a formula to calculate the quarterly payment required for a
$20,000 loan at an annual interest rate of 10% for 6 years. Any help is
appreciated. Thanks in advance.

 

[Joel]

Using simple quarterly interest of (10%/4)

=PMT(0.1/4,24,20000)

the 24 is the number of payments = 6 years x 4 payments per year

 

[JoeU2004]

I need to create a formula to calculate the quarterly payment required
for a $20,000 loan at an annual interest rate of 10% for 6 years.

That depends on the jurisdiction of the loan. The following works for US
loans:

=pmt(10%/4, 6*4, -20000)

With Excel financial functions, normally (but, sigh, not always), inflows
and outflows should have opposites, positive and negative. But which
direction is postive or negative is an arbitrary choice, depending on your
point of view (borrower v. lender; saver v. bank). Generally, I choose a
point of view so that the function returns a positive number.


----- original message -----

 

[Fred Smith]

If you truly have an "annual interest rate" of 10%, then you need to convert
it to quarterly to do your calculation. To do this, ask the question "what
quarterly interest rate will turn $1.00 into $1.10 in a year?" The answer
is:

=RATE(4,0,-1.00,1.10)

So your payment calculation becomes:
=PMT(RATE(4,0,-1,1.1),6*4,-20000)

My bet is that you actually have a quarterly rate of 2.5% (10%/4), and
therefore the other answers are more applicable to your situation.

Regards,
Fred

 

[JoeU2004]

If you truly have an "annual interest rate" of 10%, then you
need to convert it to quarterly to do your calculation. [....]
=RATE(4,0,-1.00,1.10)

As I said, this depends on the jurisdiction.

For US loans, all annual rates are always "truly" the periodic rate times
the payment frequency per year, as required by the Truth In Lending
regulations. Ergo, the period rate is always the annual rate divided by the
payment frequency per year.

This is true of both the APR and the simple annual rate.

However, for the purposes of computing the periodic payment, you do need to
use the simple periodic rate. That may or may not be the same as the APR
divided by the payment frequency per year.

In the US, the difference between the APR and simple annual rate is not the
compounding frequency (since neither is compounded).

Instead, the difference is: the APR may include finance charges other than
just interest.

If the APR includes more than interest, there is no way to convert the APR
to a simple annual or periodic rate without knowing all the finance charges
that were included in determining the APR.

For example, consider an APR of 10% for a 6-yr loan of $20,000 paid
quarterly, with loan origination fees of $300 and an annual PMI rate of
0.75%. (PMI is just one example of finance charges that might be added
periodically to the nominal loan payment.)

Then the payment covering principal, interest and PMI can be computed by:

=PMT(10%/4, 6*4, -(20000-300))

which is about $1101.48.

That might be sufficient for practical purposes.

But to determine the simple annual interest or the nominal quarterly loan
payment covering only principal and interest, we would need to subtract the
quarterly PMI premium. That is 20000*0.75%/4, about $37.50.

So the nominal quarterly loan payment covering just principal and interest
is about $1063.98

And the simple annual rate can be computed by:

=12*RATE(6*4, 1063.98, -20000)

which is about 8.2187%.

And to reiterate: all of the above applies to the US, but not necessarily
to other jurisdictions.


----- original message -----