Solving for Amortization with PMT function
 

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 dont know what formula to use to return the amortization (in years). Can
anyone help?

Much appreciated!
George

Solving for Amortization with PMT function
 

GeorgeA" wrote:
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 dont 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" wrote in message
...
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 dont know what formula to use to return the amortization (in years).
Can
anyone help?

Much appreciated!
George

Solving for Amortization with PMT function
 

Excellent, that works! Thanks.

"JoeU2004" wrote:

GeorgeA" wrote:
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 dont 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" wrote in message
...
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 dont know what formula to use to return the amortization (in years).
Can
anyone help?

Much appreciated!
George

Solving for Amortization with PMT function
 

Errata/clarification....

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

"JoeU2004" wrote in message
...
GeorgeA" wrote:
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 dont 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" wrote in message
...
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 dont know what formula to use to return the amortization (in years).
Can
anyone help?

Much appreciated!
George