Clarification and errata....

I wrote:
The formula is a little perverse because of the mixed signs for
pv, fv and pmt; and I hope I got it right.


1. If you write the terms of the loan with mixed signs, for example:

120,000,000 Lease amount (pv)
-30,000,000 Residual amount (fv)
8.25% Annual interest rate (monthly rate r = 8.25%/12)
120 Monthly payments

Note that fv is negative, and PMT(8.25%/12,120,120000000,-30000000,t)
returns a negative number, where t is the payment type (0 for in arrears; 1
for in advance).

In that case, I would compute the principal and interest components of the
payment as negative numbers, which differs from PPMT and IPMT. And I would
maintain the balance due as a negative number.

Thus, the formula for the principal and interest paid between periods i and
k, inclusive, of n periods is:

principal paid: =FV(r,i-1,pmt,pv,t) - FV(r,k,pmt,pv,t)

interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,pv,t) + FV(r,k,pmt,pv,t)

Note: The signs of the amounts -- positive pv and negative fv, pmt, etc are
chosen from the point of view of the borrower. They could be flipped for
the lender's point of view. The only requirement is that pv and fv/pmt have
opposite signs. I would make the sign of the balance due, principal and
interest paid the same as the sign of pmt, which is consistent with the
straight-forward use of the FV function.


2. If you write the terms of the loan with unsigned values, which is typical
of loan calculators and amortization schedules, for example:

120,000,000 Lease amount (pv)
30,000,000 Residual amount (fv)
8.25% Annual interest rate (monthly rate r = 8.25%/12)
120 Monthly payments

Note that fv is positive. To make pmt postive, use
PMT(8.25%/12,120,-120000000,30000000,t).

In that case, I would compute the principal and interest components of the
payment as positive numbers, similar to PPMT and IPMT. And I would maintain
the balance due as a positive number.

Thus, the formula for the principal and interest paid between periods i and
k, inclusive, of n periods is:

principal paid: =FV(r,i-1,pmt,-pv,t) - FV(r,k,pmt,-pv,t)

interest paid: =pmt*(k-i+1) - FV(r,i-1,pmt,-pv,t) + FV(r,k,pmt,-pv,t)

Note that the only difference between these formulas in #2 versus #1 is:
(a) using of -pv instead of pv in all formulas; and (b) reversing FV(i-1)
and FV(k) to return consistent signs.

The difference between these formulas and my previously posted formulas is
the consistent sign of pmt, principal paid and interest paid.


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

"JoeU2004" wrote in message
...
"sgl" wrote:
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 ???

I tried to explain that before. By "total repayments", you mean the sum
of the PPMT results. PPMT is assuming that the first payment applies
entirely against principal, whereas standard loan functions (and the
mathematics behind them) apply some of the payment toward interest in
advance for the period.

Alternatively, you can use the formula that I provided in the previous
thread, with a tweak, to compute the principal paid down between any two
periods, i and k, out of n periods (i <= k <= n). Namely:

=fv(r,k,pmt,pv,fv,1) - fv(r,i-1,pmt,pv,fv,1)

where r is periodic interest rate corresponding to the payment frequency,
and pmt is PMT(r,n,pv,fv,1). This assumes that pv is positive and fv is
negative; ergo, pmt is also negative.

(In the real world, the PMT function result is rounded to cents, at least.
But that will just add to the confusion because in that case, we do not
expect things to add up properly anyway.)


JoeU2004 your suggestion for the workaround equates to the first
intalment
of 485,123.63 for principal which is the same result as using the Excel
PPMT
function in arrears ("0").

Yes; and there is nothing wrong with that. The same amount of principal
is paid down each period for payment in arrears and in advance. The only
difference is the amount of interest paid down and, therefore, the
installment payment itself.


What about the interest portion for the first instalment in the event
that
the payment has to be made in advance? In my exercise this equates to
"0"?.
Any suggestions on how to work around the interest portion?

I do not know what you did wrong, since you neglected to show your formula
or method.

The amount of interest paid between any two periods, i and k, out of n
periods (i <= k <= n), can be computed by:

=-pmt*(k - i + 1) - fv(r,k,pmt,pv,fv,1) + fv(r,i-1,pmt,pv,fv,1)

That is: the sum of the payments less the principal paid down, expressed
as a non-negative number like IPMT.

(The formula is a little perverse because of the mixed signs for pv, fv
and pmt; and I hope I got it right. I prefer to express all numbers as
non-negative values and make the necessary adjustments when using the
financial functions. Let me know if you would like me to restate
everything in non-negative terms.)

It might be easier for you to understand all this if you created an
amortization schedule.

HTH.


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

"sgl" wrote in message
...
Thank you both (Fred Smith and JoeU2004) for your responses. My apologies
for
replying so late therefore have lost the thread!

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 the 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 ???

JoeU2004 your suggestion for the workaround equates to the first
intalment
of 485,123.63 for principal which is the same result as using the Excel
PPMT
function in arrears ("0").

What about the interest portion for the first instalment in the event
that
the payment has to be made in advance? In my exercise this equates to
"0"?.
Any suggestions on how to work around the interest portion?

The exercise is actually involved within the shipping industry where
Bareboat (Lease) Charters have to be paid monthly in advance.

Thanks in advance/sgl