Google Answers Logo
View Question
 
Q: Term Loan with Amortization Period ( Answered 5 out of 5 stars,   1 Comment )
Question  
Subject: Term Loan with Amortization Period
Category: Business and Money > Finance
Asked by: burnt_toast-ga
List Price: $3.00
Posted: 04 Oct 2004 14:01 PDT
Expires: 03 Nov 2004 13:01 PST
Question ID: 410248
I am starting a business and wish to borrow $1,000,000.
I was offered a 10 year term loan with a 50 year amortization period.
I will be making monthly payments.
My quoted interest rate is 8%.

Does this mean that they expect me to pay off my loan within 10 years
(120 months) or 50 years (600 months)?

An excel amortization table would be great.

Request for Question Clarification by efn-ga on 04 Oct 2004 20:38 PDT
We can't give you an Excel file here.  Would you like a list showing
the balance after each payment, or instructions about how to make an
amortization table in Excel?  Or something else, such as a reference
to a loan calculator on the Web?

Clarification of Question by burnt_toast-ga on 04 Oct 2004 22:03 PDT
Sure, instructions and maybe the first three lines would be great, thanks!
Answer  
Subject: Re: Term Loan with Amortization Period
Answered By: efn-ga on 05 Oct 2004 00:43 PDT
Rated:5 out of 5 stars
 
Hi burnt_toast,

They expect you to pay off the loan in ten years (120 months).

This is what's called a "balloon loan."  The fifty-year amortization
period means that the payments are set as if you were taking fifty
years to pay it off, but the ten-year term means that it must be paid
in ten years.  The fifty-year-based payments are not enough to pay off
the whole loan in ten years, so at the end of the term, you are
expected to pay a big balloon payment to pay off the remaining
balance.

There are a variety of ways to get an amortization table in an Excel
spreadsheet.  I'll describe a couple.

Microsoft has slick amortization table spreadsheet templates you can
download.  One that will work for you is the loan amortization
schedule template at:

http://office.microsoft.com/en-us/templates/TC010197771033.aspx

This template as you can download it is not useful for your purpose,
because it is limited to amortizing for a maximum of 30 years.  But it
is easy to tweak it so you can use it.  Just select the cell labeled
"Loan Period in Years," which is D8.  Then open the Data menu, select
Validation, make sure the Settings tab is selected, change the Maximum
field to 50, and click OK.  Then you will be able to enter a period of
50 years.  The spreadsheet will still only fill in thirty years of
data, but that's OK, since you are only interested in the first ten
years.  The principal balance after payment 120 tells you what the
final balloon payment will be.

I filled in your values and the spreadsheet said you would pay
$6,792.74 per month, with a balloon payment of the remaining balance
of $792,064.02 at the end of the term.

The template has a ten-column format that won't fit in this web page
very well, so I have edited some data from the first three lines, so
you can get an idea of how it looks.  This is not exactly what you
would see in the spreadsheet.

PmtNo. Beginning Balance Scheduled Payment Principal Interest  Ending Balance
 
1      $1,000,000.00     $6,792.74         $126.08   $6,666.67 $999,873.92
2         999,873.92      6,792.74          126.92    6,665.83  999,747.01
3         999,747.01      6,792.74          127.76    6,664.98  999,619.25

If you would like to do more of the work yourself, Dick Kusleika's
weblog has straightforward instructions for making a simple
amortization table.

http://www.dicks-blog.com/excel/2004/06/amortization_ta.html


If you need any more information about any of this, please ask for a clarification.

--efn

Clarification of Answer by efn-ga on 05 Oct 2004 01:00 PDT
I should note that the template requires Excel 97 or later and my
instructions for changing the loan period maximum are based on Excel
2000.  If they don't work with your version and you can't figure out
how to change the maximum, tell me what version you have and I will
try to dig up the corresponding procedure.
burnt_toast-ga rated this answer:5 out of 5 stars and gave an additional tip of: $1.00
sounds good, thanks!

Comments  
Subject: Re: Term Loan with Amortization Period
From: efn-ga on 12 Oct 2004 19:32 PDT
 
Thanks for the rating and the tip.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy