Anyone have a Excel amortization schedule template that will let you do Fixed principal plus interest, that I could have?
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
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.
Use the PMT() function. Super easy.
^^^ 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?
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.
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
Did your original post just mean a fixed sum of principal + interest?
If so, lots of Excel templates for that.
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
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.
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.
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
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.
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.
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
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
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
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?
Bookmarks