Problems with PPMT function

[Nolan Habegger]

I'm trying to use the PPMT function (which calculates the
total amount of principal paid towards a mortgage loan). I
have triple-checked my arguments and structure, and
everything is correct, but the function is not calculating
properly. Here is the function:

=PPMT(rate,per,nper,pv,fv,type)

Here is my code:

=PPMT(7%/12,24,360,100000,0,1)

The Excel result is $93.
The true result is supposed to be $2,105.

I have the following Add-Ins installed:

Analysis ToolPak
Analysis ToolPak VBA
Autosave Add-In
Conditional Sum Wizard
Small Business Financial Wizard
Solver Add-In
Update Add-In Links

Any ideas? What am I missing here?

Thanks in advance,

Nolan Habegger

 

[Norman Harker]

Hi Nolan!

Nothing wrong with the function PPMT. It's just not the one you want.
PPMT returns the principal element in a particular repayment.

You need:
=CUMPRINC(7%/12,360,100000,1,24,0)
Returns: -2105.05263328434

Note that to get your required answer, you must use Type argument = 0
(i.e. first payment is made after 1 period)
Also note that the - is because of the sign of money flows convention
adopted by Excel whereby the loan for the borrower is regarded as
positive. The result indicates a payment back of 2105.05 in principal.

CUMPRINC is an Analysis ToolPak function:
=CUMPRINC(rate,nper,pv,start_period,end_period,type)
Returns the cumulative principal paid on a loan between two periods

PPMT is Built In:
=PPMT(rate,per,nper,pv,fv,type)
Returns the amount of principal element in a payment for an investment
for a given period

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

 

[Nolan Habegger]

DOH! I'm a monkey with a loaded gun when it comes to this
stuff. Thanks for the help!