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

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

:

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