Constant loan payments vs. constant payments of principal

[lalli945]

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

 

[Martin Fishlock]

I think that you are looking for ipmt.

 

[joeu2004]

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