APR Question

[Scott]

I'm new at this, and I posted on an old string, so I
thought it might get buried, so I'm pasting my question
again here. If my old post isn't likely to get buried, I
apologize. Please let me know if you can help me here.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

What about when the APR is also effected by PMI? For
example, a simple 30 year loan of $100,000 at 6%, let's
assume no fees so we can just concentrate on how PMI
effect APR, has a payment of $599.55. If the sale price
was also $100,000, the lender will charge PMI until the
loan balance drops below 80%, or here $80,000. The PMI
payment is $65.83. If there was no PMI the APR would
equal the rate at 6%, and if the PMI wasn't canceled
until the loan was paid off, the PMI is still easy to
find at 7.001%. The problem is, how do you calculate the
APR when the PMI drops off in the middle? It's easy to
find the number of payments that have PMI and the number
that don't, and also to add up all of the interest over
the life of the loan or even each month etc., but no
matter what I do I can't seem to get the same APR that
the computer's loan software, the one loan companies use,
finds. Here's some examples of the PMI dropping of at
different percentages of the starting loan amount, and
the APR, all with the loan criteria from above.
1) % at which PMI cancels
2) # of payments that have PMI
3) APR
4) Total finance charge (Interest + PMI payments)
1) 2) 3) 4)
100 0 6.000 115838.44
90 82 6.421 121236.50
80 140 6.629 125054.64
70 185 6.749 128016.99
60 221 6.825 130386.87
50 252 6.878 132427.60
40 279 6.917 134205.01
30 303 6.946 135784.93
20 324 6.969 137167.36
10 343 6.987 138418.13
0 360 7.001 139537.24

Please let me know if you can come up with something.
Thanks!
Scott

-----Original Message-----
Hi warnimont!

Use the RATE function:

Description:
Returns the interest rate per period of an annuity
Syntax:
=RATE(nper,pmt,pv,fv,type,guess)
Nper: The number of periods
pmt: The payment made each period
pv: The Present Value (the loan)
fv: Future Value (Optional)
type: 0 = Payment in arrears; 1 = payment in advance (Optional)
guess: a guide for the function to work on (Optional and not often
required)

Typically:

Loan 100000
Term 20 years paid monthly
payments 740 inclusive of fees
fv: no balloon
type: payments at the end of each month
fees 2% of loan

=RATE(240,-740,100000*(1-2%),0,0)
Returns: 0.555111736656528%

This is effective rate per month.

So APR:
=RATE(240,-740,100000*(1-2%),0,0)*12
Returns: 6.66134083987834%

And Annual Effective:
=(1+RATE(240,-740,100000*(1-2%),0,0))^12-1
Returns: 6.86852988699223%
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


.

..

 

[Norman Harker]

Hi Scott!

You're going to need an IRR function to work this out.

You need a schedule as follows:

Repayments use the PMT formula on the original loan but add to the
repayments the amount of PMI if the preceding Running Balance is >80%
of original loan.
Interest paid will be easy to calculate APR(12)/12 * preceding Balance
Principal repaid will be PMT calculation on original loan (without
PMI) - Interest paid that month
Running Balance will be Preceding month - Principal repaid that month.

The IRR needs calculating on a cash flow that comprises the Original
loan and the Repayment schedule as above.

That IRR will be the monthly effective. So * 12 = APR(12)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Scott]

Norman,
Thanks for the reply! I've never used IRR before, but I
think that might help. I've got the schedule down, as far
as what the payments are each month, what the old and new
loan balance is each month, and when the PMI payment
stops, so I should be able to figure out what to do with
the IRR function. Principle paid each month is simply the
difference between the previous balance and the current
balance. So you're saying use the cash flow as total
payment is the money coming in, and priciple paid is
money comeing out?

Also, without PMI, APR is simple to find, just use find
the rate using amount financed (loan amount - closing
costs). When I get the PMI calculations right, how would
you suggest combining it with the simple APR rules to
find an APR on a loan that has both PMI and closing
costs? Maybe IRR will work universally. However, my
concern is that it will only find the psuedo APR over one
month at a time. A value that will change as time goes
by. The sample APR's I listed in my original post
represent the APR over the life of the loan.

Thanks again!
Scott

 

[Norman Harker]

Hi Scott!

Re:
"Principle paid each month is simply the difference between the
previous balance and the current balance. So you're saying use the
cash flow as total payment is the money coming in, and priciple paid
is money comeing out?"

The Principal repaid is the difference between previous and current.
The trouble is that we don't know the current balance! So I approach
this in amortization schedule form by calculating interest on the
previous balance. If I deduct that from the amount of the repayment
(excluding PMI), then that gives me the amount of principal repayment
that month. Now I can get the balance for this month as it is previous
balance - principal repaid this month.

Re: Calcs of APR

With a non-PMI loan you've got it right. We can calculate the
effective APR rate after closing costs (using RATE function) by
deducting closing costs from the loan and using PMTs based on the loan
before closing cost adjustment. But note here that the PMTs are
constant. The RATE function can't handle a variation in PMTs. So we
have to use IRR. The IRR returns the rate of interest over the full
life of the loan in the same way that RATE returns the rate of
interest over the full life of the loan.

You can calculate the prospective IRR at any time by establishing a
flow comprising the outstanding balance and a schedule of the
prospective payments. You could do the same with the RATE function
with constant payments.

Now that you're into IRR's, you might take a look at XIRR which
removes some of the approximations made with APR based rates.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Scott]

Thanks again for the reply Norman!

Re:
The Principal repaid is the difference between previous
and current. The trouble is that we don't know the current
balance! So I approach this in amortization schedule form
by calculating interest on the previous balance. If I
deduct that from the amount of the repayment (excluding
PMI), then that gives me the amount of principal repayment
that month. Now I can get the balance for this month as
it is previous balance - principal repaid this month.

That's one way to do it. The way I made my schedule
was with the FV function. I have a column that counts the
number of payments, then my end balance column uses the FV
funtion with my known P+I payment, original loan amount
and term of the number of payments to date. I did a test
and it seems to work exactly the same as multiplying by
the monthly interest and subtracting the payment, as well
as using the FV function with a term of 1, beginning loan
amount given from the month before.

I haven't yet had a chance to put the IRR into play, but I
should have a minute tomorrow. I appreciate the advise.

Scott

By the way, what is "available free to good homes."?

 

[Norman Harker]

Hi Scott!

Re: That's one way to do it. The way I made my schedule
was with the FV function. I have a column that counts the
number of payments, then my end balance column uses the FV
function with my known P+I payment, original loan amount
and term of the number of payments to date. I did a test
and it seems to work exactly the same as multiplying by
the monthly interest and subtracting the payment, as well
as using the FV function with a term of 1, beginning loan
amount given from the month before.

Yes! Nothing wrong with that. I tend to use my method because it is a
little easier to amend format for interest rate changes.

Always remember to cross check IRR and NPV calculations as it's so
easy to make mistakes.

BTW "Free to good homes" are my comprehensive function lists that
contain Name, Source, Classification, Description, Syntax and
Arguments plus (for Excel 2000 and above, button click access to Help.
Direct e-mail request only as we don't want list flooded with "Me
too".

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Scott]

Norman,
I've tried many different ways to get this done with
IRR, but I can't seem to come up with a way to get it to
work with my data. I started with the example in the help
file, and gradually changed the numbers to match my
schedule. I guess there are some things that I know, and
some things that I know and don't know, and some things
that I don't know I don't know. Right now this is
something that I know I don't know. From your posts, and
the help file, I <i>think</i> it should look like this:

[first row] -100000 (Original loan - costs to start
business)
[second row] 99.55 (First month's principal pmt - net
income from first year)
[third row] 100.05
100.55
101.05
101.56 etc.. And I referenced this column for 361 rows
(30 year mortgage plus one row at the beginning for the
starting loan amount) =IRR(A1:A361) and I'm getting
#DIV/0! I don't have any zeros in my data, so obviously
something's going on that I don't yet understand. Also,
when I was tweeking the example to make my data, I could
only make the net income values a certain amount smaller,
after that, I'd get #NUM! or #VALUE! or #DIV/0!

Your insight is appreciated.

Scott

 

[Norman Harker]

Hi Scott!

It's the data!

It looks like the total of your repayments is a lot less than the
amount of the loan; it seems <40000 on the data given.

In that case the loan never gets repaid and along comes Al Pacino and
his men and Excel's Div/0! is its response.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Norman

If I sum the total of the principal payments it always
equals the beginning loan amount. I'm still not sure
what's going on here. Perhaps you could show me an
example of how you would set it up.

Scott

 

[Norman Harker]

Hi Scott!

If the sum of the principal repayments = total loan then it has been
repaid.

You need to compare the loan with the repayments inclusive of
interest, principal and any periodic charges.

Email me and I'll send you some amortization workbooks that allow you
to see how it's done.

Microsoft also have a template but it's not quite so easy to see how
or why they work.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Rusty]

I would like a copy of this book when you get it done. Sounds like something
I could use...

 

[Norman Harker]

Hi!

Sorted this out off group. All that was needed was to introduce an
empty row above the data. In the cell above the payments column
(negatives) I put the amount of the loan (positive). I can now take
the IRR of this cash flow. Since the flow is monthly, we multiply the
IRR by 12 to get the APR(12).


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.