Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to calculate interest only on a short term loan? | Excel Worksheet Functions | |||
calculate loan term for excel 2003 | Excel Discussion (Misc queries) | |||
how to calculate average loan period of a 5 year loan | Excel Worksheet Functions | |||
how do i calculate age in term of years, months, days | Excel Worksheet Functions | |||
Calculate Term | Excel Worksheet Functions |