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!

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.

sounds good, thanks!

Thanks for the rating and the tip.