Loan Amortization Table template

You cant make a simple amort table yourself?

We have a genius on a rampage.

Some advice for your general understanding of how the world works - when asking for something, don't be sarcastic/condescending.

Best of luck.

HA

Here's a very basic 5 year amort schedule with I/O option. Fill out loan amount, interest rate, amort, i/o period. Should be able to copy and paste into excel.

Loan Amount 100000
Interest Rate 0.05
Amortization (years) 25
I/O Period (years) 2
I/O Payment (monthly) =IF(C4>0,C2/12C1,"")
P&I Payment (monthly) =IF(ISERROR(PMT(C2/12,C312,-C1)),"",PMT(C2/12,C3*12,-C1))

Month Interest Principal Balance 1 =$C$2/12C1 =IF(A9=$C$412,0,$C$6-B9) =C1-C9 =A9+1 =$C$2/12D9 =IF(A10=$C$412,0,$C$6-B10) =D9-C10 =A10+1 =$C$2/12D10 =IF(A11=$C$412,0,$C$6-B11) =D10-C11 =A11+1 =$C$2/12D11 =IF(A12=$C$412,0,$C$6-B12) =D11-C12 =A12+1 =$C$2/12D12 =IF(A13=$C$412,0,$C$6-B13) =D12-C13 =A13+1 =$C$2/12D13 =IF(A14=$C$412,0,$C$6-B14) =D13-C14 =A14+1 =$C$2/12D14 =IF(A15=$C$412,0,$C$6-B15) =D14-C15 =A15+1 =$C$2/12D15 =IF(A16=$C$412,0,$C$6-B16) =D15-C16 =A16+1 =$C$2/12D16 =IF(A17=$C$412,0,$C$6-B17) =D16-C17 =A17+1 =$C$2/12D17 =IF(A18=$C$412,0,$C$6-B18) =D17-C18 =A18+1 =$C$2/12D18 =IF(A19=$C$412,0,$C$6-B19) =D18-C19 =A19+1 =$C$2/12D19 =IF(A20=$C$412,0,$C$6-B20) =D19-C20 =A20+1 =$C$2/12D20 =IF(A21=$C$412,0,$C$6-B21) =D20-C21 =A21+1 =$C$2/12D21 =IF(A22=$C$412,0,$C$6-B22) =D21-C22 =A22+1 =$C$2/12D22 =IF(A23=$C$412,0,$C$6-B23) =D22-C23 =A23+1 =$C$2/12D23 =IF(A24=$C$412,0,$C$6-B24) =D23-C24 =A24+1 =$C$2/12D24 =IF(A25=$C$412,0,$C$6-B25) =D24-C25 =A25+1 =$C$2/12D25 =IF(A26=$C$412,0,$C$6-B26) =D25-C26 =A26+1 =$C$2/12D26 =IF(A27=$C$412,0,$C$6-B27) =D26-C27 =A27+1 =$C$2/12D27 =IF(A28=$C$412,0,$C$6-B28) =D27-C28 =A28+1 =$C$2/12D28 =IF(A29=$C$412,0,$C$6-B29) =D28-C29 =A29+1 =$C$2/12D29 =IF(A30=$C$412,0,$C$6-B30) =D29-C30 =A30+1 =$C$2/12D30 =IF(A31=$C$412,0,$C$6-B31) =D30-C31 =A31+1 =$C$2/12D31 =IF(A32=$C$412,0,$C$6-B32) =D31-C32 =A32+1 =$C$2/12D32 =IF(A33=$C$412,0,$C$6-B33) =D32-C33 =A33+1 =$C$2/12D33 =IF(A34=$C$412,0,$C$6-B34) =D33-C34 =A34+1 =$C$2/12D34 =IF(A35=$C$412,0,$C$6-B35) =D34-C35 =A35+1 =$C$2/12D35 =IF(A36=$C$412,0,$C$6-B36) =D35-C36 =A36+1 =$C$2/12D36 =IF(A37=$C$412,0,$C$6-B37) =D36-C37 =A37+1 =$C$2/12D37 =IF(A38=$C$412,0,$C$6-B38) =D37-C38 =A38+1 =$C$2/12D38 =IF(A39=$C$412,0,$C$6-B39) =D38-C39 =A39+1 =$C$2/12D39 =IF(A40=$C$412,0,$C$6-B40) =D39-C40 =A40+1 =$C$2/12D40 =IF(A41=$C$412,0,$C$6-B41) =D40-C41 =A41+1 =$C$2/12D41 =IF(A42=$C$412,0,$C$6-B42) =D41-C42 =A42+1 =$C$2/12D42 =IF(A43=$C$412,0,$C$6-B43) =D42-C43 =A43+1 =$C$2/12D43 =IF(A44=$C$412,0,$C$6-B44) =D43-C44 =A44+1 =$C$2/12D44 =IF(A45=$C$412,0,$C$6-B45) =D44-C45 =A45+1 =$C$2/12D45 =IF(A46=$C$412,0,$C$6-B46) =D45-C46 =A46+1 =$C$2/12D46 =IF(A47=$C$412,0,$C$6-B47) =D46-C47 =A47+1 =$C$2/12D47 =IF(A48=$C$412,0,$C$6-B48) =D47-C48 =A48+1 =$C$2/12D48 =IF(A49=$C$412,0,$C$6-B49) =D48-C49 =A49+1 =$C$2/12D49 =IF(A50=$C$412,0,$C$6-B50) =D49-C50 =A50+1 =$C$2/12D50 =IF(A51=$C$412,0,$C$6-B51) =D50-C51 =A51+1 =$C$2/12D51 =IF(A52=$C$412,0,$C$6-B52) =D51-C52 =A52+1 =$C$2/12D52 =IF(A53=$C$412,0,$C$6-B53) =D52-C53 =A53+1 =$C$2/12D53 =IF(A54=$C$412,0,$C$6-B54) =D53-C54 =A54+1 =$C$2/12D54 =IF(A55=$C$412,0,$C$6-B55) =D54-C55 =A55+1 =$C$2/12D55 =IF(A56=$C$412,0,$C$6-B56) =D55-C56 =A56+1 =$C$2/12D56 =IF(A57=$C$412,0,$C$6-B57) =D56-C57 =A57+1 =$C$2/12D57 =IF(A58=$C$412,0,$C$6-B58) =D57-C58 =A58+1 =$C$2/12D58 =IF(A59=$C$412,0,$C$6-B59) =D58-C59 =A59+1 =$C$2/12D59 =IF(A60=$C$412,0,$C$6-B60) =D59-C60 =A60+1 =$C$2/12D60 =IF(A61=$C$412,0,$C$6-B61) =D60-C61 =A61+1 =$C$2/12D61 =IF(A62=$C$412,0,$C$6-B62) =D61-C62 =A62+1 =$C$2/12D62 =IF(A63=$C$412,0,$C$6-B63) =D62-C63 =A63+1 =$C$2/12D63 =IF(A64=$C$412,0,$C$6-B64) =D63-C64 =A64+1 =$C$2/12D64 =IF(A65=$C$412,0,$C$6-B65) =D64-C65 =A65+1 =$C$2/12D65 =IF(A66=$C$412,0,$C$6-B66) =D65-C66 =A66+1 =$C$2/12D66 =IF(A67=$C$412,0,$C$6-B67) =D66-C67 =A67+1 =$C$2/12D67 =IF(A68=$C$412,0,$C$6-B68) =D67-C68

curious

Don't you amortize the loan principal over 23 years after the I/O period is over, resulting in higher monthly payments as opposed to if the loan was a CPM?

If so then I would tweak the 'P&I Payment (monthly)" formula to: =IF(C4>0,(PMT(C2/12,(C3-C4)12,-C1)),PMT(C2/12,C312,-C1))

If not, disregard :D

The amortization period is usually stated as how many years of amort, starting after the I/O period. SO it'd be interest-only for 2 years, followed by a 25 year amort, not 23 years. This is how my bank always states it anyway, but maybe others do it differently?

It would be a "unique" loan that stated 25 yr amort with 2 yrs IO then proceeded to amort on a 23 yr schedule after the IO period.

Thanks for clarifying RE Dev and SHB. With the provided excel, interest is being calculated on a "30/360" basis. How would you alter the interest formula if the interest rate calculation had to be on a "actual/360" basis?

I've tried: Beginning_BalanceInterest_RateYEARFRAC(4/1/2009,5/1/2009,2) but my Ending Balance down the line doesn't tie out.

To do an exact (probably far more exact than what you need) actual/360 you would need to change the table up and have inputs for loan start date and loan end date, as you would need ot know if month 1 was a 30 or 31 day month. From there you can use the ability of excel to count days, etc, etc. I havn't done it but I'm not really inclined to as well.

What would save you a ton of time and effort is to do the following to your interest rate: Rate*(365/360), or 366 on leap years, while its not perfect it will approximate the actual interest rate.

=(D10-C10)/360$F$5E10 is the interest payment from an amort table I put together when we want actual interest. C is first day of month, D is first day of next month (I use edate), F5 is the interest rate and E is the outstanding principal. Basically you're taking the days in that month/360 * interest rate.