Hi. If interested, here is a custom function for cumulative interest.
You can bypass the Pmt() function as it is already incorporated into the
function.
I left the interest rate at 8.25 so as to make it easier to enter.


Function CumInt(Pv, Fv, IntRate, n, s, e)
' n = Number of payments
' s = start
' e = end
Dim k, r
r = IntRate / 1200
k = 1 + r
CumInt = (Fv * (k ^ (e + 1) + r * (-e + s - 1) - k ^ s) + _
Pv * (-k ^ (e + 1) + r * (e - s + 1) * k ^ n + k ^ s)) _
/ (k * (k ^ n - 1))

End Function


Sub TestIt()
'Period 5 only
Debug.Print CumInt(120000000, 30000000, 8.25, 120, 5, 5)
'All periods 1 - 120
Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 120)

'Periods 1 - 10
Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 10)
End Sub

So, on a worksheet for periods 1-10, you would enter:

=CumInt(120000000, 30000000, 8.25, 120, 1, 10)

The 3 answers above a

802575.291677935
66141363.223381
8007673.94895974

= = =
HTH :)
Dana DeLouis




sgl wrote:
Can't thank you enough for your assistance and your patience and the time you
put into this.
Finally got it to work!!! bleary eyed and full of ---/+++ but it works
Thanks a million/sgl


"JoeU2004" wrote:

Errata....

I wrote:
"sgl" wrote:
Therefore where I must be going wrong is the pmt*(k-i+1) element.
Oops: I should have written simply pmt*(k-i). "That might be
contributing to your problem" :-). Klunk!

From the above what is k and what is i in say period 5.
k is 5, the period of interest. i is 4, the previous period number.
My mistake: I was right the first time.

My intent was that for a single period, k and i would be the same, e.g. both
5. We can compute the total interest for the term of the lease by using
k=120 and i=1.

Thus, for period 5, we have:

principal paid: =FV(8.25%/12,5-1,pmt,-120000000,1) -
FV(8.25%/12,5,pmt,-120000000,1)

interest paid: =pmt*(5-5+1) - FV(8.25%/12,5-1,pmt,-120000000,1) +
FV(8.25%/12,5,pmt,-120000000,1)


For the entire lease, periods 1 through 120, we have:

principal paid: =FV(8.25%/12,1-1,pmt,-120000000,1) -
FV(8.25%/12,120,pmt,-120000000,1)

interest paid: =pmt*(120-1+1) - FV(8.25%/12,1-1,pmt,-120000000,1) +
FV(8.25%/12,120,pmt,-120000000,1)

where pmt is the positive montly payment,
PMT(8.25%/12,120,-120000000,30000000,1).

In the latter case, the computed principal paid should be the same as the
original lease amount less the residual.


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

"JoeU2004" wrote in message
...
"sgl" wrote:
I may be getting totally confused
Sorry about that. My explanation became unduly abstract. Also, I made an
error in my previous posting. I'll try to clarify.


In my amortisation the terms of the loan are entered with unsigned
values.
[....]
For "pmt" I am using the Excel PMT function with residual
value as a negative "-" i.e - 30,000,000.
If all the terms of the loan are unsigned values, I suggested that you
compute pmt as an unsigned value as well. My interest formula in part 2
depends on that. That might be contributing to your problem with the
interest calculation.

Thus, either compute -PMT(...,120000000,-30000000,1), or compute
PMT(...,-1200000000,30000000,1).

The second form looks at the loan from the lender's point of view, whereas
the first form looks at it from the borrower's point of view. Both views
are equally valid.


Therefore where I must be going wrong is the pmt*(k-i+1) element.
Oops: I should have written simply pmt*(k-i). "That might be
contributing to your problem" :-). Klunk!


From the above what is k and what is i in say period 5.
k is 5, the period of interest. i is 4, the previous period number. So
pmt*(k-i) is simply pmt.

In my original formulation in the other thread, I had used simply k and
k-1. That was probably clearer.

I generalized to k and i, i <= k, to show that we can compute the sum of
payments (principal or interest) directly with the formulas instead of
having to sum over a range of payments, as we must do with PPMT and IPMT.
I'm afraid that over-generalized added confusion.


I still can't get the interest element to work for me.
No wonder, considering my mistake. Sorry.

The interest paid in a period is simply the full payment less the
principal paid in the period.

Conversely, the principal paid in a period is the full payment less the
interest paid in the period.

I presented the "FV - FV" formula to demonstrate how to compute PPMT
(corrected) for any specified period or range of periods. Similarly, the
"pmt - FV + FV" formula demonstrates how to compute IPMT (corrected) for
any specified period or range of periods.

But when building an amortization table, you don't need to use those
formulas. It is a good idea to use an alternative method in order to
validate the formulas. ("He said to himself." ;-)

My amortization tables usually have the following columns: payment
number, payment amount, principal paid, interest paid, new remaining
balance. The first row, before payment number 1, has the loan amount in
the "new remaining balance" column.

The interest paid is:

1. For payment in arrears: (previousBalance - payment) * (monthlyRate)

2. For payment in advance: previousBalance * monthlyRate

The other columns have the same formulas regardless of arrears or
in-advance, namely:

Principal paid: payment - interestPaid

New remaining balance: previousBalance + interestPaid - payment

or equivalently: previousBalance - principalPaid


I would appreciate if you could, from the loan constnts that I have given
you, calculate for me the first 10 periods of interest and the last say
5
periods (116 to 120) so that I can grasp where I am going wrong.
I will do my best to present this. But my experience has been that the
format might get mangled and difficult to read. Sorry if that's the case.

Payment Principal Interest Balance

1 1,301,178.03 485,123.63 816,054.40 119,514,876.37

2 1,301,178.03 488,458.85 812,719.18 119,026,417.52

3 1,301,178.03 491,817.01 809,361.02 118,534,600.52

4 1,301,178.03 495,198.25 805,979.78 118,039,402.27

5 1,301,178.03 498,602.74 802,575.29 117,540,799.54

6 1,301,178.03 502,030.63 799,147.40 117,038,768.91

7 1,301,178.03 505,482.09 795,695.94 116,533,286.82

8 1,301,178.03 508,957.28 792,220.75 116,024,329.54

9 1,301,178.03 512,456.36 788,721.67 115,511,873.18

10 1,301,178.03 515,979.50 785,198.53 114,995,893.68



116 1,301,178.03 1,066,698.21 234,479.81 34,340,634.28

117 1,301,178.03 1,074,031.77 227,146.26 33,266,602.51

118 1,301,178.03 1,081,415.73 219,762.29 32,185,186.78

119 1,301,178.03 1,088,850.47 212,327.56 31,096,336.31

120 1,301,178.03 1,096,336.31 204,841.71 30,000,000.00



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

"sgl" wrote in message
...
You have been very patient with me and very explicit in your replies and
I
thank you for this but I still can't get the interest element to work for
me.
I will set out the detail of the amortisation schedule below. All input
amounts are as per my previous loan constants. The Principal element I
have
worked out and is listed below

In my amortisation the terms of the loan are entered with unsigned
values.

Period
no Pricipal
1 485,123.63
2 488,458.85
3 491,817.01
4 495,198.25
5 498,602.74
6 502,030.63
7 505,482.09
8 508,957.28
9 512,456.36
10 515,979.50
.
.
.
120 1,096,336.31

..... and up to period 120 (n). The total equates to 90,000,000 which is
what we want. In the interest paid formula that you provided the two FV
elements are exactly the same as for the principal calculation except
that
the signs are reversed.

Therefore where I must be going wrong is the pmt*(k-i+1) element. From
the
above what is k and what is i in say period 5. Similarly what is k and
what
is i in period 120. For "pmt" I am using the Excel PMT function with
residual
value as a negative "-" i.e - 30,000,000.

I would appreciate if you could, from the loan constnts that I have given
you, calculate for me the first 10 periods of interest and the last say
5
periods (116 to 120) so that I can grasp where I am going wrong.

I may be getting totally confused with the signs which give totally
confusing results or looking at numbers for too long!!! Please bear with
me.

Many thanks in advance/sgl




"JoeU2004" wrote:

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