Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 25th 10, 04:06 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to calculate interest only on a short term loan? Excel2007Help Excel Worksheet Functions 2 December 1st 09 07:48 PM
calculate loan term for excel 2003 CHARI Excel Discussion (Misc queries) 2 March 21st 09 05:24 PM
how to calculate average loan period of a 5 year loan Jayant Excel Worksheet Functions 2 March 25th 08 11:47 PM
how do i calculate age in term of years, months, days shan Excel Worksheet Functions 1 March 15th 06 10:21 AM
Calculate Term Anita Excel Worksheet Functions 3 December 21st 05 04:27 AM


All times are GMT +1. The time now is 08:06 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017