PPMT Function

[sgl]

using Excel 2003

I have taken the following from the Excel Help. I cannot Understand exactly
how this function works.

10% Annual interest
1 Period for which you want to find the interest
3 Years of loan
8,000.00 Present value of loan
-2,000 FV
0 Type (payments at end of period)

the results for each of the periods are as follows

-€1,983.43 period 1
-€1,999.95 period 2
-€2,016.62 period 3

-€6,000.00 Total repayment principal - which is correct

If I change the type to 1 (payments in advance) the total amounts are

-€2,033.15 period 1
-€1,983.43 period 2
-€1,999.95 period 3

-€6,016.53 Total repayment principal - This cannot be correct !!!!!

What am i doing wrong???

What about the IPMT Function. Would this have the same error??

Many thanks in advance for all help/sgl

 

[Fred Smith]

I would agree. It looks like PPMT and IPMT calculate incorrect values when
FV is greater than zero and payments are in advance.

Let us know what you are trying to calculate, and we should be able to find
a workaround for you.

By the way, you are using 0.83333% (10%/12) annual interest, not 10%.

Regards,
Fred.

 

[JoeU2004]

It looks like PPMT and IPMT calculate incorrect values when FV is greater
than zero and payments are in advance.

Actually, they return incorrect values whenever payments are in advance
(type 1), independent of FV. The error is simply not so evident when FV is
zero because the sum of the PPMT results is correct. But the amounts for
individual periods are incorrect insofar as they differ from the mathematics
of FV and PMT.

The work-around is simply not to rely on PPMT or IPMT at all, at least not
for payments in advance. For payments in advance, the formula for the
principal paid in period k of n periods is:

=FV(r,k,pmt,pv,1) - FV(r,k-1,pmt,pv,1)

where "pmt" is PMT(r,n,pv,fv,1). Note that PMT and FV are the Excel
functions, whereas pmt and fv are the constants from the terms of the loan.

By the way, you are using 0.83333% (10%/12) annual interest, not 10%.

Thank you for that; I missed it myself.

I hope the OP understands that this is what he/she is doing wrong.
Actually, I suspect the real error is in using periods numbered 1 through 3
instead of numbering 1 through 36 and summing over 12 periods. Most loans
are repaid sub-annually, for example monthly.

In any case, the rate and period count must be consistent with the payment
frequency. If payments are truly made annually, the rate should be 10%.
But if payments are made monthly, the total periods (nper) should be 3*12,
just as the rate is 10%/12.


Explanation of the type-1 PPMT behavior....

Using an "annual" rate of 10%/12 in order to provide numbers consistent with
the OP, the annual payment is about 2033.15 -- PMT(10%/12,3,8000,-2000,1).

Note that PPMT(10%/12,1,3,8000,-2000,1) returns about 2033.15. Ergo, PPMT
is treating the first payment as entirely principal. Also, note that
PPMT(10%/12,2,3,8000,-2000,1) returns about 1983.43. That is the second
payment (2033.15) less about 49.72, the interest on the remaining balance
after the first payment, about 5966.85. Similarly for the third payment.

Ostensibly, that seems to make some sense. The thinking might be: when the
payment is at the beginning of the period, there is no accrued interest for
the first payment, and for subsequent periods, the interest is based on the
balance at the beginning of the period.

However, that is not consistent with the mathematics of the FV and PMT
functions. The mathematics of the FV and PMT functions effectively assume
that when the payment is in advance, so is the payment of interest. For
example, the balance after the first payment, FV(10%/12,1,pmt,8000,1), is
about 6016.57, where pmt is the result of the PMT function above, about
2033.15. Even though the interest is based on the initial balance less the
full payment, the net effect is to reduce the balance by less than the
payment; that is, to reduce the amount applied toward the principal.

Which method is used in the real world? I have no idea. However, with
PPMT, the ending balance -- the initial balance less the sum of the PPMT
results -- is about 1983.47, which is less than the 2000 balloon payment in
the terms of the loan. Clearly, that is incorrect.


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

 

[sgl]

Thank you both for your responses. My apologies for replying so late!

What I am trying to compile is an Annuity Template where the user enters the
various parameters and all calculations are compiled automatically. How I
picked up the problem is that I have the following loan constants as an
example on which template is being built.

120,000,000 PV - Total loan
30,000,000 FV - Balloon
8.25% i - Annual Interest
12 n - Monthly payments - Interest calculated at 8.25%/12
1 Jan 00 Start date
31 Dec 09 End date - Baloon payment date + last instalment
10 Term loan - 10 year repayment period
120 NPer - Periods
1 PPMT type - in advance

If you calculate on an arrears basis the total Principal repayments are 90mn
over the period (120 months) and the Balloon correctly stands at 30mn whereas
if you calculate for payments in advance the total repayments are
90,204,841.71 ??? and the Balloon stands at 29,795,158.29 ???
Thank you/sgl

 

[JoeU2004]

I see you finally found your "lost" thread. Please see my response to your
"repost".


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