Results 1 to 3 of 3

loan amortization table with uneven payments

This is a discussion on loan amortization table with uneven payments within the Excel Questions forums, part of the Question Forums category; I need some help. Does anyone know how to create an amortization table where the interest and principal payments are ...

  1. #1
    New Member
    Join Date
    Apr 2006
    Location
    KANSAS
    Posts
    16

    Default loan amortization table with uneven payments

    I need some help. Does anyone know how to create an amortization table where the interest and principal payments are based on number of units shipped and the shipment schedule instead of even payments for a set number of months.

  2. #2
    New Member
    Join Date
    Feb 2005
    Posts
    37

    Default

    shouldn't be a problem. Your payments will be variable, based on the qty shipped. You calc the principal reduction as a % of the total volume as you can see in the formula bar below.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    1
    ********
    2
    ***Loan*Amount10,000****
    3
    ***Rate8%***
    4
    ***Total*Units*to*Ship50,000****
    5
    ********
    6
    ********
    7
    ********
    8
    ********
    9
    MonthUnits*ShippedBeginning*BalInterestPrincipalTotal*PaymentEnd*Balance*
    10
    11,000.00*10,000.00*66.67*200.00*266.67*9,800.00**
    11
    22,000.00*9,800.00*65.33*400.00*465.33*9,400.00**
    12
    33,000.00*9,400.00*62.67*600.00*662.67*8,800.00**
    13
    410,000.00*8,800.00*58.67*2,000.00*2,058.67*6,800.00**
    14
    512,000.00*6,800.00*45.33*2,400.00*2,445.33*4,400.00**
    15
    615,000.00*4,400.00*29.33*3,000.00*3,029.33*1,400.00**
    16
    74,000.00*1,400.00*9.33*800.00*809.33*600.00**
    17
    82,000.00*600.00*4.00*400.00*404.00*200.00**
    18
    9500.00*200.00*1.33*100.00*101.33*100.00**
    19
    10500.00*100.00*0.67*100.00*100.67*0.00**
    20
    ********
    21
    *50000*343.33*10,000.00*10,343.33***
    22
    ********
    23
    ********
    24
    ********
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,983

    Default

    Something like this?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book8___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    1
    Rate7%
    2
    Loan10000.0031/12/05
    3
    DatePrincipalInterestTotalBalanceShipments
    4
    31/12/05 10,000.00
    5
    30/06/06-1,600.00-347.12-1,947.128,400.00400
    6
    31/12/06-2,000.00-296.42-2,296.426,400.00500
    7
    30/06/07-1,200.00-222.16-1,422.165,200.00300
    8
    31/12/07-2,400.00-183.50-2,583.502,800.00600
    9
    30/06/08-2,000.00-97.73-2,097.73800.00500
    10
    31/12/08-800.00-28.23-828.230.00200
    11
    -10,000.00-1,175.16-11,175.16 2,500
    Sheet1

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com