Home |
Search |
Today's Posts |
#12
![]() |
|||
|
|||
![]()
click even! lol
"Alan" wrote in message ... Yes, just open Lisa's mail and cick 'Reply Group' Regards, Alan. "MK Manzer" wrote in message ... How do I do that exactly? Go to the first box where Lisa asked the question and reply there? "Alan" wrote: A word of advice, repost this as a reply in the original thread, there's a good chance it wont be seen by the person you're addressing by starting a new one, Regards, Alan. "MK Manzer" <MK wrote in message ... GREAT help. Can you help with TWO interest only loans? First -- $500,000 loan for 2 years (24 months) at 9.5% (3,958.33/mo). Fees of $11,090.56 are deducted from loan amount for a net funding of 488,909.44 and include a 2 point origination fee ($10,000); prepaid interest from 6/23-6/30 of $1,055.56; and a $35 wire fee. Is prepaid interest is considered a fee for this calculation??? Second - $1,250,000 loan for 3 years (36 months). First 2 years at 9.5% (9,895.83/mo); 3rd year at 10.5% (10,937.50/mo). Fees of $54,479.44 are deducted from loan amount for a net funding of $1,195,520.56 and include a 4.25% origination fee (53,125); prepaid interest from 6/27-6/30 ($1,319.44); and a $35 wire fee. Again, is prepaid interest considered a fee for this calculation? "N Harkawat" wrote: =RATE(360,PMT(5.75%/12,360,56000),56000-361.22)*12 gives me 5.81% "Lisa M" wrote in message ... Hope this info helps. It's kind of hard to type it and I wouldn't think it be a good idea to post it. I can use a HP Financial Calculator to get the number I need but I would rather have it on the spreadsheet that I am using. I need to calculate an APR with Fees included. I tried the =Rate(TERM, -PMT, $ AMT)*12 and it gave me the actually rate of interest charged. That is not what I need. The APR is greater than the interest rate charged because it takes the original loan amt plus certain fees charged. An example: loan amount of $56,000 at rate of 5.75% for 30 yrs with a P&I pmt of $326.80 and fees of $361.22. If I plug the numbers into my HP, it calcs an APR of 5.810%. I would like to have the speadsheet do the calculation that my HP calculator does. I have in the spreadsheet the loan amount, int rate, pmt and fees. Do you have any other suggestions? Thanks for all of your help. I hope that I gave you better info than before. Lisa M "JE McGimpsey" wrote: How could anyone know what to suggest when you don't tell what information you have available? RATE() certainly can "calculate for mortgages". What does "it didn't work" mean? Did you get the wrong answer? no answer? an error? a crash? Nobody can see your worksheet (and please don't post it). Instead you need to actually describe how your data is laid out, what you've tried, and what hasn't worked. In article , Lisa M wrote: I tried that but it didn't work. Thanks!!!! I need to calculate for mortgages. Any other sugestions? |
#13
![]() |
|||
|
|||
![]()
I have a term of 30 years or 360 payments
loan amount of $156,462 Prepaid finance costs of $7,421.77 Amount Financed after Prepaid Fin costs of $149,040.23 zero future value starting rate for 5 years @ 5.125% Mortgage insurance of $101.7 for 153 payments and then it drops Rate goes to 8.049 after 60 payments. My mortgage processing software is coming up with 7.981% for APR I don't believe the starting rate or adjusting rate is a factor needed. What function or formula can I use in excel? -- JON "Alan" wrote: Yes, just open Lisa's mail and cick 'Reply Group' Regards, Alan. "MK Manzer" wrote in message ... How do I do that exactly? Go to the first box where Lisa asked the question and reply there? "Alan" wrote: A word of advice, repost this as a reply in the original thread, there's a good chance it wont be seen by the person you're addressing by starting a new one, Regards, Alan. "MK Manzer" <MK wrote in message ... GREAT help. Can you help with TWO interest only loans? First -- $500,000 loan for 2 years (24 months) at 9.5% (3,958.33/mo). Fees of $11,090.56 are deducted from loan amount for a net funding of 488,909.44 and include a 2 point origination fee ($10,000); prepaid interest from 6/23-6/30 of $1,055.56; and a $35 wire fee. Is prepaid interest is considered a fee for this calculation??? Second - $1,250,000 loan for 3 years (36 months). First 2 years at 9.5% (9,895.83/mo); 3rd year at 10.5% (10,937.50/mo). Fees of $54,479.44 are deducted from loan amount for a net funding of $1,195,520.56 and include a 4.25% origination fee (53,125); prepaid interest from 6/27-6/30 ($1,319.44); and a $35 wire fee. Again, is prepaid interest considered a fee for this calculation? "N Harkawat" wrote: =RATE(360,PMT(5.75%/12,360,56000),56000-361.22)*12 gives me 5.81% "Lisa M" wrote in message ... Hope this info helps. It's kind of hard to type it and I wouldn't think it be a good idea to post it. I can use a HP Financial Calculator to get the number I need but I would rather have it on the spreadsheet that I am using. I need to calculate an APR with Fees included. I tried the =Rate(TERM, -PMT, $ AMT)*12 and it gave me the actually rate of interest charged. That is not what I need. The APR is greater than the interest rate charged because it takes the original loan amt plus certain fees charged. An example: loan amount of $56,000 at rate of 5.75% for 30 yrs with a P&I pmt of $326.80 and fees of $361.22. If I plug the numbers into my HP, it calcs an APR of 5.810%. I would like to have the speadsheet do the calculation that my HP calculator does. I have in the spreadsheet the loan amount, int rate, pmt and fees. Do you have any other suggestions? Thanks for all of your help. I hope that I gave you better info than before. Lisa M "JE McGimpsey" wrote: How could anyone know what to suggest when you don't tell what information you have available? RATE() certainly can "calculate for mortgages". What does "it didn't work" mean? Did you get the wrong answer? no answer? an error? a crash? Nobody can see your worksheet (and please don't post it). Instead you need to actually describe how your data is laid out, what you've tried, and what hasn't worked. In article , Lisa M wrote: I tried that but it didn't work. Thanks!!!! I need to calculate for mortgages. Any other sugestions? |
#14
![]() |
|||
|
|||
![]()
JON wrote:
I have a term of 30 years or 360 payments loan amount of $156,462 Prepaid finance costs of $7,421.77 Amount Financed after Prepaid Fin costs of $149,040.23 zero future value starting rate for 5 years @ 5.125% Mortgage insurance of $101.7 for 153 payments and then it drops Rate goes to 8.049 after 60 payments. My mortgage processing software is coming up with 7.981% for APR I don't believe the starting rate or adjusting rate is a factor needed. What function or formula can I use in excel? For a US loan, the following describes one way. I suspect it is not the best way. But it does produce the same result as your mortgage software. The loan payment (excluding the mortgage insurance premium) for the first 60 payments can be computed as follows ($851.92): A1: =ROUND(PMT(5.125%/12, 360, -156462), 2) The remaining balance of the loan can be computed as follows ($143,929.57): A2: =FV(5.125%/12, 60, A1, -156462) The loan payment (excluding the mortgage insurance premium) for the remaining 300 payments can be computed as follows ($1115.55): A3: =ROUND(PMT(8.049%/12, 300, -A2), 2) Now fill in the following table. The first entry is the mortgage amount less finance costs ($149,040.23). The next 153 entries are the appropriate payment plus mortgage insurance, expressed as a negative number. B1: =156462 - 7421.77 B2:B62: =-($A$1 + 101.70) B63:B154: =-($A$3 + 101.70) B155:B361: =-$A$3 Finally, in any cell, compute APR as follows (7.9812%): =12*IRR(B1:B361, 8%/12) Note: I did not think that mortgage insurance premiums (as well as any other periodic insurance premiums and taxes) are included with the loan payment for the purpose estimating APR. I base that primarily on the results of some online ARM APR calculators the allow you to specify such additional periodic amounts separately. Their APR estimate is the same whether the additional amounts are zero or not. Excluding the mortgage insurance premium, the ARM APR is 7.0198%. On the other hand, after a quick reading of Reg Z (Truth in Lending Act), it seems that such additional amounts might be included, at least under some circumstances. See TLA section 226.4(b) and exceptions in 226.4(c)-(e). |
#15
![]() |
|||
|
|||
![]()
You can't use the standard Rate function, because your payments change after
five years. So your choices are IRR and XIRR. Both will require that you set up a table of your 361 cash flows. XIRR would be a more popular choice among knowledgeable users, because it will automatically calculate the annual APR. IRR works like the other financial functions -- the rate and the payment must be for the same period, so you would be calculating a monthly rate, then converting it to annual. XIRR has the additional benefit that you can tweak the payment dates to actual. If the normal payment date falls on a weekend or holiday, you can specify the following Monday or preceding Friday depending on your bank's policy. -- Regards, Fred "JON" wrote in message ... I have a term of 30 years or 360 payments loan amount of $156,462 Prepaid finance costs of $7,421.77 Amount Financed after Prepaid Fin costs of $149,040.23 zero future value starting rate for 5 years @ 5.125% Mortgage insurance of $101.7 for 153 payments and then it drops Rate goes to 8.049 after 60 payments. My mortgage processing software is coming up with 7.981% for APR I don't believe the starting rate or adjusting rate is a factor needed. What function or formula can I use in excel? -- JON "Alan" wrote: Yes, just open Lisa's mail and cick 'Reply Group' Regards, Alan. "MK Manzer" wrote in message ... How do I do that exactly? Go to the first box where Lisa asked the question and reply there? "Alan" wrote: A word of advice, repost this as a reply in the original thread, there's a good chance it wont be seen by the person you're addressing by starting a new one, Regards, Alan. "MK Manzer" <MK wrote in message ... GREAT help. Can you help with TWO interest only loans? First -- $500,000 loan for 2 years (24 months) at 9.5% (3,958.33/mo). Fees of $11,090.56 are deducted from loan amount for a net funding of 488,909.44 and include a 2 point origination fee ($10,000); prepaid interest from 6/23-6/30 of $1,055.56; and a $35 wire fee. Is prepaid interest is considered a fee for this calculation??? Second - $1,250,000 loan for 3 years (36 months). First 2 years at 9.5% (9,895.83/mo); 3rd year at 10.5% (10,937.50/mo). Fees of $54,479.44 are deducted from loan amount for a net funding of $1,195,520.56 and include a 4.25% origination fee (53,125); prepaid interest from 6/27-6/30 ($1,319.44); and a $35 wire fee. Again, is prepaid interest considered a fee for this calculation? "N Harkawat" wrote: =RATE(360,PMT(5.75%/12,360,56000),56000-361.22)*12 gives me 5.81% "Lisa M" wrote in message ... Hope this info helps. It's kind of hard to type it and I wouldn't think it be a good idea to post it. I can use a HP Financial Calculator to get the number I need but I would rather have it on the spreadsheet that I am using. I need to calculate an APR with Fees included. I tried the =Rate(TERM, -PMT, $ AMT)*12 and it gave me the actually rate of interest charged. That is not what I need. The APR is greater than the interest rate charged because it takes the original loan amt plus certain fees charged. An example: loan amount of $56,000 at rate of 5.75% for 30 yrs with a P&I pmt of $326.80 and fees of $361.22. If I plug the numbers into my HP, it calcs an APR of 5.810%. I would like to have the speadsheet do the calculation that my HP calculator does. I have in the spreadsheet the loan amount, int rate, pmt and fees. Do you have any other suggestions? Thanks for all of your help. I hope that I gave you better info than before. Lisa M "JE McGimpsey" wrote: How could anyone know what to suggest when you don't tell what information you have available? RATE() certainly can "calculate for mortgages". What does "it didn't work" mean? Did you get the wrong answer? no answer? an error? a crash? Nobody can see your worksheet (and please don't post it). Instead you need to actually describe how your data is laid out, what you've tried, and what hasn't worked. In article , Lisa M wrote: I tried that but it didn't work. Thanks!!!! I need to calculate for mortgages. Any other sugestions? |
#16
![]() |
|||
|
|||
![]()
Fred Smith wrote:
XIRR would be a more popular choice among knowledgeable users, because it will automatically calculate the annual APR. I suppose you could say it depends on your goal. If your goal is to compute a (US) RegZ-compliant APR, XIRR gives the wrong answer. (Reg Z is called the Truth in Lending Act.) The reason is that XIRR annualizes the rate by compounding the daily rate over 365 days. In other words, it is a compounded or effective annual rate. But Reg Z specifies that the APR is "the __nominal__ annual percentage rate determined by __multiplying__ the unit-period rate by the number of unit-periods in a year". (The unit-period is determined by the payment frequency -- monthly, in the OP's case.) For example, using the OP's loan specifications and assuming payments on the 1st of each month starting in Oct 2006, XIRR computes 8.2736%. IRR computes 7.9812%. The IRR number matches the APR computed by the OP's mortgage software, which presumably computes the APR in accordance with the laws of some jurisdiction, perhaps the US. By the way, "nominalizing" the XIRR number results in 7.9755%. Since that is within 0.125% of the RegZ-compliant APR, as generally required by Reg Z, arguably you could use XIRR if you "nominalize" its result. One way to do that is: =12 * rate(12, 0, -1, 1+XIRR(...)) Seems to me like a lot of trouble to compute the wrong answer. ;-) |
#17
![]() |
|||
|
|||
![]()
Joe, In the IRR function you used 8%/12! Why Why does a functuion say guess?
and why did you use 8%? I keep coming up with the $NUM! I had created an amortization chart and came up with $401,339.68 and finance charges of $252,299.45. Thus I am using "=12*IRR(-252299.45,(.08/12))" & getting the $NUM! feedback. What am I doing wrong? -- JON " wrote: JON wrote: I have a term of 30 years or 360 payments loan amount of $156,462 Prepaid finance costs of $7,421.77 Amount Financed after Prepaid Fin costs of $149,040.23 zero future value starting rate for 5 years @ 5.125% Mortgage insurance of $101.7 for 153 payments and then it drops Rate goes to 8.049 after 60 payments. My mortgage processing software is coming up with 7.981% for APR I don't believe the starting rate or adjusting rate is a factor needed. What function or formula can I use in excel? For a US loan, the following describes one way. I suspect it is not the best way. But it does produce the same result as your mortgage software. The loan payment (excluding the mortgage insurance premium) for the first 60 payments can be computed as follows ($851.92): A1: =ROUND(PMT(5.125%/12, 360, -156462), 2) The remaining balance of the loan can be computed as follows ($143,929.57): A2: =FV(5.125%/12, 60, A1, -156462) The loan payment (excluding the mortgage insurance premium) for the remaining 300 payments can be computed as follows ($1115.55): A3: =ROUND(PMT(8.049%/12, 300, -A2), 2) Now fill in the following table. The first entry is the mortgage amount less finance costs ($149,040.23). The next 153 entries are the appropriate payment plus mortgage insurance, expressed as a negative number. B1: =156462 - 7421.77 B2:B62: =-($A$1 + 101.70) B63:B154: =-($A$3 + 101.70) B155:B361: =-$A$3 Finally, in any cell, compute APR as follows (7.9812%): =12*IRR(B1:B361, 8%/12) Note: I did not think that mortgage insurance premiums (as well as any other periodic insurance premiums and taxes) are included with the loan payment for the purpose estimating APR. I base that primarily on the results of some online ARM APR calculators the allow you to specify such additional periodic amounts separately. Their APR estimate is the same whether the additional amounts are zero or not. Excluding the mortgage insurance premium, the ARM APR is 7.0198%. On the other hand, after a quick reading of Reg Z (Truth in Lending Act), it seems that such additional amounts might be included, at least under some circumstances. See TLA section 226.4(b) and exceptions in 226.4(c)-(e). |
#18
![]() |
|||
|
|||
![]()
Thanks for the information. Now I can see why US banks use daily compounding,
because it allows them to advertise a lower APR. I'm amazed the regulations don't require the same annualizing as XIRR does, because that's the only effective way of comparing rates. Live and learn. I'm glad Reg Z doesn't apply in Canada. -- Regards, Fred wrote in message ups.com... Fred Smith wrote: XIRR would be a more popular choice among knowledgeable users, because it will automatically calculate the annual APR. I suppose you could say it depends on your goal. If your goal is to compute a (US) RegZ-compliant APR, XIRR gives the wrong answer. (Reg Z is called the Truth in Lending Act.) The reason is that XIRR annualizes the rate by compounding the daily rate over 365 days. In other words, it is a compounded or effective annual rate. But Reg Z specifies that the APR is "the __nominal__ annual percentage rate determined by __multiplying__ the unit-period rate by the number of unit-periods in a year". (The unit-period is determined by the payment frequency -- monthly, in the OP's case.) For example, using the OP's loan specifications and assuming payments on the 1st of each month starting in Oct 2006, XIRR computes 8.2736%. IRR computes 7.9812%. The IRR number matches the APR computed by the OP's mortgage software, which presumably computes the APR in accordance with the laws of some jurisdiction, perhaps the US. By the way, "nominalizing" the XIRR number results in 7.9755%. Since that is within 0.125% of the RegZ-compliant APR, as generally required by Reg Z, arguably you could use XIRR if you "nominalize" its result. One way to do that is: =12 * rate(12, 0, -1, 1+XIRR(...)) Seems to me like a lot of trouble to compute the wrong answer. ;-) |
#19
![]() |
|||
|
|||
![]()
Fred Smith wrote:
I'm glad Reg Z doesn't apply in Canada. Can you provide a pointer to an online copy of the equivalent Canadian regulations? I inferred that, in fact, Canada does also compute a nominal rate for the APR, perhaps the same way as the US, because one online Canada APR calculator states that the APR is the same as the nominal rate if there are no "borrowing charges" (loan fees). I do not believe that would be the case if the Canadian APR is computed by compounding. |
#20
![]() |
|||
|
|||
![]()
JON wrote:
Joe, In the IRR function you used 8%/12! Why Why does a functuion say guess? and why did you use 8%? All good questions. This is a limitation of the Excel implementation, in my opinion. I am familiar with IRR implementations that work perfectly well without a guess -- at least for a wider range of solutions. The answer is.... First, I tried IRR(B1:B361) without a "guess", and I got the #NUM! error. Ergo, I knew we needed a "guess". Ordinarily, it is very difficult for us humans to come up with a "guess". That is why we invented computers. Klunk! But since you provided a likely solution (7.981%), and since I knew that it should be computed by 12*monthlyRate, I decided to try 8%/12 as an approximation of that monthly rate. In short, you already provided the answer. I just encouraged Excel to compute it. ;-) I keep coming up with the $NUM! I had created an amortization chart and came up with $401,339.68 and finance charges of $252,299.45. Thus I am using "=12*IRR(-252299.45,(.08/12))" & getting the $NUM! feedback. What am I doing wrong? Just about everything. For starters, the first argument for IRR() is incorrect. It must be a series of equally-spaced cash flows (although some cash flows can be zero). Look at the IRR help page. Secondly, it is not clear to me what you intend to do with "$252,299.45" and "$401,339.68". (I come up with slightly different numbers -- $252,300.07 and $401,340.30.) Yes, that is the total interest (based on the loan amount less loan costs); and yes, that is the total payments. But I am not aware of any mathematical use of those two numbers that would result in the correct ARM APR -- where by "correct", I mean: in compliance with Reg Z, assuming you are talking about a US loan. That __is__ the kind of simplication I was hoping might work in some way. But after thinking about it and after Fred's corroborating response, I feel more confident that a simpler solution does not exist -- at least, not using Excel. I think we could break up the payment series into three groups, each with equal payments. The sum of the PV of each group, properly adjusted for time, should equal the loan amount less the loan costs. But therein lies the rub: in order to compute the PVs, I believe we need to know the IRR. We could write a VBA function to do that computation, iterating with successively better guesses. That is what the IRR() function does. But I do not believe any Excel function allows us to specify groups of equal payments, like the HP 12C calculator does. By the way, that approach is wild speculation. I have not tried to implement that algorithm to see if really works. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Annual Percentage Rate for Mortgage | Excel Discussion (Misc queries) | |||
Annual Percentage Rate | Excel Discussion (Misc queries) | |||
how do i find percentage from 2 different pages in Excel? | Excel Worksheet Functions | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
APR - Annual Percentage Rate | Excel Worksheet Functions |