Solving for Amortization with PMT function

[GeorgeA]

Hello,
I need to solve for Amortization and think I should be using the PMT
function “PMT(rate,nper,pv,fv,type)â€
My data gives me the desired payment, rate, pv, fv and type.
I don’t know what formula to use to return the amortization (in years). Can
anyone help?

Much appreciated!
George

 

[JoeU2004]

I need to solve for Amortization

"Solve for amortization" is a meaningless phrase. Why don't you say in
normal, non-jargon English what you really need?

think I should be using the PMT function “PMT(rate,nper,pv,fv,type)â€
My data gives me the desired payment, rate, pv, fv and type.

If you already have the payment, don't use PMT.

I don’t know what formula to use to return the amortization (in years).

So I think you want to determine the number of years to amortize the loan or
other financial arrangement.

Use NPER. For example, for a loan 100,000 at 5% with a residual (balloon
payment) of 20,000 and monthly payments of 1000:

=ROUNDUP(NPER(5%/12,1000,-100000,20000,type),0)

Note that that returns the number of monthly payments; divide by 12 for the
number of years.

ROUNDUP ensures an integral number of payments. The last payment (including
the residual) can be computed by:

=ROUND(FV(5%/12,months-1,1000,-100000,20000,type)*(1+5%/12),2)

where "months" is the result of the NPER formula above, rounded-up as
indicated.

Note: The last formula assumes type=0. I have never given it much thought
for type=1, which occurs rarely, if ever.


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

 

[GeorgeA]

Excellent, that works! Thanks.

"Solve for amortization" is a meaningless phrase. Why don't you say in
normal, non-jargon English what you really need?



If you already have the payment, don't use PMT.



So I think you want to determine the number of years to amortize the loan or
other financial arrangement.

Use NPER. For example, for a loan 100,000 at 5% with a residual (balloon
payment) of 20,000 and monthly payments of 1000:

=ROUNDUP(NPER(5%/12,1000,-100000,20000,type),0)

Note that that returns the number of monthly payments; divide by 12 for the
number of years.

ROUNDUP ensures an integral number of payments. The last payment (including
the residual) can be computed by:

=ROUND(FV(5%/12,months-1,1000,-100000,20000,type)*(1+5%/12),2)

where "months" is the result of the NPER formula above, rounded-up as
indicated.

Note: The last formula assumes type=0. I have never given it much thought
for type=1, which occurs rarely, if ever.


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

 

[JoeU2004]

Errata/clarification....

For example, for a loan 100,000 at 5% with a residual (balloon payment) of
20,000 and monthly payments of 1000
[....]
The last payment (including the residual) can be computed by:
=ROUND(FV(5%/12,months-1,1000,-100000,20000,type)*(1+5%/12),2)

For type=0 (typical), the last payment __not_including__ the residual
(balloon payment) is:

=ROUND(FV(5%/12,months-1,1000,-100000,type)*(1+5%/12) - 20000, 2)

The last formula assumes type=0. I have never given it much thought for
type=1, which occurs rarely, if ever.

The more general formula (works for both type=0 and type=1) is:

=ROUND((FV(5%/12,months-1,1000,-100000,type)*(1+5%/12) - 20000) /
(1+type*5%/12), 2)

But another way to do this is simply:

=ROUND(PMT(5%/12,1,-FV(5%/12,months-1,1000,-100000,type),20000,type), 2)


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