Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
APR Rate on a Loan
Can anyone help me with this? I'm trying to find the Interest Rate of a loan if I'm given Original amount of loan, term and Payment. Example A2 = Original Amt of Loan A3 = Term in Months A4 = Payment A5 = Interest Rate Yearly B2 = 17420.00 B3 = 48 B4 = 413 B5 = This should be 6.49 %. Either I'm slow or I'm missing some steps. Can anyone give me a formula to get this rate? Thanks, Union -- Union70 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
APR Rate on a Loan
On Fri, 27 Jan 2006 21:50:46 +0000, Union70
wrote: Can anyone help me with this? I'm trying to find the Interest Rate of a loan if I'm given Original amount of loan, term and Payment. Example A2 = Original Amt of Loan A3 = Term in Months A4 = Payment A5 = Interest Rate Yearly B2 = 17420.00 B3 = 48 B4 = 413 B5 = This should be 6.49 %. Either I'm slow or I'm missing some steps. Can anyone give me a formula to get this rate? Thanks, Union Look at the RATE worksheet function. It will give you the interest rate PER PERIOD. Since your periods are MONTHS and you want the annual rate, merely multiply your answer by 12. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
APR Rate on a Loan
"Union70" wrote:
I'm trying to find the Interest Rate of a loan if I'm given Original amount of loan, term and Payment. Example A2 = Original Amt of Loan A3 = Term in Months A4 = Payment A5 = Interest Rate Yearly B2 = 17420.00 B3 = 48 B4 = 413 B5 = This should be 6.49 % 6.49% is the nominal annual interest rate. I suspect that is indeed what you want. But your subject line asks for the APR, aka effective annual interest rate, which is something else. To compute 6.49% with the above numbers, you could use: =12*RATE(48, 413, -17420) RATE(...) computes the nominal monthly rate. Multiplying by 12 gives the nominal annual rate -- 6.4858% in this case. To determine the effective annual interest rate (what you really pay in interest due to monthly compounding), you could use: =FV(RATE(48,413,-17420),12,,-1) - 1 Remember to format the cell as Percentage with 2 or more decimal places. The result is 6.6821% in this case. Caveat: Technically, even that is not the "APR", at least not in the US mortgage business. US law requires that the mortgage APR on the Reg Z document include a litany of closing costs. Unfortunately, not every lender interprets the law the same way, so it is difficult to compare what I call the "Reg Z APRs". However, I believe that most lenders quote the "APR" computed above in advertistements, since the "Reg Z APR" cannot be known until near closing. Be sure you understand which "APR" a lender is quoting. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
APR Rate on a Loan
Everyone thanks for the suggetions. I'm going to use the rate formula. It works out great. I want to thank everyone for their input. Thanks, Union -- Union70 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fv & Emi! | Excel Worksheet Functions | |||
XNPV vs. NPV(quarterly) different results | Excel Worksheet Functions | |||
loan calculator, int rate changes and fixed prin payment | Excel Discussion (Misc queries) | |||
Variable rate loan amortization template | Excel Discussion (Misc queries) | |||
Variable rate loan amortization template | Excel Discussion (Misc queries) |