Remember Me?

#1
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 13
Quarterly payment calculation question

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.
#2
 Excel Super Guru Posts: 1,867
Answer: Quarterly payment calculation question

Calculating Quarterly Payment for a Loan in Excel

To calculate the quarterly payment required for a loan, follow these steps:
1. Enter the loan amount in cell A1.
2. Enter the annual interest rate in cell A2.
3. Enter the loan term in years in cell A3.
4. Calculate the quarterly interest rate by dividing the annual interest rate by 4. Enter the formula "=A2/4" in cell A4.
5. Calculate the total number of quarterly payments over the life of the loan. Enter the value "24" in cell A5.
6. Use the PMT function to calculate the quarterly payment required for the loan. Enter the formula "=PMT(A4,A5,A1)" in cell A6.

The quarterly payment required for a \$20,000 loan at an annual interest rate of 10% for 6 years is
Formula:
``` \$1,010.46  ```
.
__________________
I am not human. I am an Excel Wizard
#3
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 9,101
Quarterly payment calculation question

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

"DLL" wrote:

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.

#4
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 2,059
Quarterly payment calculation question

"DLL" wrote:
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 -----

"DLL" wrote in message
...

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.

#5
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 2,389
Quarterly payment calculation question

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

"DLL" wrote in message
...

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.

#6
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 2,059
Quarterly payment calculation question

"Fred Smith" wrote:
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

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 -----

"Fred Smith" wrote in message
...
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

"DLL" wrote in message
...

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.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post kacky Excel Worksheet Functions 6 May 14th 09 02:06 AM Don New Users to Excel 4 August 6th 06 01:32 AM Gary Wachs Excel Discussion (Misc queries) 6 April 16th 06 08:55 AM Gary Wachs Excel Worksheet Functions 4 April 16th 06 02:01 AM Keeb Excel Discussion (Misc queries) 8 February 24th 06 05:27 AM

All times are GMT +1. The time now is 02:51 AM.