View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Joe User[_2_] Joe User[_2_] is offline
external usenet poster
 
Posts: 905
Default Calculate Loan Term

"JD McLeod" wrote:
I am trying to determine how the payment amount was calculated
(for example - it's a 3 year loan with a 15 year amortiztaion payment)


You might not be able to determine that because the payment can be chosen
arbitrarily.


I want to know how long it would take to pay off the loan at the
payment amount currently being charged, or in other words, the
original amortization period.


Ostensibly, that is easy: just use the NPER function.

Example: If the loan balance is $50,000 with monthly payments of $275 and
an annual interest rate of 3.25%, the fully-amortized term of the loan is:

=ROUND(NPER(3.25%/12, -275, 50,000), 0)

Note: The last payment will likely be different from $275 -- higher or
lower, depending on how the NPER result is rounded.

However....


I am trying to determine the loan term for a loan where I know
the following variables:
Loan date = 10/31/2008
Maturity date = 09/30/2011
Balance = $50,000
Payment amount = $275
Baloon at end = $20,000
Annual IR = 3.25%


Those terms do not make sense. Assuming monthly payments, the balloon
payment should be:

=-FV(3.25%/12, 35, -275, 50000)

which is about $44,883, too much different from $20,000 to be due to any
kind of rounding error that I can think of.

Those terms would make sense if the payment is made about every 9 days(!).
Even then, the balloon payment would be about $20,679.

What is the payment frequency?

(Note: There is a difference between "every 2 weeks" and "semi-monthly".
Be precise.)

I am suspicious of the term of the loan. 10/31/2008 to 9/30/2011 is 35
months. I suspect it should be 36 months; so either the "loan date" should
be 9/30/2008, or the "maturity date" should be 10/31/2011.

Also, the "balance" might be the current balance, not the amount of the
initial loan on the "loan date".

Ring any bells?

It would be helpful if you posted "exact" numbers: dollar amounts to the
dollar, if not to the cent, and interest rates to the actual precision (4
decimal places of the percentage is very common).


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

"JD McLeod" wrote in message
...
I am trying to determine the loan term for a loan where I know the following
variables:
Loan date = 10/31/2008
Maturity date = 09/30/2011
Balance = $50,000
Payment amount = $275
Baloon at end = $20,000
Annual IR = 3.25%
I have several loans I am looking at where the payment amount is not enough
to pay off the loan at maturity, in other words, there will be a balance due
at maturity (balloon). I want to know how long it would take to pay off the
loan at the payment amount currently being charged, or in other words, the
original amortization period. I am trying to determine how the payment
amount was calculated (for example - it's a 3 year loan with a 15 year
amortiztaion payment)
Thanks.