IPMT & PPMT functions

[Shahid Majeed]

Can some one told the underlying algebric formula behind
these functions. For example, formula to calculate
instalment is
PR(1+R)^N/(1+R)^N -1

IPMT and PPMT functions gives interest and principal
portion in the instalment.

Also where can I get algebric formulas of other functions.

thnx.

[Norman Harker]

Hi Shahid!

IPMT and PPMT are both functions that involve manipulations of the common
amortization formula function use by Excel:

PV + (PMT*((1-(1+RATE)^(-NPER))/RATE)*(1+RATE)^TYPE) + (FV*(1+RATE)^(-NPER))
= 0

Additional parentheses added for purposes of clearly showing the A+B+C=0
structure. We can re-express that formula for PV, PMT, FV, and NPER.
Solutions for RATE require iteration unless the PMT = 0 in which case there
is an explicit solution.

In the case of PPMT the function is calculating the FV after n periods and
comparing it with the FV after n-1 periods. That gives the amount of
principal repaid during the period n-1 to n. We could derive a rather long
formula for this or use the difference between two FV functions for periods
n and n-1.

In the case of IPMT the function is deducting the result of the PPMT
calculation for that period from the amount of the Payment for that period.
(i.e. we can use the splendidly simple logic that the amount of repayment
that does not constitute principal repayment must represent interest).

I'd add that in most instances we would be using an amortization schedule to
make these calculations for the full loan period. In that instance the
process is to calculate interest on the *previous* balance. (That gives the
IPMT equivalent in the table). We deduct the interest from the repayment to
get the principal repaid. (That gives the PPMT equivalent in the table). The
balance at the end of that period is the previous balance adjusted for the
principal repaid.

I have some live annotated examples of these schedules if wanted. Just send
a private email request. Microsoft do have an amortization template but it's
hard work to see how it works.
--

Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au

"Shahid Majeed" <gi...@hotmail.com> wrote in message
news:027901c2de5e$d664af50$2f01...@phx.gbl...

[Norman Harker]

Hi Shahid!

I should add that the problem becomes rather confusing if you have payments
in advance.

Do a Google Search under IPMT and PPMT and you'll come up with a post I did
on this particular problem back in January. In essence the crucial point is
to realise that the calculation is based on the number of the payment rather
than the payment periods. Thus with payments in advance the 5th payment is
made after 4 periods!

--

Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au

"Norman Harker" <njha...@optusnet.com.au> wrote in message
news:#W5#IIA4CH...@TK2MSFTNGP09.phx.gbl...