| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating APR on a Mortgage
Can anybody help me determine APR using the following example:
30 year amortization 7% Rate $100,000.00 loan amount |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Oh, sorry figure $2000 in closing costs...
"Sean B" wrote: Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Haven't you already specified the APR (7%)?
If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
No, APR is different than interest rate, it takes into account the costs
associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. I just don't have a clue how to get there from here. any ideas? anybody? "Fred Smith" wrote: Haven't you already specified the APR (7%)? If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
We're still going to need to know the payment.
-- Regards, Fred "Sean B" wrote in message ... No, APR is different than interest rate, it takes into account the costs associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. I just don't have a clue how to get there from here. any ideas? anybody? "Fred Smith" wrote: Haven't you already specified the APR (7%)? If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Payment would be $665.30
I used the PMT calc for that which is no big deal, but I gotta tell ya, the APR thing has me stumped... Thanks Fred and anybody else out there who can help! Sean "Fred Smith" wrote: We're still going to need to know the payment. -- Regards, Fred "Sean B" wrote in message ... No, APR is different than interest rate, it takes into account the costs associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. I just don't have a clue how to get there from here. any ideas? anybody? "Fred Smith" wrote: Haven't you already specified the APR (7%)? If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Then the APR is 6.78%
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Sean B" wrote in message ... Payment would be $665.30 I used the PMT calc for that which is no big deal, but I gotta tell ya, the APR thing has me stumped... Thanks Fred and anybody else out there who can help! Sean "Fred Smith" wrote: We're still going to need to know the payment. -- Regards, Fred "Sean B" wrote in message ... No, APR is different than interest rate, it takes into account the costs associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. I just don't have a clue how to get there from here. any ideas? anybody? "Fred Smith" wrote: Haven't you already specified the APR (7%)? If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Thank you Peo. Now can you help me with the excel formula so I can
incorporate it into a spreadsheet that has all of the other info present? I could email you a bare bones setup... Thanks (hopefully), Sean "Peo Sjoblom" wrote: Then the APR is 6.78% -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Sean B" wrote in message ... Payment would be $665.30 I used the PMT calc for that which is no big deal, but I gotta tell ya, the APR thing has me stumped... Thanks Fred and anybody else out there who can help! Sean "Fred Smith" wrote: We're still going to need to know the payment. -- Regards, Fred "Sean B" wrote in message ... No, APR is different than interest rate, it takes into account the costs associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. I just don't have a clue how to get there from here. any ideas? anybody? "Fred Smith" wrote: Haven't you already specified the APR (7%)? If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
The formula would be
=RATE(30*12,-650.30,100000)*12 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Sean B" wrote in message ... Thank you Peo. Now can you help me with the excel formula so I can incorporate it into a spreadsheet that has all of the other info present? I could email you a bare bones setup... Thanks (hopefully), Sean "Peo Sjoblom" wrote: Then the APR is 6.78% -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Sean B" wrote in message ... Payment would be $665.30 I used the PMT calc for that which is no big deal, but I gotta tell ya, the APR thing has me stumped... Thanks Fred and anybody else out there who can help! Sean "Fred Smith" wrote: We're still going to need to know the payment. -- Regards, Fred "Sean B" wrote in message ... No, APR is different than interest rate, it takes into account the costs associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. I just don't have a clue how to get there from here. any ideas? anybody? "Fred Smith" wrote: Haven't you already specified the APR (7%)? If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Thank you Peo!
"Peo Sjoblom" wrote: The formula would be =RATE(30*12,-650.30,100000)*12 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Sean B" wrote in message ... Thank you Peo. Now can you help me with the excel formula so I can incorporate it into a spreadsheet that has all of the other info present? I could email you a bare bones setup... Thanks (hopefully), Sean "Peo Sjoblom" wrote: Then the APR is 6.78% -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Sean B" wrote in message ... Payment would be $665.30 I used the PMT calc for that which is no big deal, but I gotta tell ya, the APR thing has me stumped... Thanks Fred and anybody else out there who can help! Sean "Fred Smith" wrote: We're still going to need to know the payment. -- Regards, Fred "Sean B" wrote in message ... No, APR is different than interest rate, it takes into account the costs associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. I just don't have a clue how to get there from here. any ideas? anybody? "Fred Smith" wrote: Haven't you already specified the APR (7%)? If you want to check to see if 7% is correct, we would need to know the payment. You can also calculate it yourself using the Rate function. -- Regards, Fred "Sean B" <Sean wrote in message ... Can anybody help me determine APR using the following example: 30 year amortization 7% Rate $100,000.00 loan amount |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Sean B wrote:
No, APR is different than interest rate, it takes into account the costs associated with the loan and adds that to the actual interest charged during the life of the loan. Then it backs into what the interst rate is given those costs + interest rate = APR. That is correct. So I presume you want to know how to compute the US "Truth in Lending" APR. Right? With respect to US mortgages, "APR" is a technical term defined in Regulation Z and Appendix J. It should not be confused with the (nominal) annual interest rate. There seems to be two common ways to compute APR, exhibited by the following online calculators: http://www.mortgageloan.com/calculators/aprcalc.cgi [1] and http://www.lendingtree.com/cec/tools...ortgageAPR.asp [2]. For the first method, the actual payment is determined based on the actual loan amount. Then an effective monthly rate is computed based on the actual loan amount minus the applicable closing costs, which is then multiplied by 12 per US regulation. In your example, the APR would be: =12 * rate(12*30, pmt(7%/12, 12*30, -100000), -100000+2000) The result of the formula (7.2014%) matches the result of the mortgageloan.com calculator. For the second method, a fictious payment is determined based on the actual loan amount plus the applicable closing costs. Then an effective monthly rate is computed based on the actual loan amount, which is again multiplied by 12. Note that this fictious payment is solely for the purpose of computing an APR; it is not the actual payment. In your example, the APR would be: =12 * rate(12*30, pmt(7%/12, 12*30, -100000-2000), -100000) The result of that formula (7.1973%) matches the result of the lendingtree.com calculator. Despite the apparent difference in the results of the two methods, note that in this case at least, they are the same when rounded to a basis point (1/100 of a percent), which is well within the accuracy requirements of the federal regulations. I have not examined the underlying math formulas to see if perhaps the two methods are in fact identical mathematically. Having said all that, it should be noted that despite attempts by federal law to create a standardized "rate" to use when comparing loan offerings, the fact is: the APR is virtually useless, IMHO. The main reason is that different lenders include different fees in what I called "applicable closing costs". Federal regulations specify only a minimum set. More importantly, federal regulations permit the exclusion of many fees that are highly variable among lenders. As a consequence, the APR fails to give a truly comparable value of the cost of lending. Refer to http://www.mtg-net.com/sfaq/faq/apr.htm for details. Bottom line: Other than as an academic exercise, ignore the APR. ----- [1] At one time, I found an online explanation of the first APR method, exhibited by http://www.mortgageloan.com/calculators/aprcalc.cgi . But, sigh, I cannot find that link again. [2] An explanation of the second APR method, exhibited by http://www.lendingtree.com/cec/tools...ortgageAPR.asp , can be found by clicking View Report on that web page. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
Peo Sjoblom wrote:
Then the APR is 6.78% Peo Sjoblom wrote later: The formula would be =RATE(30*12,-650.30,100000)*12 That RATE() formula does result in 6.78%. Bjut I am curious: where does $650.30 come from? Just a typo? The OP computed a payment of $665.30 based on the terms of the loan, namely PMT(7%/12, 12*30, -100000), I presume. And I believe that is the correct payment to use for the loan. Some methods of computing the APR do involve computing a fictitious payment based on a modified loan amount. But the one method I am familiar with would compute $678.61. If you are using some other method, could you elaborate for my edification? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating APR on a Mortgage
A typo
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey wrote in message oups.com... Peo Sjoblom wrote: Then the APR is 6.78% Peo Sjoblom wrote later: The formula would be =RATE(30*12,-650.30,100000)*12 That RATE() formula does result in 6.78%. Bjut I am curious: where does $650.30 come from? Just a typo? The OP computed a payment of $665.30 based on the terms of the loan, namely PMT(7%/12, 12*30, -100000), I presume. And I believe that is the correct payment to use for the loan. Some methods of computing the APR do involve computing a fictitious payment based on a modified loan amount. But the one method I am familiar with would compute $678.61. If you are using some other method, could you elaborate for my edification? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Goal Seek on PMT function give different answer than a mortgage ca | Excel Worksheet Functions | |||
| Linked Check Boxes | Excel Discussion (Misc queries) | |||
| Calculating Cells n% displayed in a cell? | Excel Worksheet Functions | |||
| calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
| AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) | |||
Linear Mode
