Constant loan payments vs. constant payments of principal
 

Hi,

I´m looking for a formula/function that calculates loan payments with a
constant payment on the principal (plus intrests on the remaining
principal),not equal payments throughout the loan period (annuitet) like PMT
gives.

In other words, a formula/function that would give me payment that is always
a fixed payment of the principal plus intrests (and the intrests part gets
smaller and smaller as the principal is paid up).

Any help greatly appreciated.

Thanks

Constant loan payments vs. constant payments of principal
 

I think that you are looking for ipmt.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"lalli945" wrote:

Hi,

I´m looking for a formula/function that calculates loan payments with a
constant payment on the principal (plus intrests on the remaining
principal),not equal payments throughout the loan period (annuitet) like PMT
gives.

In other words, a formula/function that would give me payment that is always
a fixed payment of the principal plus intrests (and the intrests part gets
smaller and smaller as the principal is paid up).

Any help greatly appreciated.

Thanks

Constant loan payments vs. constant payments of principal
 

lalli945 wrote:
I´m looking for a formula/function that calculates loan payments with a
constant payment on the principal (plus intrests on the remaining
principal)

I assume you mean that you want the interest on the outstanding balance
before the payment for the period. For example, consider a 5-yr loan
of $120,000 at 12% with the first payment due in a month. When the
first payment is made, I would expect it to be $2000 in principal plus
$1200 in interest (1% of $120,000); so the total payment is $3200. The
remaining (new outstanding) balance is $118,000 (120000 - 2000). Do
you agree?

In other words, a formula/function that would give me payment that is always
a fixed payment of the principal plus intrests (and the intrests part gets
smaller and smaller as the principal is paid up).

For the loan structure I describe above, for any period, the
outstanding balance (before payment), Bal, is Loan * (1 - (N-1)/Nper),
where Loan is the initial finance advance (120000), N is the period
number (1, 2,..., 60), and Nper is the number of payment periods (60).

The principal portion, Prin, is the smaller of Bal and Loan / Nper.
Bal is potentially smaller only in the last period.

The interest portion, Intr, is Rate * Bal, where Rate is the period
rate (e.g. 1% = 12%/12).

The total payment, Paymt, is Prin + Intr.

In Excel terms:

A1 (Loan): 120000
A2 (Nper): =5*12
A3 (Annual rate): 12%
A4 (Rate): =A3/12
A5 (N): 1 or 2 or ... up to Nper
A6 (Bal): =A1 * (1 - (A5-1)/A2)
A7 (Prin): =min(A6, A1/A2)
A8 (Intr): =A4 * A6
A9 (Paymt): =A7 + A8

Constant loan payments vs. constant payments of principal
 

I assume you mean that you want the interest on the outstanding balance
before the payment for the period. For example, consider a 5-yr loan
of $120,000 at 12% with the first payment due in a month. When the
first payment is made, I would expect it to be $2000 in principal plus
$1200 in interest (1% of $120,000); so the total payment is $3200. The
remaining (new outstanding) balance is $118,000 (120000 - 2000). Do
you agree?

Yes, I agree. And the last payment in your example would be $2020 (the
remaining balance + intrest (1% of $2000). This is exactly what I was looking
for.
Thank you very much!