Loan payment formula - need more info

[The Hun]

Need more info than Excel offers.

We need a formula to put into 24 cells that'll return the principal
+interest amount to pay down a loan. Consider these hypothetical
parameters:

Principal = $100
Annual Interest = 7.5% compounded
Number of pay periods = 24 (equal amounts)
Paymts Due = 1st of each period

Can anyone help?
Thanks

 

[Ron@Buy]

Microsoft have already thought of this: Type "Where do I find Templates" into
Excel Help. You may find that you have a Loan Amortization Schedule template

 

[Bruno Campanini]

Need more info than Excel offers.

We need a formula to put into 24 cells that'll return the principal
+interest amount to pay down a loan. Consider these hypothetical
parameters:

Principal = $100
Annual Interest = 7.5% compounded
Number of pay periods = 24 (equal amounts)
Paymts Due = 1st of each period

Can anyone help?
Thanks

=(100*0.075)/(1-(1/(1+0.075))^24)
Payment Due = End of each period = 9.105008

=(100*0.075)/(1-(1/(1+0.075))^(24-1)+0.075)
Payment Due = 1st of each period = 8.469775

Bruno

 

[JoeU]

We need a formula to put into 24 cells that'll return
the principal +interest amount to pay down a loan.

Well, you omit some critical facts. Assuming a US (or similar) loan with
normal amortization and monthly payments, you might compute the monthly
principal and interest amounts the following way.

Note: With these assumptions, the fact that interest is compounded is
irrelevant because the periodic payment for "normal amortization" covers the
interest charged for each period. So I wonder if you have a different loan
structure in mind.

If B2:B25 contains the payment number (1 through 24), then put the following
into C2 and D2:

C2, principal: =PPMT(7.5%/12,B2,24,-100)
D2, interest: =IPMT(7.5%/12,B2,24,-100)

Copy C22 down through C2525.

Note: You could dispense with B2:B25 by replacing B2 with ROW(C1) above.
But that becomes unreliable if you insert rows above. There are ways to
avoid that; but I digress.

That's probably good enough for your example. But generally, I do not like
PPMT and IPMT because they assume that the periodic payment is exactly
PMT(7.5%/12,24,-100).

In the real world, that is almost never the case. The periodic payment is
at least rounded to the penny (in the US), and lenders are free to round it
differently, e.g. rounding to the dollar, or even to choose an arbitrary
payment amount, as long as it meets the terms of the loan.

And because of those real-world constraints, the last payment amount might
not be the same as the regular payment amount. Or the prudent lender might
accept the regular payment amount and treat the excess as additional
interest.

So alternatively, put the regular payment amount into A1, for example:

A1: =ROUNDUP(PMT(7.5%/12,24,-100),2)

Then set up the following, again with B2:B25 set to 1 through 24:

E1, initial loan: 100

C2, principal: =IF(B2=24, E1, $A$1-D2)
D2, interest: =IF(B2=24, ROUNDUP(E1+E1*7.5%/12,2)-E1, E1*7.5%/12)
E2, remaining balance: =E1-C2

Copy C2:E2 down through C25:E25.

PS: Of course, it would be better to put the constants 100, 7.5%, 24 and 12
into cells, and replace them with absolute cell references in the formulas
above.


----- original message -----

 

[Joe User]

Annual Interest = 7.5% compounded

Note: With these assumptions, the fact that interest
is compounded is irrelevant because the periodic payment
for "normal amortization" covers the interest charged
for each period.

Unless you mean that 7.5% is a compounded rate, which is how the UK APR is
disclosed, for example.

In that case, replace 7.5%/12 with (1+7.5%)^(1/12)-1 or
RATE(12,0,-1,1+7.5%).

That really should be computed one time in a cell, because of its
complexity.

(But recall that I was assuming a US or similar loan. For US loans, the APR
is not a compounded rate. And in general, you should not use an APR for
interest calculations unless the APR is based on only principal and interest
payments, excluding all other finance charges. It is unclear whether UK
payments are calculated in a manner similar to the US, notwithstanding
differences in how the APR is computed. I have conflicting sources of
information.)


----- original message -----

 

[Bruno Campanini]

Unless you mean that 7.5% is a compounded rate, which is how the UK APR is
disclosed, for example.

In that case, replace 7.5%/12 with (1+7.5%)^(1/12)-1 or

I think that's the first time I read such a statement in a NG.
Nobody seems to know that, first of all Excel.

Bruno

 

[Niek Otten]

Well, That's why Excel has the NOMINAL() and EFFECT() functions.......

 

[Joe User]

I think that's the first time I read such a statement in a NG.

I am glad to contribute to broadening your understanding. My postings are
intended to be educational.

But it is not clear to me whether you still have questions. If so, are you
asking about the UK APR calculation? Or are you asking about how that is
applied to the monthly interest rate -- the formulas?

I have explained this several times in the m.p.excel.* newsgroups. For a
recent discussion (and digression; sigh), see the m.p.e.misc thread "UK
loans: how is monthly pmt determine?", started 22 Sep 2009. The Google
Groups link is
http://www.google.com/url?url=http:...groups&usg=AFQjCNEAxgQCl1eYv749jNnM0ZWODyBF7g .

That thread mentions several online sources of information. They are: an
MS KB at http://support.microsoft.com/kb/294396/en-us ; an APR wiki article
at http://en.wikipedia.org/wiki/Annual_percentage_rate ; and several online
UK mortgage calculators, e.g.
http://www.bbc.co.uk/homes/property/mortgagecalculator.shtml .

As I noted, these sources are somewhat conflicting.

KB 294396 states that the UK annual interest rate is a compounded rate. To
determine the monthly interest rate for computing monthly payment, the KB
describes effectively the formulas that I presented, although it uses the
NOMINAL function instead. Compare NOMINAL(7.5%,12)/12, (1+7.5%)^(1/12)-1,
and RATE(12,0,-1,1+7.5%).

But I suspect they are confused by the UK method of computing APR, described
in the wiki article. APR is not necessarily the same as the annual interest
rate; and the methods for computing and applying each can be different.

Some UK mortgage calculators compute the monthly payment exactly the same
way we do in the US. But other UK mortgage calculators use a very different
method, which I explain in the Sep 22 thread. However, most online mortgage
calculators include a disclaimer that the calculations are not necessarily
accurate. So it is unclear whether any of those calculators reflect the
actual method(s) used in the UK.

Unfortunately, there were no dispositive responses to my inquiry.

Nobody seems to know that, first of all Excel.

It should not be surprising that you do not find these explanations in Excel
help pages. They only cover example uses of the functions, not solutions to
problems. If the example fits your problem, great! But be forewarned about
over-generalizing: they often fail to specify all the assumptions that the
examples are based on.

For example, the method of computing a monthly payment described in the
Excel PMT help page does not apply to Canadian mortgage loans. (Note: I am
looking at the US Excel product. I don't know if MS tailors the help page
for other locales.) This is explained in KB 294396; and it has been
corroborated by knowledgable Canadian participants in these newsgroups as
well as by online Canadian mortgage calculators.


----- original message -----

 

[Bruno Campanini]

I am glad to contribute to broadening your understanding. My postings are
intended to be educational.

But it is not clear to me whether you still have questions.

[...]
All ok Joe.
No, I've no questions.
I'm only glad to read that a monthly rate is not an
annual one divided by 12.
It's strange to see that only few people know that.

Regards
Bruno

 

[Joe User]

I'm only glad to read that a monthly rate is not an
annual one divided by 12.

I would agree with your summary conclusion if you meant to write "is not
__always__".

It depends on what type of number the disclosed annual rate is. And that
usually depends on the jurisdiction (locale) of the loan.

In the US, the disclosed annual rate should always be a nominal rate, i.e.
the periodic rate times the number of periods per year, whether it is the
annual interest rate or the APR. So the monthly interest rate is indeed the
disclosed annual rate divided by 12.

(In general, the APR is not useful for computing periodic payments,
principal and interest if the APR includes unstated finance charges other
than periodic interest.)

In Canada, the disclosed annual rate should be the periodic rate compounded
over 6 months, then doubled. So the monthly interest rate is
(1+i/2)^(1/6) - 1, or the equivalent, where "i" is the disclosed annual
rate.

For the UK, I have read conflicting information, nothing that I consider
dispositive.

I don't know about other locales, e.g. Italy. However, based on some
inquiries posted in these newsgroups, I believe that in some locales, the
disclosed annual rate is the annual equivalent rate (AER), sometimes called
the annual effective rate or the effective annual rate (EAR).

The AER is the periodic interest rate compounded over the number of periods
per year. So the monthly rate is derived according to one of the formulas
that I mentioned before, e.g. (1+i)^(1/12) - 1, where "i" is the disclosed
annual rate (AER).

But it is best not to get hung up on terminology. I am using the terms as
they are defined in US law or by US English usage. Other locales and
languages might use the terms differently; and the terminology in other
languages might translate into conflicting US English language terms.

If I were from another galaxy, I would not know there is a difference
between "annual percentage rate" and "annual interest rate", which is
expressed as a percentage.

And one seemingly authoritative Canadian web site uses the term "equivalent
interest rate" (sounds like AER, a compounded rate, heh?) to describe the
__nominal__ annual interest rate, as it is defined in the US.


----- original message -----

I am glad to contribute to broadening your understanding. My postings
are intended to be educational.

But it is not clear to me whether you still have questions.

[...]
All ok Joe.
No, I've no questions.
I'm only glad to read that a monthly rate is not an
annual one divided by 12.
It's strange to see that only few people know that.

Regards
Bruno

 

[Bruno Campanini]

I would agree with your summary conclusion if you meant to write "is not
__always__".

It depends on what type of number the disclosed annual rate is. And that
usually depends on the jurisdiction (locale) of the loan.

I know that if i = EAR (effective annual rate)
i12 = (1+i)^(1/12)-1 = EMR (effective monthly rate)
j12 = 12 * i12 = NAE (nominal annual rate)
These are undisputable rules under mathematical principles.

All the other things are banks' or countries' conventions,
i.e. matter of businness, finance, etc but not matter
of science.

Ciao
Bruno