Calculating APR on a Mortgage

[Sean B]

Can anybody help me determine APR using the following example:

30 year amortization
7% Rate
$100,000.00 loan amount

[Sean B]

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

[Fred Smith]

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 B@discussions.microsoft.com> wrote in message
news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
> Can anybody help me determine APR using the following example:
>
> 30 year amortization
> 7% Rate
> $100,000.00 loan amount

[Sean B]

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 B@discussions.microsoft.com> wrote in message
> news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
> > Can anybody help me determine APR using the following example:
> >
> > 30 year amortization
> > 7% Rate
> > $100,000.00 loan amount
>
>
>

[Fred Smith]

We're still going to need to know the payment.

--
Regards,
Fred


"Sean B" <SeanB@discussions.microsoft.com> wrote in message
news:F612FD03-1161-401B-999B-20AF2E481787@microsoft.com...
> 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 B@discussions.microsoft.com> wrote in message
>> news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
>> > Can anybody help me determine APR using the following example:
>> >
>> > 30 year amortization
>> > 7% Rate
>> > $100,000.00 loan amount
>>
>>
>>

[Sean B]

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" <SeanB@discussions.microsoft.com> wrote in message
> news:F612FD03-1161-401B-999B-20AF2E481787@microsoft.com...
> > 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 B@discussions.microsoft.com> wrote in message
> >> news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
> >> > Can anybody help me determine APR using the following example:
> >> >
> >> > 30 year amortization
> >> > 7% Rate
> >> > $100,000.00 loan amount
> >>
> >>
> >>
>
>
>

[Peo Sjoblom]

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" <SeanB@discussions.microsoft.com> wrote in message
news:2122277D-205C-4185-ADCB-C8BA864CE15B@microsoft.com...
> 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" <SeanB@discussions.microsoft.com> wrote in message
>> news:F612FD03-1161-401B-999B-20AF2E481787@microsoft.com...
>> > 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 B@discussions.microsoft.com> wrote in message
>> >> news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
>> >> > Can anybody help me determine APR using the following example:
>> >> >
>> >> > 30 year amortization
>> >> > 7% Rate
>> >> > $100,000.00 loan amount
>> >>
>> >>
>> >>
>>
>>
>>

[Sean B]

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" <SeanB@discussions.microsoft.com> wrote in message
> news:2122277D-205C-4185-ADCB-C8BA864CE15B@microsoft.com...
> > 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" <SeanB@discussions.microsoft.com> wrote in message
> >> news:F612FD03-1161-401B-999B-20AF2E481787@microsoft.com...
> >> > 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 B@discussions.microsoft.com> wrote in message
> >> >> news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
> >> >> > Can anybody help me determine APR using the following example:
> >> >> >
> >> >> > 30 year amortization
> >> >> > 7% Rate
> >> >> > $100,000.00 loan amount
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Peo Sjoblom]

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" <SeanB@discussions.microsoft.com> wrote in message
news:F26D78D8-E805-4E78-A9B6-7316D0EE143D@microsoft.com...
> 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" <SeanB@discussions.microsoft.com> wrote in message
>> news:2122277D-205C-4185-ADCB-C8BA864CE15B@microsoft.com...
>> > 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" <SeanB@discussions.microsoft.com> wrote in message
>> >> news:F612FD03-1161-401B-999B-20AF2E481787@microsoft.com...
>> >> > 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 B@discussions.microsoft.com> wrote in message
>> >> >> news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
>> >> >> > Can anybody help me determine APR using the following example:
>> >> >> >
>> >> >> > 30 year amortization
>> >> >> > 7% Rate
>> >> >> > $100,000.00 loan amount
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[Sean B]

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" <SeanB@discussions.microsoft.com> wrote in message
> news:F26D78D8-E805-4E78-A9B6-7316D0EE143D@microsoft.com...
> > 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" <SeanB@discussions.microsoft.com> wrote in message
> >> news:2122277D-205C-4185-ADCB-C8BA864CE15B@microsoft.com...
> >> > 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" <SeanB@discussions.microsoft.com> wrote in message
> >> >> news:F612FD03-1161-401B-999B-20AF2E481787@microsoft.com...
> >> >> > 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 B@discussions.microsoft.com> wrote in message
> >> >> >> news:45E69DD7-02AE-4EFE-9635-2BA5590DA988@microsoft.com...
> >> >> >> > Can anybody help me determine APR using the following example:
> >> >> >> >
> >> >> >> > 30 year amortization
> >> >> >> > 7% Rate
> >> >> >> > $100,000.00 loan amount
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[joeu2004@hotmail.com]

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.

[joeu2004@hotmail.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?

[Peo Sjoblom]

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


<joeu2004@hotmail.com> wrote in message
news:1150084516.108715.202280@i40g2000cwc.googlegroups.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?
>