How are PMT and other functions calculated

[Jason]

I am trying to create an amortization scheldule for a
mortgage loan. When I use the PMT function to calculate
the PMT I get a answer that is different from the answer
that I calculate using a financial calculator or manually,
regardless of if payments are at the begining of end of a
period. Does anyone know how PMT is actually calcuted
using this PMT formula? Thank You!

 

[Harlan Grove]

I am trying to create an amortization scheldule for a
mortgage loan. When I use the PMT function to calculate
the PMT I get a answer that is different from the answer
that I calculate using a financial calculator or manually,
regardless of if payments are at the begining of end of a
period. Does anyone know how PMT is actually calcuted
using this PMT formula? Thank You!

PMT uses standard anuity formula. The present value of $1 paid at the end of
each month for N months at *effective* monthly interest rate i is a finite
geometric series, and the answer is

PV = [1 - (1 + i)^-N] / i

One trap with this is that interest rates are almost never quoted on an
effective basis, but usually on an APR or similar nominal basis. If the APR were
6%, then the effective monthly interest rate would be 0.5%. In addition, if the
term of the loan were 360 months, the present value of the payments viewed as an
anuity would be 166.7916144 = (1-(1.005)^-360)/0.005. If the loan amount were
$100,000, then the monthly payment would be $100,000 divided by the present
value of the payments, or $599.55 (rounded to the nearest cent). The PMT
function reproduces this result.

=PMT(0.005,360,-100000) [note the idiosyncratic use of sign convention]

returns $599.55.

If your PMT and financial calculator results are wildly different, then the most
likely cause would be that your financial calculator knows that it needs to
adjust the APR to a monthly effective rate basis, but PMT doesn't do this. If
you're entering the APR in PMT, that's the reason why it's giving different
results. In that case, the function is correct, but you're not feeding it what
it needs.

 

[Norman Harker]

Hi Harlan!

You've simplified the PMT and PV algorithms used by Excel. PMT does
not use the formula that you gave except after simplification for the
fact that there was a 0 FV.

The base formula for the financial functions PV, PMT, FV, RATE and
NPER is:

PV*(1+Rate)^NPer +PMT*(1+Rate*Type)*((1+Rate)^Nper-1)/Rate + FV = 0
Excel solves for one financial argument in terms of the others

See Help for PV in Excel from Excel 97 onwards (at least).

Each of the functions resolves the answer by re-expression of that
formula. The use of this formula explains the "idiosyncratic" sign
convention of inputs and outputs from the financial functions.
Following that sign convention is essential if there are three
non-zero elements involved in the calculation; e.g. PV of right to
receive 20000 for 5 years with a future obligation to pay 70000.

Financial calculators use similar formulas although apart from in Help
for the PV function (Excel 2003), I think the formula is usually
expressed in it's discounting form:

PV + PMT*((1-(1+Rate)^-NPer)/Rate)*(1 +Rate*Type)+FV*(1+Rate)^-NPer =
0

If there are only two flow elements involved (i.e. the third is 0),
then one of the elements in the base formula will resolve to 0 and you
get the simplified formulas that we usually use with long hand
calculation.


--
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.

I am trying to create an amortization scheldule for a
mortgage loan. When I use the PMT function to calculate
the PMT I get a answer that is different from the answer
that I calculate using a financial calculator or manually,
regardless of if payments are at the begining of end of a
period. Does anyone know how PMT is actually calcuted
using this PMT formula? Thank You!

PMT uses standard anuity formula. The present value of $1 paid at the end of
each month for N months at *effective* monthly interest rate i is a finite
geometric series, and the answer is

PV = [1 - (1 + i)^-N] / i

One trap with this is that interest rates are almost never quoted on an
effective basis, but usually on an APR or similar nominal basis. If the APR were
6%, then the effective monthly interest rate would be 0.5%. In addition, if the
term of the loan were 360 months, the present value of the payments viewed as an
anuity would be 166.7916144 = (1-(1.005)^-360)/0.005. If the loan amount were
$100,000, then the monthly payment would be $100,000 divided by the present
value of the payments, or $599.55 (rounded to the nearest cent). The PMT
function reproduces this result.

=PMT(0.005,360,-100000) [note the idiosyncratic use of sign convention]

returns $599.55.

If your PMT and financial calculator results are wildly different, then the most
likely cause would be that your financial calculator knows that it needs to
adjust the APR to a monthly effective rate basis, but PMT doesn't do this. If
you're entering the APR in PMT, that's the reason why it's giving different
results. In that case, the function is correct, but you're not feeding it what
it needs.

 

[Harlan Grove]

You've simplified the PMT and PV algorithms used by Excel. PMT does
not use the formula that you gave except after simplification for the
fact that there was a 0 FV.

....

True, but dealing with FV is trivial.

The base formula for the financial functions PV, PMT, FV, RATE and
NPER is:

PV*(1+Rate)^NPer +PMT*(1+Rate*Type)*((1+Rate)^Nper-1)/Rate + FV = 0
Excel solves for one financial argument in terms of the others ....

Following that sign convention is essential if there are three
non-zero elements involved in the calculation; e.g. PV of right to
receive 20000 for 5 years with a future obligation to pay 70000.

....

No, it's not essential. If there were no sign convention (as there isn't in
both 123 and Quattro Pro), so PV, FV and PMT were all positive, you'd just
have to rewrite the formula as

PV*(1+Rate)^NPer + FV = PMT * (1+Rate*Type)*((1+Rate)^NPer-1)/Rate

which has both sides expressed in terminal period cost basis, i.e., future
value. Dividing both sides by (1+Rate)^NPer gives both sides expressed in
initial cost basis, i.e., present value. Magically, that's

PV + FV/(1+Rate)^NPer = PMT * (1+Rate*Type)*(1-(1+Rate)^-NPer)/Rate

Perhaps appraisers would consider the left hand side (LHS) some hybrid
value, but anyone aquainted with life insurance mathematics would see that
the LHS is just a verbose way of stating the overall present value, the
present value of the 'present value' and the present value of the 'future
value'.

So, if one must become verbose and add the term 'overall present value',
OPV, defined in terms of Excel quantities as PV + FV/(1+Rate)^NPer, then the
cognitive leap from what I wrote before to the formulas above is that the
payment is equal to the overall present value divided by the present value
of an annuity of 1 over NPer terms at intererst rate Rate, so

PMT = OPV / ([1 - (1+Rate)^-NPer] / Rate) / (1+Rate*Type)

The key is the ([1 - (1+Rate)^-NPer] / Rate) bit. Everything else is
trivial.

If there are only two flow elements involved (i.e. the third is 0),
then one of the elements in the base formula will resolve to 0 and you
get the simplified formulas that we usually use with long hand
calculation.

Whattcha mean 'we'? I guarantee you there are professions that use these
relationships on a daily basis but don't screw around maintaining a
pointless distinction between PV and FV. To solve the equation in Excel's
online help, the PV and FV terms must be put on the same cost basis, and
that means either both on present value basis or future value basis. Keeping
them separate is unnecessary bookkeeping. Calculating PV or FV when the
other is given and nonzero and all other terms are given reduces the problem
to

PV + FV/<immediately calculated value 1> = <immediately calculated value 2>

and that ain't too terribly difficult to solve. Indeed, the relationship is
linear in terms of PV, FV and PMT, linear in terms of NPer after rearranging
terms and taking logarithms, and requiring iterative methods only for Rate.

This ain't rocket science.

 

[Norman Harker]

Hi Harlan!

Interesting.

But OP asked, "Does anyone know how PMT is actually calcuted using
this PMT formula?"

The formula given was not the one Excel uses unless Help is misleading
us.

--
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.

 

[Harlan Grove]

Interesting.

But OP asked, "Does anyone know how PMT is actually calcuted using
this PMT formula?"

The formula given was not the one Excel uses unless Help is misleading
us.

Help isn't misleading, but unless the OP had nonzero PV and FV (unlikely),
the approach I used in my original response reproduces Excel's PMT
function's result every time for payments at the end of each period. Since I
showed the PMT formula result I was comparing my approach to, some (likely
the OP) would have been clever enough to figure out my unstated assumptions
with respect to FV and PV. Looks like it's clear others wouldn't be so
clever.

The key is the [1 - (1+Rate)^-NPer] / Rate term. Fuss all you want about the
other details and lockstep duplication of the exact formula. The term above
is a standard annuity function (annuity immediate). Multiplying it by
(1+Rate) when Type = 1 is another standard annuity function (annuity due).
These are part of Excel's own formula, again they're annuity functions, and
they're used as I described in my original responce.

For some people, given a formula

a * <something simple> + b * <something complicated> + c = 0

it's self-evident that it could be rewritten as

-(a * <something simple> + c) = b * <something complicated>

then have a change of variables

d = b * <something complicated>

and be the same thing. I'll try to keep in mind that there are other people
who just don't get this.

 

[Norman Harker]

Hi Harlan!

The question was:

"Does anyone know how PMT is actually calcuted using this PMT formula"

The algorithm and formula is given in Help which says how the PMT
formula is actually calculated.
--
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.

 

[Harlan Grove]

The question was:

"Does anyone know how PMT is actually calcuted using this PMT formula"

The algorithm and formula is given in Help which says how the PMT
formula is actually calculated.

...

Fine. Maybe the Excel programmers are as stupid as you're inferring they are.

Why?

Using x for Type. Top eq'ns ignore sign convention, bottom eq'ns follow it.

PV when all other terms are given:
PV = PMT * (1+x*Rate) * (1 - (1+Rate)^-NPer) / Rate - FV * (1+Rate)^-NPer
PV = -PMT * (1+x*Rate) * (1 - (1+Rate)^-NPer) / Rate - FV * (1+Rate)^-NPer

FV when all other terms are given:
FV = PMT * (1+x*Rate) * ((1+Rate)^NPer - 1) / Rate - PV * (1+Rate)^NPer
FV = -PMT * (1+x*Rate) * ((1+Rate)^NPer - 1) / Rate - PV * (1+Rate)^NPer

PMT when all other terms are given:
PMT = (PV + FV * (1+Rate)^-NPer) * (1+x*Rate) * Rate / (1 - (1+Rate)^-NPer))
PMT = -(PV + FV * (1+Rate)^-NPer) * (1+x*Rate) * Rate / (1 - (1+Rate)^-NPer))

NPer when all other terms are given:
NPer = log(PMT*(1+x*Rate) + Rate*FV)/(PMT*(1+x*Rate) - Rate*PV)) / log(1+Rate)
NPer = log(PMT*(1+x*Rate) - Rate*FV)/(PMT*(1+x*Rate) + Rate*PV)) / log(1+Rate)

Only Rate can't be reduced to a simple formula, so *only* Rate requires the
formula shown in online help for the PV function to be solved iteratively.

So unless the Excel programmers were so monumentally stupid as to use a common
iterative procedure to solve for PV, FV, PMT and NPer in terms of the other
parameters rather than sensibly calculating these 4 directly (and I've never
considered them *that* stupid), then *NO* I don't believe they use the formula
exactly as shown in online help for the PV function to solve any of the
financial functions other than Rate. I give them credit for making common sense
algebraic manipulations in order to solve for PV, FV, PMT and NPer directly. You
don't?

 

[Norman Harker]

Hi Harlan!

I fully agree the usefulness of critically analysing approaches but
usually place that after an examination of the approach actually used.

--
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.

 

[Harlan Grove]

I fully agree the usefulness of critically analysing approaches but
usually place that after an examination of the approach actually used.

So without seeing the actual Excel source code, how do you know what @#$%&*!
formulas Microsoft's Excel programmers used in any of the functions PV(), FV(),
NPER(), PMT() or RATE()? Are you omnicient? Are you taking online help as gospel
truth, meaning you're not aware of any errors at all in it?!

I'll stick with what I've already stated and suggest that one of the
participants in this thread may be as (if not more) obtuse as (than) he's
inferring the Microsoft Excel programmers must be incompetent.

 

[Norman Harker]

Hi Harlan!

Agreed that we don't know what they've done with the code any more
than how they coded the RAND function in 2003!

--
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.