Depends on what information you start with...

Take a look at the RATE() function in XL Help.

In article ,
Lisa M <Lisa 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:

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?

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?

=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?

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:

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 out-of-pocket.

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?

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 pre-loan
documents as long as they state that the APR might increase after
consummation.

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
straight-forward 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 out-of-pocket.

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?

Calculating an Annual Percentage Rate (APR) in Excel

Calculating an Annual Percentage Rate (APR) in Excel can be done using a simple formula. Here are the steps:

1. First, you need to know the periodic interest rate. This is the interest rate that is charged on a loan or investment over a specific period of time. For example, if the loan has a monthly interest rate of 1%, then the periodic interest rate is 1%.
   
2. Next, you need to know the number of periods in a year. For example, if the loan has a monthly interest rate, then there are 12 periods in a year.
   
3. Now, you can use the following formula to calculate the APR:
   
   
   Formula:

   =(1+periodic interest rate)^number of periods in a year-1 

   For example, if the periodic interest rate is 1% and there are 12 periods in a year, the formula would be:
   
   
   Formula:

   =(1+0.01)^12-1 

4. Once you have entered the formula into Excel, press enter and the result will be the APR. In this example, the APR would be 12.68%.
   

That's it! You can now use this formula to calculate the APR for any loan or investment with a known periodic interest rate and number of periods in a year.