Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




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




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:
The quarterly payment required for a $20,000 loan at an annual interest rate of 10% for 6 years is Formula:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.misc




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




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




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




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 6yr 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, (20000300)) 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 
Display Modes  


Similar Threads  
Thread  Forum  
mortgage payment calculation  Excel Worksheet Functions  
Question on Payment function.  New Users to Excel  
Mortgage calculation after a large extra payment  Excel Discussion (Misc queries)  
Mortgage calculation after a large extra payment  Excel Worksheet Functions  
Payment calculation w/ back interest  Excel Discussion (Misc queries) 