PPMT Function Repost

[sgl]

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

 

[JoeU2004]

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]

Thanks for the reply, will give it a try tomorrow and come back to you, too
late this side of the world.
Many tahnks/sgl

 

[JoeU2004]

Clarification and errata....

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

 

[sgl]

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]

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

 

[JoeU2004]

Errata....

Oops: I should have written simply pmt*(k-i). "That might be
contributing to your problem" . Klunk!


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

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

 

[sgl]

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

Errata....

Oops: I should have written simply pmt*(k-i). "That might be
contributing to your problem" . Klunk!


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

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

 

[Dana DeLouis]

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

802575.291677935
66141363.223381
8007673.94895974

= = =
HTH :>)
Dana DeLouis

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

Errata....

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

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

 

[sgl]

Dana,

Thank you vm. This works very well. I have a separate column for the period
(1,2,3 etc ...120) and therefore declaring the start and end dates in the
CumInt column as absolute and relative respectively you get the CumInt to
date. In the interest for a specific period I have both start and end dates
as relative and therefore I get the interest for that particular period.

This is very good formula to have and thank you for your assistance/sgl

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

802575.291677935
66141363.223381
8007673.94895974

= = =
HTH :>)
Dana DeLouis

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

Errata....

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

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

 

[Dana DeLouis]

Hi. Glad you found it useful.
If interested, here is the change in balance formula between two periods.

Function CumPrin(Pv, Fv, IntRate, n, s, e)
' Payment at beginning of period
' n = Number of payments in loan
' s = start
' e = end
Dim k, r
r = IntRate / 1200
k = 1 + r
CumPrin = (Pv - Fv) * (k ^ (e + 1) - k ^ s) / (k * (k ^ n - 1))
CumPrin = Round(CumPrin, 2)
End Function

The balance change from period 1-10

? CumPrin(120000000, 30000000, 8.25, 120, 1, 10)
5004106.32

The balance change from 1 - 120 should be the difference pv - fv

? CumPrin(120000000, 30000000, 8.25, 120, 1, 120)
90000000

The Excel formulas for CumIpmt() and CumPrinc() don't have an option for
Future value. Unfortunately, they therefore assume a 0 future value.

HTH
Dana DeLouis
= = = =

Dana,

Thank you vm. This works very well. I have a separate column for the period
(1,2,3 etc ...120) and therefore declaring the start and end dates in the
CumInt column as absolute and relative respectively you get the CumInt to
date. In the interest for a specific period I have both start and end dates
as relative and therefore I get the interest for that particular period.

This is very good formula to have and thank you for your assistance/sgl

<snip>

 

[sgl]

Dana,

Thank you for your great assistance. This also works verry well. Only slight
rounding differences when you put the formula in a worksheet. The total loan
repayments amount to 90,000,000.05 leaving a balloon of 29,999,999.95. In the
VBE it works fine when you run the macro.

sgl

 

[JoeU2004]

Thank you for your great assistance. This also works verry well. Only
slight
rounding differences when you put the formula in a worksheet. The total
loan
repayments amount to 90,000,000.05 leaving a balloon of 29,999,999.95. In
the VBE it works fine when you run the macro.

I cannot imagine why anyone would resort to such a complex solution,
especially a UDF, when a straight-forward Excel solution exists. (Not to
mention an Excel solution that works for in-arrears as well as in-advance
payments with one obvious, well-documented change to a function argument.)

But FYI, I do not get any such numerical error, whether I use the UDF as
Dana wrote it, or I implement it in an Excel formula. I am using Excel
2003.

Nonetheless, I would not be surprised by a 0.01 difference between an Excel
and VB implementation with some loan parameters. The VB Round function
behaves differently from the Excel ROUND function; specifically, the VB
Round function does "banker's rounding". Unless that is what you want (!),
I would suggest the following change to Dana's UDF:

CumPrin = WorksheetFunction.Round(CumPrin, 2)


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