Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
DLL DLL is offline
external usenet poster
 
Posts: 13
Default 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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default 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
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 -----

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
mortgage payment calculation kacky Excel Worksheet Functions 6 May 14th 09 02:06 AM
Question on Payment function. Don New Users to Excel 4 August 6th 06 01:32 AM
Mortgage calculation after a large extra payment Gary Wachs Excel Discussion (Misc queries) 6 April 16th 06 08:55 AM
Mortgage calculation after a large extra payment Gary Wachs Excel Worksheet Functions 4 April 16th 06 02:01 AM
Payment calculation w/ back interest Keeb Excel Discussion (Misc queries) 8 February 24th 06 06:27 AM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"