Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to figure mortgage APR in excel?
I am trying to create a simple formula to accurately figure APR. Nothing
have used so far is close to the true answer. Is there a formula for this? Example Paramaters: loan amount: 150,000 Term: 30 years Rate: 6.5% Closing costs that affect APR: 2,000 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to figure mortgage APR in excel?
<is close to the true answer
If you tell us what you think the "true answer" is, it might save a lot of time. -- Kind regards, Niek Otten Microsoft MVP - Excel "Toby" wrote in message ... |I am trying to create a simple formula to accurately figure APR. Nothing | have used so far is close to the true answer. Is there a formula for this? | Example Paramaters: | | loan amount: 150,000 | Term: 30 years | Rate: 6.5% | Closing costs that affect APR: 2,000 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to figure mortgage APR in excel?
Toby wrote:
I am trying to create a simple formula to accurately figure APR. Nothing have used so far is close to the true answer. Is there a formula for this? Example Paramaters: loan amount: 150,000 Term: 30 years Rate: 6.5% Closing costs that affect APR: 2,000 For a US fixed-rate loan, try: =rate(30*12, round(pmt(6.5%/12, 30*12, -150000), 2), -(150000-2000)) Also try it without rounding (just pmt(...)). The formula might need to be modified for certain jurisdictions, notably Canada. Please indicate. See my response at http://groups.google.com/group/micro...537c3697f6 e7 for an explanation of why the APR might never be "the true answer", if by that you mean you are trying to match the results of a mortgage calculator. Notably, there might be hidden assumptions about the amount of prepaid interest. Finally, another potential mistake just occurred to me: some people might incorrectly include extraneous amounts in the periodic payment, such as property taxes and mortgage insurance. Those should not be included for the purposes of computing a Truth-in-Lending-compliant APR. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to figure mortgage APR in excel?
Errata....
I wrote: =rate(30*12, round(pmt(6.5%/12, 30*12, -150000), 2), -(150000-2000)) That should be ("12 *" added): =12 * rate(30*12, round(pmt(6.5%/12, 30*12, -150000), 2), -(150000-2000)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to figure mortgage APR in excel?
The correct APR should be 7.4459
"Niek Otten" wrote: <is close to the true answer If you tell us what you think the "true answer" is, it might save a lot of time. -- Kind regards, Niek Otten Microsoft MVP - Excel "Toby" wrote in message ... |I am trying to create a simple formula to accurately figure APR. Nothing | have used so far is close to the true answer. Is there a formula for this? | Example Paramaters: | | loan amount: 150,000 | Term: 30 years | Rate: 6.5% | Closing costs that affect APR: 2,000 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to figure mortgage APR in excel?
Toby wrote:
[Toby wrote earlier:] Example Paramaters: loan amount: 150,000 Term: 30 years Rate: 6.5% Closing costs that affect APR: 2,000 [....] The correct APR should be 7.4459 Who says? That is, where does that APR figure come from? What jurisdiction are you talking about? For example, US or Canadian? What is the periodic payment? What is the frequency of payment; for example, monthly, semimonthly or biweekly? For a US loan with monthly payments based on the parameters above, the correct APR is: =12 * RATE(30*12, ROUND(PMT(6.5%/12, 30*12, -150000), 2), -(150000-2000)) which results in 6.6295%, whether or not PMT(...) is rounded. Conversely, an APR of 7.4459% corresponds to total "loan fees" of about $13,683.50. Actually, between $13,682.83 and $13,684.17 because "7.4459%" can be the rounded value of a number between 7.44585% and 7.445949...9%. This can be computed as follows: =150000 - PV(7.4459%/12, 30*12, -ROUND(PMT(6.5%/12, 30*12, -150000), 2)) That seems to be too great a difference to be explained by "hidden loan fees". I wonder if the loan amount above ($150,000) does not take the down payment into account. But if that is the case, that changes everything above. PS: I really do not know how Canadian's determine APR. But based on a comment at one web site [1], I believe the following does the trick. =2 * (FV(RATE(30*12, ROUND(PMT(RATE(6, 0, -1, 1+6.5%/2), 30*12, -150000), 2), -(150000-2000)), 6, 0, -1) - 1) That results in 6.6324%; 6.6323% if PMT(...) is not rounded. Footnote ------------- [1] http://www.rbcroyalbank.com/products...iew_rates.html states that if there are no borrowing charges, the APR and interest rate are the same. That led to me to develop the formula above for the Canadian APR. I know the PMT(RATE(...)...) part is correct. And 2*(FV(...)-1) works fine when there are no borrowing charges; that is, it returns the original interest rate. But I could not find a Canadian mortgage calculator online that computes the APR with borrowing charges, and I could not find a web page that explains the math or Excel formulas for computing the Canadian APR. So I cannot confirm my computation. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to figure mortgage APR in excel?
I wrote:
Toby wrote: The correct APR should be 7.4459 Who says? That is, where does that APR figure come from? [....] For a US loan with monthly payments based on the parameters above, the correct APR is: [....] 6.6295%, whether or not PMT(...) is rounded. Conversely, an APR of 7.4459% corresponds to total "loan fees" of about $13,683.50. [....] That seems to be too great a difference to be explained by "hidden loan fees". I wonder if the loan amount above ($150,000) does not take the down payment into account. All of the above computations ass-u-me a fixed-rate US loan for the duration of the mortgage term, because you did not specify index and margin rates, which would be the telltale signs of an ARM. However, the APR for ARM might very well much higher than 6.63% (rounded). Also, all of the above computations ass-u-me that payments are regular and equal, since you did not specify otherwise. But irregular payments -- for example, fractional periods in the beginning or at the end -- might have a dramatic impact on the APR, even for a fixed-rate loan. Please be sure that you have provided all of the information possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
HOW CAN I PROTECT A FORMULA IN EXCEL | Excel Discussion (Misc queries) | |||
Excel formula Q | Excel Worksheet Functions | |||
Trouble writing an excel formula. | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions |