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.

 Calculate Loan Term
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Calculate Loan Term

#1
February 25th 10, 04:06 AM posted to microsoft.public.excel.worksheet.functions
 Joe User[_2_] external usenet poster Posts: 905
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.

 Thread Tools Display Modes Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 02:16 PM.