Check Out Our Shop
Results 1 to 16 of 16

Thread: Excel amortization schedule Fixed principal plus interest

  1. #1
    Join Date
    Jan 2003
    Location
    nh
    Posts
    8,221

    Excel amortization schedule Fixed principal plus interest

    Anyone have a Excel amortization schedule template that will let you do Fixed principal plus interest, that I could have?
    People should learn endurance; they should learn to endure the discomforts of heat and cold, hunger and thirst; they should learn to be patient when receiving abuse and scorn; for it is the practice of endurance that quenches the fire of worldly passions which is burning up their bodies.
    --Buddha

    *))
    ((*
    *))
    ((*


    www.skiclinics.com

  2. #2
    Join Date
    Apr 2006
    Location
    Tahoe
    Posts
    1,425
    Isn't there a wizard that will do this? I looked around and couldn't find anything, but I'm almost sure I've done it in the past. Just throwing it out there since you've had no replies.

  3. #3
    Join Date
    Feb 2007
    Location
    Hood River
    Posts
    677
    Use the PMT() function. Super easy.

  4. #4
    Join Date
    Mar 2006
    Location
    Missoula, MT
    Posts
    22,984
    ^^^ i don't think it's that one, but it's something similar. That sounds like a function that already exists, or at least part of something I did in a class for Fall semester. Like there's already a function for it or something.
    Are we talking an investment or a loan?
    No longer stuck.

    Quote Originally Posted by stuckathuntermtn View Post
    Just an uneducated guess.

  5. #5
    Join Date
    Dec 2004
    Location
    Amherst MA & Twin Mtn NH
    Posts
    4,723
    PMT = fixed principal + interest, not fixed principal

    Some quick help search & googling doesn't turn up a single function w/in Excel, but lots o' freebie templates are out there. Some are even downloadable within Excel. I'm using the version w/ the funky "ribbon" at the top, so the specific keystrokes might be different for you, but essentially go to New / Template and then search on amortization and/or other terms -- I haven't gone through all the options yet to see if any of them are exactly what you're looking for, but many variations seemed to be available.

  6. #6
    Join Date
    Jan 2003
    Location
    nh
    Posts
    8,221
    It's to look at bonds and loans for various projects. We have been using a program called TValue but it seems like we should be able to get this done in excel. We also need to see a amortization of the bond, or loan.
    People should learn endurance; they should learn to endure the discomforts of heat and cold, hunger and thirst; they should learn to be patient when receiving abuse and scorn; for it is the practice of endurance that quenches the fire of worldly passions which is burning up their bodies.
    --Buddha

    *))
    ((*
    *))
    ((*


    www.skiclinics.com

  7. #7
    Join Date
    Dec 2004
    Location
    Amherst MA & Twin Mtn NH
    Posts
    4,723
    Did your original post just mean a fixed sum of principal + interest?
    If so, lots of Excel templates for that.

  8. #8
    Join Date
    Jan 2003
    Location
    nh
    Posts
    8,221
    Yeah I want to be able to put in a fixed principal payment + interest = reoccurring (monthly, weekly, bi annual etc) payment.
    People should learn endurance; they should learn to endure the discomforts of heat and cold, hunger and thirst; they should learn to be patient when receiving abuse and scorn; for it is the practice of endurance that quenches the fire of worldly passions which is burning up their bodies.
    --Buddha

    *))
    ((*
    *))
    ((*


    www.skiclinics.com

  9. #9
    Join Date
    Aug 2008
    Location
    PNW
    Posts
    1,643
    Curious what kind of bonds/loans you are looking at?

    ime there aren't a lot of deals structured with a constant periodic principal pmt. Usually only happens for me when modifying a previously interest only loan to include some sort of amortization but that is still pretty few and far between.

    Edit: just re-read the thread. if you are indeed just looking for a template for a fixed payment (consisting of principal & interest components) then Jon S is correct. Just download one the hundreds available from MS, they're widely available.

  10. #10
    Join Date
    Jan 2003
    Location
    nh
    Posts
    8,221
    Quote Originally Posted by SeatownSlackey View Post
    Curious what kind of bonds/loans you are looking at?



    ime there aren't a lot of deals structured with a constant periodic principal pmt. Usually only happens for me when modifying a previously interest only loan to include some sort of amortization but that is still pretty few and far between.
    I work in IT (my boss is the finance director, so I have no clue how they do what they do and they have no clue how I do what I do it's messed up.) for a municipality. They do all sorts of strange shit.


    Quote Originally Posted by SeatownSlackey View Post
    Edit: just re-read the thread. if you are indeed just looking for a template for a fixed payment (consisting of principal & interest components) then Jon S is correct. Just download one the hundreds available from MS, they're widely available.
    It looks like from the example I saw (I'll look at it again) the payment is not fixed. Or maybe just the last payment was different anyway the payments were not the same throughout the life of the loan.
    People should learn endurance; they should learn to endure the discomforts of heat and cold, hunger and thirst; they should learn to be patient when receiving abuse and scorn; for it is the practice of endurance that quenches the fire of worldly passions which is burning up their bodies.
    --Buddha

    *))
    ((*
    *))
    ((*


    www.skiclinics.com

  11. #11
    Join Date
    Apr 2006
    Location
    Tahoe
    Posts
    1,425
    How complicated is the initial info, on one extreme it would be:
    $1000 bond bought for $1000, 5% annual interest compounded monthly for 10 years
    on the other extreme its something like:
    $1000 bonds bought for $900, paying 5% interest compounded monthly for 9.5 years, but you bought it mid-month so some of what you paid is accumulated interest.

    The first case is easy, the second case is not. Hopefully its number 1. Give us an example of an actual case.

  12. #12
    Join Date
    Jul 2005
    Posts
    3,230
    Quote Originally Posted by Tuckerman View Post
    I work in IT (my boss is the finance director, so I have no clue how they do what they do and they have no clue how I do what I do it's messed up.) for a municipality. They do all sorts of strange shit.



    It looks like from the example I saw (I'll look at it again) the payment is not fixed. Or maybe just the last payment was different anyway the payments were not the same throughout the life of the loan.
    interest and principal amortization schedules can vary, but here are the basics:

    1. fixed principal, fixed interest
    i.e. classic bond calcs. easy to model because the amounts are very stable.

    2. fixed total payment

    i.e. classic mortgage structure wherein interest is fixed & the remainder of the amount is subtracted as principal from the total payment. that new principal amount becomes the new amount for next interest calc/period.

    3. floating total amount, fixed principal
    is this what you need? if so, pm me an address and i'll send you a file. they are a little funky b/c you need to figure out how much the principal will reduce by each period, then whether the interest is fixed or floating. these can be messy due to changing variables vs. fixed variables & the amortization schedule needs to be tailored. loan calculators will handle it for you but i doubt you will understand them enough to reverse engineer into something for IT (which i assume is the point).

    4. other - bullets, other loans such as 5yr terms with 30yr amortizations, etc. (more typical in commercial real estate, bank loans to companies for long lived assets, equipment, etc, various bizarro leasing style modified loans that draw out principal amortizations.
    probably outside the scope of muni loans and your request.

    5. zeros, treasuries, I/O's, etc.

  13. #13
    Join Date
    Jan 2003
    Location
    nh
    Posts
    8,221
    Quote Originally Posted by mtnwriter View Post

    3. floating total amount, fixed principal
    is this what you need? if so, pm me an address and i'll send you a file. they are a little funky b/c you need to figure out how much the principal will reduce by each period, then whether the interest is fixed or floating. these can be messy due to changing variables vs. fixed variables & the amortization schedule needs to be tailored. loan calculators will handle it for you but i doubt you will understand them enough to reverse engineer into something for IT (which i assume is the point).
    This is it what a PITA.
    People should learn endurance; they should learn to endure the discomforts of heat and cold, hunger and thirst; they should learn to be patient when receiving abuse and scorn; for it is the practice of endurance that quenches the fire of worldly passions which is burning up their bodies.
    --Buddha

    *))
    ((*
    *))
    ((*


    www.skiclinics.com

  14. #14
    Join Date
    Jul 2005
    Posts
    3,230
    Quote Originally Posted by Tuckerman View Post
    This is it what a PITA.
    thought so, and yep, no real easy way to model & streamline - i.e. PITA.

    notional $10,000,000
    years 30
    pmts/year 4
    nper 120
    int 5%
    int/per 0.0125

    principal period interest prin pmt. total pmt.
    $10,000,000 1 $31,250.00 $83,333.33 $114,583.33
    $9,916,666.67 2 $30,989.58 $83,333.33 $114,322.92
    $9,833,333.33 3 $30,729.17 $83,333.33 $114,062.50
    $9,750,000 4 $30,468.75 $83,333.33 $113,802.08
    $9,666,666.67 5 $30,208.33 $83,333.33 $113,541.67
    $9,583,333.33 6 $29,947.92 $83,333.33 $113,281.25
    $9,500,000 7 $29,687.50 $83,333.33 $113,020.83
    $9,416,666.67 8 $29,427.08 $83,333.33 $112,760.42
    $9,333,333.33 9 $29,166.67 $83,333.33 $112,500
    $9,250,000.00 10 $28,906.25 $83,333.33 $112,239.58
    $9,166,666.67 11 $28,645.83 $83,333.33 $111,979.17
    $9,083,333.33 12 $28,385.42 $83,333.33 $111,718.75
    $9,000,000.00 13 $28,125.00 $83,333.33 $111,458.33
    $8,916,666.67 14 $27,864.58 $83,333.33 $111,197.92
    $8,833,333.33 15 $27,604.17 $83,333.33 $110,937.50
    $8,750,000.00 16 $27,343.75 $83,333.33 $110,677.08
    $8,666,666.67 17 $27,083.33 $83,333.33 $110,416.67
    $8,583,333.33 18 $26,822.92 $83,333.33 $110,156.25
    $8,500,000.00 19 $26,562.50 $83,333.33 $109,895.83
    $8,416,666.67 20 $26,302.08 $83,333.33 $109,635.42
    $8,333,333.33 21 $26,041.67 $83,333.33 $109,375
    $8,250,000.00 22 $25,781.25 $83,333.33 $109,114.58
    $8,166,666.67 23 $25,520.83 $83,333.33 $108,854.17
    $8,083,333.33 24 $25,260.42 $83,333.33 $108,593.75
    $8,000,000.00 25 $25,000.00 $83,333.33 $108,333.33
    $7,916,666.67 26 $24,739.58 $83,333.33 $108,072.92
    $7,833,333.33 27 $24,479.17 $83,333.33 $107,812.50
    $7,750,000.00 28 $24,218.75 $83,333.33 $107,552.08
    $7,666,666.67 29 $23,958.33 $83,333.33 $107,291.67
    $7,583,333.33 30 $23,697.92 $83,333.33 $107,031.25
    $7,500,000.00 31 $23,437.50 $83,333.33 $106,770.83
    $7,416,666.67 32 $23,177.08 $83,333.33 $106,510.42
    $7,333,333.33 33 $22,916.67 $83,333.33 $106,250
    $7,250,000.00 34 $22,656.25 $83,333.33 $105,989.58
    $7,166,666.67 35 $22,395.83 $83,333.33 $105,729.17
    $7,083,333.33 36 $22,135.42 $83,333.33 $105,468.75
    $7,000,000.00 37 $21,875.00 $83,333.33 $105,208.33
    $6,916,666.67 38 $21,614.58 $83,333.33 $104,947.92
    $6,833,333.33 39 $21,354.17 $83,333.33 $104,687.50
    $6,750,000.00 40 $21,093.75 $83,333.33 $104,427.08
    $6,666,666.67 41 $20,833.33 $83,333.33 $104,166.67
    $6,583,333.33 42 $20,572.92 $83,333.33 $103,906.25
    $6,500,000 43 $20,312.50 $83,333.33 $103,645.83
    $6,416,666.67 44 $20,052.08 $83,333.33 $103,385.42
    $6,333,333.33 45 $19,791.67 $83,333.33 $103,125
    $6,250,000 46 $19,531.25 $83,333.33 $102,864.58
    $6,166,666.67 47 $19,270.83 $83,333.33 $102,604.17
    $6,083,333.33 48 $19,010.42 $83,333.33 $102,343.75
    $6,000,000 49 $18,750.00 $83,333.33 $102,083.33
    $5,916,666.67 50 $18,489.58 $83,333.33 $101,822.92
    $5,833,333.33 51 $18,229.17 $83,333.33 $101,562.50
    $5,750,000 52 $17,968.75 $83,333.33 $101,302.08
    $5,666,666.67 53 $17,708.33 $83,333.33 $101,041.67
    $5,583,333.33 54 $17,447.92 $83,333.33 $100,781.25
    $5,500,000 55 $17,187.50 $83,333.33 $100,520.83
    $5,416,666.67 56 $16,927.08 $83,333.33 $100,260.42
    $5,333,333.33 57 $16,666.67 $83,333.33 $100,000
    $5,250,000 58 $16,406.25 $83,333.33 $99,739.58
    $5,166,666.67 59 $16,145.83 $83,333.33 $99,479.17
    $5,083,333.33 60 $15,885.42 $83,333.33 $99,218.75
    $5,000,000 61 $15,625.00 $83,333.33 $98,958.33
    $4,916,666.67 62 $15,364.58 $83,333.33 $98,697.92
    $4,833,333.33 63 $15,104.17 $83,333.33 $98,437.50
    $4,750,000 64 $14,843.75 $83,333.33 $98,177.08
    $4,666,666.67 65 $14,583.33 $83,333.33 $97,916.67
    $4,583,333.33 66 $14,322.92 $83,333.33 $97,656.25
    $4,500,000 67 $14,062.50 $83,333.33 $97,395.83
    $4,416,666.67 68 $13,802.08 $83,333.33 $97,135.42
    $4,333,333.33 69 $13,541.67 $83,333.33 $96,875
    $4,250,000 70 $13,281.25 $83,333.33 $96,614.58
    $4,166,666.67 71 $13,020.83 $83,333.33 $96,354.17
    $4,083,333.33 72 $12,760.42 $83,333.33 $96,093.75
    $4,000,000 73 $12,500.00 $83,333.33 $95,833.33
    $3,916,666.67 74 $12,239.58 $83,333.33 $95,572.92
    $3,833,333.33 75 $11,979.17 $83,333.33 $95,312.50
    $3,750,000 76 $11,718.75 $83,333.33 $95,052.08
    $3,666,666.67 77 $11,458.33 $83,333.33 $94,791.67
    $3,583,333.33 78 $11,197.92 $83,333.33 $94,531.25
    $3,500,000 79 $10,937.50 $83,333.33 $94,270.83
    $3,416,666.67 80 $10,677.08 $83,333.33 $94,010.42
    $3,333,333.33 81 $10,416.67 $83,333.33 $93,750
    $3,250,000 82 $10,156.25 $83,333.33 $93,489.58
    $3,166,666.67 83 $9,895.83 $83,333.33 $93,229.17
    $3,083,333.33 84 $9,635.42 $83,333.33 $92,968.75
    $3,000,000 85 $9,375.00 $83,333.33 $92,708.33
    $2,916,666.67 86 $9,114.58 $83,333.33 $92,447.92
    $2,833,333.33 87 $8,854.17 $83,333.33 $92,187.50
    $2,750,000 88 $8,593.75 $83,333.33 $91,927.08
    $2,666,666.67 89 $8,333.33 $83,333.33 $91,666.67
    $2,583,333.33 90 $8,072.92 $83,333.33 $91,406.25
    $2,500,000 91 $7,812.50 $83,333.33 $91,145.83
    $2,416,666.67 92 $7,552.08 $83,333.33 $90,885.42
    $2,333,333.33 93 $7,291.67 $83,333.33 $90,625
    $2,250,000 94 $7,031.25 $83,333.33 $90,364.58
    $2,166,666.67 95 $6,770.83 $83,333.33 $90,104.17
    $2,083,333.33 96 $6,510.42 $83,333.33 $89,843.75
    $2,000,000 97 $6,250.00 $83,333.33 $89,583.33
    $1,916,666.67 98 $5,989.58 $83,333.33 $89,322.92
    $1,833,333.33 99 $5,729.17 $83,333.33 $89,062.50
    $1,750,000 100 $5,468.75 $83,333.33 $88,802.08
    $1,666,666.67 101 $5,208.33 $83,333.33 $88,541.67
    $1,583,333.33 102 $4,947.92 $83,333.33 $88,281.25
    $1,500,000 103 $4,687.50 $83,333.33 $88,020.83
    $1,416,666.67 104 $4,427.08 $83,333.33 $87,760.42
    $1,333,333.33 105 $4,166.67 $83,333.33 $87,500
    $1,250,000 106 $3,906.25 $83,333.33 $87,239.58
    $1,166,666.67 107 $3,645.83 $83,333.33 $86,979.17
    $1,083,333.33 108 $3,385.42 $83,333.33 $86,718.75
    $1,000,000 109 $3,125.00 $83,333.33 $86,458.33
    $916,666.67 110 $2,864.58 $83,333.33 $86,197.92
    $833,333.33 111 $2,604.17 $83,333.33 $85,937.50
    $750,000.00 112 $2,343.75 $83,333.33 $85,677.08
    $666,666.67 113 $2,083.33 $83,333.33 $85,416.67
    $583,333.33 114 $1,822.92 $83,333.33 $85,156.25
    $500,000.00 115 $1,562.50 $83,333.33 $84,895.83
    $416,666.67 116 $1,302.08 $83,333.33 $84,635.42
    $333,333.33 117 $1,041.67 $83,333.33 $84,375
    $250,000.00 118 $781.25 $83,333.33 $84,114.58
    $166,666.67 119 $520.83 $83,333.33 $83,854.17
    $83,333.33 120 $260.42 $83,333.33 $83,593.75
    $0.00 121 $0.00 $83,333.33 $83,333.33

  15. #15
    Join Date
    Jul 2005
    Posts
    3,230
    where

    notional = $10,000,000
    years = 30
    pmts/year = 4
    nper (pmts/yr * years) = 120
    int(erest rate) = 5%
    int/per (5%/4) = 0.0125


    cell a1 "principal" = $10,000,000
    cell b1 "period" = 1 (to 120)
    cell c1 "interest" = "principal" (10,000,000) * 1/pmts./yr (1/4)* int/per (.0125)
    cell d1 "prin pmt." = original principal (10,000,000)/ nper (120)
    cell e1 "total pmt." = interest + prin pmt

  16. #16
    Join Date
    Jul 2005
    Posts
    3,230
    there are ways to "model" it with different software, excel, etc. but you will invariably be stuck with these core variables plus if the interest rate is floating, each interest amount each period will be unique and discreet.

    the only other wrinkle - and it can be another pain, is whether the principal payments are uniform or bullet or some odd variant. e.g. 10% first 10 yrs. 20% next 10 years, 70% last 10, etc, etc.

    make sense?

Posting Permissions

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