A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Calculate Loan Term



 
 
Thread Tools Display Modes
  #1  
Old February 25th 10, 04:06 AM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
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.

Ads
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 11:42 AM.


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