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: