"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