Home 
Search 
Today's Posts 
#21




How do I caluclate an Annual Percentage Rate in Excel for an ARM?
Clarification....
I wrote: 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. I did not intend to imply that Canadians "nominalize" the monthly rate in the same way as the US, namely 12*RATE(...) in the simple case. And perhaps I should not use the term "nominal" altogether for the Canadian APR, since Canadian mortgage interest rates are specified as twice a semiannual compounded rate. Using the OP's terms, the following is my __guess__ as to how a Canadian APR might be computed for both fixedrate mortgages and ARMs. I cannot find a (free) online Canadian mortgage calculator that shows APR, perhaps because Canadians are enlightened enough to realize that the APR is virtually useless ;). So I cannot confirm my guess. If someone has access to professional mortgage software for Canada, it would be great if you would post its results for these examples. Canadian FIXED RATE, FIXED PAYMENT LOAN (simplified): Amortization term: 360 months Loan amount: $156,462 Balloon payment: $0 Prepaid loan cost: $7421.77 Additional financial payments: $0 Fixed rate: 5.125% Monthly payment ($846.74): A1: =round( pmt( rate(6, 0, 1, 1+5.125%/2), 360, 156462 ), 2 ) APR (5.5689%): =2 * ( fv( rate(360, A1, (156462  7421.77)), 6, 0, 1 )  1 ) Canadian VARIABLE RATE, VARIABLE PAYMENT LOAN: Amortization term: 360 months Loan amount: $156,462 Balloon payment: $0 Prepaid loan cost: $7421.77 Additional financial payments: $101.70 for 1st 153 payments (mortgage insurance) Fixed rate: 5.125% Fixed term: 60 months Initial variable rate: 8.049% Variable term: 300 months Loan payment (excluding the mortgage insurance premium) for the first 60 payments ($846.74): A1: =round( pmt( rate(6, 0, 1, 1 + 5.125%/2), 360, 156462 ), 2 ) Remaining balance of the loan after the first 60 payments ($143,821.10): A2: =fv( rate(6, 0, 1, 1 + 5.125%/2), 60, A1, 156462 ) Loan payment (excluding the mortgage insurance premium) for the remaining 300 payments ($1102.16): A3: =round( pmt( rate(6, 0, 1, 1 + 8.049%/2), 300, A2 ), 2 ) Cash flow table: B1: =156462  7421.77 B2:B62: =($A$1 + 101.70) B63:B154: =($A$3 + 101.70) B155:B361: =$A$3 APR (8.0147%) =2 * ( fv( irr(B1:B361, 8%/12), 6, 0, 1 ) 1 ) Caveat: As I noted elsewhere, I am not sure that the mortgage insurance premium should be included in the APR calculation. I retain it, in part, for comparison with the US loan and, in part, because it makes the problem more interesting ;). 
#22




How do I caluclate an Annual Percentage Rate in Excel?
Hi,
I trying to solve and validate the APR on an ARM loan with the IRR function based on the following,  3/1 ARM with initial of 5.75% (rate good for 36 months)  Loan term of 30 years (360 months)  Loan amount of $250,000  Initial payment of $1,458.93; pmt(.0575/12,360,250000)  1% in origination points; $2,500  $750 in processing fees  Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)  An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the 37th month  Loan balance at end of 36 month period is $239,771.05; fv(.0575/12,36,1458.93,250000)  Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05) My worksheet is set up as follows, A1:A3; header information A4; 12*IRR(A5:A365,0.1/12) A5; $250,000 (the loan amount) A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96)) A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05) This produces an APR of 6.887%; the APR on one website illustrates an APR of 7.168% (includes the prepaid interest); another site that does not take into account the prepaid interest illustrates an APR of 7.144% (note: when I strip out the prepaid interest at the first website, it also produces a 7.144% APR). What am I missing? "Lisa M" wrote: I tried that but it didn't work. Thanks!!!! I need to calculate for mortgages. Any other sugestions? Lisa M "JE McGimpsey" wrote: Depends on what information you start with... Take a look at the RATE() function in XL Help. In article , Lisa M <Lisa wrote: 
#23




How do I caluclate an Annual Percentage Rate in Excel?
On Jul 25, 12:20 pm, mlion wrote:
I trying to solve and validate the APR on an ARM loan with the IRR function based on the following, [....]  Initial payment of $1,458.93; pmt(.0575/12,360,250000) [....]  Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05) My worksheet is set up as follows, [....] A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96)) A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05) Why did you choose to use different payment amounts in your worksheet? The first pair of payment amounts ("based on the following") is the correct one to use. In your computation, the last parameter to PMT() for the first 36 months is incorrect. It should be simply 250000. It is computed as if the lender gave you $250,000, and you paid the lender the amount of the initial costs outofpocket. The parameters to PMT() for the last 324 months seem correct; and indeed, with those parameters, PMT() returns the correct amount, namely $1688.45. I don't know how you got $1693.05; perhaps you mistyped ".0728" for 7.25%. A4; 12*IRR(A5:A365,0.1/12) A5; $250,000 (the loan amount) For the IRR, each cash flow is the net of inflows and outflows in that period. For the initial period, the inflow is the initial loan amount ($250,000), and the outflow is the sum of the initial loan costs (at least 2500 + 750 + 598.96). That is, A5 should be (at least): =250000  2500  750  598.96 the APR on one website illustrates an APR of 7.168% (includes the prepaid interest); another site that does not take into account the prepaid interest illustrates an APR of 7.144% (note: when I strip out the prepaid interest at the first website, it also produces a 7.144% APR). What am I missing? With the corrections above, I compute an annualized IRR of 7.0079% (rounded), including the prepaid interest. To get 7.168% and 7.144%, I would have to assume that the lender included an addition $4010 to $4040 in initial costs, which you (or the lender) did not mention. Although it seems unlikely that two lenders would make the same such assumption, note that often different online APR calculators use the same underlying "engine". What am I missing? Only that the APR is really not a good way to compare loans, at least in the US. I would compare the loan terms and the initial loan costs separately. But even then, comparing ARMs is tricking business because of the flexibility that lenders have in determining rates and payments after the initial fixed period.  complete original posting  On Jul 25, 12:20 pm, mlion wrote: I trying to solve and validate the APR on an ARM loan with the IRR function based on the following,  3/1 ARM with initial of 5.75% (rate good for 36 months)  Loan term of 30 years (360 months)  Loan amount of $250,000  Initial payment of $1,458.93; pmt(.0575/12,360,250000)  1% in origination points; $2,500  $750 in processing fees  Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)  An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the 37th month  Loan balance at end of 36 month period is $239,771.05; fv(.0575/12,36,1458.93,250000)  Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05) My worksheet is set up as follows, A1:A3; header information A4; 12*IRR(A5:A365,0.1/12) A5; $250,000 (the loan amount) A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96)) A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05) This produces an APR of 6.887%; the APR on one website illustrates an APR of 7.168% (includes the prepaid interest); another site that does not take into account the prepaid interest illustrates an APR of 7.144% (note: when I strip out the prepaid interest at the first website, it also produces a 7.144% APR). What am I missing? 
#24




How do I caluclate an Annual Percentage Rate in Excel?
Errata....
I On Jul 25, 2:15 pm, I wrote: With the corrections above, I compute an annualized IRR of 7.0079% (rounded), including the prepaid interest. To get 7.168% and 7.144%, I would have to assume that the lender included an addition $4010 to $4040 in initial costs, which you (or the lender) did not mention. I think it is more likely that that is simply the NPV of costs added to each payment, for example to cover PMI. If you provide the URL for the mortgage calculators that you used, perhaps I can explain the descrepancy better. What am I missing? Only that the APR is really not a good way to compare loans, at least in the US. Interestingly, the Consumer Handbook on ARMs (at http://www.federalreserve.gov/Pubs/a...msbrochure.pdf) says exactly the opposite. It states: "Because all lenders follow the same rules when calculating the APR, it provides you with a good basis for comparing the cost of loans". Well, yes, "all lenders follow the same rules". But the rules have such great flexibility that two lenders with identical loan terms and costs can disclose different APRs in advertisements and preloan documents as long as they state that the APR might increase after consummation. 
#25




How do I caluclate an Annual Percentage Rate in Excel?
Hi,
Thanks for the response. I made the adjustments and came up with the same answer. In regard to that $1,693.05 payment reference for cells a42:a365, that was a typo. Here are the websites that I was using to validate my APR; http://www.lenderhomepage.com/tools/...calculator.php http://www.dinkytown.net/java/Mortga...djustable.html The information displayed for their calculation of the APR is pretty straightforward and there is no mention of any additional fees ($4010 plus). I've ran my IRR function with many different variables trying to match their APR, and just could not accept that their number could be "wrong" and the solution via excel was "correct". Take a look at the URLs and let me know what you think they are doing to derive their number other than to add some mystery fees. Once again, thank you for your input. "joeu2004" wrote: On Jul 25, 12:20 pm, mlion wrote: I trying to solve and validate the APR on an ARM loan with the IRR function based on the following, [....]  Initial payment of $1,458.93; pmt(.0575/12,360,250000) [....]  Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05) My worksheet is set up as follows, [....] A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96)) A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05) Why did you choose to use different payment amounts in your worksheet? The first pair of payment amounts ("based on the following") is the correct one to use. In your computation, the last parameter to PMT() for the first 36 months is incorrect. It should be simply 250000. It is computed as if the lender gave you $250,000, and you paid the lender the amount of the initial costs outofpocket. The parameters to PMT() for the last 324 months seem correct; and indeed, with those parameters, PMT() returns the correct amount, namely $1688.45. I don't know how you got $1693.05; perhaps you mistyped ".0728" for 7.25%. A4; 12*IRR(A5:A365,0.1/12) A5; $250,000 (the loan amount) For the IRR, each cash flow is the net of inflows and outflows in that period. For the initial period, the inflow is the initial loan amount ($250,000), and the outflow is the sum of the initial loan costs (at least 2500 + 750 + 598.96). That is, A5 should be (at least): =250000  2500  750  598.96 the APR on one website illustrates an APR of 7.168% (includes the prepaid interest); another site that does not take into account the prepaid interest illustrates an APR of 7.144% (note: when I strip out the prepaid interest at the first website, it also produces a 7.144% APR). What am I missing? With the corrections above, I compute an annualized IRR of 7.0079% (rounded), including the prepaid interest. To get 7.168% and 7.144%, I would have to assume that the lender included an addition $4010 to $4040 in initial costs, which you (or the lender) did not mention. Although it seems unlikely that two lenders would make the same such assumption, note that often different online APR calculators use the same underlying "engine". What am I missing? Only that the APR is really not a good way to compare loans, at least in the US. I would compare the loan terms and the initial loan costs separately. But even then, comparing ARMs is tricking business because of the flexibility that lenders have in determining rates and payments after the initial fixed period.  complete original posting  On Jul 25, 12:20 pm, mlion wrote: I trying to solve and validate the APR on an ARM loan with the IRR function based on the following,  3/1 ARM with initial of 5.75% (rate good for 36 months)  Loan term of 30 years (360 months)  Loan amount of $250,000  Initial payment of $1,458.93; pmt(.0575/12,360,250000)  1% in origination points; $2,500  $750 in processing fees  Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)  An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the 37th month  Loan balance at end of 36 month period is $239,771.05; fv(.0575/12,36,1458.93,250000)  Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05) My worksheet is set up as follows, A1:A3; header information A4; 12*IRR(A5:A365,0.1/12) A5; $250,000 (the loan amount) A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96)) A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05) This produces an APR of 6.887%; the APR on one website illustrates an APR of 7.168% (includes the prepaid interest); another site that does not take into account the prepaid interest illustrates an APR of 7.144% (note: when I strip out the prepaid interest at the first website, it also produces a 7.144% APR). What am I missing? 
#26




How do I caluclate an Annual Percentage Rate in Excel?
On Jul 25, 8:30 pm, mlion wrote:
Here are the websites that I was using to validate my APR; http://www.lenderhomepage.com/tools/...ageaprcalcul... http://www.dinkytown.net/java/Mortga...djustable.html [....] I've ran my IRR function with many different variables trying to match their APR, and just could not accept that their number could be "wrong" and the solution via excel was "correct". In this posting, I'll pick apart the dinkytown calculator. I do conclude that its APR computation is incorrect when the interest rate varies. Like you, I am relunctant to reach that conclusion. But consider the following. First, it appears that the dinkytown calculator treats the starting interest rate plus margin as the (lifetime) interest rate cap. Also, it appears that it adds the "max adjustment" to the starting rate until that rate cap is reached, starting after "months before first adjustment" at intervals of "months between adjustments". The current index does not seem to be a factor at all. I believe that is a misuse of those factors  or those factors are misnamed. But I will take their usage for granted. If we set "max adjustment" to 0%, we get a fixedrate loan. With a starting rate of 5.75% for a loan amount of 250,000 over 30 years, the first 359 payments are 1458.93 (rounded), and the last payment is 1460.88 (rounded). [Aside: Using FV() in Excel, I compute a final payment of 1460.98 rounded. The descrepancy arises because it appears the dinkytown rounds each periodic interest amount. I cannot say with impunity whether that is right or wrong, or common or uncommon among lenders.] With points of 2500 (1%), other fees of 750 and 0% origination fee (and no prepaid interest), the dinkytown calculator reports an APR of 5.871%. In Excel, I compute an IRR of 5.8706% (rounded)  essentially the same  by setting the initial cash flow to 250000  2500  750 and by multiplying the resulting monthly IRR by 12. I believe that demonstrates that the IRR paradigm that I suggested in my previous posting is correct, at least to the extent that it matches the dinkytown calculator. So far, so good. But.... If we set "max adjustment" to 0.5%, the dinkytown calculator computes payments of 1458.93, 1533.77, 1608.39 and 1682.64 and interest rates of 5.75%, 6.25%, 6.75% and 7.25% starting with payment numbers 1, 37, 49 and 61 respectively, with a last payment of 1682.46. The reported APR is 7.144%. But the calculator reports the same 7.144% APR when "max adjustment is 1.0% and 1.5% or more, even though: (a) for a 1.0% adjustment, the payments are 1458.93, 1610.30 and 1686.58 starting with payment numbers 1, 37 and 49, with a last payment of 1678.71; and (b) for a 1.5% adjustment or more, the payments are 1458.93 and 1688.45 starting with payment numbers 1 and 37, with a last payment of 1681.81. I do not believe the APR should be the same in all 3 cases. FYI, I compute IRRs of 6.8779%, 6.9479% and 6.9844% (rounded) for the 3 cases respectively. (I will examine the lenderhomepage calculator in a later posting. It's late now for me.) 
#27




How do I caluclate an Annual Percentage Rate in Excel?
Errata...
On Jul 26, 2:06 am, I wrote: First, it appears that the dinkytown calculator treats the starting interest rate plus margin as the (lifetime) interest rate cap. Correction: It treats the current index plus margin as the lifetime rate cap. 
#28




How do I caluclate an Annual Percentage Rate in Excel?
On Jul 25, 8:30 pm, mlion wrote:
Here are the websites that I was using to validate my APR; http://www.lenderhomepage.com/tools/...ageaprcalcul... http://www.dinkytown.net/java/Mortga...djustable.html Aha! I believe I know how those calculators arrive at an "APR" of 7.168% and 7.144% with and without the prepaid interest of 598.96  at least in the case of an initial rate of 5.75% for 36 months and 7.25% for the remaining 324 months. Note that in that case, the total of payments is 599,582.80 and, dividing by 360, the average payment is 1665.51. (The lenderhomepage calculator truncates the average to 1665.50.) Making the simplifying assumption of a constant payment of 1665.51 over the full 360month term, we do indeed compute an annual rate of 7.168% and 7.144% with and without the prepaid interest of 598.96. In Excel, they can be computed with the RATE() function as follows: =12*rate(360, 1665.51, (25000002500750598.66)) =12*rate(360, 1665.51, (25000002500750)) Clearly, that is only an approximation of the APR  and not a good one(!). I do not believe it follows the algorithm for computing the APR in Appendix J of Reg Z  the US regulation commonly called "Truth in Lending". PS: That still does not explain how the dinkytown calculator computes the same "APR" of 7.144% (without prepaid interest) with different payment schedules based on varying adjustments. The average payments differ significantly, resulting in different "APRs" based on the simplifying paradigm above. For example, for adjustments of 0.5% and 1.0%, the simplified "APR" would be 7.067% and 7.118% respectively. I think the dinkytown calculator simply has a defect. 
#29




How do I caluclate an Annual Percentage Rate in Excel?
Kudos for your effort; that was outstanding! Iâ€™m interested to see the TIL
position on calculating the APR. Thanks again for your assistance. "joeu2004" wrote: On Jul 25, 8:30 pm, mlion wrote: Here are the websites that I was using to validate my APR; http://www.lenderhomepage.com/tools/...ageaprcalcul... http://www.dinkytown.net/java/Mortga...djustable.html Aha! I believe I know how those calculators arrive at an "APR" of 7.168% and 7.144% with and without the prepaid interest of 598.96  at least in the case of an initial rate of 5.75% for 36 months and 7.25% for the remaining 324 months. Note that in that case, the total of payments is 599,582.80 and, dividing by 360, the average payment is 1665.51. (The lenderhomepage calculator truncates the average to 1665.50.) Making the simplifying assumption of a constant payment of 1665.51 over the full 360month term, we do indeed compute an annual rate of 7.168% and 7.144% with and without the prepaid interest of 598.96. In Excel, they can be computed with the RATE() function as follows: =12*rate(360, 1665.51, (25000002500750598.66)) =12*rate(360, 1665.51, (25000002500750)) Clearly, that is only an approximation of the APR  and not a good one(!). I do not believe it follows the algorithm for computing the APR in Appendix J of Reg Z  the US regulation commonly called "Truth in Lending". PS: That still does not explain how the dinkytown calculator computes the same "APR" of 7.144% (without prepaid interest) with different payment schedules based on varying adjustments. The average payments differ significantly, resulting in different "APRs" based on the simplifying paradigm above. For example, for adjustments of 0.5% and 1.0%, the simplified "APR" would be 7.067% and 7.118% respectively. I think the dinkytown calculator simply has a defect. 
#30




How do I caluclate an Annual Percentage Rate in Excel?
On Jul 26, 5:52 pm, mlion wrote:
I'm interested to see the TIL position on calculating the APR. The Truth in Lending method of computing APR is essentially the IRR. And I might note that it is the Excel function IRR(), not XIRR(). A (US) loan APR is a nominal rate, not a compounded rate like the APY. You can find all of Reg Z at http://www.fdic.gov/regulations/laws...65001400.html . The link to Appendix J takes you to http://www.fdic.gov/regulations/laws...ndixjtopart226 .. Unfortunately, the presentation and confusing terminology leave much to be desired, IMHO . But you might recognize the "general equation" as an NPV formula, where the left side is the NPV of the advances (e.g. loan amount) and the right side is the NPV of the payments. Thanks again for your assistance. My pleasure. Helping others is always an education for myself, too. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
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 